Perils of DataMover Access– Part 2a

PeopleSoft provides trigger-based auditing functionality as an alternative to the record-based auditing that PeopleSoft Application Designer provides. Perform the following steps to setup trigger based auditing for PSAUTHITEM.

1.  Create a custom table to store the audit data for PSAUTHITEM. And build the record in the database.
datamover2a1

2.  Navigate to PeopleTools –> Utilities –> Audit –> Update Database Level Auditing.

datamover2a2

3.  Add a New Value and select Record Name PSAUTHITEM
datamover2a3

4.  Select the record AUDIT_AUTHITEM (we created in step 1) as the Audit Record.
datamover2a4

5.  Check all the audit options.

6.  Click on Generate Code button. This will generate the SQL for creating the trigger..

datamover2a5

7.   Modify the script as below to include the MENUNAME.

CREATE OR REPLACE TRIGGER PSAUTHITEM_TR

8.  Ensure that the GET_PS_OPRID function exists. Copy the SQL and execute it in SQLPLUS .

Now we are ready to audit any changes to PSAUTHITEM.
Summary
In my next post, I will demonstrate a test scenario and also provide insights into monitoring the audit results.

AFTER INSERT OR UPDATE OR DELETE ON PSAUTHITEM

FOR EACH ROW

DECLARE

V_AUDIT_OPRID VARCHAR2(64);

BEGIN

DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);

IF INSERTING

THEN

INSERT INTO PS_AUDIT_AUTHITEM

VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,’A’,:NEW.CLASSID,

:NEW.MENUNAME,:NEW.BARITEMNAME,:NEW.PNLITEMNAME,:
NEW.DISPLAYONLY,

:NEW.AUTHORIZEDACTIONS);

ELSE

IF DELETING

THEN

INSERT INTO PS_AUDIT_AUTHITEM

VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,’D’,:OLD.CLASSID,

:OLD.MENUNAME,:OLD.BARITEMNAME,:OLD.PNLITEMNAME,:
OLD.DISPLAYONLY,

:OLD.AUTHORIZEDACTIONS);

ELSE

INSERT INTO PS_AUDIT_AUTHITEM

VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,’K’,:OLD.CLASSID,

:OLD.MENUNAME,:OLD.BARITEMNAME,:OLD.PNLITEMNAME,:
OLD.DISPLAYONLY,

:OLD.AUTHORIZEDACTIONS);

INSERT INTO PS_AUDIT_AUTHITEM

VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,’N’,:NEW.CLASSID,

:NEW.MENUNAME,:NEW.BARITEMNAME,:NEW.PNLITEMNAME,:
NEW.DISPLAYONLY,

:NEW.AUTHORIZEDACTIONS);

END IF;

END IF;

END PSAUTHITEM_TR;

/

Posted by Nitin Pai
Comments (2)
June 5th, 2007

Comments (2)

Nitin Pai - June 7th, 2007

Charles - Thanks for your comments! I have responded to your comments by creating a new post titled 'How to recreate Audit triggers when performing security restores/imports?'. It has instructions on how to copy the triggers. Thanks Nitin

Charles - June 7th, 2007

Nitin, In your example for PSAUTHITEM, does the trigger gets stored as part of the tools? In other words, if I run security export from this instance to another one, will the trigger get migrated too or should I run the create trigger SQL in the target system?