DDL Audit in Oracle

DDL Audit in Oracle

---Create one table  for audit records

CREATE TABLE "STATS$DDL_LOG"
   (    "USER_NAME" VARCHAR2(30),
        "DDL_DATE" DATE,
        "DDL_TYPE" VARCHAR2(30),
        "OBJECT_TYPE" VARCHAR2(18),
        "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        IP_ADDRESS VARCHAR2(15),
        MACHINE VARCHAR2(125)
   );
 

-- The following trigger audits the username and ip address and machine from which the DDL is issued

 
create or replace trigger DDLTrigger
 AFTER DDL ON DATABASE
 BEGIN
 if ora_login_user<>ora_dict_obj_name then
 insert into
 stats$ddl_log
 (
 user_name,
 ddl_date,
 ddl_type,
 object_type,
 owner,
 object_name,
 ip_address,
 machine
 )
 VALUES
 (
 ora_login_user,
 sysdate,
 ora_sysevent,
 ora_dict_obj_type,
 ora_dict_obj_owner,
 ora_dict_obj_name,
 sys_context('USERENV','IP_ADDRESS'),
 SYS_CONTEXT('USERENV','HOST')
 );
 end if;
 END;
 /


-- Monitoring the audit records

column ddl_type format a10

column user_name format a10

column object_type format a20

column owner format a10

column object_name format a25

select        user_name,

       to_char(ddl_date, 'mm/dd/yy hh:mi am'),

       ddl_type,

       object_type,

       owner,

       object_name,

       ip_address,

       machine

from stats$ddl_log

where trunc(ddl_date)>=trunc(sysdate-1);

clear columns