Associating database connections with PeopleSoft users

By Chris Heller • August 13, 2006

A common headache for DBAs managing PeopleSoft applications is not knowing which user a particular piece of SQL is being executed for. This happen because the SQL is being executed by the application server under a privileged user account and not the PeopleSoft user (who probably doesn’t even have a database login).

The DBA can go ahead and kill the connection at the database, but they can’t explain to the user what happened. Of course, the end user just sees it as a PeopleSoft error (PeopleSoft does not handle killed connections very gracefully) and they try it again. Which just annoys the DBA even further. A vicious circle indeed.

At the Mid-Atlantic RUG last week, this topic came up in a few different discussions. Some of the DBAs there weren’t familiar with the EnableDBMonitoring option for PeopleSoft application server domains.

When this is turned on (and it’s on by default in PeopleTools 8.43 and up), then each time the application server begins doing work on behalf of a different user than it was previously doing work for, it will set a variable in the database session with information on that user.

Platform Support.

  • Oracle. You can see the information in the CLIENT_INFO field of the V$SESSION Oracle system view.
  • SQL Server. The information is available in the context_info field of the sysprocesses system view in the master database. This is a varbinary field, so you’ll need to cast it as varchar to view the data. There is also a PeopleSoft version of the sp_who stored procedure (called sp_pswho) that will return this information. Unfortunately this stored procedure does not get installed automatically when you install PeopleSoft. You’ll need to do it manually. A script to install this can be found in appendix B of the SQL Server for PeopleSoft Performance Tuning Red Paper.
  • DB2/zOS. The DISPLAY THREAD command will display the PeopleSoft user ID in addition to it’s regular information. Other DB2 platforms (Windows, Unix, Linux) do not support this command.
  • Sybase. Later versions of Sybase support this feature, but I’m not sure about how to access the information in the database. I would assume that an additional column was added to the sysprocesses view (similar to SQL Server), but I don’t have access to a Sybase installation to verify that at the moment.
  • Informix. Informix does not support this feature.

Labels: Database, Performance

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

One Reply to “Associating database connections with PeopleSoft users”

  1. Chris,

    A few months ago I wrote an SQL fragment that I use to determine the PeopleSoft OPRID of the current user within the Oracle database. Here is the fragment:

    SUBSTR(sys_context(‘USERENV’, ‘CLIENT_INFO’), 1, INSTR(sys_context(‘USERENV’, ‘CLIENT_INFO’), ‘,’, 1, 1) – 1)

    You can read more about my approach in my September, 2006 blog post PeopleSoft on Oracle WHOAMI

Comments are closed.

Start your free demo

"Learn how you can reduce risk with rapid threat protection, audit response and access control. All from a single, comprehensive platform"

Trusted by hundreds of leading brands