Chapter 18: Auditing

Reading the extrainfo column

The extrainfo column contains a sequence of data separated by semicolons. The data is organized in the following categories.

**Table 18-4: Information in the extrainfo column**
Position	Category	Description
1	Roles	A list of active roles, separated by blanks.
2	Keywords or Options	The name of the keyword or option that was used for the event. For example, for the alter table command, the add column or drop constraint options might have been used. If multiple keywords or options are listed, they are separated by commas.
3	Previous value	If the event resulted in the update of a value, this item contains the value prior to the update.
4	Current value	If the event resulted in the update of a value, this item contains the new value.
5	Other information	Additional security-relevant information that is recorded for the event.
6	Proxy information	The original login name if the event occurred while a set proxy was in effect.
7	Principal name	The principal name from the underlying security mechanism if the user’s login is the secure default login, and the user logged in to Adaptive Server via unified login. The value of this item is NULL if the secure default login is not being used.

This example shows an extrainfo column entry for the event of changing an auditing configuration parameter.

sso_role;suspend audit when device full;1;0;;ralph;

This entry indicates that a System Security Officer changed suspend audit when device full from 1 to 0. There is no “other information” for this entry. The sixth category indicates that the user “ralph” was operating with a proxy login. No principal name is provided.

The other fields in the audit record give other pertinent information. For example, the record contains the server user ID (suid) and the login name (loginname).

Table 18-5 lists the values that appear in the event column, arranged by sp_audit option. The “Information in extrainfo” column describes information that might appear in the extrainfo column of an audit table, based on the categories described in Table 18-4.

**Table 18-5: Values in event and extrainfo columns**
Audit option	Command or access to be audited	event	Information in extrainfo
(Automatically audited event not controlled by an option)	Enabling auditing with: sp_configure auditing	73	—
(Automatically audited event not controlled by an option)	Disabling auditing with: sp_configure auditing	74	—
Unlocking Administrator’s account	Disabling auditing with: sp_configure auditing	74	—
adhoc	User-defined audit record	1	extrainfo is filled by the text parameter of sp_addauditrecord
alter	alter database	2	Keywords or options: alter maxhold alter size
alter	alter table	3	Keywords or options: add/drop/modify columns add constraint drop constraint
bcp	bcp in	4	—
bind	sp_bindefault	6	Other information: Name of the default
	sp_bindmsg	7	Other information: Message ID
	sp_bindrule	8	Other information: Name of the rule
cmdtext	All commands	92	Full text of command, as sent by the client
create	create database	9	—
	create default	14	—
	create procedure	11	—
	create rule	13	—
	create table	10	—
	create trigger	12	—
	create view	16	—
	create index	104	Other information: Name of the index
	create function	97	—
	sp_addmessage	15	Other information: Message number
dbaccess	Any access to the database by any user	17	Keywords or options: use cmd outside reference
dbcc	dbcc all keywords	81	Keywords or options: Any of the dbcc keywords such as checkstorage and the options for that keyword.
delete	delete from a table	18	Keywords or options: delete
delete	delete from a view	19	Keywords or options: delete
disk	disk init	20	Keywords or options: disk init Other information: Name of the disk
	disk mirror	23	Keywords or options: disk mirror Other information: Name of the disk
	disk refit	21	Keywords or options: disk refit Other information: Name of the disk
	disk reinit	22	Keywords or options: disk reinit Other information: Name of the disk
	disk release	87	Keywords or options: disk release Other information: Name of the disk
	disk remirror	25	Keywords or options: disk remirror Other information: Name of the disk
	disk unmirror	24	Keywords or options: disk unmirror Other information: Name of the disk
	disk resize	100	Keywords or options: disk resize Other information: Name of the disk
drop	drop database	26	—
	drop default	31	—
	drop procedure	28	—
	drop table	27	—
	drop trigger	29	—
	drop rule	30	—
	drop view	33	—
	drop index	105	Other information: Index name
	drop function	98	—
	sp_dropmessage	32	Other information: Message number
