Thanks for the question, Christian.
Asked: May 12, 2020 - 9:55 am UTC
Last updated: May 13, 2020 - 10:05 am UTC
Version: 19.0
Viewed 1000+ times
You Asked
Hi all,
we are migrating from Oracle 11.2 to Oracle 19.0. In 11.2 we are using a after logon trigger to check, if a user has a specific role. According to that role, we set a session context, which itself is used in a huge amount of views. The check has been done using SYS_CONTEXT(SYS_SESSION_ROLE, role_name). But this context is always empty in logon trigger itself. Calling the package to set context is possible in established session, but not in AFTER LOGON Trigger itself.
using dba_role_privs is not possible, as we have also kerberos authenticated users which do not appear there.
using SESSION_ROLES is also not possible, is also not yet populated in logon trigger
doing a lookup on Kerberos server is also not a good idea as lookup can take some time which will slow down session creation
Anyone an idea, how this can be solved in LOGON Trigger ?
and Chris said...
DBMS_session.session_is_role_enabled will detect roles in a logon trigger:
create table t (
c1 varchar2(100)
);
create or replace trigger init_roles
after logon on database
begin
for r in ( select * from dba_roles ) loop
if dbms_session.session_is_role_enabled ( r.role ) then
insert into t values ( user || ' has ' || r.role );
end if;
end loop;
end;
/
create role r;
grant create session to r;
grant r to u identified by u;
grant dba to d identified by d;
conn u/u
conn d/d
select * from chris.t;
C1
U has R
D has DBA
D has SELECT_CATALOG_ROLE
D has EXECUTE_CATALOG_ROLE
D has CAPTURE_ADMIN
D has EXP_FULL_DATABASE
D has IMP_FULL_DATABASE
D has DATAPUMP_EXP_FULL_DATABASE
D has DATAPUMP_IMP_FULL_DATABASE
D has GATHER_SYSTEM_STATISTICS
D has OPTIMIZER_PROCESSING_RATE
D has EM_EXPRESS_BASIC
D has EM_EXPRESS_ALL
D has SCHEDULER_ADMIN
D has HS_ADMIN_SELECT_ROLE
D has HS_ADMIN_EXECUTE_ROLE
D has XDBADMIN
D has XDB_SET_INVOKER
D has WM_ADMIN_ROLE
D has JAVA_ADMIN
D has OLAP_XS_ADMIN
D has OLAP_DBA