Exposing PeopleSoft User Information Through the CLIENT

Exposing PeopleSoft User Information Through the CLIENT_IDENTIFIER Column

Additional monitoring information was included enhancing the availability of PeopleSoft user information in Oracle products like Oracle Audit Vault and Oracle Enterprise Manager. PeopleSoft user ID information is also stored in the CLIENT_IDENTIFIER column of the V$SESSION table.

The CLIENT_IDENTIFIER column contains only the user ID, whereas the CLIENT_INFO column also contains the user ID value, but it is typically accompanied by other user information, like machine name for example. In some cases, a monitoring application may only need the user ID information. To get this information from the CLIENT_INFO column would require programmatic transformation and parsing of the CLIENT_INFO string. Displaying only the user ID in the CLIENT_IDENTIFIER column, simplifies the retrieval of the user ID by products like Oracle Audit Vault and Oracle Enterprise Manager. No further transformation or parsing of the string is required.

Database administrators can also retrieve the information directly from the database with queries similar to the following:

SQL> select module, client_identifier, client_info from v$session where module = 'pside.exe';

MODULE          CLIENT_IDENTIFIER       CLIENT_INFO
--------------- ----------------------  ------------------------------
pside.exe       QEDMO                   QEDMO,bng2,BENG-PC,,pside.exe,

The example above displays the User ID information in both the CLIENT_INFO and the CLIENT_IDENTIFIER columns. The latter can be used by Oracle Audit Vault. The user ID information can be retrieved from the following connection types:

two-tier connections.
three-tier connections.
programs run through Process Scheduler.

The following sections provide sample queries and results.

Example: Working with CLIENT_IDENTIFIER Information and Three-Tier Connections

The following query displays the user ID information associated with a three-tier connection:

SQL> select module, client_identifier, client_info from v$session where client_identifier like 'QEDMO%;

MODULE                                        CLIENT_IDENTIFIER           CLIENT_INFO
--------------------------------------------  --------------------------  -------------------------------
PSAPPSRV@myserver.example.com (TNS V1-V3)  QEDMO                       QEDMO,,192.0.2.10,FS850U02,PSAPPSRV,

In this example the user ID information is available under CLIENT_INFO and CLIENT_IDENTIFIER, however CLIENT_IDENTIFIER only stores the user ID information while CLIENT_INFO stores other information, like the client connection details.

Example: Working with CLIENT_IDENTIFIER Information and Process Scheduler

It is also possible to monitor the user ID for programs running through Process Scheduler. In the following example an Application Engine program ran through Process Scheduler using the user ID QESS. By running the following query, it is possible to display the user ID information.

SELECT module, client_identifier, client_info 
FROM v$session 
WHERE client_identifier like 'QESS%';

MODULE          CLIENT_IDENTIFIER               CLIENT_INFO
--------------  ------------------------------  -------------------------------------
PSAE            QESS                            QESS,,myserver.example.com,,PSAESRV,