set echo off feedback off verify off pagesize 0 linesize 120
define v_grantee = &1
define v_grant_dml_command_file = .loggrant_dml_&v_grantee..sql
define v_grant_dml_log_file = .loggrant_dml_&v_grantee..log
spool &v_grant_dml_command_file.
prompt spool &v_grant_dml_log_file.
prompt set echo on feedback on
prompt show user
select
'grant select,insert,update,delete on ' || t.table_name || ' to &v_grantee;'
from user_tables t
where not exists
(select null
from user_tab_privs p
where p.owner = user
and p.table_name = t.table_name
and p.grantee = upper('&v_grantee'))
and user != upper('&v_grantee')
order by t.table_name
/
select
'grant select on ' || v.view_name || ' to &v_grantee;'
from user_views v
where not exists
(select null
from user_tab_privs p
where p.owner = user
and p.table_name = v.view_name
and p.grantee = upper('&v_grantee'))
and user != upper('&v_grantee')
order by v.view_name
/
select
'grant select on ' || s.sequence_name || ' to &v_grantee;'
from user_sequences s
where not exists
(select null
from user_tab_privs p
where p.owner = user
and p.table_name = s.sequence_name
and p.grantee = upper('&v_grantee'))
and user != upper('&v_grantee')
order by s.sequence_name
/
select
'grant execute on ' || o.object_name || ' to &v_grantee;'
from user_objects o
where object_type in ('PACKAGE')
and not exists
(select null
from user_tab_privs p
where p.owner = user
and p.table_name = o.object_name
and p.grantee = upper('&v_grantee'))
and user != upper('&v_grantee')
order by o.object_name
/
prompt set echo off feedback off
prompt spool off
spool off
@&v_grant_dml_command_file.