Oracle DDL Trigger on Database

Jephe Wu - http://linuxtechres.blogspot.com

Objective: create DDL trigger on database to audit all DDL statement.
Environment: CentOS 5.5 64bit, Oracle 11g 64bit


Steps:
1. create a separate tablespace and user called audittrail
2. create trigger on database, record all ddl log to audittrail user ddl_log table.

Trigger: 
1. filter _TMP table in JEPHE schema
2. create the following trigger in sys schema
CREATE TABLE audittrail.DDL_LOG
(
  USER_NAME    VARCHAR2(30 BYTE),
  DDL_DATE     DATE,
  DDL_TYPE     VARCHAR2(30 BYTE),
  OBJECT_TYPE  VARCHAR2(25 BYTE),
  OWNER        VARCHAR2(30 BYTE),
  OBJECT_NAME  VARCHAR2(128 BYTE),
  CLIENT_USER  VARCHAR2(40 BYTE),
  MACHINE      VARCHAR2(40 BYTE)
);

CREATE OR REPLACE TRIGGER DDL_LOG AFTER DDL ON DATABASE
DECLARE
MACHINE VARCHAR2(400);
OSUSER VARCHAR2(40);
IPINFO VARCHAR2(30);
IP1 VARCHAR2(30);
NO1 NUMBER(1);
erno NUMBER;
ermsg VARCHAR2(1500);
USER_INFO VARCHAR2(50);
SIDINFO VARCHAR2(50);
Host_name VARCHAR2(50);
USER_NAME VARCHAR2(25);
MACHINE_NAME VARCHAR2(40);
BEGIN
SELECT sys_context('USERENV','IP_ADDRESS') INTO IPINFO FROM DUAL;
SELECT SYS_CONTEXT('USERENV','TERMINAL') INTO MACHINE FROM DUAL;
SELECT sys_context('USERENV','OS_USER') INTO OSUSER FROM DUAL;

ermsg:=SUBSTR(ora_dict_obj_name,LENGTH(ora_dict_obj_name)-3,LENGTH(ora_dict_obj_name));
if  ora_dict_obj_owner= 'JEPHE'  AND upper(ermsg) = '_TMP' 
then
null;
else
INSERT INTO audittrail.ddl_log VALUES(ora_login_user,SYSDATE,ora_sysevent,ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name,OSUSER,MACHINE);
END IF;
END;
/