sysauditoptions  syscharsets

Chapter 12: System Tables

sysaudits_01 – sysaudits_08

sybsecurity database

Description

These system tables contain the audit trail. Only one table at a time is active. The active table is determined by the value of the current audit table configuration parameter. An installation can have up to eight audit tables. For example, if your installation has three audit tables, the tables are named sysaudits_01, sysaudits_02, and sysaudits_03. An audit table contains one row for each audit record.

Columns

The columns for sysaudits_01 ­– sysaudits_08 are:

Name

Datatype

Description

event

smallint

Type of event being audited. See Table 12-2.

eventmod

smallint

Further information about the event. Possible values are:

  • 0 = no modifier for this event

  • 1 = the event passed permission checking

  • 2 = the event failed permission checking

spid

smallint

Server process ID of the process that caused the audit record to be written.

eventtime

datetime

Date and time of the audited event.

sequence

smallint

Sequence number of the record within a single event; some events require more than one audit record.

suid

smallint

Server login ID of the user who performed the audited event.

dbid

int null

Database ID in which the audited event occurred or the object/stored procedure/trigger resides, depending on the type of event.

objid

int null

ID of the accessed object or stored procedure/trigger.

xactid

binary(6) null

ID of the transaction containing the audited event. For a multi-database transaction, this is the transaction ID from the database where the transaction originated.

loginname

varchar(30) null

Login name corresponding to the suid.

dbname

varchar(30) null

Database name corresponding to the dbid.

objname

varchar(30) null

Object name corresponding to the objid.

objowner

varchar(30) null

Name of the owner of objid.

extrainfo

varchar(255) null

Additional information about the audited event. This field contains a sequence of items separated by semicolons. See Table 12-1.

The extrainfo column contains a sequence of items separated by semicolons as shown in Table 12-1:

Table 12-1: Items in the extrainfo column

Item

Contents

Roles

Lists the roles that are active. The roles are separated by blanks.

Subcommand

The name of the subcommand or command option that was used for the event. For example, for the alter table command, the options add column or drop constraint might be used. Multiple subcommands or options are separated by commas.

Previous value

The value prior to the update if the event resulted in the update of a value.

Current value

The new value if the event resulted in the update of a value.

Other information

Additional security-relevant information that is recorded for the event.

Proxy information

The original login name, if the event occurred while a set proxy was in effect.

Principal information

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 field is NULL, if the secure default login is not being used.

An example of an extrainfo column for the security-relevant event of changing an auditing configuration parameter might be:

sso_role;suspend auditing when full;1;0;;;;

This extrainfo column indicates that a System Security Officer changed the configuration parameter suspend auditing when full from 1 (suspend all processes that involve an auditing event) to 0 (truncate the next audit table and make it the current audit table). The other columns in the audit record give other pertinent information. For example, the record contains the server user id (suid) and the login name (loginname).

The event column values that pertain to each audit event are listed in Table 12-2.

Table 12-2: Values in event and extrainfo column

Event

Audit option

Command or access audited

extrainfo

1

adhoc

User-defined audit record

extrainfo is filled by the text parameter of sp_addauditrecord

2

alter

alter database

  • Roles – Current active roles

  • Subcommand– ALTER SIZE

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if a set proxy is in effect

3

alter

alter table

  • Roles – Current active roles

  • Subcommand – ADD COLUMN, REPLACE COLUMN, ADD CONSTRAINT, or DROP CONSTRAINT

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if a set proxy is in effect

4

bcp

bcp in

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

6

bind

sp_bindefault

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of default

  • Proxy information – Original login name, if set proxy in effect

7

bind

sp_bindmsg

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Message ID

  • Proxy information – Original login name, if set proxy in effect

8

bind

sp_bindrule

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the rule

  • Proxy information – Original login name, if set proxy in effect

9

create

create database

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

10

create

create table

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

11

create

create procedure

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

12

create

create trigger

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

13

create

create rule

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

14

create

create default

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Other information – NULL

  • Current value – NULL

  • Proxy information – Original login name, if set proxy in effect

15

create

sp_addmessage

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Message Number

  • Proxy information – Original login name, if set proxy in effect

16

create

create view

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

17

dbaccess

Any access to the database by any user

  • Roles – Current active roles

  • Subcommand – USE CMD or OUTSIDE REFERENCE

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

18

delete

delete from a table

  • Roles – Current active roles

  • Subcommand – DELETE

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

19

delete

delete from a view

  • Roles – Current active roles

  • Subcommand – DELETE

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

20

disk

disk init

  • Roles – Current active roles

  • Subcommand – disk init

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the disk

  • Proxy information – Original login name, if set proxy in effect

21

disk

disk refit

  • Roles – Current active roles

  • Subcommand – disk refit

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the disk

  • Proxy information – Original login name, if set proxy in effect

22

disk

disk reinit

  • Roles – Current active roles

  • Subcommand – disk reinit

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the disk

  • Proxy information – Original login name, if set proxy in effect

23

disk

disk mirror

  • Roles – Current active roles

  • Subcommand – disk mirror

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the disk

  • Proxy information – Original login name, if set proxy in effect

24

disk

