Skip to Main Content
  • Questions
  • How to list enable role(s) for all user sessions

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sébastien.

Asked: August 04, 2021 - 2:27 pm UTC

Last updated: August 05, 2021 - 9:17 am UTC

Version: 11g 12c 19c

Viewed 1000+ times

You Asked

I have a user who has several roles which are not enable by default.
The roles are enabled (set role...) by the user randomly for each of his sessions.
Is there a way in Oracle to list, session by session, which role(s) has been enabled?

Thank you very much.
Sébastien Tromme.

and Chris said...

You can see the roles enabled in the current session by querying session_roles. But there's no view that lists the current privileges for current sessions.

Using privilege analysis, you can see which roles were used and for what during the capture period.

After stopping the capture and analyzing the result, you can view this information in the DBA_USED* views

For example:

begin
  dbms_privilege_capture.create_capture(
    name          => 'db_wide_capture_pol',
    description   => 'Captures database-wide privileges',
    type          => dbms_privilege_capture.g_database);
end;
/
exec dbms_privilege_capture.enable_capture ('db_wide_capture_pol');

grant create session, resource, select_catalog_role 
  to u
  identified by u;
  
alter user u default role none;

conn u/u

create table t ( x int );

ORA-01031: insufficient privileges

set role resource;

create table t ( x int );

Table T created.

conn chris

exec dbms_privilege_capture.disable_capture ('db_wide_capture_pol');

exec dbms_privilege_capture.generate_result ('db_wide_capture_pol');

select username, used_role, sys_priv, obj_priv, path 
from   dba_used_privs
where  capture = 'db_wide_capture_pol'
and    username = 'U';

USERNAME    USED_ROLE    SYS_PRIV          OBJ_PRIV    PATH            
U           U            CREATE SESSION    <null>      [U]              
U           PUBLIC       <null>            EXECUTE     [PUBLIC]         
U           RESOURCE     <null>            <null>      [U, RESOURCE]    
U           RESOURCE     CREATE TABLE      <null>      [U, RESOURCE]    
U           PUBLIC       <null>            SELECT      [PUBLIC]         
U           PUBLIC       <null>            READ        [PUBLIC]         
U           PUBLIC       <null>            READ        [PUBLIC]         
U           PUBLIC       <null>            READ        [PUBLIC]   

exec dbms_privilege_capture.drop_capture ('db_wide_capture_pol');


https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/performing-privilege-analysis-find-privilege-use.html#GUID-44CB644B-7B59-4B3B-B375-9F9B96F60186

Note these views don't include a timestamp for the action. So if you're trying to debug what roles a user has enabled when they're performing certain actions, you may be better off logging SESSION_ROLES at these points.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Sébastien Tromme, August 05, 2021 - 6:45 am UTC

Tested and it works, thank you Chris.
Not possible with Oracle 11, as DBMS_PRIVILEGE_CAPTURE is introduced in Oracle 12.

Regards,
Sébastien.
Chris Saxon
August 05, 2021 - 9:17 am UTC

You're welcome!

More to Explore

DBMS_PRIVILEGE_CAPTURE

More on PL/SQL routine DBMS_PRIVILEGE_CAPTURE here