Sybase Auditing

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:

  • sp_auditoption: sets system-wide auditing options.
    1> sp_auditoption "enable auditing", "on"
    2> go
    
    this 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

  • sp_addauditrecord: insert a comment into audit trail

    There are 4 types of audit levels:
    1. databases: sp_auditdatabase: only logs big database events (drop database, truncate table, drop table, etc)
    2. objects: sp_auditobject: on a table/view gives select/insert/update/delete request auditing. Not so good b/c it only audits that there was a select REQUEST, not the actual code. Example:
      1> sp_auditobject tablename, db_name, "both", "s"
      2> go
      
      this 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 commands

    3. stored procedures: sp_auditsproc; Only an option for database that use stored procedures. In the times I've installed auditing, the applications did not use stored procs to any great extent.

    4. logins: sp_auditlogin; w/ cmdtext option gives exact query syntax requested. Gives the level of what we want.
      1> sp_auditlogin sa, "cmdtext", "on"
      2> go
      1> sp_auditlogin user, "cmdtext", "on"
      2> go
      
      sp_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.