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 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;
• 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.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;
/
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.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;
*****
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'));
Reference:
http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11090
