This page will serve as a quick and dirty sybase auditing overview/tutorial and will discuss the levels of auditing one would typically configure. Auditing is installed via sybinit; you have to specify that you want it to be installed, and you'll have to create a seperate database (sybsecurity) for it. Typically have a 50mb database on its own device (sybsecurity is the device name too). All audit information is stored in the sysaudits table in sybsecurity; you'll need sso_role to view it (sa gets sso_role automatically).
These are the stored procedures that control auditing:1> sp_auditoption "enable auditing", "on" 2> gothis must be done before any auditing can begin. sp_auditoption by itself shows all option settings. You can just toggle this on/off and play with all the auditing options before turning it back on... very nice
1> sp_auditobject tablename, db_name, "both", "s" 2> gothis audits failed and successful attempts to do selects on the table tablename in database db_name.
this ended up being bad having the failed select attempts b/c we were logging some ct_results() pending errors that were huge; so i turned off failures. The only way to turn off individual ok/fail/both is to clear them by turning everything off then reset.
sp_auditobject [table] will show status of table-level commands1> sp_auditlogin sa, "cmdtext", "on" 2> go 1> sp_auditlogin user, "cmdtext", "on" 2> gosp_auditlogin by itself shows status of all logins; table/view/cmdtext are options
How do i see the audit trail?
Only users w/ sso_role can view
the sybsecurity..sysaudits table where the audit trail resides. You can
perform a select from this table and delimit your query by any of several
useful fields (loginname, database name, object or table name, eventtime,
etc). Example sysaudits queries are:
1> select loginname,extrainfo from sysaudits where dbname="db_name" 2> go 1> select dbname,objname,extrainfo from sysaudits where loginname="sa" 2> go 1> select loginname,dbname,objname from sysaudits where eventtime>"9/4/97 12:00" 2> go
Archiving/Cleaning up the audit trail: very important: if you don't have a process to truncate the sysaudits table, you WILL crash your application users. Guaranteed. Even worse, often the whole server crashes...so archiving is important
Here's our plan: we have a special sp_thresholdaction process in place that looks like the following:
use sybsecurity go if exists (select * from sysobjects where name = "sp_thresholdaction" and type = 'P') drop proc sp_thresholdaction go create procedure sp_thresholdaction @dbname varchar(30), @segmentname varchar(30), @space_left int, @status int as insert db_name..sysaudits_db_name select loginname,dbname,objname,extrainfo from sysaudits where dbname="db_name" dump transaction @dbname with truncate_only truncate table sysaudits print "LCT fired on '%1!' with truncate_only and sysaudit table truncate", @dbname go
Notice this will insert all audit records we desire to keep (i.e., all for the database db_name) into a holding table in db_name, then truncates sysaudits before truncating the log. the db_name..sysaudits_db_name table MUST be existant for this process to work, thus if you are cleaning up db_name..sysaudits_db_name just truncate it (don't drop it). The text of the queries being run is in the "extrainfo" field; the other three fields should be self-explaitory
Performance/Tuning Aspects: tune the audit queue size through sp_configure: if its too small the system will slow down actual transactions...caveat: when large, audit records are buffered which can mean data loss during server crash.
We're getting thousands of these messages in sysaudits...why?
Open Client Message Layer 1, Origin 1, Severity 1, Number 163 ct_results(): user api layer: external error: This routine cannot be called until all fetchable results have been completely processed.