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