2

I need to write a query for granting the select privilege to all synonyms and tables of one schema to another schema in Oracle.

Example:

Admin schema has all the tables. I want the User schema to access all the tables owned by Admin. So I want to grant select privilege to User. How can I write a query to accomplish this task?

1

Here the script to generate grant select on all the tables and synonyms.

select 'grant select on Admin.' || object_name || ' to User;' 
from user_objects 
where object_type in('TABLE','SYNONYM');

Then you have to create a script to run these grant statements at once or you can use PL/SQL as well. Type the following in the SQL prompt.

SQL> spool grant_statements.sql
SQL> set pagesize 0
SQL> set feedback off
SQL> set linesize 300
SQL> set trimout on
SQL> set trimspool on
SQL> select 'grant select on Admin.' || object_name || ' to User;' from user_objects where object_type in('TABLE','SYNONYM')
/
SQL> spool off

And you have got the script file you can run it.
OR
You can run the following PL/SQL block (Run as admin user).

BEGIN
  FOR s IN (SELECT *
              FROM user_objects where object_type in('TABLE','SYNONYM'))
  LOOP
    EXECUTE IMMEDIATE  'grant select on admin.' || s.object_name || ' to user';
  END LOOP;
END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.