Script to Grant Privileges and Create Synonyms

Extending the previous post Granting privileges on all or multiple objects to user/role , here is another script.

Often we have a requirement to create read only accounts for a schema. Typically these accounts are named _QUERY. The script when executed will prompt for the user name of the read only account, and the schema name where read privileges on the objects to be granted. It produces output to the screen and writes to file named tmpgrants.sql. Execute the tmpgrants.sql to grant SELECT privilege and to create a synonym under the read only account, so that the query account need not worry about qualifying the table with schema name.

First create the read only user account using similar syntax as below…

create user tdmaa_query identified by tdm123q default tablespace users;
grant create session to tdmaa_query;

Be sure to save all the below lines to a file and run the file in SQL*Plus… Executing this script will produce a script file named tmpgrants.sql. Run that script to grant privileges and create synonyms.

set pages 0 lines 200 trims on verify off feedback off


accept grants_to  prompt ‘Enter user to grant privileges: ‘
accept schema     prompt ‘Enter schema on which to grant: ‘


spool tmpgrants.sql


select ‘grant select on ‘||owner||’.’||table_name ||’ to &grants_to;’, chr(10),
       ‘create synonym &grants_to..’||table_name ||’ for ‘||owner||’.’||table_name||’;’, chr(10)
from  dba_tables
where owner = upper(‘&schema’)
union all
select ‘grant select on ‘||owner||’.’||view_name ||’ to &grants_to;’, chr(10),
       ‘create synonym &grants_to..’||view_name ||’ for ‘||owner||’.’||view_name||’;’, chr(10)
from  dba_views
where owner = upper(‘&schema’)
;


spool off


set pages 99 lines 80 verify on feedback on


prompt “Run tmpgrants.sql if you are satistified with the script…”

Enjoy!