Saturday, 18 January 2025

Synonyms without a prefix or accessing the object without prefix

 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
set pages 0

-- Query for tables (views) in the specified schema
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 all_tables  -- Change from user_views to all_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 all_views  -- Change from dba_views to all_views
where owner = upper('&schema');

spool off;

No comments:

Post a Comment