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