Auditing database activity

User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 22. Keeping Your Data Secure

Auditing database activity

Auditing is a way of keeping track of the activity performed on a database. The record of activities is kept in the transaction log. By turning on auditing, the DBA increases the amount of data saved in the transaction log to include the following:

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 DBA authority.

The transaction log

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.

For information about the transaction log, see The transaction log.

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 no failed events, nor select statements.

Turning on auditing

The database administrator can turn on auditing. Auditing adds security-related information to the transaction log.

Auditing is disabled by default. To enable auditing on a database, the DBA must set the value of the public option AUDITING to ON. DBA permissions are required to set this option. Auditing then remains enabled until explicitly disabled, by setting the value of the AUDITING option to OFF.

To turn on auditing:

Ensure that your database is upgraded to at least version 6.0.2.
If you had to upgrade your database, create a new transaction log.
Execute the following statement:
```
SET OPTION PUBLIC.AUDITING = 'ON'
```

For Info For more information, see AUDITING option.

Retrieving audit information

You can use the Log Translation utility to retrieve audit information. You can access this utility from Sybase Central or from the command line. It operates on a transaction log to produce a SQL script containing all of the transactions, along with some information on what user executed each command. By using the -g option, dbtran includes more comments containing the auditing information.

To ensure a complete and readable audit record, the -g option automatically sets the following switches:

-d Display output in chronological order.
-t Include trigger-generated operations in the output.
-a Include rolled back transactions in the output.

The Log Translation Utility can be run against a running database server or against a database log file

To retrieve auditing information from a running database server:

With the database server running, execute the following statement at a system command prompt.
```
dbtran -g -c "uid=dba;pwd=sql;..." -n asademo.sql
```
The user ID must have DBA authority.

For information about connection strings, see Connection parameters.

To retrieve auditing information from a transaction log file:

Ensure the log file is not in use by closing down the database server.
At a system command prompt, execute the following statement to place the information from the file asademo.log and places it in the file asademo.sql.
```
dbtran -g asademo.log
```
The -g command-line option includes auditing information in the output file.

For Info For more information see The Log Translation utility.

Adding audit comments

You can add comments to the audit trail using the sa_audit_string system stored procedure. You must have DBA permissions to call this 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 transaction log as an audit statement.

An auditing example

This example shows how the auditing feature records attempts to access unauthorized information.

As database administrator, turn on auditing.

You can do this from Sybase Central as follows:
- Connect to the ASA 6.0 Sample data source. This connects you as the DBA user.
- Right-click on the asademo database icon, and select Set Options from the popup menu.
- Select Auditing from the list of options, and enter the value ON in the Public Setting box. Click Set Permanent Now to set the option and Close to exit.
Alternatively, you can use Interactive SQL. Connect to the sample database from Interactive SQL, as user ID DBA using password SQL and execute the following statement:
```
SET OPTION PUBLIC.AUDITING = 'ON'
```
Add a user to the sample database, named BadUser, with password BadUser. You can do this from Sybase Central. Alternatively, you can use Interactive SQL and enter the following statement:
```
GRANT CONNECT TO BadUser
IDENTIFIED BY 'BadUser'
```
Using Interactive SQL, connect to the sample database as BadUser and attempt to access confidential information in the employee table with the following query:
```
SELECT emp_lname, salary
FROM dba.employee
```
You receive an error message: do not have permission to select from employee.
From a command prompt, change directory to your Adaptive Server Anywhere installation directory, which holds the sample database, and execute the following command:
```
dbtran -g -c "dsn=ASA 6.0 Sample" -n asademo.sql
```
This command produces a file named asademo.sql, containing the transaction log information and a set of comments holding audit information. The lines indicating the unauthorized BadUser attempt to access the employee table are included in the file as follows:
```
--AUDIT-1001-0000287812 -- 1999/02/11 13:59:58.765 Checking Select permission on employee - Failed
--AUDIT-1001-0000287847 -- 1999/02/11 13:59:58.765 Checking Select permission on employee(salary) - Failed
```
Restore the sample database to its original state so that other examples you try in this documentation give the expected results.

Connect as the DBA user, and carry out the following operations:
- Revoke Connect privileges from the user ID BadUser.
- Set the PUBLIC.AUDITING option to 'OFF'.

Auditing actions outside the database server

Some database utilities act on the database file directly. In a secure environment, the database files should not be accessible to any other than trusted users.

In order to provide auditing of actions directly on the file, under Windows NT only, any use of dbtran, dbwrite, and dblog generates Windows NT Application Events, which can be viewed in the Windows NT Event Viewer.

The user's name and program name are stored in the Event log.