Installing Sybase Auditing

Sybase Auditing

  1. 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 –PpasswordSserver_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