Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

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 using the SQL Anywhere 10 Demo data source. This connects you as a DBA user.

   • From the Context dropdown list, choose demo - DBA to ensure the SQL Anywhere sample database is selected, and then from the File menu, choose Options.

   • Select auditing from the list of options, and type the value On in the Value field. Click Set Permanent Now to set the option and then click Close.

   Alternatively, you can use Interactive SQL. Connect to the sample database from Interactive SQL as the DBA user 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. Use Interactive SQL to connect to the sample database as BadUser and attempt to access confidential information in the Employees table with the following query:

   SELECT Surname, Salary
   FROM GROUPO.Employees;

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

4. At a command prompt, execute the following command:

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

   This command produces a file named demo.sql, which contains the transaction log information and a set of comments holding audit information. The lines that indicate the unauthorized BadUser attempt to access the Employees table are included in the file as follows:

   --AUDIT-1001-0000287812 -- 2004/02/11 13:59:58.765 Checking Select permission on Employees - Failed
   --AUDIT-1001-0000287847 -- 2004/02/11 13:59:58.765 Checking Select permission on Employees(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 a DBA user, and perform the following operations:

   • Revoke Connect privileges from the user ID BadUser.

   • Set the PUBLIC.auditing option to Off.