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.
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
(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.
We discuss the following instructions in our episode APEX Instant Tips #43 Flashback Data Archive.
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:
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.
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!