Schema DDL Audit

Sometimes you might need to check who is messing up with your DB 🙂 You can either turn on the AUDIT TRAIL or, if you are crafty, you might wanna look into the solution below 🙂

-- create a target table first
CREATE TABLE schema_audit_ddl (
 timestamp_ddl DATE,
 session_user VARCHAR2(50),
 proxy_user VARCHAR2(50),
 os_user VARCHAR2(255),
 host_name VARCHAR2(50),
 obj_type VARCHAR2(50),
 obj_name VARCHAR2(50),
 ddl_type VARCHAR2(50)
);


-- trigger
CREATE OR REPLACE TRIGGER schema_audit_ddl
   AFTER DDL ON SCHEMA
BEGIN
   INSERT INTO ddl_audit
   VALUES
      (sysdate,
       sys_context('USERENV', 'SESSION_USER'),
       sys_context('USERENV', 'PROXY_USER'),
       sys_context('USERENV', 'OS_USER'),
       sys_context('USERENV', 'HOST'),
       ora_dict_obj_type,
       ora_dict_obj_name,
       ora_sysevent);
END;      

Please remember that it applies to an active schema only (the one the trigger is created in).

_{To get all possible parameters check SYS_CONTEXT manual page
To get all possible event attributes functions check PL/SQL Triggers manual page}