Regulatory compliance is a critical aspect of the IT landscape these days, and the ability to audit database activities showing who did what to which data when is a specific requirement of many industry and governmental regulations.
Different types of database activities may be required to be tracked to ensure compliance. Typical categories of activity that need to be audited include the DDL (or Data Definition Language) for database structure changes, DML (or Data Manipulation Language) for data retrieval and modification, DCL (or Data Control Language) for authorization grants and revokes, security exceptions, and other types of access (such as database utilities that load and unload data).
Another significant aspect of database activity that needs to be monitored is privileged user auditing. This means tracking all of the activities of super users, such as the DBADMIN or SYSADMIN, because these users have high-level access to the system. In addition, many regulations specifically require tracking the actions of privileged users.
So How Can This Be Done?
There are six primary methods that can be used to accomplish database auditing:
Audit using DBMS traces. Database systems typically enable DBAs to start traces to track specific activities. For example, DB2 provides an AUDIT trace that can be started to track multiple categories of events, particular AUTHIDs or programs, and other system details. The benefit here is that the capability is provided by the DBMS at no additional cost. The drawback is that it can generate a significant number of trace records, cause performance slowdowns, and is difficult to report on without a reporting tool that understands the format of the trace output.
Audit using temporal capabilities. Modern DBMS offerings have begun to support temporal data management. The system time temporal option can be used to offer a form of data modification auditing. System time support stores every change made to the data in a related history table. Support for managing system changes enables users to be able to query the database as of a point in time and returning the value of the data as of that time frame. The benefit is the simplicity of implementing the solution if the DBMS offers temporal capabilities. The problem is that it is useful only for tracking modifications and cannot tell you explicitly who made each change.
Audit using database transaction log files. Every database logs the changes that are made to the data in a transaction log file. Using the information on the log, it is possible to track who modified which data and when. Again, the benefit is that the capability is built into the DBMS. But there are several problems with this approach. You will need a tool that can interpret and report on complex log records, you may need to modify the retention period for your log files, the volume of data can be significant, not every modification may be logged depending on your database settings, and again, this method cannot track read access, only modification.
Audit over the network. Sometimes called network sniffing, this approach is used by several tools vendors to trap SQL calls on the network as they are sent over the network. But be careful, because not all SQL requests go across the wire. A DBA can log directly onto a server and make requests that will not be directed over a network. And, on the mainframe, many applications that use CICS, IMS, and batch may never go over a network connection.
Hand-coded audit trails. Sometimes the approach is to add “audit columns” to tables, such as LAST_MODIFIED_DATE and LAST_MODIFIED_USER, that must be modified programmatically whenever data is changed. But this is a problematic “solution” because it is easy to miss a program or a modification request, ad hoc modifications will not be tracked, and data read access is not tracked. Auditors do not like this approach because audit trails should be kept outside of the database (if you delete the row you lose the audit data) and the audit trail is easily corrupted.
Audit access directly on the server. The sixth and final approach is to audit directly against the DBMS server control blocks. Sometimes called a “tap,” this approach is beneficial because it can capture all SQL requests directly at the server, without starting a potentially costly trace or relying on log files. The potential concern is that this approach interfaces directly with DBMS internals, and bugs can cause outages. Additionally, it requires purchasing ISV software.
An Emerging Requirement
Database auditing is increasingly becoming a requirement for ensuring data protection and compliance with industry and governmental regulations. Be sure to study the auditing capabilities of your DBMS and to examine any third-party database auditing tools to understand which of the methods discussed here are used to bolster the auditability of your databases.