Oracle XE does not have FGA (Fine Grained Auditing).
So I had to implement a package and a trigger in order to keep a fine grained record of what data and what column and on what table was the data added,deleted or inserted.
I received quite a bit of help from the Oracle Forum.
I have created a table called "audit_logs" to store the records of inserts,deletes and updates.
Below is the SQL structure for the
"audit_logs" table.
CREATE TABLE "AUDIT_LOGS"
( "TABLENAME" VARCHAR2(4000),
"COLUMNNAME" VARCHAR2(4000),
"USERNAME" VARCHAR2(4000),
"OLDVAL" VARCHAR2(4000),
"NEWVAL" VARCHAR2(4000),
"AUDITDATE" TIMESTAMP (6)
)
/
Below is the code for the
"auditpkg" package.
create or replace package auditpkg AS -- package spec
procedure auditproc( p_tablename varchar2, p_columnname varchar2,
p_oldval varchar2, p_newval varchar2 );
procedure auditproc( p_tablename varchar2, p_columnname varchar2,
p_oldval number, p_newval number );
procedure auditproc( p_tablename varchar2, p_columnname varchar2,
p_oldval date, p_newval date );
end auditpkg;
/
SHOW ERRORS
create or replace package body auditpkg AS -- package body
procedure auditproc
( p_tablename varchar2, p_columnname varchar2, p_oldval varchar2, p_newval varchar2 )
IS
BEGIN
if (p_oldval is null and p_newval is not null)
or (p_oldval is not null and p_newval is null)
or (p_oldval != p_newval)
then
insert into audit_logs( tablename, columnname, username, oldval, newval, auditdate )
values (p_tablename, p_columnname, USER, p_oldval, p_newval, SYSDATE );
end if;
END auditproc;
procedure auditproc
( p_tablename varchar2, p_columnname varchar2, p_oldval number, p_newval number )
IS
BEGIN
if (p_oldval is null and p_newval is not null)
or (p_oldval is not null and p_newval is null)
or (p_oldval != p_newval)
then
insert into audit_logs( tablename, columnname, username, oldval, newval, auditdate )
values (p_tablename, p_columnname, USER, p_oldval, p_newval, SYSDATE );
end if;
END auditproc;
procedure auditproc
( p_tablename varchar2, p_columnname varchar2, p_oldval date, p_newval date )
IS
BEGIN
if (p_oldval is null and p_newval is not null)
or (p_oldval is not null and p_newval is null)
or (p_oldval != p_newval)
then
insert into audit_logs( tablename, columnname, username, oldval, newval, auditdate )
values (p_tablename, p_columnname, USER, p_oldval, p_newval, SYSDATE );
end if;
END auditproc;
END auditpkg;
/
SHOW ERRORS
Below is the code for the
"audit_test_audit_trig" trigger
There must be one trigger for each table, each with its own unique trigger name of course.
In the case below, the trigger is for a table called
"audit_test"create or replace trigger audit_test_audit_trig
after insert or update or delete on audit_test
for each row
begin
auditpkg.auditproc( 'AUDIT_TEST', 'COL1', :old.col1, :new.col1 );
auditpkg.auditproc( 'AUDIT_TEST', 'COL2', :old.col2, :new.col2 );
auditpkg.auditproc( 'AUDIT_TEST', 'COL3', :old.col3, :new.col3 );
end;
Posts
