Table Auditing Using DML Triggers In Oracle PL/SQL
Step-1: Connect system & grant those privileges to a user(hr)
grant dba,select any dictionary,select_catalog_role to hr;
Step-2: connect the user(hr)
Step-3: Create 2 Table in this user. Like-
CREATE TABLE LOGGING_DATA_HDR
(
LOG_ID NUMBER,
TABLE_NAME VARCHAR2 (30 CHAR) NOT NULL,
PK_DATA VARCHAR2 (500 BYTE),
ROW_ID ROWID NOT NULL,
LOG_DATE DATE NOT NULL,
OPERATION_TYPE VARCHAR2 (1 BYTE) NOT NULL,
DB_USER VARCHAR2 (100 BYTE),
CLIENT_IP VARCHAR2 (40 BYTE),
CLIENT_HOST VARCHAR2 (100 BYTE),
CLIENT_OS_USER VARCHAR2 (100 BYTE),
APP_USER VARCHAR2 (50 BYTE)
);
ALTER TABLE LOGGING_DATA_HDR ADD (
CONSTRAINT LOGGING_DATA_HDR_PK
PRIMARY KEY
(LOG_ID)
);
CREATE TABLE LOGGING_DATA_DTL
(
LOG_ID NUMBER,
COLUMN_NAME VARCHAR2 (30 CHAR),
OLD_VALUE VARCHAR2 (4000 BYTE),
NEW_VALUE VARCHAR2 (4000 BYTE)
);
Step-4: Alter the LOGGING_DATA_DTL table
ALTER TABLE LOGGING_DATA_DTL ADD (
CONSTRAINT LOGGING_DATA_DTL_PK
PRIMARY KEY
(LOG_ID, COLUMN_NAME));
Step-5: Create this sequence
CREATE SEQUENCE LOG_ID_SEQ
START WITH 1
MAXVALUE 99999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
Step-6: Create this package
CREATE OR REPLACE PACKAGE DML_LOG
AS
TYPE GT$LOGGING_DATA_DTL IS TABLE OF LOGGING_DATA_DTL%ROWTYPE;
GC$APP_USER LOGGING_DATA_HDR.APP_USER%TYPE;
PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
IN_TABLE_NAME VARCHAR2,
IN_ROWID ROWID,
IN_OPERATION_TYPE VARCHAR2);
FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
RETURN VARCHAR2;
FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
IN_ROWID ROWID,
IN_DELIMETER VARCHAR2 DEFAULT '-')
RETURN VARCHAR2;
PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE);
FUNCTION GET_APP_USER
RETURN LOGGING_DATA_HDR.APP_USER%TYPE;
END DML_LOG;
/
Step-7: Create this package body
CREATE OR REPLACE PACKAGE BODY DML_LOG
AS
PROCEDURE ADD_LOG (IN_ARRAY IN GT$LOGGING_DATA_DTL,
IN_TABLE_NAME VARCHAR2,
IN_ROWID ROWID,
IN_OPERATION_TYPE VARCHAR2)
IS
LN$LOG_ID LOGGING_DATA_HDR.LOG_ID%TYPE;
BEGIN
SELECT LOG_ID_SEQ.NEXTVAL INTO LN$LOG_ID FROM DUAL;
INSERT INTO LOGGING_DATA_HDR (LOG_ID,
TABLE_NAME,
PK_DATA,
ROW_ID,
LOG_DATE,
OPERATION_TYPE,
DB_USER,
CLIENT_IP,
CLIENT_HOST,
CLIENT_OS_USER,
APP_USER)
VALUES (LN$LOG_ID,
IN_TABLE_NAME,
GET_COMPOSITE_KEY (IN_TABLE_NAME, IN_ROWID),
IN_ROWID,
SYSDATE,
IN_OPERATION_TYPE,
SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
SYS_CONTEXT ('USERENV', 'ip_address'),
SYS_CONTEXT ('USERENV', 'host'),
SYS_CONTEXT ('USERENV', 'os_user'),
GET_APP_USER);
IF IN_ARRAY IS NOT NULL AND IN_ARRAY.COUNT > 0
THEN
FOR INDX IN IN_ARRAY.FIRST .. IN_ARRAY.LAST
LOOP
IF IN_ARRAY (INDX).COLUMN_NAME IS NOT NULL
AND (IN_ARRAY (INDX).OLD_VALUE IS NOT NULL
OR IN_ARRAY (INDX).NEW_VALUE IS NOT NULL)
THEN
INSERT INTO LOGGING_DATA_DTL (LOG_ID,
COLUMN_NAME,
OLD_VALUE,
NEW_VALUE)
VALUES (LN$LOG_ID,
IN_ARRAY (INDX).COLUMN_NAME,
IN_ARRAY (INDX).OLD_VALUE,
IN_ARRAY (INDX).NEW_VALUE);
END IF;
END LOOP;
END IF;
END ADD_LOG;
FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)
RETURN VARCHAR2
IS
LC$TRIGGER_STMT VARCHAR2 (4000);
CURSOR LCUR$COLUMNS
IS
SELECT COLUMN_NAME
FROM USER_TAB_COLS
WHERE TABLE_NAME = IN_TABLE_NAME
ORDER BY COLUMN_ID;
BEGIN
LC$TRIGGER_STMT :=
'CREATE OR REPLACE TRIGGER '
|| SUBSTR (IN_TABLE_NAME, 1, 23)
|| '_LOGTRG '
|| CHR (10)
|| 'AFTER INSERT OR UPDATE OR DELETE'
|| CHR (10)
|| 'ON '
|| IN_TABLE_NAME
|| ' FOR EACH ROW '
|| CHR (10)
|| 'DECLARE '
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL DML_LOG.GT$LOGGING_DATA_DTL;'
|| CHR (10)
|| 'LC$OPERATION VARCHAR2 (1);'
|| CHR (10)
|| 'PROCEDURE ADD_ELEMENT ('
|| CHR (10)
|| 'IN_OPERATION VARCHAR2,'
|| CHR (10)
|| 'IN_COLUMN_NAME LOGGING_DATA_DTL.COLUMN_NAME%TYPE,'
|| CHR (10)
|| 'IN_OLD_VALUE LOGGING_DATA_DTL.OLD_VALUE%TYPE,'
|| CHR (10)
|| 'IN_NEW_VALUE LOGGING_DATA_DTL.NEW_VALUE%TYPE)'
|| CHR (10)
|| 'IS'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL LOGGING_DATA_DTL%ROWTYPE;'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| ' IF NOT (IN_OPERATION = ''U'' AND IN_NEW_VALUE = IN_OLD_VALUE)'
|| CHR (10)
|| 'THEN'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.OLD_VALUE :=IN_OLD_VALUE;'
|| CHR (10)
|| 'LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL.EXTEND;'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;'
|| CHR (10)
|| 'END IF;'
|| CHR (10)
|| 'END ADD_ELEMENT;'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| 'LT$LOGGING_DATA_DTL := DML_LOG.GT$LOGGING_DATA_DTL ();'
|| CHR (10)
|| 'LC$OPERATION :='
|| CHR (10)
|| 'CASE WHEN INSERTING THEN ''I'' WHEN UPDATING THEN ''U'' ELSE ''D'' END;'
|| CHR (10);
FOR LREC$COLUMNS IN LCUR$COLUMNS
LOOP
LC$TRIGGER_STMT :=
LC$TRIGGER_STMT
|| ' ADD_ELEMENT (LC$OPERATION,'''
|| LREC$COLUMNS.COLUMN_NAME
|| ''',:OLD.'
|| LREC$COLUMNS.COLUMN_NAME
|| ',:NEW.'
|| LREC$COLUMNS.COLUMN_NAME
|| ');'
|| CHR (10);
END LOOP;
LC$TRIGGER_STMT :=
LC$TRIGGER_STMT
|| ' DML_LOG.ADD_LOG (LT$LOGGING_DATA_DTL,'''
|| IN_TABLE_NAME
|| ''',:NEW.ROWID,LC$OPERATION);'
|| CHR (10)
|| 'END '
|| SUBSTR (IN_TABLE_NAME, 1, 23)
|| '_LOGTRG ;';
RETURN LC$TRIGGER_STMT;
END GENERATE_TRIGGER;
FUNCTION GET_COMPOSITE_KEY (IN_TABLE VARCHAR2,
IN_ROWID ROWID,
IN_DELIMETER VARCHAR2 DEFAULT '-')
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
LC$COLUMNS VARCHAR2 (512) := '';
LC$KEY VARCHAR2 (512);
CURSOR LCUR$COLUMNS (
IN_TABLE_NAME VARCHAR2)
IS
SELECT CON_C.COLUMN_NAME
FROM USER_CONS_COLUMNS CON_C, USER_CONSTRAINTS CON
WHERE CON.CONSTRAINT_NAME = CON_C.CONSTRAINT_NAME
AND CON.CONSTRAINT_TYPE = 'P'
AND CON.TABLE_NAME = IN_TABLE_NAME
ORDER BY POSITION;
BEGIN
FOR LREC$COLUMNS IN LCUR$COLUMNS (IN_TABLE)
LOOP
LC$COLUMNS :=
LC$COLUMNS
|| LREC$COLUMNS.COLUMN_NAME
|| '||'''
|| IN_DELIMETER
|| '''||';
END LOOP;
LC$COLUMNS := RTRIM (LC$COLUMNS, '||''' || IN_DELIMETER || '''||');
EXECUTE IMMEDIATE
'SELECT '
|| LC$COLUMNS
|| ' FROM '
|| IN_TABLE
|| ' WHERE ROWID='''
|| IN_ROWID
|| ''''
INTO LC$KEY;
RETURN LC$KEY;
END GET_COMPOSITE_KEY;
PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE)
IS
BEGIN
GC$APP_USER := IN_APP_USER;
END SET_APP_USER;
FUNCTION GET_APP_USER
RETURN LOGGING_DATA_HDR.APP_USER%TYPE
IS
BEGIN
RETURN GC$APP_USER;
END GET_APP_USER;
END DML_LOG;
/
Step-8: Then create this trigger to generate automatically log register trigger.
set serveroutput on
BEGIN
DBMS_OUTPUT.PUT_LINE (DML_LOG.GENERATE_TRIGGER ('DEPARTMENTS'));
END;
/
Step-9: Copy the trigger and paste it in SQL command line for creating this required trigger.
Step-10: After that generate a DML statement.
Step-12: Now you can tracking the users when they have connected the schema and what change they made.
snowghoul
kaledo-art
gummygunk
titsay
kianamaiart
andulkaphoto
vaporwave
janainaart
izzy806
theartofmadeline
ellievsbear
baratheonpilled