Menu [toggle]

Tikiwiki Assistant

Thank you for installing Tikiwiki!

LoginTo begin configuring Tiki, please login as the Admin.

The Tikiwiki CommunityTo learn more, visit: http://www.tikiwiki.org.

Tikiwiki DocumentationFor help, visit http://doc.tikiwiki.org.

Print

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.

Install

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
go
alter database sybsecurity on sysaudit_data02=100
go

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

sp_configure 'auditing', 1
go
--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
go

--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'
go
sp_addaudittable 'sysaudit_data02'
go

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

--# Create audit threshold action SP #--
create proc audit_switch
as
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 sc.name = "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
begin
insert sybsecurity_archive.dbo.sysaudits
select * from sysaudits_01
truncate table sysaudits_01
end
else if @audit_table_number = 2
begin
insert sybsecurity_archive.dbo.sysaudits
select * from sysaudits_02
truncate table sysaudits_02
end
return(0)
go

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

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
go

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