Logging Actual Application User Names for Oracle E-Business Suite, SAP, PeopleSoft, and OBIEE
Knowing which person, not just which database account, has been a challenge for database logging and auditing when working with enterprise software applications such as the Oracle E-Business Suite, SAP, PeopleSoft, and OBIEE. Knowing which application user did what and when is now much easier because of adoption of standard Oracle functionality.
Standard functionality of Oracle database is the CLIENT_IDENTIFER attribute. 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.
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 examples of how CLIENT_IDENTIFIER is now being used by the Oracle E-Business Suite, SAP, and PeopleSoft. If you are running one of these software packages, Integrigy highly recommends that you incorporate the information that the CLIENT_IDENTIFIER provides into your logging and auditing solution.
|
Oracle CLIENT_IDENTIFIER |
|
|---|---|
|
Application |
Application Usage |
|
Oracle 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) |
|
Oracle 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)') |
If you have questions, please contact us at mailto:info@integrigy.com
Reference
- Integrigy Oracle 12c Unified Auditing Whitepaper Oracle 12c Unified Auditing
