Grant select on all synonyms of schema to another schema in Oracle 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?
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&gt; spool grant_statements.sql
SQL&gt; set pagesize 0
SQL&gt; set feedback off
SQL&gt; set linesize 300
SQL&gt; set trimout on
SQL&gt; set trimspool on
SQL&gt; select 'grant select on Admin.' || object_name || ' to User;' from user_objects where object_type in('TABLE','SYNONYM')
/
SQL&gt; 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;