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.
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.