Oracle Audit Vault - Oracle Client Identifier and Last Login
Several standard features of the Oracle database should be kept in mind when considering what alerts and correlations are possible when combining Oracle database and application log and audit data.
Client Identifier
Default Oracle database auditing stores the database username but not the application username. In order to pull the application username into the audit logs, the CLIENT IDENTIFIER attribute needs to be set for the application session which is connecting to the database. The CLIENT_IDENTIFIER is a predefined attribute of the built-in application context namespace, USERENV, and can be used to capture the application user name for use with global application context, or it can be used independently.
CLIENT IDENTIFIER is set using the DBMS_SESSION.SET_IDENTIFIER procedure to store the application username. The CLIENT IDENTIFIER attribute is one the same as V$SESSION.CLIENT_IDENTIFIER. Once set you can query V$SESSION or select sys_context('userenv','client_identifier') from dual.
The table below offers several examples of how CLIENT_IDENTIFIER is used. For each example, for Level 3 alerts, consider how the value of CLIENT_IDENTIFIER could be used along with network usernames, enterprise applications usernames as well as security and electronic door system activity logs.
|
Oracle CLIENT_IDENTIFIER |
|
|---|---|
|
Application |
Example of how used |
|
E-Business Suite |
As of Release 12, the Oracle E-Business Suite automatically sets and updates client_identifier to the FND_USER.USERNAME of the user logged on. Prior to Release 12, follow Support Note How to add DBMS_SESSION.SET_IDENTIFIER(FND_GLOBAL.USER_NAME) to FND_GLOBAL.APPS_INITIALIZE procedure (Doc ID 1130254.1) |
|
PeopleSoft |
Starting with PeopleTools 8.50, the PSOPRID is now additionally set in the Oracle database CLIENT_IDENTIFIER attribute. |
|
SAP |
With SAP version 7.10 above, the SAP user name is stored in the CLIENT_IDENTIFIER. |
|
Oracle Business Intelligence Enterprise Edition(OBIEE) |
When querying an Oracle database using OBIEE the connection pool username is passed to the database. To also pass the middle-tier username, set the user identifier on the session. To do this in OBIEE, open the RPD, edit the connection pool settings and create a new connection script to run at connect time. Add the following line to the connect script:
CALL DBMS_SESSION.SET_IDENTIFIER('VALUEOF(NQ_SESSION.USER)') |
Last Login
Tracking when database users last logged in is a common compliance requirement. This is required in order to reconcile users and cull stale users. New with Oracle12c, Oracle provides this information for database users. The system table SYS.DBA_USERS has a column, last_login.
Example:
select username, account_status, common, last_login
from sys.dba_users
order by last_login asc;
|
Username |
Account_Status |
Common |
Last_Login |
|---|---|---|---|
|
C##INTEGRIGY |
OPEN |
YES |
05-AUG-14 12.46.52.000000000 PM AMERICA/NEW_YORK |
|
C##INTEGRIGY_TEST_2 |
OPEN |
YES |
02-SEP-14 12.29.04.000000000 PM AMERICA/NEW_YORK |
|
XS$NULL |
EXPIRED & LOCKED |
YES |
02-SEP-14 12.35.56.000000000 PM AMERICA/NEW_YORK |
|
SYSTEM |
OPEN |
YES |
04-SEP-14 05.03.53.000000000 PM AMERICA/NEW_YORK |
If you have questions, please contact us at mailto:info@integrigy.com
Reference
- Integrigy Guide to Oracle Audit Vault Integrigy Guide to Oracle Audit Vault
- Presentation “Developing Value with Oracle Audit Vault Developing Value with Oracle Audit Vault
