Login auditing capability

The following information will be added to the Sybase IQ System Administration Guide.

Sybase IQ 12.7 provides the ability to log connection details for a given user in the transaction log file, then use your own scripts to extract information such as who logged in, how long they were logged in, and what operations were performed.

For details, see:

“Auditing database activity” in Chapter 1, “Keeping Your Data Secure” in the SQL Anywhere Studio Security Guide. (Note that Sybase IQ does not support the -sf or -sk server option, and uses the sample database asiqdemo.db.)
“The transaction log file” in Chapter 5, “Managing System Resources” in the Sybase IQ Performance and Tuning Guide.
“AUDITING option [database]” in Sybase IQ Reference Manual.
“Connection parameters” in Chapter 4, “Connection and Communication Parameters” in Sybase IQ System Administration Guide.
“Log Translation utility (dbtran)” in Chapter 3, “Database Administration Utility” in the Sybase IQ Utility Guide.
“sa_audit_string system procedure” in Chapter 8, “System Procedures” in Sybase IQ Reference Manual.

Auditing example

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

As database administrator, turn on the auditing option in the database.

You can do this from Sybase Central as follows:
- Connect using the Adaptive Server IQ Demo data source. This connects you as a DBA user.
- Select the asiqdemo database and from the File menu, choose Options.
- Select Auditing from the list of options, and type 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, as follows:
- Connect to the sample database from Interactive SQL as the DBA user.
- Execute the following statement:
```
SET OPTION PUBLIC.AUDITING = 'ON'
```
To enable auditing, you must also specify the type of events to audit, as follows. In Interactive SQL, enter:
```
sa_enable_auditing_type('Connect')
```
Add a user to the sample database, named AuditedUser, with password AuditedUser. You can do this from Sybase Central. Alternatively, you can use Interactive SQL and enter the following statement:
```
GRANT CONNECT TO AuditedUser
IDENTIFIED BY 'AuditedUser'
```
Use Interactive SQL to connect to the sample database as AuditedUser and attempt to access confidential information in the employee table with the following query:
```
SELECT emp_lname, salary
FROM employee
```
This returns the error: Permission denied: you do not have permission to select from "employee".
At a command prompt, execute the following command:
```
dbtran -g asiqdemo.log asiqdemo.sql
```
or, to run against the server:
```
dbtran -c "DSN=Adaptive Server IQ Demo" 
-g asiqdemo.sql
```
Either command produces a file named asiqdemo.sql, which contains the transaction log information and a set of comments holding audit information. The lines that indicate the unauthorized AuditedUser attempt to access the Employees table are included in the file as follows:
```
----AUDIT-1010-0000936326 -- 2007/06/06
10:34:06.164 Checking Select permission on
DBA.employee - Failed----AUDIT-1010-0000936365 -- 2007/06/06
10:34:06.164 Checking Select permission on
DBA.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 a DBA user, and perform the following operations:
- Revoke Connect privileges from the user ID AuditedUser.
- Set the PUBLIC.AUDITING option to OFF.