Sometimes we have had request that grant to new or existing user privileges which already another user has. Example User2 is new database user and we have to grant him privilege(s) User1 table(s). To do it, we can use some methods, below is optimal solution. This PLSQL block can use DBA, USER1 or user which has USER1 tables WITH ADMIN OPTION rights.
begin
for cTab in (select * from dba_tables where owner='USER1') loop
execute immediate 'grant select on '||cTab.owner||'.'||cTab.table_name || ' to ' || 'USER2';
dbms_output.put_line(cTab.owner||'.'||cTab.table_name || ' granted to '||'USER2');
end loop;
end;
begin
for cTab in (select * from dba_tables where owner='USER1') loop
execute immediate 'grant select on '||cTab.owner||'.'||cTab.table_name || ' to ' || 'USER2';
dbms_output.put_line(cTab.owner||'.'||cTab.table_name || ' granted to '||'USER2');
end loop;
end;
Comments
Post a Comment