dump	dump database	34	—
dump	dump transaction	35	—
errors	Fatal error	36	Other information: Error number.Severity.State
errors	Non-fatal error	37	Other information: Error number.Severity.State
exec_procedure	Execution of a procedure	38	Other information: All input parameters
exec_trigger	Execution of a trigger	39	—
func_obj_access, func_dbaccess	Accesses to objects and databases via Transact-SQL functions	86	—
grant	grant	40	—
insert	insert into a table	41	Keywords or options: If insert is used: insert If select into is used: insert into followed by the fully qualified object name
insert	insert into a view	42	Keywords or options: insert
install	install	93	—
load	load database	43	—
load	load transaction	44	—
login	Any login to the server	45	Other information: Host name and IP address of the machine from which the login was performed.
logout	Any logouts from the server	46	Other information: Host name
mount	mount database	101	—
quiesce	quiesce database	96	—
reference	Creation of references to tables	91	Keywords or options: reference Other information: Name of the referencing table
remove	remove java	94	—
revoke	revoke	47	—
rpc	Remote procedure call from another server	48	Keywords or options: Name of client program Other information: Server name, host name of the machine from which the RPC was executed.
rpc	Remote procedure call to another server	49	Keywords or options: Procedure name
security	connect to (CIS only)	90	Keywords or options: connect to
	online database	83	—
	proc_role function (executed from within a system procedure)	80	Other information: Required roles
	Regeneration of a password by an SSO	76	Keywords or options: Setting SSO password Other information: Login name
	Role toggling	55	Previous value: on or off Current value: on or off Other information: Name of the role being set
	Server start	50	Other information: -dmasterdevicename -iinterfaces file path -Sservername -eerrorfilename
	sp_webservices	111	Keywords or options: deploy if deploying a web service. deploy_all if deploying all web services
	sp_webservices	111	Keywords or options: undeploy if undeploying a web service. undeploy_all if undeploying all web services
	Server shutdown	51	Keywords or options: shutdown
	set proxy or set session authorization	88	Previous value: Previous suid Current value: New suid
	sp_configure	82	Keywords or options: SETCONFIG Other information: If a parameter is being set: number of configuration parameter If a configuration file is being used to set parameters: name of the configuration file
	sp_ssladmin administration enabled	99	Keywords contains SSL_ADMIN addcert, if adding a certification.
	Audit table access	61	—
	create login, drop login	103	Keywords or options: create login, drop login
	create, drop, alter, grant, or revoke role	85	Keywords or options: create, drop, alter, grant, or revoke role
	built-in functions	86	Keywords or options: Name of function
	Security command or access to be audited, specifically, starting Adaptive Server with -u option to unlock the administrator’s account..	95	Other information contains 'Unlocking admin account'
select	select from a table	62	Keywords or options: select into select readtext
select	select from a view	63	Keywords or options: select into select readtext
setuser	setuser	84	Other information: Name of the user being set
table_access	delete	18	Keywords or options: delete
	insert	41	Keywords or options: insert
	select	62	Keywords or options: select into select readtext
	update	70	Keywords or options: update writetext
truncate	truncate table	64	—
unbind	sp_unbindefault	67	—
	sp_unbindmsg	69	—
	sp_unbindrule	68	—
unmount	unmount database	102	—
update	update to a table	70	Keywords or options: update writetext
update	update to a view	71	Keywords or options: update writetext
view_access	delete	19	Keywords or options: delete
	insert	42	Keywords or options: insert
	select	63	Keywords or options: select into select readtext
	update	71	Keywords or options: update writetext

Table 18-6 lists the values that appear in the event column, arranged by the audit event..

**Table 18-6: Audit event values**
Audit event ID	Command name	Audit event ID	Command name
1	ad hoc audit record	56	Reserved
2	alter database	57	Reserved
3	alter table	58	Reserved
4	bcp in	59	Reserved
5	Reserved	60	Reserved
6	bind default	61	access to audit table
7	bind message	62	select table
8	bind rule	63	select view
9	create database	64	truncate table
10	create table	65	Reserved
11	create procedure	66	Reserved
12	create trigger	67	unbind default
13	create rule	68	unbind rule
14	create default	69	unbind message
15	create message	70	update table
16	create view	71	update view
17	access to database	72	Reserved
18	delete table	73	auditing enabled
19	delete view	74	auditing disabled
20	disk init	75	Reserved
21	disk refit	76	SSO changed password
22	disk reinit	77	Reserved
23	disk mirror	78	Reserved
24	disk unmirror	79	Reserved
25	disk remirror	80	role check performed
26	drop database	81	dbcc
27	drop table	82	config
28	drop procedure	83	online database
29	drop trigger	84	setuser command
30	drop rule	85	UDR command
31	drop default	86	built-in function
32	drop message	87	Disk release
33	drop view	88	set SSA command
34	dump database	89	kill or terminate command
35	dump transaction	90	connect
36	Fatal error	91	reference
37	Non-fatal error	92	command text
38	execution of stored procedure	93	JCS install command
39	Execution of trigger	94	JCS remove command
40	grant	95	Unlock admin account
41	insert table	96	quiesce database
42	insert view	97	create SQLJ function
43	load database	98	drop SQLJ function
44	load transaction	99	SSL administration
45	login	100	disk resize
46	logout	101	mount database
47	revoke	102	unmount database
48	rpc in	103	login command
49	rpc out	104	create index
50	server boot	105	drop index
51	server shutdown	106	Reserved
52	Reserved	107	Reserved
53	Reserved	108	Reserved
54	Reserved	109	Reserved
55	role toggling	110 111	deploy user-defined web services undeploy user defined web services

View this book as PDF