This new extension in Oracle 12C closes security gap in previous release. When  a user (INVOKER) calls a PL/SQL code with pragma AUTHID CURRENT_USER of other user  then the code is executed with privileges of current user (INVOKER rights).

It’s very useful but can be dangerous if called code has got harm code. For example we can create simple function “get_me_dba_fnc” in schema SH with the pragma AUTHID CURRENT_USER and call it as user TOMASZ with high privileges like DBA and  by mistake grant DBA role to SH without noticing it.

In Oracle 12C you can control inheritance of privileges by using following clauses

• INHERIT PRIVILEGES
• INHERIT ANY PRIVILEGES

Create following code as user SH

sqlplus sh>

create or replace function sh.get_me_dba_fnc return varchar2
authid current_user
is
  pragma autonomous_transaction;
begin
  execute immediate 'grant dba to sh';
  
  return 'OK';
exception
 when others then
   return 'OK'; 
end;
/ 

grant execute on sh.get_me_dba_fnc to public;

Then I execute code of user SH using TOMASZ account. User TOMASZ is very powerful user with DBA role. The function sh.get_me_dba_fnc is executed with rights of user TOMASZ(the rights are inherited by the function) because of pragma AUTHID CURRENT_USER and the code silently grants role DBA to user SH.

sqlplus tomasz>
show user
USER is TOMASZ

select * from session_roles
where role='DBA';

ROLE  
------
DBA 

select grantee, granted_role 
from dba_role_privs 
where grantee='SH';

GRANTEE    GRANTED_ROLE           
---------- -----------------------
SH         SELECT_CATALOG_ROLE    
SH         RESOURCE               

select sh.get_me_dba_fnc from dual;
GET_ME_DBA_FNC 
---------------
OK             

select grantee, granted_role 
from dba_role_privs 
where grantee='SH';

GRANTEE    GRANTED_ROLE           
---------- -----------------------
SH         SELECT_CATALOG_ROLE    
SH         RESOURCE  
SH         DBA

the block inheritance of privileges for a given user you can call following command REVOKE INHERIT PRIVILEGES

revoke inherit privileges on user <USER> from <OTHER_USER>;

for example we can block inheritance of privileges of user TOMASZ from all users

sqlplus tomasz>

revoke inherit privileges on user TOMASZ from public;

so if we call again the function sh.get_me_dba_fnc we should see following error

sqlplus tomasz>

select sh.get_me_dba_fnc from dual;

ORA-06598: insufficient INHERIT PRIVILEGES privilege

There is another option to grant explicit inheritance to dedicated user by using following command GRANT INHERIT PRIVILEGES

grant inherit privileges on user <USER> to <OTHER_USER>;

example

sqlplus tomasz>

grant inherit privileges on user tomasz to sh;

select sh.get_me_dba_fnc from dual;
GET_ME_DBA_FNC 
---------------
OK

Another option you can grant more powerful option to a user INHERIT ANY PRIVILEGES so the user is very powerful and inherits privileges from any other user

grant inherit any privileges to <USER>;

example

sqlplus tomasz>

revoke inherit privileges on user tomasz from sh;
grant inherit any privileges to sh;

select sh.get_me_dba_fnc from dual;

GET_ME_DBA_FNC 
---------------
OK

You need to remember that for backward compatibility any new user <NEW_USER> created in 12C implicit grants

INHERITS PRIVILEGES ON <NEW_USER>  TO PUBLIC;

Of course INHERITS PRIVILEGES has got as well impact on views created with clause BEQUEATH CURRENT_USER. Please read about BEQUEATH CURRENT_USER in following article:

View security extension BEQUEATH CURRENT_USER Oracle Database 12C release 1 (12.1)

Example

sqlplus tomasz>

create view sh.test_vw bequeath current_user
as
select get_me_dba_fnc ok
from dual;

select * from sh.test_vw;

GET_ME_DBA_FNC 
---------------
OK

revoke inherit any privileges from sh;

select * from sh.test_vw;

ORA-06598: insufficient INHERIT PRIVILEGES privilege

Have a fun 🙂

Tomasz