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.

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).

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

AUDIT SELECT TABLE, INSERT
		TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT
		SUCCESSFUL;

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.

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.

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
		both

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.

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.

EXEC sp_configure 'c2 audit
		  mode','1'

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