Database Auditing (DDL & DML)

Today I want to share some simple trigger to set the database wise auditing.

Method1:

CREATE TABLE ddl_audit_log (
   stamp     DATE,
   username  VARCHAR2(30),
   osuser    VARCHAR2(30),
   machine   VARCHAR2(30),
   terminal  VARCHAR2(30),
   operation VARCHAR2(30),
   objtype   VARCHAR2(30),
   objname   VARCHAR2(30))
/

CREATE OR REPLACE TRIGGER fkhalid.audit_ddl_changes
   AFTER create OR drop OR alter
      ON FKHALID.SCHEMA  -- for individual schema auditing
      -- ON DATABASE     -- for whole database auditing
BEGIN
  INSERT INTO fkhalid.ddl_audit_log VALUES
        (SYSDATE,
         SYS_CONTEXT('USERENV', 'SESSION_USER')||' FOR '||sys.dictionary_obj_owner,
         SYS_CONTEXT('USERENV', 'OS_USER'),
         SYS_CONTEXT('USERENV', 'HOST'),
         SYS_CONTEXT('USERENV', 'TERMINAL'),
         ORA_SYSEVENT,
         ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME
        );
END;
/
show errors

-- Now, let's try that
CREATE TABLE my_test_table (col1 DATE)
/
DROP TABLE my_test_table purge
/
SELECT * FROM ddl_audit_log
/

Method 2:

CREATE TABLE FKHALID.EVENT_TABLE
(
  OBJECT_OWNER  VARCHAR2(100 BYTE),
  OBJECT_NAME   VARCHAR2(30 BYTE),
  OBJECT_TYPE   VARCHAR2(20 BYTE),
  DATE_CREATED  DATE,
  DDL_TYPE      varchar2(50)
);

create or replace trigger fkhalid.after_create_trg
  after create
   on fkhalid.schema
begin
  /* This trigger tracks the creation of table, index, sequence,
      procedure, function, package and type. */
   if sys.dictionary_obj_type='TABLE'
   OR sys.dictionary_obj_type='SEQUENCE'
    OR sys.dictionary_obj_type='PROCEDURE'
     OR sys.dictionary_obj_type='INDEX'
      OR sys.dictionary_obj_type='FUNCTION'
       OR sys.dictionary_obj_type='TYPE'
        OR sys.dictionary_obj_type='PACKAGE'
    then
     insert into fkhalid.event_table values
          (sys.dictionary_obj_owner,sys.dictionary_obj_name, sys.dictionary_obj_type, sysdate,'CREATE');
   end if;
end;
/

create or replace trigger fkhalid.after_alter_trg
  after alter
   on fkhalid.schema
begin
  /* This trigger tracks the creation of table, index, sequence,
      procedure, function, package and type. */
   if sys.dictionary_obj_type='TABLE'
   OR sys.dictionary_obj_type='SEQUENCE'
    OR sys.dictionary_obj_type='PROCEDURE'
     OR sys.dictionary_obj_type='INDEX'
      OR sys.dictionary_obj_type='FUNCTION'
       OR sys.dictionary_obj_type='TYPE'
        OR sys.dictionary_obj_type='PACKAGE'
    then
     insert into fkhalid.event_table values
          (sys.dictionary_obj_owner,sys.dictionary_obj_name, sys.dictionary_obj_type, sysdate,'ALTER');
   end if;
end;
/

alter table test_table2
modify (ename varchar2(100),enumber number);

create table test_table2
(ename varchar2(20),enumber number);

Method 3:

SHOW PARAMETER AUDIT
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SHUTDOWN
STARTUP

-----------------------------------------------------
CREATE USER audit_test IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp1
  QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;
AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
-----------------------------------------------------
CREATE TABLE test_tab (
  id  NUMBER
);

INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;

DROP TABLE test_tab;
-----------------------------------------------------

SELECT username,
    extended_timestamp,
        owner,
        obj_name,
        action_name
FROM    dba_audit_trail
WHERE   owner = 'audit_test'
ORDER BY timestamp;

Hope this will help
Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!