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;
Thursday, June 30, 2011
Subscribe to:
Post Comments (Atom)
Cannot access dba_directories inside procedure
Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist I got it in while compiling procedure, becaus...
-
While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not ...
-
Today after restarting primary and standby database servers I faced with ORA-16810 error DGMGRL> show configuration; Configuration - ...
No comments:
Post a Comment