Reading the extrainfo column

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

Table 11-5: 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 into 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; SETCONFIG; 1; 0; 261; ralph; ;

This entry indicates that a System Security Officer changed the value of a configuration parameter from 1 to 0. The fifth catagory (Other Information) indicates that configuration parameter number 261 (which is the suspend audit when device full configuration parameter) was changed. The sixth category (Proxy Information) 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 11-6 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 11-5.

Table 11-6: 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

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 table

3

Keywords or options:

  • add column

  • drop column

  • replace column

  • 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

17

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 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 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 transaction

35

errors

Fatal error

36

Other information: Error number.Severity.State

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 into a view

42

Keywords or options: insert

install

install

93

load

load database

43

load transaction

44

login

Any login to the server

45

Other information: Host name and IP adress of the machine from which the login was done.

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

roles

create role, drop role, alter role, grant role, revoke role

85

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 done.

Remote procedure call to another server

49

Keywords or options: Procedure name

security

connect to (CIS only)

90

Keywords or options: connect to

kill (CIS only)

89

Keywords or options: kill

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

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

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

select

select from a table

62

Keywords or options:

  • select into

  • select

  • readtext

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