Wednesday, February 10, 2016

AUDIT DDLS in database with trigger



-- Simple trigger to audit to audit basic schema changes :

--- CREATE TABLE TO STORE AUDIT DATA

CREATE TABLE DDL_AUDIT_LOG
(
  STAMP DATE
, USERNAME VARCHAR2(30 BYTE)
, OSUSER VARCHAR2(30 BYTE)
, MACHINE VARCHAR2(30 BYTE)
, TERMINAL VARCHAR2(30 BYTE)
, OPERATION VARCHAR2(30 BYTE)
, OBJTYPE VARCHAR2(30 BYTE)
, OBJNAME VARCHAR2(30 BYTE)
, OBJ_OWNER VARCHAR2(30 BYTE)
) TABLESPACE USERS ;

-- NOW CREATE TRIGGER TO AUDIT CHANGES

ALTER TRIGGER AUDIT_DDL_CHANGES DISABLECREATE TRIGGER AUDIT_DDL_CHANGES
   AFTER create OR drop OR alter
      ON ATOORPU.SCHEMA  -- Change SCOTT to your schema name!!!
      -- ON DATABASE
BEGIN
  INSERT INTO ddl_audit_log VALUES
        (SYSDATE,
         SYS_CONTEXT('USERENV', 'SESSION_USER'),
         SYS_CONTEXT('USERENV', 'OS_USER'),
         SYS_CONTEXT('USERENV', 'HOST'),
         SYS_CONTEXT('USERENV', 'TERMINAL'),
         ORA_SYSEVENT,
         ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME,
         ORA_DICT_OBJ_OWNER
        );
END;

Sample output :



Sample Audit Table Output





No comments:

Post a Comment