How To Supplement Oracle Unified Audit With USERENV Context

I work with customers on a weekly basis on how to centralize Oracle AI Database user authentication, authorization and user life cycle management. One very important topic that often comes up is how to uniquely identify users actions for a shared user schema authentication setup.  This enables database administrators to have a tamper proof forensic trail to identify who did what when on the database

The normal method for identifying users in the Oracle AI database unified audit log of 19c and newer database versions is to the DBUSERNAME or CURRENT_USER fields of the unified audit log.

For example, the following query shows who (DBUSERNAME) performed which action (ACTION_NAME):

SQL> SELECT EVENT_TIMESTAMP,ACTION_NAME,dbusername from UNIFIED_AUDIT_TRAIL order by 1;

                   EVENT_TIMESTAMP            ACTION_NAME    DBUSERNAME
__________________________________ ______________________ _____________
06-MAY-26 02.22.11.707376000 PM    AUDIT                  SYSTEM       
06-MAY-26 02.23.17.545825000 PM    CREATE AUDIT POLICY    SYSTEM       
06-MAY-26 02.23.31.787023000 PM    AUDIT                  SYSTEM       
06-MAY-26 02.23.46.212854000 PM    LOGON                  ALLDBUSERS   
06-MAY-26 02.23.46.216907000 PM    ALTER SESSION          ALLDBUSERS   
06-MAY-26 02.23.46.319741000 PM    SELECT                 ALLDBUSERS   
06-MAY-26 02.23.46.320954000 PM    SELECT                 ALLDBUSERS    
06-MAY-26 02.23.46.321219000 PM    LOGON                  JSMITH

However, the shared user schema ALLDBUSERS represents a group of users rather than an individual user.  There is supplemental information available to uniquely identify users within the shared user schema from information in the SYS_CONTEXT USERENV data including AUTHENTICATED_IDENTITY and ENTERPRISE_IDENTITY.  These additional fields can be added to the APPLICATION_CONTEXTS field of the unified audit record with the following:

SQL> AUDIT CONTEXT NAMESPACE userenv ATTRIBUTES AUTHENTICATED_IDENTITY, ENTERPRISE_IDENTITY

If we add APPLICATION_CONTEXTS to the output of the audit log, we see that additional context:

SQL> SELECT EVENT_TIMESTAMP,ACTION_NAME,dbusername,APPLICATION_CONTEXTS from UNIFIED_AUDIT_TRAIL order by 1;
                   EVENT_TIMESTAMP    ACTION_NAME    DBUSERNAME                                                                                                          APPLICATION_CONTEXTS 
__________________________________ ______________ _____________ _____________________________________________________________________________________________________________________________ 
06-MAY-26 03.59.13.290254000 PM    LOGON          ALLDBUSERS    (USERENV,AUTHENTICATED_IDENTITY=dbuser@DBAuthDemo.com); (USERENV,ENTERPRISE_IDENTITY=9608cae1-95a9-493d-af94-f4d36d9ba633)    
06-MAY-26 03.59.42.992825000 PM    CREATE USER    ALLDBUSERS    (USERENV,AUTHENTICATED_IDENTITY=dbuser@DBAuthDemo.com); (USERENV,ENTERPRISE_IDENTITY=9608cae1-95a9-493d-af94-f4d36d9ba633)    
06-MAY-26 03.59.44.661566000 PM    LOGOFF         ALLDBUSERS    (USERENV,AUTHENTICATED_IDENTITY=dbuser@DBAuthDemo.com); (USERENV,ENTERPRISE_IDENTITY=9608cae1-95a9-493d-af94-f4d36d9ba633)    

From the above output from the unified audit log, we can now identify the user by their Entra ID user principal name (dbuser@DBAuthDemo.com) and their Entra ID unique user ID (9608cae1-95a9-493d-af94-f4d36d9ba633).

We can present this data a little more clearly with the application of regular expression manipulation.

SQL> SELECT EVENT_TIMESTAMP AS TIMESTAMP,ACTION_NAME AS OPERATION,dbusername AS USERNAME,REGEXP_SUBSTR(APPLICATION_CONTEXTS, 'AUTHENTICATED_IDENTITY\s*=\s*([^,; \)]+)', 1, 1, 'i', 1) as entra_upn from UNIFIED_AUDIT_TRAIL order by 1;

                         TIMESTAMP      OPERATION      USERNAME                ENTRA_UPN 
__________________________________ ______________ _____________ ________________________ 
             
06-MAY-26 03.58.59.012149000 PM    SELECT         SYSTEM        SYSTEM                   
06-MAY-26 03.58.59.012404000 PM    SELECT         SYSTEM        SYSTEM                   
06-MAY-26 03.59.13.290254000 PM    LOGON          ALLDBUSERS    dbuser@DBAuthDemo.com    
06-MAY-26 03.59.42.992825000 PM    CREATE USER    ALLDBUSERS    dbuser@DBAuthDemo.com    
06-MAY-26 03.59.44.661566000 PM    LOGOFF         ALLDBUSERS    dbuser@DBAuthDemo.com    

This concept can be extended to include other SYS_CONTEXT USERENV content such as cryptographic connection details.  I'll save that for another day.

As a bonus, the EntraID user's UPN is available by default in the legacy audit log in comment_text. Here is an example search:

SQL> select extended_timestamp, STATEMENT_TYPE, db_user, REGEXP_SUBSTR(comment_text, 'AZURE_USER\s*=\s*([^,; \;]+)', 1, 1, 'i', 1) as azure_upn FROM dba_common_audit_trail;

                    EXTENDED_TIMESTAMP    STATEMENT_TYPE       DB_USER                AZURE_UPN 
______________________________________ _________________ _____________ ________________________ 
06-MAY-26 07.54.56.115998000 PM GMT    LOGON             SYSTEM                                 
06-MAY-26 07.57.23.830030000 PM GMT    LOGON             ALLDBUSERS    dbuser@DBAuthDemo.com    
06-MAY-26 07.57.24.324858000 PM GMT    SELECT            ALLDBUSERS                                

If no data is returned, you will need to add an audit policy.  For example:

$ORACLE_HOME/bin/sqlplus system@pdb1_ssl
SQL> audit create session;                                

I hope you find this helpful and beneficial.

Blessings