"A little Sybase a day"-audit

2. Close all objects ' audit

3. select  ' exec sp_audit  ' exec_procedure ', ' All ', '  + name + ' ', ' off '  AS  ' --'  from sysobjects where audflags !=0 and type in  (' P ', ' TR ') select  "exec sp_audit  ' select ', ' All ', '"  + name + "', ' off '"  AS  '--'  from sysobjects where audflags !=0 and type in  (' U ', ' V ') select   ' exec sp_audit  ' insert ', ' all ', '  + name + ' ', ' off '  AS  '--'  from  sysobjects where audflags !=0 and type in  (' U ', ' V ') select  "exec  sp_audit  ' Update ', ' All ', '  + name + ' ', ' off '  AS  '--'  from  sysobjects where audflags !=0 and type in  (' U ', ' V ') select  "exec  sp_audit  ' delete ', ' all ', '  + name + ' ', ' off '  AS  '--'  from sysobjects  where audflags !=0 and type in  (' U ', ' V ') 

5. Create 3 his_audits tables in DB for archiving

SELECT * Into DB. His_audits_01 from Sybsecurity. sysaudits_01 where 1=2select * into DB. His_audits_02 from Sybsecurity. sysaudits_02 where 1=2select * into DB. His_audits_03 from Sybsecurity. sysaudits_03 where 1=2

4. Set up a cron job for auto archiving every week

#!/bin/sh

. /etc/.syb_password.sh

. /etc/sybase.sh

Server=$1

User=snxsa

errlog= '/logs/sybase/audit_logs_archive.log '

Usage ()

{

echo "Archive_audit_log.sh SERVER"

Exit 0

}

If [$#-ne 1]

Then

Usage

Exit 1

Fi

Case $SERVER in

SERVER1)

password= $SERVER 1_password

;;

SERVER2)

password= $SERVER 2_password

;;

*)

Usage

;;

Esac

Isql-s$server-u$user <<+ > $ERRLOG

$PASSWORD

DECLARE @cur_audit_num int

Select @cur_audit_num =cur.value from Master. Sysconfigures Con,master. Syscurconfigs cur

where Con.config=cur.config

and Con.name= ' current audit table '

If @cur_audit_num =1

EXEC sp_configure "current audit table", 0, "with truncate"

Begin

INSERT INTO DB. HIS_AUDITS_01 SELECT * from Sybsecurity. Sysaudits_01

TRUNCATE TABLE sybsecurity. Sysaudits_01

End

If @cur_audit_num =2

Begin

INSERT INTO DB. HIS_AUDITS_02 SELECT * from Sybsecurity. Sysaudits_02

TRUNCATE TABLE sybsecurity. Sysaudits_02

End

If @cur_audit_num =3

Begin

INSERT INTO DB. HIS_AUDITS_03 SELECT * from Sybsecurity. Sysaudits_03

TRUNCATE TABLE sybsecurity. Sysaudits_03

End

Return

+

num= ' Grep-c ' msg| LIB ' $ERRLOG '

If [$num-GT 0]

Then

Mailx-r "[Email protected]"-S "$SERVER: Archive audits logs ended at ' Date ' with errors" [email protected] "< $DUMPLOG

Else

Mailx-r "[Email protected]"-S "$SERVER: Archive audits logs finished at ' date ' OK" [email protected] "< $DUMPLOG

Fi

Exit 0

5. Set up threshold

Use Sybsystemprocs

Go

CREATE PROCEDURE dbo.sp_sybsecurity_audseg_used_80

As

declare @dw int, @hour int

Select @dw = DatePart (dw,getdate ())

Select @hour = DatePart (hh,getdate ())

Execute xp_cmdshell ' mailx-r "[email protected]"-S "server1:sybsecurity aud_segment usage is over 80%!" "[Email protected]" </dev/null '

Go

Use sybsecurity

Go

EXEC sp_addthreshold sybsecurity,aud_seg_01,30720,sp_sybsecurity_audseg_used_80

EXEC sp_addthreshold sybsecurity,aud_seg_02,30720,sp_sybsecurity_logseg_used_80

EXEC sp_addthreshold sybsecurity,aud_seg_03,30720,sp_sybsecurity_logseg_used_80

--exec Sp_addthreshold sybsecurity,logsegment,10240,sp_sybsecurity_logseg_used_80

This article is from "Dream flying" blog, please be sure to keep this source http://upboy.blog.51cto.com/3058036/1636781

"A little Sybase a day"-audit