PeopleSoft provides 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 the delivered functionality.

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.

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:

1. Create the relevant audit tables and build them
2. Implement the GET_PS_OPRID function (or a better version of it)
3. Configure database level auditing triggers in PeopleSoft
4. Create the auditing triggers in the database
5. Test your triggers
6. Fix Issues with PSOPRDEFN_TR trigger

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:

AUDIT_USERS

AUDIT_ROLES

Remember to:

• Make the AUDIT_ fields keys and required on both audit records
• Make OPRID a key in AUDIT_USERS so it is indexed
• Make ROLEUSER a key in AUDIT_ROLES so it is indexed
• Include the fields shown the screenshots. These are the most relevant fields that you will want to audit.

Implementing the GET_PS_OPRID Function

The trigger statements automatically generated by PeopleSoft 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 %PS_HOME%\scripts\getpsoprid.sql.

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.

DROP FUNCTION GET_PS_OPRID
/
CREATE FUNCTION GET_PS_OPRID (V_CLIENT_INFO VARCHAR2 ) RETURN VARCHAR2 IS
    V_OS_USER VARCHAR2(30);
    V_IP_ADDRESS VARCHAR2(15);
    I INTEGER;
 
BEGIN
 
    /*
        GET_PS_OPRID
 
        Written by Praj Basnet
        April 2009
        PeopleSoft Wiki
        http://peoplesoft.wikidot.com/database-level-auditing
 
        Alternative to the GET_PS_OPRID PL/SQL function delivered by Oracle
        that does not correctly return the user in all cases. This version uses
        the OS_USER if the CLIENT_INFO is empty and appends the IP address.
    */
 
    SELECT SUBSTR(V_CLIENT_INFO, 1, (INSTR(V_CLIENT_INFO, ',')) - 1) INTO V_OS_USER FROM DUAL;
    SELECT UPPER(SYS_CONTEXT('USERENV', 'IP_ADDRESS')) INTO V_IP_ADDRESS FROM DUAL;
 
    IF (LENGTH(V_OS_USER) IS NULL) THEN
        SELECT UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) INTO V_OS_USER FROM DUAL;
    END IF;
 
    IF (LENGTH(V_IP_ADDRESS) IS NULL) THEN
        V_IP_ADDRESS := 'UNKNOWN';
    END IF;
 
    IF (LENGTH(V_OS_USER) IS NULL) THEN
        V_OS_USER := 'UNKNOWN';
    ELSE
        V_OS_USER := rtrim(V_OS_USER, ' ') || '@' || rtrim(V_IP_ADDRESS, ' ');
    END IF;
 
    RETURN V_OS_USER;
 
END GET_PS_OPRID;
/
GRANT EXECUTE ON GET_PS_OPRID TO PUBLIC
/

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.

Configuring database level auditing triggers in PeopleSoft

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 PL/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! It is actually quicker just to copy the generated code from your audit trigger definitions (generated in the previous step) and to run them in your database with your SQL client.

Either way is fine - just make sure that the triggers exist using this query:

select * 
from ALL_TRIGGERS 
where TABLE_OWNER = '<OWNER>'
and TRIGGER_NAME like '%TR'

Replace <OWNER> with the database user that owns the PeopleSoft tables (e.g. SYSADM).

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 most like get an 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 to 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 delivered 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 developed my own version of the PSOPRDEFN_TR script which is based on the generated trigger code but has been modified to check for changes in any field in PSOPRDEFN which is being audited except for VERSION. Use this script at your own risk. If you're uncomfortable using my script, then stick to the delivered one but be aware of this limitation.

This version of the script will write audit actions whenever a row is added or deleted or when a value in the PSOPRDEFN record is updated. It will not any cases where PSOPRDEFN is updated but no field are changes (which is what happens during signon).

CREATE OR REPLACE TRIGGER PSOPRDEFN_TR
AFTER INSERT OR UPDATE OR DELETE ON PSOPRDEFN
FOR EACH ROW
DECLARE
    V_AUDIT_OPRID VARCHAR2(64);
BEGIN
 
/*
    PSOPRDEFN_TR
    Developed by Praj Basnet - April 2009
    PeopleSoft Wiki: http://peoplesoft.wikidot.com/
 
    This version of the trigger has conditional logic to prevent an audit row
    being created for signon where no data in PSOPRDEFN actually changes but an
    update is performed. The delivered PSOPRDEFN_TR script generated through the
    update database level auditing functionality does not take this into account.
 
    Remember to replace PS_AUDIT_USERS with the appropriate audit table you are
    using. Also check that the fields in your audit table match those in this
    script. If not add/remove accordingly.
*/
 
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);
 
