Auditing options: Types and requirements

adhoc

(user-specific)

all

all

Any

Allows users to use sp_addauditrecord

Example: sp_audit "adhoc", "all", "all", "on"

(Enables ad hoc user-defined auditing records.)

all

(user-specific)

A login name or role

all

Any

All actions of a particular user or by users with a particular role active

Example sp_audit "all", "sa_role", "all", "on"

(Turns auditing on for all actions in which the sa_role is active.)

alter

(database-specific)

all

Database to be audited

Any

alter database, alter table

Example sp_audit @option = "alter", @login_name = "all", @object_name = "master", @setting = "on"

(Turns auditing on for all executions of alter database and alter table in the master database.)

bcp

(database-specific)

all

Database to be audited

Any

bcp in

Example sp_audit "bcp", "all", "pubs2"

(Returns the status of bcp auditing in the pubs2 database. If you do not specify a value for setting, Adaptive Server returns the status of auditing for the option you specify)

bind

(database-specific)

all

Database to be audited

Any

sp_bindefault, sp_bindmsg, sp_bindrule

Example sp_audit "bind", "all", "planning", "off"

(Turns bind auditing off for the planning database.)

cmdtext

(user-specific)

A login name, role, or “all” for all users in the database

all

Any

SQL text entered by a user.(Does not reflect whether or not the text in question passed permission checks or not. eventmod always has a value of 1.)

Example sp_audit "cmdtext", "sa", "all", "off"

(Turns text auditing off for Database Owners.)

create

(database-specific)

all

Database to be audited

Any

create database, create table, create procedure, create trigger, create rule, create default, sp_addmessage, create view, create index, create function

Example sp_audit "create", "all", "planning", "pass"

(Turns on auditing of successful object creations in the planning database. The current status of auditing create database is not affected because you did not specify the master database.)

dbaccess

(database-specific)

all

Database to be audited

Any

Any access to the database from another database

Example sp_audit "dbaccess", "all", "project", "on"

(Audits all external accesses to the project database.)

dbcc

(global)

all

all

Any

All dbcc commands that require permissions

Example sp_audit "dbcc", "all", "all", "on"

(Audits all executions of the dbcc command.)

delete

(object-specific)

all

Name of the table or view to be audited, or default view or default table

The database of the table or view (except tempdb)

delete from a table, delete from a view

Example sp_audit "delete", "all", "default table", "on"

(Audits all delete actions for all future tables in the current database.)

disk

(global)

all

all

Any

disk init, disk refit, disk reinit, disk mirror, disk unmirror, disk remirror, disk resize

Example sp_audit "disk", "all", "all", "on"

(Audits all disk actions for the server.)

drop

(database-specific)

all

Database to be audited

Any

drop database, drop table, drop procedure, drop index, drop trigger, drop rule, drop default, sp_dropmessage, drop view, drop function

Example sp_audit "drop", "all", "financial", "fail"

(Audits all drop commands in the financial database that fail permission checks.)

dump

(database-specific)

all

Database to be audited

Any

dump database, dump transaction

Example sp_audit "dump", "all", "pubs2", "on"

(Audits dump commands in the pubs2 database.)

errors

(global)

all

all

Any

Fatal error, non-fatal error

Example sp_audit "errors", "all", "all", "on"

(Audits errors throughout the server.)

exec_procedure

(object-specific)

all

Name of the procedure to be audited or default procedure

The database of the procedure (except tempdb)

execute

Example sp_audit "exec_procedure", "all", "default procedure", "off"

(Turns automatic auditing off for new procedures in the current database.)

exec_trigger

(object-specific)

all

Name of the trigger to be audited or default trigger

The database of the trigger (except tempdb)

Any command that fires the trigger

Example sp_audit "exec_trigger", "all", "trig_fix_plan", "fail"

(Audits all failed executions of the trig_fix_plan trigger in the current database.)

func_dbaccess

(database-specific)

all

Name of the database you are auditing

Any

Access to the database using the following functions: curunreserved_pgs, db_name, db_id, lct_admin, setdbrepstat, setrepstatus, setrepdefmode, is_repagent_enabled, rep_agent_config, rep_agent_admin

Example sp_audit @option="func_dbaccess", @login_name="all", @object_name = "strategy", @setting = "on"

(Audits accesses to the strategy database via built-in functions.)

func_obj_access

(object-specific)

all

Name of any object that has an entry in sysobjects

Any

