PeopleSoft user profile security is not audited by PeopleSoft and as far as I know there is no delivered functionality to turn on auditing of user profiles. However, PeopleSoft does provide the ability to implement database level auditing using audit records and triggers. This article goes through how to set up user profile auditing and points out some issues with implementing the delivered functionality and some modified scripts to deal with this.
The specific tables being audited in this example are:
- PSOPRDEFN which stores user information
- PSROLEUSER which stores user roles
Yes there are other tables that you might want to audit but these two provide a good start. The reason for using database level auditing for PeopleSoft user profile security (as opposed to application designer record and field auditing) is it covers scenarios where tables are updated outside of PeopleSoft (e.g. through the database, data mover, SQR, COBOLs etc).
The process for implementing database level auditing is:
- Create the relevant audit tables and build them
- In Oracle, implement the GET_PS_OPRID function (or a better version of it)
- In SQL Server, fix the trigger scripts to correctly record the database user
- Configure database level auditing triggers in PeopleSoft
- Create the auditing triggers in the database
- Test your triggers
- Fix Issues with the generated PSOPRDEFN_TR trigger in Oracle/SQL Server
- Other security tables that are audit candidates
Table of Contents
Creating the Audit Tables
In this example, two audit tables are required - one for each user profile table we will be auditing:
- AUDIT_USERS for PSOPRDEFN
- AUDIT_ROLES for PSROLEUSER
Note that you will want to include your organisations customisation code in the naming convention. So if you work for Acme Inc, and your code is AI, you might call these tables AUDIT_AI_USERS and AUDIT_AI_ROLES.
The structure of the audit tables will be:
- AUDIT_OPRID (key field and required)
- AUDIT_STAMP (key field and required)
- AUDIT_ACTN (key field and required)
- Relevant fields from the user profile tables
The following are screenshots of the structure of the audit records used in this example. Note I've only included relevant fields from PSOPRDEFN and PSROLEUSER:
- Make the AUDIT_ fields keys and required on both audit records
- Make OPRID a key in AUDIT_USERS so it is indexed
- Make ROLEUSER and ROLENAME keys in AUDIT_ROLES. Yes, all fields need to be keys otherwise you will get duplicate key errors on deletes.
- Include the fields shown the screenshots. These are the most relevant fields that you will want to audit.
Implementing the GET_PS_OPRID Function (Oracle)
The trigger statements automatically generated by PeopleSoft on an Oracle database reference the function GET_PS_OPRID. This has to exist first.
If you search PeopleBooks for Database Level Auditing you can find the source code for this function. You should also be able to find it in the file:
Unfortunately this function doesn't quite cut it. In particular it doesn't always track the operator ID correctly if the update happens outside of PeopleSoft, which makes it less than ideal for auditing.
I've created a substitute function that tracks the user ID and appends the IP address from which the user connected to the database.
Use this function at your own risk. Make sure you test it properly and please post a comment in the forums if you find any issues. If you're not comfortable using the modified function, use the delivered one - but be aware that it may not always store a user in the AUDIT_OPRID field.
Correctly get the database user (SQL Server)
The SQL server version of the trigger does not correctly store the database user when a database level update is performed. All that is stored is the value Native SQL in the AUDIT_OPRID field. This isn't particularly helpful. This is similar to the limitation of the GET_PS_OPRID function for Oracle databases.
To fix this, find the following line in each of your trigger scripts (PSOPRDEFN_TR and PSROLEUSER_TR):
SELECT @OPRID = case (charindex(',',cast(context_info as char(128)))) when 0 then 'Native SQL'
Change the when 0 then 'Native SQL' line to:
SELECT @OPRID = case (charindex(',',cast(context_info as char(128)))) when 0 then (select SYSTEM_USER + '@' + HOST_NAME())
This will then store the database_user@the_machine_they_connected_from into the AUDIT_OPRID field.
Remember to change this in all trigger scripts.
Configuring the database level auditing triggers
Database level auditing triggers can be configured in PeopleSoft using the following navigation:
- PeopleTools > Utilities > Audit > Update Database Level Audit
Add a new value for the operator definition table PSOPRDEFN.
In the Audit Triggers page:
- Enter the audit record AUDIT_USERS which is the one you created
- Select Add, Change and Delete for your audit options
- Press the generate code button to generate the triggert SQL that will need to be run on your database through your SQL client.
Repeat this process and create another audit trigger for the roles table PSROLEUSER.
Creating the triggers
You can use the Perform Database Level Audit process to generate the SQL scripts for your audit triggers. This doesn't create the triggers - it just generates the SQL to be executed on your database. This caught me out initially!
Once you have run the trigger creation scripts in your database, check that the triggers existing using the following SQL:
Replace <OWNER> with the database user that owns the PeopleSoft tables (e.g. SYSADM).
select * from ALL_TRIGGERS where TABLE_OWNER = '<OWNER>' and TRIGGER_NAME like '%TR'
For SQL Server:
select * from sys.triggers where name like '%TR';
Or alternatively, check the triggers on each of the tables, PSOPRDEFN and PSROLEUSER using the management studio.
Testing the triggers
A simple test for your triggers is to go to user profiles:
- PeopleTools > Security > User Profiles > User Profiles
Open your user ID, and in the ID tab, change your user description and save. If there are any issues with your trigger or your audit record, you will probably get a SQL error which will be logged to a trace file. Use the trace file to resolve the issue.
If the page saves, go to your SQL client and query the users audit table (PS_AUDIT_USERS) and check that two rows were created - one with an audit action of "K" - this is the old value and one with an audit action of "N" - this is the new value. The field OPRDEFNDESC should have changed values and reflect the change you made in user profiles.
To test the roles trigger, move to the roles tab in user profiles, and either add or remove a role and save. Again if you get an error, check your trace files. Now go to the audit table (PS_AUDIT_ROLES) and check that a row was added with either an audit action of "A" if you added a role or "D" if you deleted a role and that the correct role information is returned. To check a new/old value change in roles, change an existing role name to another role and save (do not add or remove rows in this case).
Issues with the generated PSOPRDEFN_TR trigger
There is an issue with creating a trigger on PSOPRDEFN. This table has the field VERSION which is updated on every signon. The update doesn't change anything in PSOPRDEFN (not even the VERSION number) however, because it is an update, the trigger will automatically record it as a change. So it will create a "K" audit action (change - old values) row for every signon. However there won't be a corresponding "N" (change - new values) audit action row because the update doesn't actually change anything.
Technically the trigger is doing the right thing - auditing a change, however in this scenario nothing has changed, so these rows aren't very useful. This is compounded by the fact that there will be a lot of signon triggered rows compared to normal audit rows. Furthermore, the delivered trigger writes multiple audit action rows - more than is necessary.
To test this out, try logging in to your PeopleSoft environment (through PIA or application designer) and check the PS_AUDIT_USERS table.
I've modified the PSOPRDEFN_TR script files to cater for this scenario:
Use these scripts at your own risk. If you're uncomfortable using my script, then stick to the delivered one but be aware of this limitation.
Other Security Tables
This article only covers auditing two user profile tables, PSOPRDEFN and PSROLEUSER. However, following the same process, you might also want to audit:
- PSOPRALIAS for information about user alias changes, e.g. what Employee ID is set for the user.
- PSUSEREMAIL for email address changes
Similarly you might want to capture changes made to other security tables such as:
- PSCLASSDEFN for changes to permission lists
- PSROLEDEFN for changes to roles
See the article on PeopleTools Security for a list of other tables.