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_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;
/