sp_audit

Description

Allows a System Security Officer to configure auditing options.

Syntax

sp_audit option, login_name, object_name [,setting]

Parameters

option

is the name of the auditing option to set. Table 1-5 lists the valid auditing options.

Table 1-5: Auditing options

Option

Description

adhoc

Allows users to use sp_addauditrecord to add their own user-defined audit records to the audit trail.

all

Audits all actions performed by a particular user or by users with a particular role. You can only use this option to specify system roles.

NoteAuditing all actions does not affect whether users can add ad hoc audit records.

alter

Audits the execution of the alter table or alter database commands.

bcp

Audits the execution of the bcp in utility.

bind

Audits the execution of sp_bindefault, sp_bindmsg, and sp_bindrule system procedures.

cmdtext

Audits all actions of a particular user.

create

Audits the creation of database objects.

dbaccess

Audits access to the current database from another database.

dbcc

Audits the execution of any dbcc command.

delete

Audits the deletion of rows from a table or view.

disk

Audits the execution of disk init, disk refit, disk reinit, disk mirror, disk unmirror, and disk remirror.

drop

Audits the dropping of database objects.

dump

Audits the execution of dump database or dump transaction.

errors

Audits errors, whether fatal or not.

exec_procedure

Audits the execution of a stored procedure.

exec_trigger

Audits the execution of a trigger.

func_dbaccess

Audits access to a database via a Transact-SQL function.

func_obj_access

Audits access to a database object via a Transact-SQL function.

grant

Audits the execution of the grant.

insert

Audits the insertion of rows into a table or view.

install

Audits the installation of Java classes.

load

Audits the execution of the load database or load transaction

login

Audits all login attempts into Adaptive Server.

logout

Audits all logout attempts from Adaptive Server.

mount

Audits mount database commands.

quiesce

Audits quiesce database commands.

reference

Audits references between tables.

remove

Audits the removal of Java classes.

revoke

Audits the execution of the revoke.

rpc

Audits the execution of remote procedure calls.

security

Audits the following security-relevant events:

  • Starting up or shutting down the server

  • Activating or deactivating a role

  • Issuing any of the following commands:

    • addcert

    • connect

    • create and drop login

    • dropcert

    • create, drop, alter, grant, and revoke role

    • kill

    • online database

    • set proxy

    • set session authorization

    • sp_configure

  • The following functions:

    • config_admin

    • set_password

    • valid_user

    • attr_notify

    • ha_add_companion

    • ha_remove_companion

    • ha_check_alive

    • ha_getversion

    • ha_failback

    • ha_retrestrictionclass

    • ha_getrcs

    • ha_setrcs

    • ha_hacluster_verify

    • js_wakeup

    • unlock_admin_account

    • ssl_admin

  • Using any of the following functions:

    • valid_user

    • proc_role (from within a system procedure)

  • Regenerating the SSO passwords

select

Audits the execution of the select.

setuser

Audits the execution of the setuser.

table_access

Audits access to any table by a specific user.

truncate

Audits the execution of the truncate table.

unbind

Audits the execution of the sp_unbindrule, sp_unbindmsg, and sp_unbindefault.

unmount

Audits the execution of the umount database command.

update

Audits updates to rows in a table or view.

view_access

Audits access to any view by a specific user.

login_name

is the parameter that lets you specify all, a system role, or the name of a specific login to be audited. However, system roles can only be specified if you use the all option. You cannot audit individual options for a system role.

object_name

is the name of the object to be audited. Valid values, depending on the value you specified for option, are:

See the System Administration Guide for more information about the object_name values that are valid with each option value.

setting

is the level of auditing. If you do not specify a value for setting, Adaptive Server displays the current auditing setting for the option. Valid values for the setting parameter are described in the following table:

setting value

Description

on

Activates auditing for the specified option. Adaptive Server generates audit records for events controlled by this option, whether the event passes or fails permission checks.

off

Deactivates auditing for the specified option.

pass

Activates auditing for events that pass permission checks.

fail

Deactivates auditing for events that fail permission checks.

If you specify pass for an option and later specify fail for the same option, or vice versa, the result is equivalent to specifying on. Adaptive Server generates audit records regardless of whether events pass or fail permission checks. Settings of on or off apply to all auditing options. Settings of pass and fail apply to all options except errors and adhoc. For these options, only on or off applies. The initial, default value of all options is off.

Examples

Example 1

Initiates auditing for SSL security-relevant events. Both successful and failed events are audited:

sp_audit "security", "all", "all", "on"
sample records added:

To view the events from sybsecurity:

select * from sybsecurity..sysaudits_01 where event=99

Example 2

Displays the setting of the security auditing option:

sp_audit "security", "all", "all"

Example 3

Initiates auditing for the creation of objects in the master database, including create database.

sp_audit "create", "all", master, "on"

Example 4

Initiates auditing for the creation of all objects in the db1database:

sp_audit "create", "all", db1, "on"

Example 5

Initiates auditing for all failed executions by a System Administrator.

sp_audit "all", "sa_role", "all", "fail"

Example 6

Initiates auditing for all updates to future tables in the current database. For example, if the current database is utility, all new tables created in utility will be audited for updates. The auditing for existing tables is not affected.

sp_audit "update", "all", "default table", "on"

Usage

Permissions

Only a System Security Officer can execute sp_audit.

See also

Documents For more information about configuring Adaptive Server for auditing, see sp_configure in the System Administration Guide.

System procedures sp_addauditrecord, sp_configure, sp_addaudittable

Utility commands bcp