disk unmirror

  • Roles – Current active roles

  • Subcommand – disk unmirror

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the disk

  • Proxy information – Original login name, if set proxy in effect

25

disk

disk remirror

  • Roles – Current active roles

  • Subcommand – disk remirror

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the disk

  • Proxy information – Original login name, if set proxy in effect

26

drop

drop database

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

27

drop

drop table

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

28

drop

drop procedure

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

29

drop

drop trigger

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

30

drop

drop rule

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

31

drop

drop default

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

32

drop

sp_dropmessage

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Message number

  • Proxy information – Original login name, if set proxy in effect

33

drop

drop view

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

34

dump

dump database

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

35

dump

dump transaction

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

36

errors

Fatal error

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Error number.Severity.State

  • Proxy information – Original login name, if set proxy in effect

37

errors

Non-fatal error

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Error number.Severity.State

  • Proxy information – Original login name, if set proxy in effect

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

39

exec_trigger

Execution of a trigger

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

40

grant

grant

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

41

insert

insert into a table

  • Roles – Current active roles

  • Subcommand

    • If insert – INSERT

    • If select into – INSERT INTO followed by the fully qualified object name

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

42

insert

insert into a view

  • Roles – Current active roles

  • Subcommand – INSERT

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if a set proxy is in effect

43

load

load database

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

44

load

load transaction

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

45

login

Any login to Adaptive Server

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Host name of the machine from which login was done

  • Proxy information – Original login name, if set proxy in effect

46

logout

Any logouts from Adaptive Server

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Host name of the machine from which login was done

  • Proxy information – Original login name, if set proxy in effect

47

revoke

revoke

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

48

rpc

Remote procedure call from another server

  • Roles – Current active roles

  • Subcommand – Name of client program

  • Previous value – NULL

  • Current value – NULL

  • Other information – Server name, host name of the machine from which the RPC was done.

  • Proxy information – Original login name, if set proxy in effect

49

rpc

Remote procedure call to another server

  • Roles – Current active roles

  • Subcommand – Procedure name

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

50

security

Server start

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information

    • -dmasterdevicename

    • -iinterfaces file path

    • -Sservername

    • -eerrorfilename

  • Proxy information – Original login name, if set proxy in effect

51

security

Server shutdown

  • Roles – Current active roles

  • Subcommand – shutdown

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

55

security

Role toggling

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – “on” or “off”

  • Current value – “on” or “off”

  • Other information – Name of the role being set

  • Proxy information – Original login name, if set proxy in effect

61

table_access

Table access

  • Roles – Current active roles

  • Subcommand – SELECT, SELECT INTO, INSERT, UPDATE, DELETE, REFERENCE, READTEXT, or WRITETEXT

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

62

select

select from a table

  • Roles – Current active roles

  • Subcommand – SELECT INTO, SELECT, or READTEXT

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

63

select

select from a view

  • Roles – Current active roles

  • Subcommand – SELECT, SELECT INTO, or READTEXT

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

64

truncate

truncate table

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

67

unbind

sp_unbindefault

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

68

unbind

sp_unbindrule

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

69

unbind

sp_unbindmsg

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

70

update

update to a table

  • Roles – Current active roles

  • Subcommand – UPDATE or WRITETEXT

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

71

update

update to a view

  • Roles – Current active roles

  • Subcommand – UPDATE or WRITETEXT

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

73

NoteThis event is audited automatically. It is not controlled by an audit option.

Turning the auditing parameter on with sp_configure

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

74

NoteThis event is audited automatically. It is not controlled by an audit option.

Turning the auditing parameter off with sp_configure

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

76

security

Regeneration of a password by a System Security Officer (SSO)

  • Roles – Current active roles

  • Subcommand – Setting SSO password

  • Previous value – NULL

  • Current value – NULL

  • Other information – Login name

  • Proxy information – Original login name, if set proxy in effect

80

security

proc_role within a system procedure

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Required roles

  • Proxy information – Original login name, if set proxy in effect

81

dbcc

dbcc

  • Roles – Current active roles

  • Subcommand – The dbcc subcommand name

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

82

security

sp_configure

  • Roles – Current active roles

  • Subcommand – Name of the configuration parameter

  • Previous value – Old parameter value if command is setting a new value

  • Current value – New parameter value if command is setting a new value

  • Other information – Number of configuration parameter, if a parameter is being set; name of configuration file, if a configuration file is being used to set parameters

  • Proxy information – Original login name, if set proxy in effect

83

security

online database

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

84

setuser

setuser

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the user being set

  • Proxy information – Original login name, if a set proxy is in effect

85

func_obj_access, func_dbaccess

Accesses to objects and databases via Transact-SQL functions

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

85

security

valid_user

  • Roles – Current active roles

  • Subcommand – valid_user

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

88

security

set proxy or set session authorization

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – Previous suid

  • Current value – New suid

  • Other information – NULL

  • Proxy information – Original login name, if set proxy or set session authorization had no parameters; otherwise, NULL.

92

cmdtxt

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

  • Roles – Current active roles

  • Subcommand – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect



Copyright © 2002. Sybase Inc. All rights reserved. syscharsets

View this book as PDF