Perils of DataMover Access – Part 2a

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.

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

3.  Add a New Value and select Record Name PSAUTHITEM

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

5.  Check all the audit options.

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

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;

/
Read more about DataMoverAccess.