Controlling auditing

The database administrator can turn on auditing to add security-related information to the transaction log. This can be done using Sybase Central or Interactive SQL.

Auditing is off by default. You must have DBA authority to enable and disable auditing.

To control auditing (Sybase Central)

  1. Connect to the database as a user with DBA authority.

  2. Right-click the database and choose Properties.

    The Database Properties window appears.

  3. Click the Auditing tab and specify which information to audit:

    • Do Not Collect Audit Information For This Database   No audit information is collected. This option disables auditing by setting the auditing database option to Off. See auditing option [database].

    • Collect All Audit Information For This Database   All types of auditing information are collected for the database. This option enables auditing by setting the auditing database option to On. See auditing option [database].

      The transaction log can grow significantly when this option is selected.

    • Collect The Following Type(s) Of Audit Information For This Database   Allows you to specify which auditing information to collect. For example, you can choose to collect only DDL changes. See sa_enable_auditing_type system procedure. Selecting this option changes the setting of the auditing_options database option. See auditing_options option [database].

  4. Click OK to close the Database Properties window.

To control auditing (Interactive SQL)

  1. Connect to your database as a user with DBA authority.

  2. Execute the following statement to turn on auditing:

    SET OPTION PUBLIC.auditing = 'On';

    To specify which types of auditing information you want to enable, use the following system procedure:

    CALL sa_enable_auditing_type( 'all' );

    You can control the type of auditing information that is collected by replacing all with the types of auditing you want to enable. See sa_enable_auditing_type system procedure.

  3. Execute the following statement to turn off auditing:

    SET OPTION PUBLIC.auditing = 'Off';

    To specify which types of auditing information you want to disable, use the following system procedure:

    CALL sa_disable_auditing_type( 'all' );

    You can stop collecting specific types of auditing information by replacing all with the types of auditing you want to disable. See sa_disable_auditing_type system procedure.

Auditing individual connections

Once you have enabled auditing for a database, you can set the temporary conn_auditing database option in the database login procedure to enable connection-specific auditing. You can enable auditing based on information such as the IP address of the client computer or the type of connection.

If you do not set the conn_auditing option in the login procedure, the option is on by default.

The following example shows an excerpt from a login procedure that enables auditing for all connections to the database, except those made by the DBA user: 

DECLARE usr VARCHAR(128)
SELECT CONNECTION_PROPERTY( 'Userid' ) INTO usr;
IF usr != 'DBA' THEN
   SET TEMPORARY OPTION conn_auditing='On'
ELSE
   SET TEMPORARY OPTION conn_auditing='Off'
END IF;

For more information, see login_procedure option [database] and conn_auditing option [database].

See also
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0