Audit Trail setup in a oracle application

Audit Trail setup in a oracle application
========================================

1) Keeping Track Data Change of important Table.
2) Keeping Track Source Code change (procedure,function,package,type etc).

====================================================
====================================================
1) Keeping Track Data Change of important Table setup:
====================================================
====================================================

=========================
1. create a audit table
==========================
CREATE TABLE audit_table_basel2
(
tabnam VARCHAR2(50 BYTE) NULL,
colnam VARCHAR2(50 BYTE) NULL,
oldval VARCHAR2(1000 BYTE) NULL,
newval VARCHAR2(1000 BYTE) NULL,
dbuser VARCHAR2(20 BYTE) DEFAULT USER NULL,
modusr VARCHAR2(6 BYTE) NULL,
machine VARCHAR2(100 BYTE) NULL,
host_path VARCHAR2(100 BYTE) NULL,
os_user VARCHAR2(100 BYTE) NULL,
ipaddr VARCHAR2(30 BYTE) NULL,
program VARCHAR2(1024 BYTE) NULL,
user_id VARCHAR2(30 BYTE) NULL,
TIMESTAMP DATE NULL
)
/

===========================================================
11. Create a package for inserting data into audit table
==========================================================


CREATE OR REPLACE PACKAGE audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2,
p_user_id IN VARCHAR2
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE,
p_user_id IN VARCHAR2
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER,
p_user_id IN VARCHAR2
);
END;
/


