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;
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
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
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
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.
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.
- 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.
