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

Advertisements

One Response to GRANT ANY OBJECT PRIVILEGE

  1. Jayashree says:

    Informative

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: