CODEWITHSUNDEEP
oracle-databasenamespaces
Angad
Angad

Reputation: 21

Namespace sys_Session_roles in oracle 12.2.0.1 works in sql query but not inside a plsql block

This is an issue I am facing in oracle 12.2.0.1. We have recently migrated from 12.1.0.2. It worked fine there. select SYS_CONTEXT ('SYS_SESSION_ROLES', 'ROLE_XYZ') from dual will give correct results when executed as a sql query but not when executed inside a pl/sql block say a function.

Upvotes: 1

Views: 735

Answers (1)

Jon Heller
Jon Heller

Reputation: 36798

This is probably a definer's rights versus invoker's rights issue. SQL and anonymous PL/SQL blocks are always run with invoker's rights, which means they run with all currently enabled roles. PL/SQL objects by default run with definer's rights, which means they only have the privileges granted directly to the user and exclude roles. The solution might be to change your function from definer's rights to invoker's rights.

Here's the SYS_CONTEXT function working as expected in SQL:

select sys_context('SYS_SESSION_ROLES', 'DBA') has_role
from dual;

HAS_ROLE
--------
TRUE

A function created with AUTHID DEFINER, or no AUTHID clause at all, will not recognize the role. 

create or replace function has_dba_definers_rights return varchar2 authid definer is
begin
    return SYS_CONTEXT ('SYS_SESSION_ROLES', 'DBA');
end;
/

select has_dba_definers_rights from dual;

HAS_DBA_DEFINERS_RIGHTS
-----------------------
FALSE

By adding AUTHID CURRENT_USER, the function will now recognize all currently enabled roles.

create or replace function has_dba_invokers_rights return varchar2 authid current_user is
begin
    return SYS_CONTEXT ('SYS_SESSION_ROLES', 'DBA');
end;
/

select has_dba_invokers_rights from dual;

HAS_DBA_INVOKERS_RIGHTS
-----------------------
TRUE

The above code will not work when called from a trigger. Roles are always disabled in triggers, and in any objects called from a trigger. To find roles granted to a user, inside a trigger, you must query a table like DBA_ROLE_PRIVS instead.

Upvotes: 2

Related Questions