This post is directed at Oracle database users and DBAs looking for a recommendation on how to audit their tables. I’m reviewing two common standards:  

(1)  Journaling tables vs. (2) Flashback Data Archive (FDA). 

Ultimately, I recommend using FDA.

(1) Journaling tables

This auditing standard describes adding triggers to the tables you wish to audit to populate a secondary table with a log of all the DML performed against the 1st table. 

How to

An easy way to implement this standard is described in this video: APEX Instant Tips #40 “Instantly” add full table audit capability to your applications

An alternative methodology is described in Connor McDonald’s GitHub repo: connormcd/audit_utility: Automatic generator for the typical audit triggers we have on Oracle tables with lots of options

Review

(2) Flashback Data Archive

This auditing standard describes enabling an Oracle Database feature to preserve a history of all the changes performed against the tables you wish to audit.

How to

We discuss the following instructions in our episode  APEX Instant Tips #43 Flashback Data Archive.

DBA steps

You will need to involve your DBA to get started. I recommend that a DBA perform the following:

1 . Create a tablespace for archive data, you can name it ‘archive_data’, for example.

2 . Create an archive in said-tablespace:

create flashback archive default fda1year tablespace archive_data retention 1 year;

The above command defines that all audit data will be preserved for a duration of 1 year, then purged. Consult your legal team for what your retention period should be.

3 . Instruct FDA to store context data (like APP_USER, SESSION_USER and CLIENT_IDENTIFIER) for all archive-enabled tables. This is highly recommended because you need your audit trail to capture the username of the APEX user:

exec dbms_flashback_archive.set_context_level('ALL');

4 . Allow your database user to enable archiving into the default archive:

grant flashback archive on fda1year to hayden;

5 . Allow said-database user to access the context information using dbms_flashback_archive:

grant execute on dbms_flashback_archive to hayden;

Regular database user steps

1 . Thanks to the “flashback archive” grant from your DBA you can now audit any table in your schema, eg:

alter table ait_env_var flashback archive;

2 . To access the context variables that record which APEX users perform DML on your table, I recommend creating a simple package function to catch errors in the event of the context variables being missing:

create or replace package ait_fda authid definer is

  function my_context(p_xid       in raw,
                      p_namespace in varchar2,
                      p_parameter in varchar2)
                      return varchar2;
 
end ait_fda;

create or replace package body ait_fda  is

   function my_context(p_xid       in raw,
                       p_namespace in varchar2,
                       p_parameter in varchar2)
                       return varchar2
   is

   l_context_value varchar2(256);
   fetch_is_null exception;
   pragma exception_init(fetch_is_null, -1405);
   begin
      
       select dbms_flashback_archive.get_sys_context(p_xid, p_namespace, p_parameter)
           into l_context_value
           from dual;
      
       return l_context_value;

   exception
       when fetch_is_null then
           return null;
       when others then
           raise;
   end my_context;
  
end ait_fda;

You can now query the audit history of your table by writing a Flashback version query, eg:

select
  aev.*,   versions_operation,
  versions_startscn,
  versions_starttime,
  versions_endscn,
  versions_endtime,
  versions_xid,
  ait_fda.my_context( p_xid       => versions_xid,
                      p_namespace => 'USERENV',
                      p_parameter => 'SESSION_USER') as session_user,
  ait_fda.my_context( p_xid       => versions_xid,
                      p_namespace => 'USERENV',
                      p_parameter => 'CLIENT_IDENTIFIER') as client_identifier,
  ait_fda.my_context( p_xid       => versions_xid,
                      p_namespace => 'APEX$SESSION',
                      p_parameter => 'APP_USER') as apex_user
from
  ait_env_var
versions between scn minvalue and maxvalue aev

For more on how to write a Flashback version query consult the following post.

Review

Conclusion

All in all, my recommendation is to use your database’s built-in auditing feature: Flashback Data Archive (FDA). It’s the least amount of hassle for the most reliable audit trail. Let me know if you agree or disagree!

Share this: