SQL Bible [Electronic resources] نسخه متنی

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