LLI Wiki — My Digital Knowledge Base

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;
Mastodon LLIMastodon SocialFosstodon