sp_audit

Syntax

sp_audit option, login_name, object_name [,setting]

Or:

sp_audit ‘restart’

Parameters

option – is the name of the auditing option to set. The valid auditing options are:

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. Note: 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.
cluster	Audits cluster commands.
cmdtext	Audits all actions of a particular user. When auditing is configured and enabled, and 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. This protects passwords from being seen by other with access to the audit log.
config_history	Enables or disables auditing for configuration history.
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 the SAP ASE 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 the SAP ASE server.
mount	Audits mount database commands.
network	Audits specific network-related events, such as listener events. The valid settings are: on off pass fail
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 security-relevant events. See
select	Audits the execution of the select.
setuser	Audits the execution of the setuser.
sproc_auth	Enables auditing for authorization checks that are performed inside system stored procedures
table_access	Audits access to any table by a specific user.
transfer table	Audits the execution of the transfer table command
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:
- 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.
- network – audits specific network-related events, such as listener events.
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, the SAP ASE server displays the current auditing setting for the option. Valid values for the setting parameter are:
- on – activates auditing for the specified option. The SAP ASE 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. The SAP ASE 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, the SAP ASE server replaces the value with on.
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 are audited for updates. The auditing for existing tables is not affected.
```
sp_audit "update", "all", "default table", "on"
```
Example 9 – Initiates auditing for all transfer table commands entered for the titles table:
```
sp_audit "transfer table", "all", "titles", "on"
```

Usage

sp_audit determines what is 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
- exec_procedure
- exec_trigger
- insert
- select
- update
The configuration parameters that control auditing are:
- 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. The SAP ASE 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.

Permissions

The permission checks for sp_audit differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage auditing` privilege.
Disabled	With granular permissions disabled, you must be a user with sso_role.

Auditing

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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