SQL Anywhere Studio Security Guide
  Keeping Your Data Secure
    Auditing database activity

An auditing example

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

1. As database administrator, turn on auditing.

   You can do this from Sybase Central as follows:

   • Connect to the ASA 9.0 Sample data source. This connects you as the DBA user.

   • Right-click the asademo database icon and choose 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 with password SQL and execute the following statement:

   SET OPTION PUBLIC.AUDITING = 'ON'

2. 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'

3. 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.

4. 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 7.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

5. Restore the sample database to its original state so 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'.