Wednesday, October 5, 2016

DB Security/Enterprise User Security (EUS) : Logon Trigger for auditing LDAP user logged on to Oracle DB

Use Case
Find a way to identify the AD domain user logged into a database configured with EUS

Pre-requisite: DB Users/Groups have already been centralized in an LDAP like Active Directory(AD) using Enterprise User Security (EUS)

Details:After logging in using sqlplus if we issue the following SQL we still see the shared schema user(which was used to map the Oracle DB Default Domain to the LDAP Container)

SQL> show user
GLOBAL_IDENT_SCHEMA_USER

Now the question is if we see a runaway query or a transaction causing blocking, how do we tie that back to the exact  external user instead of a Global Schema? How would we identify this information from within Oracle Enterprise Manager (OEM)session info?


A workaround could be to issue the following SQL quer
y
SQL>  SELECT SYS_CONTEXT('USERENV','EXTERNAL_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','EXTERNAL_NAME')
--------------------------------------------------------------------------------
cn=Sudipto Desmukh,cn=Users, dc=corp, dc=kdemo,dc=com

But wouldn't it be great if this is available in v$session for anyone to see real-time without specifically issuing a SQL query ?
We are able to create a logon trigger (attached) which populates enterprise user session information to client_info of V$SESSION.
create or replace trigger sys.on_logon after logon on database 
declare 
v_externalname varchar2(64) := ''; 
begin 
SELECT substr(sys_context('userenv','external_name'),1,63) into v_externalname FROM dual; 
if v_externalname is not null 
then 
DBMS_APPLICATION_INFO.SET_CLIENT_INFO (v_externalname ); 
end if; 
end; 

We should be able to pull up the blocking session information, if any from OEM tied to this enterprise user. An illustration of the result :

References: