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.....
Hi, guys!
ReplyDeleteThe 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/
...