"A little Sybase a day"-audit

Source: Internet
Author: User
Tags sybase

  1. Close all objects ' audit

  2. 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 ') 
  3. 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.