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