This example shows how the auditing feature records attempts to access unauthorized information using either Sybase Central or Interactive SQL.

Auditing example (Sybase Central)

1. Start Sybase Central and connect to the sample database using the SQL Anywhere 11 Demo data source.

   This connects you as a DBA user.

2. Turn on auditing:

   1. Right-click the database and choose Properties.

   2. Click the Auditing tab.

   3. Click Collect All Audit Information For This Database

   4. Click Apply.

   5. Click OK.

3. Add a user named Test1 to the sample database, with the password welcome:

   1. Right-click Users & Groups, and choose New » User.

   2. When prompted, name the user Test1, and type welcome as their password.

   3. Give the user Profile Authority.

   4. Click Finish.

   5. Disconnect from the sample database.

4. Using Sybase Central, connect to the sample database as Test1 and attempt to access confidential information in the Employees table:

   1. Select Tables, and then select the Employees table.

   2. Click the Data tab.

      An error message appears: Permission denied: you do not have permission to select from "Employees".

   3. Click OK.

   4. Disconnect from the sample database.

5. View the auditing information for this activity:

   1. Using Sybase Central, connect to the sample database as a user with DBA authority.

   2. Select the database, and then click the Auditing tab in the right pane.

   3. Click Retrieve Audit Messages.

   4. Click Close.

      Auditing information appears.

   5. Use the filtering options to locate the error in the auditing information table. You can find the error for BadUser by selecting the Only Errors option. Use the date and time information to pinpoint the error. For example, if BadUser tried accessing the Employees table on November 6, 2007 at 10:07:14, the corresponding audit entry resembles the following entry:

      2007-11-06 10:07:14 | Permission

6. Restore the sample database to its original state:

   1. Right-click the database, and then choose Properties.

   2. On the Auditing tab, select Do Not Collect Audit Information For This Database.

   3. Click OK.

   4. Select Users & Groups.

      Right-click Test1, and choose Delete.

Auditing example (Interactive SQL)

1. Start Interactive SQL and connect to the sample database using the SQL Anywhere 11 Demo data source.

   This connects you as a DBA user.

2. Turn on auditing using the SET OPTION statement, as follows:

   SET OPTION PUBLIC.auditing = 'On';

3. Add a user, Test1, to the sample database using the CREATE USER statement, as follows:

   CREATE USER Test1 IDENTIFIED BY welcome;

4. Open a new Interactive SQL window, connect to the sample database as BadUser, and attempt to access confidential information in the Employees table using the following SELECT statement:

   SELECT Surname, Salary FROM GROUPO.Employees;

   You receive an error message: Permission denied: you do not have permission to select from "Employees".

5. Run the following command to view the auditing information for this activity:

   dbtran -g -c "DSN=SQL Anywhere 11 Demo" -n demo.sql

6. Restore the sample database to its original state:

   • Use the DROP USER statement to remove the Test1 user from the database:

     DROP USER Test1;

   • Turn off auditing using the following SET OPTION statement:

     SET OPTION PUBLIC.auditing = 'Off';