Sybase Auditing
- Create the auditing devices and auditing database with the Transact-SQL disk init and create database commands. For example:
USE master
go
DISK INIT
NAME=’sybsecurity_data1′,
PHYSNAME=’/sybdevices/cids/sybsecurity_data1.dat’,
VDEVNO=60,
SIZE=5120,
VSTART=0,
CNTRLTYPE=0,
DSYNC=true
go
EXEC sp_diskdefault ‘sybsecurity_data1′,defaultoff
go
USE master
go
DISK INIT
NAME=’sybsecurity_data2′,
PHYSNAME=’/sybdevices/cids/sybsecurity_data2.dat’,
VDEVNO=61,
SIZE=5120,
VSTART=0,
CNTRLTYPE=0,
DSYNC=true
go
EXEC sp_diskdefault ‘sybsecurity_data2′,defaultoff
go
USE master
go
DISK INIT
NAME=’sybsecurity_data3′,
PHYSNAME=’/sybdevices/cids/sybsecurity_data3.dat’,
VDEVNO=62,
SIZE=5120,
VSTART=0,
CNTRLTYPE=0,
DSYNC=true
go
EXEC sp_diskdefault ‘sybsecurity_data3′,defaultoff
go
USE master
go
DISK INIT
NAME=’sybsecurity_log1′,
PHYSNAME=’/sybdevices/cids/sybsecurity_log1.dat’,
VDEVNO=63,
SIZE=2560,
VSTART=0,
CNTRLTYPE=0,
DSYNC=true
go
EXEC sp_diskdefault ‘sybsecurity_log1’,defaultoff
USE master
go
CREATE DATABASE sybsecurity
ON sybsecurity_data1=10
LOG ON sybsecurity_log1=5
go
USE master
go
EXEC sp_dboption ‘sybsecurity’,’trunc log on chkpt’,true
go
EXEC sp_dboption ‘sybsecurity’,’abort tran on log full’,true
go
use sybsecurity
go
EXEC sp_changedbowner ‘sa’
Go
checkpoint
go
– Use
isql to execute the
installsecurity script:
o cd $SYBASE/ASE-12_5/scripts
setenv DSQUERY
server_name
isql -Usa –
Ppassword –
Sserver_name < installsecurity
– Shut down and restart Adaptive Server to initialise the auditing subsystem
– When you have completed these steps, the
sybsecurity database has one audit table (
sysaudits_01) created on its own segment. You can enable auditing at this time, but should add more auditing tables with
sp_addaudittable.
– Now Create the extra
sysaudits tables in
sybsecurity:
Extend the
sybsecurity database to the device you initialized in step 1.
use master
go
alter database sybsecurity on sybsecurity_data2= 10
go
use sybsecurity
go
sp_addaudittable ‘sybsecurity_data2’
go
use master
go
alter database sybsecurity on sybsecurity_data3= 10
go
use sybsecurity
go
sp_addaudittable ‘sybsecurity_data3’
go
– Create the repository database sybaudit and the repository table audit_data
USE master
go
CREATE DATABASE sybaudit
ON DATA1=500
LOG ON LOG1=50
go
USE master
go
EXEC sp_dboption ‘sybaudit’,’select into/bulkcopy/pllsort’,true
go
EXEC sp_dboption ‘sybaudit’,’trunc log on chkpt’,true
go
USE sybaudit
go
CHECKPOINT
go
USE sybaudit
go
EXEC sp_changedbowner ‘sa’
Go
– Create the audit_data table in sybaudit
use sybaudit
go
CREATE TABLE dbo.audit_data
( event smallint NOT NULL,
eventmod smallint NOT NULL,
spid smallint NOT NULL,
eventtime datetime NOT NULL,
sequence smallint NOT NULL,
suid int NOT NULL,
dbid smallint NULL,
objid int NULL,
xactid binary(6) NULL,
loginname varchar(30) NULL,
dbname varchar(30) NULL,
objname varchar(255) NULL,
objowner varchar(30) NULL,
extrainfo varchar(255) NULL,
nodeid tinyint NULL
)
LOCK ALLPAGES
Go
– Add a sp_thresholdaction procedure to each of the segments created above, use the example below;
Use sybsecurity
go
create procedure sp_audit_thresholdaction
as
declare @audit_table_number int
select @audit_table_number = value
from master.dbo.sysconfigures
where name = “current audit table”
exec sp_configure “current audit table”, 0, “with truncate”
if @audit_table_number = 1
insert sybaudit.dbo.audit_data
select * from sysaudits_01
truncate table sysaudits_01
if @audit_table_number = 2
insert sybaudit.dbo.audit_data
select * from sysaudits_02
truncate table sysaudits_02
if @audit_table_number = 3
insert sybaudit.dbo.audit_data
select * from sysaudits_03
truncate table sysaudits_03
return(0)
– To apply this action to the three segments issue the following command;
Use sybsecurity
go
EXEC sp_addthreshold ‘sybsecurity’,’aud_seg_01′,200,’sp_audit_thresholdaction’
go
EXEC sp_addthreshold ‘sybsecurity’,’aud_seg_02′,200,’sp_audit_thresholdaction’
go
EXEC sp_addthreshold ‘sybsecurity’,’aud_seg_03′,200,’sp_audit_thresholdaction’
go
– Next create a procedure to flush all the data from sybsecurity into the table on sybaudit;
Use sybaudit
Go
IF OBJECT_ID(‘dbo.sp_aud_flush_display’) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_aud_flush_display
IF OBJECT_ID(‘dbo.sp_aud_flush_display’) IS NOT NULL
PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.sp_aud_flush_display >>>’
ELSE
PRINT ‘<<< DROPPED PROCEDURE dbo.sp_aud_flush_display >>>’
END
go
CREATE PROCEDURE dbo.sp_aud_flush_display
AS
BEGIN
exec sybsecurity..sp_audit_thresholdaction
exec sybsecurity..sp_audit_thresholdaction
exec sybsecurity..sp_audit_thresholdaction
select distinct loginname from audit_data
END
go
EXEC sp_procxmode ‘dbo.sp_aud_flush_display’,’unchained’
go
– Set the following parameters in the Sybase server;
sp_configure “auditing”, 1
go
sp_configure “suspend audit when device full”, 0
go
–this enables auditing
sp_configure “audit queue size”, 100
go
–this sets up the queue size for handling auditing events
–Now To audit the sa_role logins issue the following command;
sp_audit “all”, “sa_role”, “all”, “on”
go
