Oracle 12c - grant DBA to procedure user.procedure - what?

12c introduced this new piece of functionality that initially looks a little odd but actually does make sense when you follow it through, i would imagine there are limited cases though where this specific functionality is required.

What it essentially allows only is relevant  for invokers rights procedures where you might want a certain piece of the code block to have rights that the invoker might not have and you don't want to give them that right directly - you only want it to be valid when they run that code block. This new feature allows you to do that - with me so far?

Ok lets do a quick demo, we'll create a c##test user which will own the plsql procedure and a c##rich user who we will let invoke it but we don't actually want that user to have any additional rights - just be able to run the few lines of plsql with elevated rights. Now you could argue here - why bother with invokers rights then just run with definers rights - and in the simple case below that is true - but I'm sure there are more complex other cases where this new feature is useful.

Anyway lets set a few things up - starting with the user


SQL> create user c##test identified by c##test;

User created.

SQL> grant connect,resource to c##test;

Grant succeeded.

Now we create a simple proc that can create a user.

SQL> conn c##test/c##test


 create or replace procedure test authid current_user as
  begin
   execute immediate ' create user c##test2 identified by c##test2';
 end;
 /

Procedure created.

SQL> exec test;
BEGIN test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "C##TEST.TEST", line 3
ORA-06512: at line 1

So the code compiles ok - but i can;t run it as c##test does not have that right - ok so far

Lets try as sys

SQL> conn / as sysdba
Connected.
SQL> exec c##test.test;
BEGIN c##test.test; END;

*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "C##TEST.TEST", line 1
ORA-06512: at line 1

And we get some weird error - I'm putting this down to the fact that I'm doing all this stuff in the CDB (and i probably should be using a PDB) - maybe this is a bug? Anyway lets try with another DBA account


SQL> conn c##demo/c##demo
ERROR:
ORA-00942: table or view does not exist


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>  exec c##test.test;

PL/SQL procedure successfully completed.

And it works and the user is created

SQL> select username from dba_users where username like 'C##TEST2';

USERNAME
--------------------------------------------------------------------------------
C##TEST2

Lets get rid of it to tidy up

SQL> drop user C##TEST2;

User dropped.

Lets be lazy and give c##test dba rights


SQL> conn / as sysdba
Connected.
SQL> grant dba to c##test;

Grant succeeded.

SQL>  conn c##test/c##test
ERROR:
ORA-00942: table or view does not exist


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> exec test;

PL/SQL procedure successfully completed.

SQL> select username from dba_users where username like 'C##TEST2';

USERNAME
--------------------------------------------------------------------------------
C##TEST2

And we can see it now all works fine for c##test as expected - lets drop the user to tidy up again

SQL> drop user c##test2;

User dropped.

Now lets revoke the right and just confirm it doesn;t work

SQL> conn / as sysdba
Connected.
SQL> revoke dba from c##test;

Revoke succeeded.

SQL> conn c##test/c##test
ERROR:
ORA-00942: table or view does not exist


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> exec test;
BEGIN test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "C##TEST.TEST", line 3
ORA-06512: at line 1

As expected fails - now lets try and grant just that procedure the DBA right

SQL> conn / as sysdba
Connected.
SQL> grant dba to procedure c##test.test;
grant dba to procedure c##test.test
                               *
ERROR at line 1:
ORA-01924: role 'DBA' not granted or does not exist

And we can't - you can only grant the role to the procedure if the user owning that procedure already has that role - lets fix that and try again


SQL> grant dba to c##test;

Grant succeeded.

SQL> grant dba to procedure c##test.test;

Grant succeeded.

Now lets create another user who we want to let run this proc - but we don't want to give the DBA right to


SQL>  create user c##rich identified by c##rich;

User created.

Grant him execute right and create session (and nothing else)


SQL> grant execute on c##test.test to c##rich;

Grant succeeded.

SQL> grant create session to c##rich;

Grant succeeded.

SQL> conn c##rich/c##rich
ERROR:
ORA-00942: table or view does not exist


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> exec c##test.test;

PL/SQL procedure successfully completed.



And it works fine!

There is an interesting note to this though  - which may not be immediately obvious - but it makes sense when you think about it. Let me 'create or replace' that same code again in c##test.

SQL> conn c##test/c##test
ERROR:
ORA-00942: table or view does not exist


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>

SQL>  create or replace procedure test authid current_user as
  begin
   execute immediate ' create user c##test2 identified by c##test2';
 end;
 /  2    3    4    5

Procedure created.

SQL> conn  c##rich/c##rich
ERROR:
ORA-00942: table or view does not exist


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.



SQL> exec c##test.test;
BEGIN c##test.test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "C##TEST.TEST", line 3
ORA-06512: at line 1

And the rights have gone - as soon as you replace the stored code the grant has to be re-run to give the permission back. This is to prevent inadvertently giving extra rights in some other code block to do other things that were never intended - for example if somehow c##rich had rights to change the procedure - he could change it to do any dba commands and be able to execute it.


So - to sum up - another little security/privilege thing to know about. I doubt however that it will be widely used - though by saying that you can be sure i missed something and everyone will start using it.....