Oracle: grant SELECT on the tables and synonyms of a schema
Generate the GRANT SELECT statements for all tables and synonyms of the connected schema, with a SQL*Plus script or a PL/SQL block.
To grant SELECT on all tables and synonyms of the connected schema to another user, generate the GRANT statements from USER_OBJECTS, then run them. Two methods: a spooled SQL*Plus script, or a PL/SQL block.
Method 1: SQL*Plus script
The script writes the GRANT statements to the file grant_statements.sql, which is then run.
spool grant_statements.sql
set pagesize 0
set feedback off
set linesize 300
set trimout on
set trimspool on
select 'grant select on owner_schema.' || object_name || ' to grantee_user;'
from user_objects
where object_type in('TABLE','SYNONYM')
/
spool off
Method 2: PL/SQL block
The block runs the GRANT statements directly, without an intermediate file.
BEGIN
FOR s IN (SELECT *
FROM user_objects where object_type in('TABLE','SYNONYM'))
LOOP
EXECUTE IMMEDIATE 'grant select on owner_schema.' || s.object_name || ' to grantee_user';
END LOOP;
END;