Building an Audit Trail for Your Data
(Page 1 of 6 )
Auditing data of business applications is a common requirement. In this article, I’ll demonstrate one way to audit an Oracle database that is both simple and flexible. Though the code in this article is written in Oracle PL/SQL, it does not rely on Oracle-specific packages. The same process can be duplicated for use 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. This type of auditing is valuable but what about creating an audit trail for the data itself? Auditing application data is the focus of this article.
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 recurrent. For example, it conveys the time of last update but provides no snapshot of the actual data as it existed before its current state.
Oracle also allows you to audit data using the ‘AUDIT’ command. For example, ‘AUDIT DELETE ON my_table;’ will audit deletes 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 criteria for auditing based on business rules.
Oracle 9i introduces “fine-grained auditing”, enabling you to update an audit table based upon certain criteria provided by the user. For example, audit financial transactions only when the posted amount exceeds $1,000. Finally, many off-the-shelf applications provide their own auditing tables and procedures.
If you’re fortunate enough to have some existing capabilities with a commercial application or you are using Oracle 9i or higher, then by all means use them. Otherwise, the scripts below can be modified as needed to provide a rich audit trail.
Next: Creating Audit Tables >>
More Database Articles
More By Michael Kleane
