Close all objects ' audit
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 ')
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