Auditing examples

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

Sybase Central auditing example

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

This connects you as a user with the SYS_AUTH_DBA_ROLE system role.
Turn on auditing:
1. Right-click the database and click Properties.
2. Click the Auditing tab.
3. Click Collect All Audit Information For This Database.
4. Click Apply.
5. Click OK.
Add a user named Test1 to the sample database, with the password welcome, and give the user the MANAGE PROFILING system privilege:
1. Right-click Users, and click New » User.
2. When prompted, name the user Test1, and type welcome as their password.
3. Click Finish.
4. Select the new user in Users, and go to the System Privileges tab.
5. Right-click in the System Privileges tab, and click New » Granted System Privileges.
6. Select MANAGE PROFILING and click OK to grant the MANAGE PROFILING system privilege to Test1.
7. In the Sybase Central main menu, click File » Save to save your changes.
8. In the Sybase Central main menu, click Connections » Disconnect to disconnect from the sample database.
Using Sybase Central, connect to the sample database as Test1 and attempt to access confidential information in the Employees table:
1. Click Tables, and then select the Employees table.
2. Click the Data tab.
  
  An error message appears indicating that you do not have permission to select from the Employees table. See Autorisation refusée : %1.
3. Click OK.
4. Disconnect from the sample database.

View the auditing information for this activity:

Use the SQL Anywhere 16 plug-in to connect to the database.
Select the database, and then click the Auditing tab in the right pane.
Click Retrieve Audit Messages.
Click Close.

Auditing information appears.

Use the filtering options to locate the error in the auditing information table. You can find the error for Test1 by selecting the Only Errors option. Use the date and time information to pinpoint the error. For example, if Test1 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

Restore the sample database to its original state:
1. Right-click the database, and then click Properties.
2. On the Auditing tab, click Do Not Collect Audit Information For This Database.
3. Click OK.
4. Click Users.
  
  Right-click Test1, and click Delete.

Interactive SQL auditing example

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

This connects you as a user with the SYS_AUTH_DBA_ROLE system role.
Turn on auditing using the SET OPTION statement, as follows:
SET OPTION PUBLIC.auditing = 'On';
Add a user, Test1, to the sample database using the CREATE USER statement, as follows:
CREATE USER Test1 IDENTIFIED BY welcome;
Open a new Interactive SQL window, connect to the sample database as Test1, and attempt to access confidential information in the Employees table using the following SELECT statement:
SELECT Surname, Salary FROM GROUPO.Employees;
An error message appears indicating that you do not have permission to select from the Employees table. See Autorisation refusée : %1.
Run the following command to view the auditing information for this activity:
dbtran -g -c "DSN=SQL Anywhere 16 Demo" -n demo.sql

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';

Discuter à propos de cette page dans DocCommentXchange.