Access to an object using the following functions: schema_inc, col_length, col_name, data_pgs, index_col, object_id, object_name, reserved_pgs, rowcnt, used_pgs, has_subquery

Example sp_audit @option="func_obj_access", @login_name="all", @object_name = "customer", @setting = "on"

(Audits accesses to the customer table via built-in functions.)

grant

(database-specific)

all

Name of the database to be audited

Any

grant

Example sp_audit @option="grant", @login_name="all", @object_name = "planning", @setting = "on"

(Audits all grants in the planning database.)

insert

(object-specific)

all

Name of the view or table to which you are inserting rows, or default view or default table

The database of the object (except tempdb)

insert into a table, insert into a view

Example sp_audit "insert", "all", "dpt_101_view", "on"

(Audits all inserts into the dpt_101_view view in the current database.)

install

all

Database to be audited

Any

install java

Example sp_audit "install", "all", "planning", "on"

(Audits the installation of java classes in database planning)

load

(database-specific)

all

Database to be audited

Any

load database, load transaction

Example sp_audit "load", "all", "projects_db", "fail"

(Audits all failed executions of database and transaction loads in the projects_db database.)

login

(global)

all

all

Any

Any login to Adaptive Server

Example sp_audit "login", "all", "all", "fail"

(Audits all failed attempts to log in to the server.)

logout

all

all

Any

Any logout from Adaptive Server

Example sp_audit "logout", "all", "all", "off"

(Turns auditing off of logouts from the server.)

mount

(global)

all

all

Any

mount database

Example sp_audit "mount", "all", "all", "on"

(Audits all mount database commands issued.)

quiesce

(global)

all

all

Any

quiesce database

Example sp_audit "quiesce", "all", "all", "on"

(Turns auditing on for quiesce database commands.)

reference

(object-specific)

all

Name of the view or table to which you are inserting rows, or default view or default table

Any

create table, alter table

Example sp_audit "reference", "all", "titles", "off"

(Turns off auditing of the creation of references to the titles table.)

remove

(global)

all

all

Any

Audits the removal of Java classes

Example sp_audit "remove", "all", "all", "on"

(Audits the removal of Java classes in all databases.)

revoke

(database-specific)

all

Database to be audited

Any

revoke

Example sp_audit "revoke", "all", "payments_db", "off"

(Turns off auditing of the execution of revoke in the payments_db database.)

rpc

(global)

all

all

Any

Remote procedure calls (either in or out)

Example sp_audit "rpc", "all", "all", "on"

(Audits all remote procedure calls out of or into the server.)

security

(global)

all

all

Any

Server-wide security-relevant events. See the “security” option in Table 11-6.

Example sp_audit "security", "all", "all", "on"

(Audits server-wide security-relevant events in the server.)

select

(object-specific)

all

Name of the view or table to which you are inserting rows, or default view or default table

The database of the object (except tempdb)

select from a table, select from a view

Example sp_audit "select", "all", "customer", "fail"

(Audits all failed selects from the customer table in the current database.)

setuser

(database-specific)

all

all

Any

setuser

Example sp_audit "setuser", "all", "projdb", "on"

(Audits all executions of setuser in the projdb database.)

table_access

(user-specific)

Name of the login to be audited, or all if all users are to be audited.

all

Any

select, delete, update, or insert access in a table

Example sp_audit "table_access", "smithson", "all", "on"

(Audits all table accesses by the login named “smithson”.)

truncate

(database-specific)

all

Database to be audited

Any

truncate table

Example sp_audit "truncate", "all", "customer", "on"

(Audits all table truncations in the customer database.)

unbind

(database-specific)

all

Database to be audited

Any

sp_unbindefault, sp_unbindrule, sp_unbindmsg

Example sp_audit "unbind", "all", "master", "fail"

(Audits all failed attempts of unbinding in the master database.)

unmount

all

all

Any

unmount database

Example sp_audit "unmount", "all", "projects", "on"

(Audits all attempts by users to mount the projects table in the current database.)

update

(object-specific)

all

Name specifying the object to be audited, default table or default view

The database of the object (except tempdb)

update to a table, update to a view

Example sp_audit "update", "all", "projects", "on"

(Audits all attempts by users to update the projects table in the current database.)

view_access

(user-specific)

Login name of the user to be audited, or all to audit all users

all

Any

select, delete, insert, or update to a view

Example sp_audit "view_access", "joe", "all", "off"

(Turns off view auditing of user “joe”.)