Auditing DDL changes

Auditing changes within the database are becoming more and more important. As well as auditing changes to data, auditing DDL changes can be just as critical. This allows the DBA to not only know what changes have been made but also what haven’t. Being able to see what changes have been made to the database can make debugging much quicker.

This solution consists of two tables, one sequence and one trigger.
 
The Tables and Sequence

CREATE TABLE ddl_events
( eventId          NUMBER(10,0),
  eventDate        DATE,
  oraLoginUser     VARCHAR2(30),
  oraDictObjName   VARCHAR2(30),
  oraDictObjOwner  VARCHAR2(30),
  oraDictObjType   VARCHAR2(30),
  oraSysEvent      VARCHAR2(30),
  machine          VARCHAR2(64),
  program          VARCHAR2(64),
  osuser           VARCHAR2(30) );

CREATE TABLE ddl_events_sql
( eventId          NUMBER(10,0),
  sqlLine          NUMBER(10,0),
  sqlText          VARCHAR2(4000) );

CREATE SEQUENCE dsq_ddlEvents START WITH 1000; 

The parent table ddl_events stores data about the DDL event
•    eventId : Unique key generated by the sequence.
•    eventDate : Populated with SYSDATE.
•    oraLoginUser : The login username taken from the ORA_LOGIN_USER function.
•    oraDictObjName : The name of the event object taken from the ORA_DICT_OBJ_NAME function.
•    oraDictObjOwner : The owner of the event object taken from the ORA_DICT_OBJ_OWNER function.
•    oraDictObjType : The type of the event object taken from the ORA_DICT_OBJ_TYPE function.
•    oraSysEvent : The type of event, EG Create, Alter, Delete. Taken from the ORA_SYS_EVENT function.
•    machine : The name of the machine the event was issued from. Taken from v_$session.
•    program : The name of the program use to issue the command. Taken from v_$session.
•    osuser : The operating system user name. Taken from v_$session.

The ddl_events_sql table stores the sql text from the command. This is a child table of ddl_events with the eventId being the foreign key. EventId and sqlLine can be used to uniquely identify a record.
•    eventId : Link to ddl_events
•    sqlLine : The line number of the executed command
•    sqlText : An sql text line of the command. Taken from the ORA_SQL_TXT function.


The Trigger

CREATE OR REPLACE TRIGGER dtr_ddlEvents
AFTER DDL ON DATABASE
DECLARE

  l_eventId    NUMBER(10,0);
  l_sqlText    ORA_NAME_LIST_T;

BEGIN

  SELECT dsq_ddlEvents.NEXTVAL INTO l_eventId FROM SYS.DUAL;

  INSERT INTO ddl_events
  ( SELECT l_eventId,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+) );


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

Auditing the DDL changes is made easy by the DDL triggers. This solution uses and AFTER DDL ON DATABASE clause. This will audit all the changes made on the database. If you are just looking to audit a particular schema the AFTER DDL ON SCHEMA clause could be used instead.

The trigger inserts the data into the into the ddl_events table with data from built-in functions and the v_$session view. The outer join on the v_$session view enables the bulti-in functions to still populate for background processes.

The ddl_events_sql table is then populated by the ORA_SQL_TXT function. The loop populates the ddl_events_sql table for each line of the statement.

Variations
If auditing the database is too much, a single schema can be audited by using AFTER DDL ON SCHEMA in place of AFTER DDL ON DATABASE.

If auditing the sql text gives too much data, this code can easily be removed, or modified so that the first 4000 characters is inserted into the ddl_events table.

The sql text code above includes auditing for password changes. As the password is sensitive you may want to hide it from the log. You can do this by modifying the sql text loop as below.

   FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    IF  ORA_DICT_OBJ_TYPE = 'USER'
    AND INSTR(UPPER(l_sqlText(l)),'IDENTIFIED BY') != 0
    THEN
      l_sqlText(l) := SUBSTR(l_sqlText(l),1,INSTR(UPPER(l_sqlText(l)),'IDENTIFIED BY')+13)||'*';
    END IF;
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( l_eventId, l, l_sqlText(l) );
  END LOOP;

The additional if statement checks for user statements that contain IDENTIFIED BY clauses, the text to the right of the clause is then replaced with an asterisk.

I know auditing system can be more expensive so we can skip by adding a small clause to exclude username right after the below line.

 WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+) 
     and username not in ('SYS','SYSTEM'));

Further Reading

AskTom: Writing DDL triggers
DBAzine.com: Article on system event auditing