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