IF INSERTING THEN
    INSERT INTO PS_AUDIT_USERS VALUES (
        GET_PS_OPRID(V_AUDIT_OPRID),
        SYSDATE,
        'A',
        :NEW.OPRID,
        :NEW.VERSION,
        :NEW.OPRDEFNDESC,
        :NEW.EMPLID,
        :NEW.EMAILID,
        :NEW.OPRCLASS,
        :NEW.ROWSECCLASS,
        :NEW.OPERPSWD,
        :NEW.ENCRYPTED,
        :NEW.SYMBOLICID,
        :NEW.ACCTLOCK,
        :NEW.PRCSPRFLCLS,
        :NEW.DEFAULTNAVHP,
        :NEW.OPRTYPE,
        :NEW.USERIDALIAS
    );
 
ELSE
 
    IF DELETING THEN
 
        INSERT INTO PS_AUDIT_USERS VALUES (
            GET_PS_OPRID(V_AUDIT_OPRID),
            SYSDATE,
            'D',
            :OLD.OPRID,
            :OLD.VERSION,
            :OLD.OPRDEFNDESC,
            :OLD.EMPLID,
            :OLD.EMAILID,
            :OLD.OPRCLASS,
            :OLD.ROWSECCLASS,
            :OLD.OPERPSWD,
            :OLD.ENCRYPTED,
            :OLD.SYMBOLICID,
            :OLD.ACCTLOCK,
            :OLD.PRCSPRFLCLS,
            :OLD.DEFAULTNAVHP,
            :OLD.OPRTYPE,
            :OLD.USERIDALIAS
        );
 
    ELSE
 
        IF  :NEW.OPRID != :OLD.OPRID OR
            :NEW.OPRDEFNDESC != :OLD.OPRDEFNDESC OR
            :NEW.EMPLID != :OLD.EMPLID OR
            :NEW.EMAILID != :OLD.EMAILID OR
            :NEW.OPRCLASS != :OLD.OPRCLASS OR
            :NEW.ROWSECCLASS != :OLD.ROWSECCLASS OR
            :NEW.OPERPSWD != :OLD.OPERPSWD OR
            :NEW.ENCRYPTED != :OLD.ENCRYPTED OR
            :NEW.SYMBOLICID != :OLD.SYMBOLICID OR
            :NEW.ACCTLOCK != :OLD.ACCTLOCK OR
            :NEW.PRCSPRFLCLS != :OLD.PRCSPRFLCLS OR
            :NEW.DEFAULTNAVHP != :OLD.DEFAULTNAVHP OR
            :NEW.OPRTYPE != :OLD.OPRTYPE OR
            :NEW.USERIDALIAS != :OLD.USERIDALIAS
            THEN
 
                INSERT INTO PS_AUDIT_USERS VALUES (
                    GET_PS_OPRID(V_AUDIT_OPRID),
                    SYSDATE,
                    'K',
                    :OLD.OPRID,
                    :OLD.VERSION,
                    :OLD.OPRDEFNDESC,
                    :OLD.EMPLID,
                    :OLD.EMAILID,
                    :OLD.OPRCLASS,
                    :OLD.ROWSECCLASS,
                    :OLD.OPERPSWD,
                    :OLD.ENCRYPTED,
                    :OLD.SYMBOLICID,
                    :OLD.ACCTLOCK,
                    :OLD.PRCSPRFLCLS,
                    :OLD.DEFAULTNAVHP,
                    :OLD.OPRTYPE,
                    :OLD.USERIDALIAS
                );
 
                INSERT INTO PS_AUDIT_USERS VALUES (
                    GET_PS_OPRID(V_AUDIT_OPRID),
                    SYSDATE,
                    'N',
                    :NEW.OPRID,
                    :NEW.VERSION,
                    :NEW.OPRDEFNDESC,
                    :NEW.EMPLID,
                    :NEW.EMAILID,
                    :NEW.OPRCLASS,
                    :NEW.ROWSECCLASS,
                    :NEW.OPERPSWD,
                    :NEW.ENCRYPTED,
                    :NEW.SYMBOLICID,
                    :NEW.ACCTLOCK,
                    :NEW.PRCSPRFLCLS,
                    :NEW.DEFAULTNAVHP,
                    :NEW.OPRTYPE,
                    :NEW.USERIDALIAS
                );
 
        END IF;
 
    END IF;
 
END IF;
 
END PSOPRDEFN_TR;
/

Comments