Database activity audits

Each database has an associated transaction log file. The transaction log is used for database recovery. It is a record of transactions executed against a database.

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

Auditing tracks of the activity performed on a database. When you use auditing, additional data is saved in the transaction log, including:

All login attempts (successful and failed), including the terminal ID.
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.

You cannot stop using a transaction log while auditing is enabled for a database. To turn off the transaction log, you must first turn off auditing.

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, 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;

Database activity audits

Auditing individual connections

Audit comments

See also