DDL Trigger to audit schema changes

  1. 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;
  2. 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;
  3. 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;
  4. 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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: