Auditing Oracle Data

Auditing data of business applications is a common requirement. In this article, I'll demonstrate one way to audit Oracle data that is both simple and flexible. The code is written in Oracle PL/SQL and can be applied to any modern Oracle database. The process demonstrated can be duplicated on many other databases by using their default programming language.

Auditing Options

There are several different ways to audit database activity. In Oracle, you may audit things such as database connections, user logins, and so on, storing the results in a data dictionary table.

Oracle allows you to audit DML statements by using the AUDIT command. For example, AUDIT DELETE ON my_table; will capture delete statements on the table my_table. Several options can be supplied with this command. However, the data is written to the central Oracle auditing table and it's not possible to specify any auditing criteria.

Oracle 9i introduces "fine-grained auditing," in an Oracle-supplied package, enabling you to populate the audit table with DML statements based upon business rules. For example, you can use this tool's API to audit financial transactions only when the posted amount exceeds $1,000.

This type of auditing is valuable, but what about creating an audit trail for the data itself? The focus of this article is auditing application data.

A common, yet simplistic, approach to auditing data requires adding columns such as created_by/created_on, and updated_by/updated_on to every targeted table. At commit time, these fields are set to the current user and system date. The problem with this approach is that it is often not enough. For example, it conveys the time of last update but provides no snapshot of the actual data as it existed before its current state.

Also, many off-the-shelf applications provide their own auditing tables and procedures.

If you're fortunate enough to have some existing capability with a purchased application, then by all means use it. Otherwise, the two scripts below can be modified as needed to provide a rich audit trail for application data.

Creating audit tables

The code to create the audit tables (as well as the script to create audit triggers) builds an executable script by spooling to an operating system file. (Both scripts are included in this article's accompanying zip file.)

--audit_tables.sql creates an audit table for user's tables.
SET SERVEROUTPUT ON SIZE 500000
SET FEEDBACK OFF
SPOOL build_audit_tables.sql

In the declaration of audit_tables.sql, two cursor definitions are required to obtain information for any table in the user's schema that does not have an audit table. The audit table will be created as the table name (or, for tables with long names, the 1st 26 letters) followed by $AUD.

DECLARE
lv_precision_and_scale VARCHAR2(20);
--Select tables w/o an audit table
CURSOR cur_tbl2audit IS
SELECT table_name
FROM user_tables
WHERE SUBSTR(table_name,1,26)||'$AUD' NOT IN
(SELECT table_name
FROM user_tables)
AND table_name NOT LIKE '%$AUD'
--Add ineligible tables here:
AND table_name NOT IN ('PLAN_TABLE');
--Select table def of unaudited table.
CURSOR cur_col2audit(p_tbl2audit USER_TABLES.TABLE_NAME%TYPE) IS
SELECT column_name,data_type,data_length,data_precision,data_scale
FROM user_tab_columns
WHERE table_name = p_tbl2audit
--Add ineligible datatypes here :
AND data_type NOT IN ('BLOB', 'CLOB','RAW')
ORDER BY column_id;

From the first cursor, a table name is retrieved. This table name is then passed as a parameter to the second cursor, which obtains column information from the data dictionary and builds the resulting DDL script. Some data type checking is required so the proper length can be specified, or, in the case of NUMBER, a precision and scale. For brevity, exception handling has been omitted.

To audit the data, three columns are added to the audit table: aud_action captures the type of DML performed, aud_timestamp receives the system date, and aud_user records the current user performing the action.

Upon completion, the resulting build_script can be immediately executed.

As before, two FOR LOOPs do the dirty work. In Oracle, the keywords :new and :old allow you to reference the new value and old value, respectively, of a table column. Some logic is required in the trigger itself to differentiate between insert,updates, and deletes. Two variables, v_prefix and v_condition, help us build an IF/ELSE statement.

When querying Oracle's SYSDATE, you will need to use TO_CHAR to display the full date and time.

Auditing Tips/Techniques

Customizing

Building on these scripts, other possible enhancements include:

Add Grant statements that execute immediately following the table DDL.

Add criteria for when triggers should fire, possibly data-driven from a configuration table. In Oracle, this is done with the WHEN clause.

Add support for auditing views with INSTEAD OF triggers in Oracle 8 and higher.

When customizing, keep in mind that trigger logic must execute quickly. A slow performing trigger can wreak havoc on database performance (see below). Also, if the definition of an audit table is altered (due to modifications to the audited table), the associated trigger should be rebuilt to reflect those changes

Check Performance

Auditing high-volume tables in production can have serious performance ramifications. For OLTP systems with a low to moderate amount of volume, a database like Oracle is more than up to the task. It would be prudent to test the performance with triggers enabled and disabled on any tables in question. Tables containing configuration data and metadata are ideal candidates.

Purge old data

Have a system in place to routinely purge audit data. Active tables can quickly consume disk space with audit information. Set expiration dates on audit trail data and, if necessary, move it off-line.

Put it on display

An audit trail might be useful to application users. One example is in an issue tracking application where a user can view previous activity that might provide clues to their problem. A History button can be added to the user interface that will open a window displaying previously audited information for the master record.

About the Author

Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.

Thanks for your registration, follow us on our social networks to keep up-to-date