-- Please give the User Name in v_user variable
declare
cursor cur_grants(p_user varchar2)
is
select 'GRANT '
|| decode(db.object_type,'TABLE','SELECT',
'VIEW','SELECT',
'EXECUTE')
|| ' ON '
|| decode(db.owner,'PUBLIC','',db.owner || '.')
|| '"'
|| db.object_name
|| '"'
|| ' TO '||p_user sql_stmt
from all_objects db
where db.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE', 'VIEW','FUNCTION')
union
select 'GRANT '
|| decode (ao2.object_type, 'TABLE', 'SELECT',
'VIEW', 'SELECT',
'EXECUTE')
|| ' ON '
|| decode(ao.owner,'PUBLIC','',ao.owner || '.')
|| '"'
|| ao.object_name
|| '"'
|| ' TO '||p_user sql_stmt
from all_objects ao
, all_objects ao2
, dba_synonyms ds
where ao.object_type = 'SYNONYM'
and ao.object_name = ds.synonym_name
and ao2.object_name = ds.table_name
and ao2.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE', 'VIEW','FUNCTION')
;
v_user varchar2(20) := 'USER_NAME';
begin
for i in cur_grants(v_user) loop
execute immediate i.sql_stmt ;
dbms_output.put_line('Command : '||i.sql_stmt);
end loop;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
end;
/
declare
cursor cur_grants(p_user varchar2)
is
select 'GRANT '
|| decode(db.object_type,'TABLE','SELECT',
'VIEW','SELECT',
'EXECUTE')
|| ' ON '
|| decode(db.owner,'PUBLIC','',db.owner || '.')
|| '"'
|| db.object_name
|| '"'
|| ' TO '||p_user sql_stmt
from all_objects db
where db.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE', 'VIEW','FUNCTION')
union
select 'GRANT '
|| decode (ao2.object_type, 'TABLE', 'SELECT',
'VIEW', 'SELECT',
'EXECUTE')
|| ' ON '
|| decode(ao.owner,'PUBLIC','',ao.owner || '.')
|| '"'
|| ao.object_name
|| '"'
|| ' TO '||p_user sql_stmt
from all_objects ao
, all_objects ao2
, dba_synonyms ds
where ao.object_type = 'SYNONYM'
and ao.object_name = ds.synonym_name
and ao2.object_name = ds.table_name
and ao2.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE', 'VIEW','FUNCTION')
;
v_user varchar2(20) := 'USER_NAME';
begin
for i in cur_grants(v_user) loop
execute immediate i.sql_stmt ;
dbms_output.put_line('Command : '||i.sql_stmt);
end loop;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
end;
/
No comments:
Post a Comment