GRANT ANY OBJECT PRIVILEGE
June 4, 2012 1 Comment
Real world scenario:
I have users: A_DBA, B_JUNIOR, C_MAIN, D_USERS.
I want my junior, user B_JUNIOR, to grant execute privilege on C_MAIN.testProc to D_USERS, without having him execute privilege on C_MAIN.testProc.
So I want my junior to grant(JUST grant) object privilege on one schema to another schema .
Solution:
GRANT ANY OBJECT PRIVILEGE is the privilege which solves it.
SQL> connect A_DBA/ta@MYDB
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as A_DBA
SQL> grant GRANT ANY OBJECT PRIVILEGE to B_JUNIOR.
Grant succeeded
SQL> connect B_JUNIOR/a@MYDB
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as B_JUNIOR
SQL> grant execute on C_MAIN.testProc to D_USERS
Grant succeeded
————————————BUT, junior can’t execute itself
SQL> exec C_MAIN.testProc;
begin C_MAIN.testProc; end;
ORA-06550: line 2, column 7:
PLS-00201: identifier 'C_MAIN.TESTPROC' must be declared
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored
About Mariami Kupatadze
DBA Knowledge Base 
Informative