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 query
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:
