Search notes:

Oracle roles are disabled in (authid definer) PL/SQL packages

A PL/SQL object compiled with authid definer executes its code with disabled roles.

In order to demonstrate this, three PL/SQL functions are created, one with authid definer, one with authid current_user and one without explicitly stating its authid. These functions are passed the name of a role and the use sys_context[sys_context('sys_session_roles', …) to determine if the role is enabled.

All of these functions are the fed the roles found in session_roles (which lists the roles being enabled in the current session).

The function defined with authid definer always returns false while the function defined with authid current_user always returns true.

Creating the functions:

create or replace function tq84_sys_session_roles_definer(r varchar2)
   return varchar2
   authid definer
as
begin
   return sys_context('sys_session_roles', r);
end;
/
 
create or replace function tq84_sys_session_roles_current_user(r varchar2)
   return varchar2
   authid current_user
as
begin
   return sys_context('sys_session_roles', r);
end;
/
 
create or replace function tq84_sys_session_roles_(r varchar2)
   return varchar2
as
begin
   return sys_context('sys_session_roles', r);
end;
/

Executing the functions:

select
   role,
   sys_context('sys_session_roles'   , role),
   tq84_sys_session_roles_definer     (role) definer     , -- FALSE
   tq84_sys_session_roles_current_user(role) current_user, -- TRUE
   tq84_sys_session_roles_            (role) default_      -- FALSE
from
   session_roles;

Cleaning up

drop function tq84_sys_session_roles_;
drop function tq84_sys_session_roles_definer;
drop function tq84_sys_session_roles_current_user;

Index