DDL Trigger to audit schema changes
February 26, 2016 Leave a comment
- Create sequence for assigning numbers to the events:
-- Create sequence
create sequence SYS.DSQ_DDLEVENTS minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 cache 100;
- Create ddl events table
-- Create table
create table SYS.DDL_EVENTS ( eventid NUMBER, inst_id NUMBER, eventdate DATE, oraloginuser VARCHAR2(100), oradictobjname VARCHAR2(100), oradictobjowner VARCHAR2(100), oradictobjtype VARCHAR2(100), orasysevent VARCHAR2(100), machine VARCHAR2(100), program VARCHAR2(100), osuser VARCHAR2(100) ) tablespace USERS;
- Create table for saving SQL statements(this is necessary because triggered sql statements may have several lines)
-- Create table
create table SYS.DDL_EVENTS_SQL ( eventid NUMBER, sqlline NUMBER, sqltext VARCHAR2(4000) ) tablespace USERS;
- Create DDL trigger(it doesn’t degrade performance at all, by my experience)
CREATE OR REPLACE TRIGGER sys.audit_ddl_trg AFTER DDL ON DATABASE DECLARE l_eventId NUMBER; l_sqlText ORA_NAME_LIST_T; BEGIN if ORA_SYSEVENT!='TRUNCATE' then SELECT dsq_ddlEvents.NEXTVAL INTO l_eventId FROM SYS.DUAL; INSERT INTO ddl_events (eventId, inst_id, EVENTDATE, ORALOGINUSER, ORADICTOBJNAME, ORADICTOBJOWNER, ORADICTOBJTYPE, ORASYSEVENT, machine, program, osuser ) ( SELECT l_eventId, inst_id, SYSDATE, ORA_LOGIN_USER, ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_TYPE, ORA_SYSEVENT, machine, program, osuser FROM SYS.DUAL right outer join SYS.GV$SESSION s on (1=1) WHERE s.sid=SYS_CONTEXT('USERENV','SID') and SYS_CONTEXT('USERENV','INSTANCE')=s.inst_id); FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP INSERT INTO ddl_events_sql ( eventId, sqlLine, sqlText ) VALUES ( l_eventId, l, l_sqlText(l) ); END LOOP; end if; exception when others then null; END;