GRANT ANY OBJECT PRIVILEGE

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
Oracle Certified Master Linkedin: https://www.linkedin.com/in/mariami-kupatadze-01074722/

One Response to GRANT ANY OBJECT PRIVILEGE

  1. Jayashree says:

    Informative

Leave a Reply

%d bloggers like this: