|
Database management systems such as Oracle, Sybase, Microsoft SQL Server, IBM DB2 and IBM IMS all contain the ability to create audit records of all transactions that access the data stored in their databases. Additionally, these database management systems also provide the ability to audit changes to the structure and access to the database.
Each product provides different levels of granularity on what can be audited and the audit features are bundled with the database at no additional cost. Audit records created by the database management systems should not be confused with “re-do” or “archive” logs that are created by the database for any data update. Those logs are used exclusively for forward database recovery and are in a format that is optimized for a recovery. They contain basic information limited to only changed data and physical location in the table where it was placed. Additionally, re-do and archive logs do not contain any record of SQL SELECT statements, as a SELECT statement does not modify data.
Unfortunately, most native database auditing capabilities add overhead to database processing and the amount can be an issue in some situations. Native database audit tools also contain minimal functionality other than creating records of database access. Some native database management system audit tools such as Oracle, DB2 z/OS and IMS do provide some audit record storage, reporting and alerting tools, but these tools often do not meet the segregation of duties requirements that auditors require. Microsoft SQL Server and Sybase contain minimal auditing tools and very poor ability to store and report on audit records. Finally, no database management system provides the ability to detect access and changes in “real-time.”
For these reasons, a new set of software tools, commonly referred to as “Enterprise Database Auditing and Real-Time Protection” have come to market that provide low-overhead audit collection with storage, alerting and reporting capabilities.
Organizations evaluating a strategy for auditing databases should consider the trade-offs regarding OPEX (operating expense), CAPEX (capital expenditure), and accuracy. The purpose of this paper is to introduce the current options available for database auditing and trade-offs using each.
Introduction to Native Database Auditing
Native database auditing tools are turned off by default when a database is installed and must be enabled and managed by database administrators (DBAs). Examples of native database auditing tools provided by the leading databases are:
- IBM DB2 z/OS and IMS: Audit traces that are provided at no cost and optionally combined with an additional product (at an additional cost), the IBM Audit Management Expert (AME) stores audit records in a DB2 table.
- Oracle: Fine Grained Auditing (FGA) that dumps audit records into XML records or optionally combined with an additional product (at an additional cost), the Oracle Audit Vault.
- Microsoft SQL Server: C2 auditing and Server-Side traces. Microsoft does not provide significant optional storage, reporting or alerting tools.
- Sybase: Minimal native support. Provides an additional product, Sybase Data Auditing (at an additional cost), which is a re-branded third-party product from Lumigent.
Each of these native audit capabilities can be configured to be extremely granular down to the table name or in the case of Oracle FGA, down to specific SQL statements and columns. Additionally, the database management vendors continue with each release of their products to increase capabilities while reducing overhead requirements of utilizing their audit traces.
Vendor Native Auditing Capabilities
Some vendors are further ahead than others with Oracle arguably being the most advanced provider of native auditing capabilities and Microsoft SQL Server being the laggard according to industry analysts. For example, SQL Server C2 auditing, introduced in SQL Server 2000, does not provide the ability to specify individual tables to trace and therefore requires administrators to use server-side traces if they want to limit the tables audited in database instance (the use of placing triggers on tables will not catch SQL SELECT statements and therefore will not meet serious audit requirements). This functionality has not been significantly improved in SQL Server 2005 and while requiring DBA support to continuously maintain (the traces must be turned on manually whenever the database is restarted), it remains functional. The exact details are beyond the scope of this paper but are documented by Microsoft at TechNet.
Regardless of the steps required to enable SQL-level auditing (or DL/I in the case of IMS) in the database management system, they each provide the ability to document all access to individual tables including the SQL statement issued, the userid associated with the transaction, and the time.
|
