sp_audit

Description

Allows a System Security Officer to configure auditing options.

Syntax

sp_audit option, login_name, object_name [,setting]

Or:

sp_audit ‘restart’

Parameters

option

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

**Table 1-4: Auditing options for sp_audit**
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. System stored procedures and command password parameters can be replaced with a fixed-length string of asterisks for security purposes. See “Hiding system stored procedure and command password parameters” for more information.
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.
encryption_key	Audits create encryption key, sp_encryption, drop encryption key, and alter encryption key
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.
login_locked	Audits the hostname and network IP address when a login account is locked due to exceeding the configured number of failed login attempts.
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 functions listed in Table 1-5. Using either of the 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.

**Table 1-5: Functions that sp_audit security audits**
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	ws_admin

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 – audits 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

Activates 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

pass and fail – apply to all options except cmdtext, errors, and adhoc. For these options, only on or off applies. The initial, default value of all options is off. If you select the cmdtext option to either pass or fail, Adaptive Server replaces the value with on.

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	Activates auditing for events that fail permission checks.

restart: If the audit process is forced to terminate due to an error, sp_audit can be manually restarted by entering:

sp_audit restart

The audit process can be restarted provided that no audit was currently running, but that the audit process has been configured to run by entering sp_configure “auditing” 1.

Examples

Example 1

Sets the login_locked audit option to initiate auditing of hostname and network IP addresses when a login account is locked due to exceeding the configured number of failed login attempts:

sp_audit "login_locked","all","all","ON"

If the audit tables are full and the event cannot be logged, a message with the information is sent to the errorlog.

Monitoring the audit logs for the Locked Login event (112) helps to identify attacks on login accounts.

Example 2

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 3

Displays the setting of the security auditing option:

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

Example 4

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

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

Example 5

Audits commands in the pubs2 database:

sp_audit "encryption_key", "all", "pubs2", "on"

Example 6

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

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

Example 7

Initiates auditing for all failed executions by a System Administrator.

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

Example 8

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 1-6 lists the configuration parameters that control auditing.

**Table 1-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.

All auditing configuration parameters are dynamic and take effect immediately.

Hiding system stored procedure and command password parameters

When auditing is configured and enabled, and the sp_audit option 'cmdtext' is set, system stored procedure and command password parameters are replaced with a fixed length string of asterisks in the audit records contained in the audit logs.

For example, executing:

sp_password 'oldpassword', 'newpassword'

when auditing is enabled and sp_audit cmdtext is set, results in output similar to:

sp_password '******', '******'

This protects passwords from being seen by other with access to the audit log.

Permissions

Only a System Security Officer can execute sp_audit.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect