Database Auditing

Database Auditing

Auditing provides the ability to trace the information flow inside a database, including connection attempts, data updates, deletes, inserts and selects, execute functionality, and such. It is useful both for postmortem scenarios and for on-going monitoring to prevent unauthorized activity.

Auditing has nothing to do with the SQL standard, and is strictly vendor-dependent — in capabilities, implementation details, and so on. This paragraph gives a brief overview of the RDBMS auditing.

Oracle 9i allows you to choose between an operating system auditing trail, a database one, or both. The first option is, of course, operating system dependent, and would contain only such information that the OS is programmed to preserve. An audit trail generated by Windows will be much different from one generated on the Unix box, even if the Oracle database setup is the same.

The database auditing trail will be very much the same, no matter what OS Oracle is installed on; it also has an additional advantage of being able to produce audit reports using Oracle's built-in facilities. The auditing information (database statements, privileges, and so on) is stored in the SYS.AUD$ catalog table, which is commonly referred to as audit trail, in Oracle. Essentially, you select for yourself the events you wish to monitor. A number of events in Oracle are audited by default: instance startup, instance shutdown, and attempts to connect to the database with administrative privileges. You may choose to specify custom auditing options to monitor other events happening within your Oracle installation, with the Oracle-specific AUDIT statement. Table 12-14 shows Oracle 9i audit levels.

Table 12-14: Oracle 9i Audit Levels




Initiates auditing of some specific SQL statements. For example, the AUDIT TABLE command initiates audits for the CREATE TABLE, TRUNCATE TABLE, COMMENT ON TABLE, and DELETE [FROM] TABLE statements.


Initiates auditing for the SQL statements created using special system privilege. For example, AUDIT CREATE USER will monitor all statements that are issued using this particular system privilege.


Initiates audit of the events pertaining to a particular object like TABLE or VIEW.

When setting the audit level, you also can specify some of the options that narrow the scope of the events you wish to monitor (see Table 12-15).

Table 12-15: Oracle 9i AUDIT Level Options




The first option accumulates information on all the SQL statements issued for the duration of the session, the second causes Oracle to write one record for each access.


Records audit information only for statements that succeed.


Records audit information only for statements that fail or generate in errors.

For example, to audit all failed attempts to perform various database operations, the following statement could be used:


Upon execution of this statement, Oracle begins to collect information on all failed attempts to perform operations listed in the audit clause. Refer to the Oracle 9i documentation for more detailed information on auditing capabilities of the RDBMS.


Keep in mind that the amount of accumulated information could easily exceed reasonable limits. Choose wisely what events you wish to monitor and for how long.

IBM DB2 UDB introduces the auditing facility db2audit to monitor database events, and log the collected information. The audit is performed at an instance level, and the user of this facility must have SYSADM authority. The audit trail is generated for a series of predefined events. There are several categories of events for which you may want to generate an audit trail; each of these events can be monitored for failure, success, or both. The categories are listed in Table 12-16.

Table 12-16: IBM DB2 UDB Events Categories Available for Audit




Generates a log record whenever auditing settings are changed.


Generates a log record when performing authorization checking.


Generates a log record when database objects are created or destroyed.


Generates a log record when privileges and authorities are granted or revoked; also when security configuration parameters are changed.


Generates a log record when an operation requiring high-level authority is performed (e.g., STAERT_DB2, CREATE_DATABASE, etc.).


Generates a log record when a user is being authenticated, or his/her security information is retrieved.


Generates a log record of the operation context; it might help when analyzing other events' records. By its very nature, such a record could be very large and should be used with caution (e.g., CONNECT, BACKUP_DB etc.).

The collected information is written into a log file with a predefined structure. Each category of events generates its own file, and has its own structure. The file is a regular ASCII file, optimized for loading into a table; the files are not encrypted but are protected within the operating system's security framework. The loading is also done through the db2audit utility, using the extract parameter as the argument.

An audit facility runs from the command line and accepts a number of parameters (over 20). Here is a very simple example of the usage:

db2audit start checking

This would start auditing all events that fall into the CHECKING category for both failure and success.

The audit facility is very complex, and even a brief explanation of its usage would require a chapter of its own; refer to IBM DB2 UDB documentation for more information.

The Microsoft SQL Server 2000 provides an SQL Profiler tool that can be used for auditing, and, as you would expect in the Windows world, there is a visual interface. It is invoked from the Tools menu of the SQL Server Enterprise manager. However only members of the SYSADMIN security fixed role are allowed to enable it; for all other users, this option is not available.

The SQL Profiler provides a visual interface for auditing events. The events fall into several categories: user activity, database administrative activity, server events, and so on. Each category represents a collection of the events that could be selected separately — or all together. The event categories available for monitoring are shown in Table 12-17.

Table 12-17: Microsoft SQL Server 2000 Event Categories

Event Category



Events generated by cursor operations


Events generated by data and log files when an expansion/shrinkage occurs


Events generated by an exception that occurred within a process


Events generated by locks occurring during the operation


Events generated by operations with objects as they are created, destroyed, opened, etc.


Collection of events related to the DML (Data Manipulation Language) execution


Events generated by the scan performed on a database object (like table or index)


Events generated by security-related operations (like granting privilege, for example)


Events generated by connecting and disconnecting to and from the SQL Server


Events generated by executing stored procedures within SQL Server


Events produced by the execution of Microsoft Distributed Transaction Coordinator (MS DTC) transactions or by writing to the transaction log.


Events generated by execution Transact SQL statements passed to the SQL Server instance from the client


Allows user to specify custom events

The information recorded contains date/time stamp, user ID, type of event, outcome (success/failure), the source (machine name, IP address), names of the objects accessed, and full text of the SQL statement. All this information is recorded in the auditing files, placed under operating system security protection.

The SQL Server provides very fine-grained monitoring capabilities (for example, the amount of CPU time required for the event to occur) for each of the event categories. In addition, you can specify filters, stating, for example, that you would like to monitor the selected events only if they are generated by a specific application, ignoring all others.


The Microsoft SQL Server supports C2 audit requirements (see more information on C2-level security certification later in this chapter). To turn on the C2 auditing option, a member of the SYSADMIN fixed role must run the system-stored procedure sp_configure, and set the c2 audit mode option to 1:

EXEC sp_configure 'c2 audit

To access the advanced audit mode option, run this script first:

EXEC sp_configure 'show
		  advanced options', '0' RECONFIGURE

Enabling this option turns on a security trace template that collects all information needed for the C2-level security audit, as specified by the security standard.