Allows a System Security Officer to configure auditing options.
sp_audit option, login_name, object_name [,setting]
is the name of the auditing option to set. Table 1-5 lists the valid 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.
|
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:
|
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. |
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.
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.
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.
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
Displays the setting of the security auditing option:
sp_audit "security", "all", "all"
Initiates auditing for the creation of objects in the master database, including create database.
sp_audit "create", "all", master, "on"
Initiates auditing for the creation of all objects in the db1database:
sp_audit "create", "all", db1, "on"
Initiates auditing for all failed executions by a System Administrator.
sp_audit "all", "sa_role", "all", "fail"
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"
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 1-6 lists the 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. |
All auditing configuration parameters are dynamic and take effect immediately.
Only a System Security Officer can execute sp_audit.
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