Chapter 1: System Procedures

sp_addauditrecord

Description

Allows users to enter user-defined audit records (comments) into the audit trail.

Syntax

sp_addauditrecord [text [, db_name [, obj_name
	[, owner_name [, dbid [, objid]]]]]]

Parameters

text: is the text of the message to add to the current audit table. The text is inserted into the extrainfo field of the table.

db_name: is the name of the database referred to in the record. The name is inserted into the dbname field of the current audit table.

obj_name: is the name of the object referred to in the record. The name is inserted into the objname field of the current audit table.

owner_name: is the owner of the object referred to in the record. The name is inserted into the objowner field of the current audit table.

dbid: is the database ID number of db_name. Do not enclose this integer value in quotes. dbid is inserted into the dbid field of the current audit table.

objid: is the object ID number of obj_name. Do not enclose this integer value in quotes. objid is inserted into the objid field of the current audit table.

Examples

Example 1

Adds “I gave A. Smith permission to view the payroll table in the corporate database. This permission was in effect from 3:10 to 3:30 pm on 9/22/92.” to the extrainfo field; “corporate” to the dbname field; “payroll” to the objname field; “dbo” to the objowner field; “10” to the dbid field, and “1004738270” to the objid field of the current audit table:

sp_addauditrecord "I gave A. Smith permission to view the payroll table in the corporate database. This permission was in effect from 3:10 to 3:30 pm on 9/22/92.", "corporate", "payroll", "dbo", 10, 1004738270

Example 2

Adds this record to the audit trail. This example uses parameter names with the @ prefix, which allows you to leave some fields empty:

sp_addauditrecord @text="I am disabling auditing briefly while we reconfigure the system", @db_name="corporate"

Usage

Adaptive Server writes all audit records to the current audit table. The current audit table is determined by the value of the current audit table configuration parameter, set with sp_configure. An installation can have up to eight system audit tables, named sysaudits_01, sysaudits_02, and so forth, through sysaudits_08.

The records actually are first stored in the in-memory audit queue, and the audit process later writes the records from the audit queue to the current audit table. Therefore, you cannot count on an audit record being stored immediately in the audit table.
You can use sp_addauditrecord if:
- You have been granted execute permission on sp_addauditrecord – no special role is required
- Auditing is enabled – a System Security Officer used sp_configure to turn on the auditing configuration parameter
- The adhoc option of sp_audit is set to on

Permissions

Only a System Security Officer can execute sp_addauditrecord. The Database Owner of sybsecurity (who must also be a System Security Officer) can grant execute permission to other users.

Auditing

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

Event	Audit option	Command or access audited	Information in extrainfo
1	adhoc	User-defined audit record	extrainfo is filled by the text parameter of sp_addauditrecord
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