CREATE OR REPLACE PACKAGE BODY audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2,
p_user_id IN VARCHAR2
)
IS
v_user VARCHAR2 (100);
v_terminal VARCHAR2 (100);
v_sessionid VARCHAR2 (100);
v_program VARCHAR2 (200);
v_machine VARCHAR2 (200);
v_host VARCHAR2 (100);
v_os_user VARCHAR2 (100);
v_ipadd VARCHAR2 (200);
BEGIN
IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
BEGIN
SELECT USER, SYS_CONTEXT ('USERENV', 'TERMINAL') terminal,
SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,
(SELECT NVL (module, 'NULL')
FROM v$session
----grant select any dictionary to basel2 need it
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
program,
(SELECT machine
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
machine,
SYS_CONTEXT ('USERENV', 'HOST') HOST,
SYS_CONTEXT ('USERENV', 'OS_USER') os_user,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS') ip_address
INTO v_user, v_terminal,
v_sessionid,
v_program,
v_machine,
v_host,
v_os_user,
v_ipadd
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

DBMS_OUTPUT.put_line ('In package New ' || l_new || ' Old ' || l_old);

INSERT INTO audit_table_basel2
(tabnam, colnam, oldval, newval, dbuser,
modusr, machine, host_path, os_user, ipaddr,
program, user_id, TIMESTAMP
)
VALUES (UPPER (l_tname), UPPER (l_cname), l_old, l_new, USER,
p_user_id, v_machine, v_host, v_os_user, v_ipadd,
v_program, p_user_id, SYSDATE
);
END IF;
END;

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE,
p_user_id IN VARCHAR2
)
IS
v_user VARCHAR2 (100);
v_terminal VARCHAR2 (100);
v_sessionid VARCHAR2 (100);
v_program VARCHAR2 (200);
v_machine VARCHAR2 (200);
v_host VARCHAR2 (100);
v_os_user VARCHAR2 (100);
v_ipadd VARCHAR2 (200);
BEGIN
IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
BEGIN
SELECT USER, SYS_CONTEXT ('USERENV', 'TERMINAL') terminal,
SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,
(SELECT NVL (module, 'NULL')
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
program,
(SELECT machine
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
machine,
SYS_CONTEXT ('USERENV', 'HOST') HOST,
SYS_CONTEXT ('USERENV', 'OS_USER') os_user,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS') ip_address
INTO v_user, v_terminal,
v_sessionid,
v_program,
v_machine,
v_host,
v_os_user,
v_ipadd
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

INSERT INTO audit_table_basel2
(tabnam, colnam, oldval, newval, dbuser,
modusr, machine, host_path, os_user, ipaddr,
program, user_id, TIMESTAMP
)
VALUES (UPPER (l_tname), UPPER (l_cname), l_old, l_new, USER,
p_user_id, v_machine, v_host, v_os_user, v_ipadd,
v_program, p_user_id, SYSDATE
);
END IF;
END;

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER,
p_user_id IN VARCHAR2
)
IS
v_user VARCHAR2 (100);
v_terminal VARCHAR2 (100);
v_sessionid VARCHAR2 (100);
v_program VARCHAR2 (200);
v_machine VARCHAR2 (200);
v_host VARCHAR2 (100);
v_os_user VARCHAR2 (100);
v_ipadd VARCHAR2 (200);
BEGIN
IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
BEGIN
SELECT USER, SYS_CONTEXT ('USERENV', 'TERMINAL') terminal,
SYS_CONTEXT ('USERENV', 'SESSIONID') sessionid,
(SELECT NVL (module, 'NULL')
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
program,
(SELECT machine
FROM v$session
WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID'))
machine,
SYS_CONTEXT ('USERENV', 'HOST') HOST,
SYS_CONTEXT ('USERENV', 'OS_USER') os_user,
SYS_CONTEXT ('USERENV', 'IP_ADDRESS') ip_address
INTO v_user, v_terminal,
v_sessionid,
v_program,
v_machine,
v_host,
v_os_user,
v_ipadd
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

INSERT INTO audit_table_basel2
(tabnam, colnam, oldval, newval, dbuser,
modusr, machine, host_path, os_user, ipaddr,
program, user_id, TIMESTAMP
)
VALUES (UPPER (l_tname), UPPER (l_cname), l_old, l_new, USER,
p_user_id, v_machine, v_host, v_os_user, v_ipadd,
v_program, p_user_id, SYSDATE
);
END IF;
END;
END audit_pkg;
/


=====================================================
3. Create Trigger in that tables which need to track.
=====================================================

create Trigger script for individual table. save it in a txt file then execute it
in SQLPLUS> @ script_path ;

----------------------------------------------------
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
set echo off
set linesize 2000
spool tmp.sql

prompt create or replace trigger aud#&&1
prompt after update or insert or delete on &&1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name ||','||'''OPRSTAMP''' ||');'
from user_tab_columns where table_name = upper('&&1')


/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp

----------------------------------------------------

=======================================================================
=======================================================================
2) Keeping Track Source Code change (procedure,function,package,type etc).
=======================================================================
=======================================================================
For this

1. create audit source table

CREATE TABLE audit_source_hist
(
change_date DATE NULL,
NAME VARCHAR2(30 BYTE) NULL,
TYPE VARCHAR2(12 BYTE) NULL,
line NUMBER NULL,
text VARCHAR2(4000 BYTE) NULL,
dbuser VARCHAR2(20 BYTE) DEFAULT USER NULL,
modusr VARCHAR2(6 BYTE) NULL,
machine VARCHAR2(100 BYTE) NULL,
host_path VARCHAR2(100 BYTE) NULL,
os_user VARCHAR2(100 BYTE) NULL,
ipaddr VARCHAR2(30 BYTE) NULL,
program VARCHAR2(1024 BYTE) NULL,
user_id VARCHAR2(30 BYTE) NULL,
TIMESTAMP DATE NULL
);



2. create a trigger in that schema(user).
===============================================

CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON basel2.SCHEMA
DECLARE
V_USER VARCHAR2(100);
V_TERMINAL VARCHAR2(100);
V_SESSIONID VARCHAR2(100);
V_PROGRAM VARCHAR2(200);
V_MACHINE VARCHAR2(200);
V_HOST VARCHAR2(100);
V_OS_USER VARCHAR2(100);
V_IPADD VARCHAR2(200);
BEGIN

BEGIN

select user,SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
(SELECT NVL(module,'NULL')
FROM V$SESSION ----grant select any dictionary to basel2 need it
WHERE AUDSID = SYS_CONTEXT('USERENV','SESSIONID')) PROGRAM,
(SELECT MACHINE
FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT('USERENV','SESSIONID')) machine,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address
INTO V_USER,V_TERMINAL,V_SESSIONID,V_PROGRAM,V_MACHINE,V_HOST,
V_OS_USER,V_IPADD
From Dual;

EXCEPTION
WHEN OTHERS THEN
NULL;
END;


IF ora_dict_obj_type IN
('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE')
THEN
INSERT INTO AUDIT_SOURCE_HIST
SELECT SYSDATE, NAME, TYPE, LINE, TEXT,USER,V_USER,V_MACHINE,
V_HOST,V_OS_USER,V_IPADD,V_PROGRAM,V_USER,SYSDATE
FROM user_source
WHERE TYPE = ora_dict_obj_type AND NAME = ora_dict_obj_name
and line=1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000, SQLERRM);
END;
/