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 8-5 lists the valid auditing options.

**Table 8-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. Auditing 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.
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.
reference	Audits references between tables.
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 dropcert kill online database set proxy set session authorization sp_configure 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.
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:

The object name, including the owner’s name if you do not own the object. For example, to audit a table named inventory that is owned by Joe, you would specify joe.inventory for object_name.
all for all objects.
default table, default view, default procedure, or default trigger to audit access to any new table, view, procedure, or trigger.

default table and default view are valid values for object_name when you specify delete, insert, select, or update for the option parameter. default procedure is valid when you specify the exec_procedure option. default trigger is valid when you specify the exec_trigger option.

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

sp_audit determines what will be audited when auditing is enabled. No actual auditing takes place until you use sp_configure to set the auditing parameter to on. Then, all auditing options that have been configured with sp_audit take effect. For more information, see sp_configure.
If you are not the owner of the object being specified, qualify the object_name parameter value with the owner’s name, in the following format:
```
"ownername.objname"
```
You cannot activate default auditing for the following options in the tempdb database:
- delete
- insert
- select
- update
- exec_procedure
- exec_trigger

Table 8-6 lists the configuration parameters that control auditing.

**Table 8-6: Configuration parameters that control auditing**
Configuration parameter	Effect
auditing	Enables or disables auditing for the server.
audit_queue_size	Establishes the size of the audit queue.
current_audit_table	Sets the current audit table. Adaptive Server writes all audit records to that table.
suspend_auditing_when_full	Controls the behavior of the audit process when an audit device becomes full.

The auditing, current_audit_table, and suspend_auditing_when_full configuration parameters are dynamic and take effect immediately. Because audit_queue_size affects memory allocation, the parameter is static and does not take effect until Adaptive Server is restarted.

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

Permissions

Only a System Security Officer can execute sp_audit.