Menu [toggle]

Tikiwiki Assistant

Thank you for installing Tikiwiki!

LoginTo begin configuring Tiki, please login as the Admin.

The Tikiwiki CommunityTo learn more, visit:

Tikiwiki DocumentationFor help, visit


Installing Sybsecurity-Auditing

Sybase Auditing - install & configure

ASE stores the 'audit trail' in system tables, named sysaudits_01 - 08.
At any given time, only one of the audit tables is current.
ASE writes to the current audit table.


create a device for each audit trail table
i.e. sysaudit_data01, sysaudit_data02, sysaudit_log01

create database sybsecurity on sysaudit_data01=100
log on sysaudit_log01=100
alter database sybsecurity on sysaudit_data02=100

run in $SYBASE/$SYBASE_ASE/scripts/installsecurity & restart ASE

sp_configure 'auditing', 1
--suspend auditing if the tables get full. Set to 1 if using a threshold action to manage tables.
sp_configure 'suspend audit when device full', 1

--Add audit tables. This created systen audit tables called sysaudits_01, etc
-- in ASE 15.0.x the first segment will already be created, so just add teh additional tables
--see note above-- sp_addaudittable 'sysaudit_data01'
sp_addaudittable 'sysaudit_data02'

--# Create audit history DB & table #--
create database sybsecurity_archive on data01=500
log on log01=100
-- remember to set 'select into' on for sybsecurity_archive
use sybsecurity
select * into sybsecurity_archive..sysaudits from sysaudits_01 where 1=2

--# Create audit threshold action SP #--
create proc audit_switch
declare @audit_table_number int
** Select the value of the current audit table
select @audit_table_number = scc.value
from master.dbo.syscurconfigs scc, master.dbo.sysconfigures sc
where sc.config=scc.config and = "current audit table"
** Set the next audit table to be current.
** When the next audit table is specified as 0,
** the value is automatically set to the next one.
exec sp_configure "current audit table", 0, "with truncate"
** Copy the audit records from the audit table
** that became full into another table.
if @audit_table_number = 1
insert sybsecurity_archive.dbo.sysaudits
select * from sysaudits_01
truncate table sysaudits_01
else if @audit_table_number = 2
insert sybsecurity_archive.dbo.sysaudits
select * from sysaudits_02
truncate table sysaudits_02

--# Attaching the threshold procedure to each audit segment #--
-- make sure 'suspend audit when device full' = 1
-- Place threshold on each segment
sp_addthreshold sybsecurity, aud_seg_01, 250, audit_switch
sp_addthreshold sybsecurity, aud_seg_02, 250, audit_switch

Configuring & Using Auditing

sp_displayaudit --shows current audit caputre parameters
sp_audit cmdtext, sa, 'all', 'on' --for individual users
sp_audit 'all', sa_role, 'all', 'on' --for roles but you must specify 'all'

--To get information out of the audited store, try this simple SQL to get any
-- recent activity.
declare @yesterday datetime
select @yesterday=dateadd(dd, -1, getdate())
select convert(varchar(30), eventtime, 116), event, loginname, extrainfo from sysaudits_01
where eventtime > @yesterday
and event=92
order by eventtime

Additional Notes

If you are carrying out heavy auditin, i.e. adutiing many users or roles, consider increasing
the queue size.
sp_configure 'audit queue size', 200 (default is 100)

Created by: admin. Last Modification: Saturday 04 of September, 2010 00:04:29 GMT by admin.

Contact us on 0790 532 7921