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

This new feature in Oracle 12C changes behavior of calling functions within views. In previous releases all functions used within a view were called using rights of creator of the view (DEFINER rights) no matter if the functions used pragma AUTHID CURRENT_USER(INVOKER rights).

In Oracle 12C you can specify dedicated pragma  during view creation BEQUEATH CURRNT_USER so all functions that are using AUTHID CURRENT_USER will be called with INVOKER rights.

It’s worth to read article: Authid current_user, authid definer

During view creation you can specify two types of pragma

BEQUEATH DEFINER – it’s default implicit so no need to add it during view creation. Function called in the views will be always called with DEFINER rights.

create view <VIEW_NAME> bequeath definer

BEQUEATH CURRENT_USER – functions called in views with the pragma will be called with DEFINER rights(if the called functions have pragma AUTHID DEFINER) or with INVOKER rights (if the called functions have pragma AUTHID CURRENT_USER)

create view <VIEW_NAME> bequeath current_user

Example

As user TOMASZ I have created two functions:

  • invoker_right_fnc with pragma AUTHID CURRENT_USER
  • definer_right_fnc with pragma AUTHID DEFINER
sqlplus tomasz>

create or replace function invoker_right_fnc return varchar2
authid current_user
as
  v_ret varchar2(300);
begin
  select 
   'CURRENT_USER: '||sys_context('USERENV', 'CURRENT_USER')||chr(10)||
   'SESSION_USER: '||sys_context('USERENV', 'SESSION_USER')||chr(10)||
   'ORA_INVOKING_USER: '||ora_invoking_user||chr(10)||
   'ORA_INVOKING_USERID: '||ora_invoking_userid
  into v_ret
  from dual;
  
  return v_ret;
end;
/

create or replace function definer_right_fnc return varchar2
authid definer
as
  v_ret varchar2(300);
begin
  select 
   'CURRENT_USER: '||sys_context('USERENV', 'CURRENT_USER')||chr(10)||
   'SESSION_USER: '||sys_context('USERENV', 'SESSION_USER')||chr(10)||
   'ORA_INVOKING_USER: '||ora_invoking_user||chr(10)||
   'ORA_INVOKING_USERID: '||ora_invoking_userid
  into v_ret
  from dual;
  
  return v_ret;
end;
/

Then I have created two views that are calling the functions. Please remeber you don’t need to explicite specify BEQUEATH DEFINER it’s default pragma

sqlplus tomasz>

create view view_11g bequeath definer
as
select 
  invoker_right_fnc, 
  definer_right_fnc 
from dual;

create view view_12c bequeath current_user
as
select 
  invoker_right_fnc, 
  definer_right_fnc 
from dual;

Then I granted select on the views to other user TOMASZ1

sqlplus tomasz>

grant select on view_11g to tomasz1;
grant select on view_12c to tomasz1;

Now it’s time to check how it behaves when you select data from the views as other user TOMASZ1.

For view VIEW_11G both functions were called by user SESSION_USER:TOMASZ1 but executed with rights of user CURRENT_USER:TOMASZ

sqlplus tomasz1>

select * from tomasz.view_11g;

INVOKER_RIGHT_FNC            DEFINER_RIGHT_FNC             
---------------------------  ------------------------------
CURRENT_USER: TOMASZ         CURRENT_USER: TOMASZ          
SESSION_USER: TOMASZ1        SESSION_USER: TOMASZ1
ORA_INVOKING_USER: TOMASZ    ORA_INVOKING_USER: TOMASZ     
ORA_INVOKING_USERID: 110     ORA_INVOKING_USERID: 110

For view VIEW_12C both functions were called by user SESSION_USER:TOMASZ1 but function INVOKER_RIGHT_FNC was executed with rights of user CURRENT_USER:TOMASZ1 and DEFINER_RIGHT_FNC was executed with rights of user CURRENT_USER:TOMASZ.

select * from tomasz.view_12c;

INVOKER_RIGHT_FNC           DEFINER_RIGHT_FNC                
--------------------------- ---------------------------------
CURRENT_USER: TOMASZ1       CURRENT_USER: TOMASZ             
SESSION_USER: TOMASZ1       SESSION_USER: TOMASZ1            
ORA_INVOKING_USER: TOMASZ1  ORA_INVOKING_USER: TOMASZ        
ORA_INVOKING_USERID: 107    ORA_INVOKING_USERID: 110

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *