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.....

Comments

  1. Hi, guys!
    The following link provides the way to get all references to an Oracle object.
    Also, it explains how to fix ORA-00942 error that is due to lack of permissions

    http://dbpilot.net/2018/01/23/getting-all-child-objects-within-an-object/

    ...

    ReplyDelete

Post a Comment