Database activity audits

Auditing tracks all of the activity performed on a database.

Each database has an associated transaction log file that is used for database recovery and contains a record of transactions executed against a database.

The transaction log stores all executed data definition statements, along with the user ID that executed them. It also stores all updates, deletes, and inserts and which user executed those statements. However, this information is insufficient for some auditing purposes. By default, the transaction log does not contain the time of the event. It only contains the order in which events occurred. It does not contain failed events or select statements.

When you use auditing, additional data is saved in the audit log, which is in the transaction log, the user-defined ETD log, or the system event log. This data potentially includes:

All login attempts (successful and failed), including the computer name.
Accurate timestamps of all events (to a resolution of milliseconds).
All permissions checks (successful and failed), including the object on which the permission was checked (if applicable).
All actions that require system privileges.
All executions of xp_cmdshell system procedure.

You cannot stop using a transaction log while auditing is enabled for a database and the audit log is the transaction log. In this case, to turn off the transaction log, either first turn off auditing or change the audit_log option to specify FILE or SYSLOG.

Auditing individual connections

Once you have enabled auditing for a database, you can set the temporary conn_auditing database option in the database login procedure to enable connection-specific auditing. You can enable auditing based on information such as the IP address of the client computer or the type of connection.

If you do not set the conn_auditing option in the login procedure, then the option is on by default.

The following example shows an excerpt from a login procedure that enables auditing for all connections to the database, except those made by a user named DBA:

DECLARE usr VARCHAR(128)
SELECT CONNECTION_PROPERTY( 'Userid' ) INTO usr;
IF usr != 'DBA' THEN
   SET TEMPORARY OPTION conn_auditing='On'
ELSE
   SET TEMPORARY OPTION conn_auditing='Off'
END IF;

Audit comments

Add comments to the audit trail by using the sa_audit_string system stored procedure. It takes a single argument, which is a string of up to 200 bytes. For example:

CALL sa_audit_string( 'Started audit testing here.' );

This comment is stored in the audit log as an audit statement.