Synonyms and Grant Script Creation Steps

Synonyms and Grant Script Creation Steps 

 -- Run Dynamic SQL to create synonym script

SET HEA OFF;

SET PAGESIZE 0;

SET LINES 150;

SPOOL SYNONYM.SQL

select 'create public synonym '||object_name||' for '||owner||'.'||object_name||';'

from sys.dba_objects where owner = 'SYSADM' and object_type in ('TABLE', 'VIEW');

SPOOL OFF;

-- Run Dynamic SQL to create grant scripts

SET HEA OFF;

SET PAGESIZE 0;

SET LINES 150

SPOOL GRANT.SQL

select 'grant select, update, insert, delete on '||object_name||' to VOLT_SUID;'

from sys.dba_objects where owner = 'SYSADM' and object_type in ('TABLE', 'VIEW');

SPOOL OFF;

prompt

prompt Manually run synonym script 

prompt Enable spool to generate log

prompt @SYNONYM.SQL

prompt

prompt Manually run grant scripts

prompt Enable spool to generate log

prompt @GRANT.SQL