Building an Audit Trail for Your Data - Auditing Tips/Techniques
(Page 6 of 6 )
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 for auditing.
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
