Perils of DataMover Access – 3

The story is a bit different in a non-production environment. Depending on your organization change control policies, developers might have Data Mover Access in non-production. In this case, we might want to prevent the OPRID from exploiting Data Mover Access to perform undesired DDL on the database.

To tackle this problem, you can create a DDL trigger as shown below. This will ensure that no DDL operations are performed from Data Mover.

CREATE OR REPLACE TRIGGER DATAMOVER_PREVENT_DDL

BEFORE CREATE OR ALTER OR DROP OR GRANT OR RENAME OR REVOKE ON SCHEMA

DECLARE

VAR_DDLEVENT VARCHAR2(25);

VAR_OBJ_NAME VARCHAR2(128);

V_AUDIT_OPRID VARCHAR2(32);

BEGIN

DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);

SELECT ORA_SYSEVENT, ORA_DICT_OBJ_NAME, GET_PS_OPRID(V_AUDIT_OPRID)

INTO VAR_DDLEVENT, VAR_OBJ_NAME, V_AUDIT_OPRID FROM DUAL;

IF ( VAR_DDLEVENT IN (‘CREATE’,'ALTER’,'DROP’, ‘GRANT’, ‘RENAME’, ‘REVOKE’) AND V_AUDIT_OPRID != ‘!NoOPRID’)

THEN

RAISE_APPLICATION_ERROR(-20001,’**** THIS OPERATION IS NOT ALLOWED ****’);

END IF;

END;

Now, if the developer with Data Mover Access tries to grant his Oracle id DBA access then he will get the below message.

Conclusion
We have seen how dangerous Data Mover Access can be if controls are not in place. This access is often overlooked and can have serious implications. The best approach will need to start with cleaning the privileges assigned to the access id. The PeopleSoft access id should have only the required access. Do not go overboard and assign DBA role to the access id.

Perils of DataMover Access – Part 2b

 Continuing from my previous post – “Perils of DataMover Access – Part 2a“

Let’slook at a scenario where the security was modified to enable Data Mover Access.

The above results show that oprid NPAI ‘Added’ the security to enable DATA MOVER Access.

It is possible that the OPRID’s who by-passed controls to modify Data Mover Access are smart enough to also delete the rows in the audit table using Datamover. This might go undetected unless you have some additional monitoring in place.

Ideally, you might want to create a trigger to fire on any INSERT into sensitive audit records. The trigger should call a procedure which will send an email notification for alerting the group managing the sensitivity of the environment.

CREATE TRIGGER PSAUTHITEM_AUDIT_EMAIL
AFTER INSERT ON PS_AUDIT_AUTHITEM FOR EACH ROW
DECLARE
VAR_JOB_ID NUMBER;
BEGIN
DBMS_JOB.SUBMIT(VAR_JOB_ID, ‘SEND_EMAIL( JOB );’ );
INSERT INTO PS_AUDIT_EMAIL VALUES (VAR_JOB_ID, :NEW.AUDIT_OPRID, :NEW.AUDIT_STAMP, :NEW.AUDIT_ACTN );
END;

Alternatively, we can create a trigger to prevent execution of any DELETE or DROP sql on the audit table.

• Prevent delete

CREATE OR REPLACE TRIGGER PSAUTHITEM_AUDIT_PREVENT_DEL
AFTER DELETE ON PS_AUDIT_AUTHITEM FOR EACH ROW
BEGIN

RAISE_APPLICATION_ERROR(-20001,’**** DELETE NOT ALLOWED ****’);

END;

• Prevent DROP or TRUNCATE

CREATE OR REPLACE TRIGGER PSAUTHITEM_AUDIT_PREVENT_DDL
BEFORE DROP or TRUNCATE ON SCHEMA
DECLARE
VAR_DDLEVENT VARCHAR2(25);
VAR_OBJ_NAME VARCHAR2(128);
BEGIN

SELECT ORA_SYSEVENT, ORA_DICT_OBJ_NAME INTO VAR_DDLEVENT, VAR_OBJ_NAME FROM DUAL;

IF ( VAR_DDLEVENT IN (‘DROP’, ‘TRUNCATE’) AND VAR_OBJ_NAME IN (‘PS_AUDIT_AUTHITEM’, ‘PSAUTHITEM_AUDIT_PREVENT_DEL’))
THEN

RAISE_APPLICATION_ERROR(-20001,’**** DROP OR TRUNCATE NOT ALLOWED ****’);

END IF;
END;
Here is what you get when you try to drop the table or delete rows from data mover.

Note:
- Truncate is not a valid command in DataMover.
- You can also consider creating the DDL event trigger in another schema to fire on the DATABASE level instead of SCHEMA. This will ensure that the OPRID cannot DROP the trigger itself from DataMover.

How to recreate Audit triggers when performing security restores/imports?

In response to Charles’ comments (shown below), I thought it will be nice to respond with a post which provides detailed instruction.

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?”

Here are the steps.

1. When you generate the trigger SQL (step 6 in my previous post), modify the SQL (as shown in step 7 in previous post) and click on the Save button as shown below

2. This will create an entry in PSTRIGGERDEFN

Now, every time you copy/retain security. Do the following additional task
a. Go to PeopleTools —>Utilities —>Audit —>Perform Database Level Audit

b. Create a new run control id

c. Select checkbox Create all Triggers

d. Run the process 

4. The AE program will create a trgcode1.sql file in $PS_SRVRDIR. You can check the trace file and search for this file name.

5. Run this SQL to recreate the triggers.

Read more about  performing security restores/imports.

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.