SQL & PL/SQL

Hi all,

I can grant select access on tables of a schema (SAY USER1) to another (SAY USER2) by granting it to a role and in turn grant that role to another schema as below:

FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <<role1>>';
END LOOP;

Grant role1 to user2;

but my doubt is that suppose i am creating another table say 'TEMP_TAB' in USER1 schema after executing the above block, shall user2 will be able to access TEMP_TAB table. My guess is definitely not. If i am correct, i want a way to grant select on a table in user1 schema immediately as and when it is created to user2.

please suggest a solution.

Thanks & Regards,
Vipin Kumar Rai