DML_ACTION_AUDITING.sql

CONNECT username/password as sysdba

-- CREATE USERS USED FOR TESTING
CREATE USER TestDbaUser IDENTIFIED BY 1234
DEFAULT TABLESPACE USERS
Temporary TABLESPACE TEMP
Quota 25m on users
profile default
account unlock;

GRANT CREATE ANY VIEW TO TestDbaUser;
GRANT CREATE SESSION TO TestDbaUser;
GRANT CREATE ANY SEQUENCE TO TestDbaUser;
GRANT CREATE ANY TABLE TO TestDbaUser;
GRANT CREATE ANY TRIGGER TO TestDbaUser;

CREATE USER TestDbaUser2 IDENTIFIED BY 1234
DEFAULT TABLESPACE USERS
Temporary TABLESPACE TEMP
Quota 25m on users
profile default
account unlock;

GRANT CREATE SESSION TO TestDbaUser2;
GRANT SELECT ANY TABLE TO TestDbaUser2;
GRANT UPDATE ANY TABLE TO TestDbaUser2;
GRANT INSERT ANY TABLE TO TestDbaUser2;
GRANT DELETE ANY TABLE TO TestDbaUser2;

-- LOGIN AS TESTDBAUSER
DISCONNECT;
CONNECT TestDbaUser/1234


-- CREATE THE SAMPLE TABLE THAT YOU WANT TO AUDIT
CREATE TABLE DEPARTMENTS
  (
	DEPARTMENT_ID NUMBER(2) PRIMARY KEY,
	DEPARTMENT_NAME VARCHAR2(20),
	CITY            VARCHAR2(30),
	STATE           CHAR(2)
  );

  
-- CREATE THE AUDITING TABLE
-- NOTES ON TABLE FIELDS:
-- AUDIT_OBJECT (THE OBJECT OR TABLE YOU WANT TO VERIFY/AUDIT)
-- AUDIT_OPERATION (THE ACTION THAT OCCURED ON THE AUDITED TABLE. EX: USER PERFORMED AN INSERT OPERATION)
-- AUD_INS_DTTM ("CONTROL INSERT DATETIME" / TIME WHEN THE ACTION OCCURED)
-- AUD_UPD_USER ("AUDIT UPDATE USER" THE USER THAT PERFORMED THE ACTION)
-- AUD_REC_STAT ("AUDIT RECORD STATUS" THE STATUS OF A RECORD AS "ACTIVE" OR "INACTIVE". A RECORD CAN BE FLAGGED AS...
-- ...INACTIVE IF IT BECOMES OLD AND YOU DON'T WANT TO INCLUDE IT IN THE AUDIT REPORT)

CREATE TABLE APP_AUDIT_DATA
  (
	AUDIT_DATA_ID NUMBER PRIMARY KEY,
        AUDIT_OBJECT  VARCHAR2(30),
        AUDIT_OPERATION VARCHAR2(20),
        AUD_INS_DTTM    DATE,
        AUD_UPD_USER    VARCHAR2(30),
        AUD_REC_STAT    VARCHAR2(1)
   );
   
   
-- CREATE A SEQUENCE (WHICH WILL FILL AN IDENTITY PRIMARY KEY COLUMN IN THE APP_AUDIT_DATA TABLE)
-- UNFORTUNATELY, ORACLE DOES NOT HAVE 'IDENTITY 1,1' LIKE SQL SERVER.

CREATE SEQUENCE SEQ_APP_AUDIT_DATA
INCREMENT BY 1
START WITH 1
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

-- NEXT, USE THE SEQUENCE IN THE TRIGGER BELOW.
-- THIS TRIGGER WILL START AFTER IT DETECTS A DML ACTION ON THE DEPARTMENTS TABLE.
-- IT WILL SAVE THE ACTION TYPE (INSERT, UPDATE, DELETE) ON THE V_OPR VARIABLE...
-- AND PLACE IT IN THE INSERT STATEMENT FOR THE APP_AUDIT_DATA TABLE.

CREATE OR REPLACE TRIGGER TRG_DEPARTMENT_AIUD
     AFTER INSERT OR UPDATE OR DELETE
     ON DEPARTMENTS
  DECLARE
	V_OPR VARCHAR2(20);
  BEGIN
	IF INSERTING THEN
		V_OPR := 'INSERT';
	ELSIF UPDATING THEN
		V_OPR := 'UPDATE';
	ELSE
		V_OPR := 'DELETE';
	END IF;
	
	INSERT INTO APP_AUDIT_DATA (AUDIT_DATA_ID, AUDIT_OBJECT, AUDIT_OPERATION, AUD_INS_DTTM, AUD_UPD_USER, AUD_REC_STAT) 
	VALUES ( SEQ_APP_AUDIT_DATA.NEXTVAL,'DEPARTMENTS', V_OPR, SYSDATE, USER,'A');
	EXCEPTION WHEN OTHERS THEN
   
	NULL;
-- IF YOU ARE USING SQL COMMAND LINE, PLEASE ADD THE "/" TO THE END OF THE LOOP OR IT WILL NOT RUN.
	
END;
/


-- NOW TEST YOUR AUDITING PROCESS. LOG INTO ANOTHER ACCOUNT (TESTDBAUSER2) AND INSERT INTO THE DATABASE.
-- REFER TO THE "TESTDBAUSER" SCHEMA OR YOU WONT BE ABLE TO INSERT BECAUSE YOU ARE LOGGED IN WITH "TESTDBAUSER2"
DISCONNECT;
CONNECT TestDbaUser2/1234

INSERT INTO TestDbaUser.DEPARTMENTS ( DEPARTMENT_ID, DEPARTMENT_NAME, CITY,STATE) VALUES  ( 10, 'Accounting', 'Boston', 'NY');
INSERT INTO TestDbaUser.DEPARTMENTS ( DEPARTMENT_ID, DEPARTMENT_NAME, CITY,STATE) VALUES  ( 11, 'Proudction', 'Redlands', 'CA');

UPDATE TestDbaUser.DEPARTMENTS SET CITY = 'Dallas', STATE= 'TX' WHERE DEPARTMENT_ID =10;
DELETE FROM TestDbaUser.DEPARTMENTS WHERE DEPARTMENT_ID = 11;
Commit;

-- LOG BACK INTO TESTDBAUSER TO CHECK THE ACTIONS RECORDED IN YOUR AUDITING TABLE (TIP: USE THE COLUMN COMMAND TO FORMAT THE RESULTS)
-- BECAUSE COMMAND LINE RESULTS ARE HARD TO READ.
DISCONNECT;
CONNECT TestDbaUser/1234

set lines 256
--set trimout on
--set tab off

COLUMN AUDIT_DATA_ID HEADING 'ID'
COLUMN AUDIT_OBJECT HEADING 'AUDITED OBJECT'
COLUMN AUDIT_OPERATION HEADING 'OPERATION'
COLUMN AUD_INS_DTTM HEADING 'DATE'
COLUMN AUD_UPD_USER HEADING 'USER'
COLUMN AUD_REC_STAT HEADING 'RECORD STATUS'

SELECT * FROM APP_AUDIT_DATA;