AutoAudit探讨学习

 

AutoAudit介绍

 

AutoAudit这多少个是保罗Nielsen写的一个开源的审计跟踪的台本项目,项目坐落https://autoaudit.codeplex.com/上,Paul
Nielsen的一篇博客CodeGen to Create Fixed Audit Trail
Triggers上也介绍了她创建这多少个项目的前因后果。最近风靡的本子为3.30a,官方文档写着匡助SQL Server 2005, 2008,
2012。遵照自身的测试,SQL Server
2014也是辅助的。看了这个序列最后几回革新时间为二零一二年,也就是说这些时候SQL Server 2014、SQL Server 2016、SQL Server
2017都还平素不发布。可以这样说,AudoAudit这些类型自二〇一二年后,已经终止更新了。

 

 

那么AutoAudit那个剧本能做那么些跟踪审计方面的办事呢?下边笼统的下结论了一下AutoAudit的效用:

 

1:
审计、跟踪记录所有的DDL脚本。

 

   它创制了一个数据库DDL的触发器,它会捕获DDL相关脚本。例如,创设、修改、删除数据库对象等。

 

2:
审计DML(INSERT、UPDATE、DELETE)的本子以及数据变化。

 

   可以灵活配置、动态监督某个表、或一批表、或者所有表的DML操作,例如INSERT、UPDATE、DELTE操作,能够只捕获SQL语句,也可以捕获相关值的转移(记录修改前的值与修改后的值)。可以安装参数、在被监控的表上动态的变型触发器等。

 

 

AutoAudit部署

 

AutoAudit的部署是分外简单的业务,你从官方下载一个AutoAudit的SQL脚本,例如当前版本为AutoAudit
3.30a.sql,你可以修改部分变量或不做修改,在你需要做审计跟踪的数据库执行脚本就自在部署了。当然,假设你要搞懂、弄透的话,还得花费一番功力探讨一下本子。Auto奥迪t
3.30a.sql的剧本大概6000多行,里面有大气的诠释,代码分外优雅、简洁。个人拜读起来,感觉就是一件精雕细琢的艺术品,爽心悦目。上面来大概介绍一下那么些开源项目吧,揣测认真看代码的从未有过几人。

 

图片 1

 

 

比方您从未改动任何参数,执行完这一个剧本后,就会在当前数据库上开创下面一些目标:

 

1:数据库DDL触发器SchemaAuditDDLTrigger

 

2:创制一些数据库对象,假如没有改动参数@AuditSchema,默认创制在Audit那多少个Schema下边。

 

Table(8个)

[Audit].[AuditAllExclusions]     排除审计、跟踪的表

[Audit].[AuditBaseTables]       
记录审计、跟踪表的有关参数,它会取这里的相关值动态变化相关触发器。默认为空

[Audit].[AuditDetail]           
记录表数据INSERT、UPDATE、DELETE变化前后的值(可以记录整个字段或局部字段)

[Audit].[AuditDetailArchive]    
[Audit].[AuditDetail]的存档数据。

[Audit].[AuditHeader]           
记录表DML操作的连带用户、应用程序、以及SQL语句等。

[Audit].[AuditHeaderArchive]    
[Audit].[AuditDetail]表的数量归档表

[Audit].[AuditSettings]         
AutoAudit的相干参数

[Audit].[SchemaAudit]           
记录数据库的DDL新闻,例如SQL、应用程序等等。

 

 

View(5个)

 

[Audit].[vAudit]

[Audit].[vAuditAll]

[Audit].[vAuditArchive]

[Audit].[vAuditDetailAll]

[Audit].[vAuditHeaderAll]

 

 

Procedure(16个)

 

[Audit].[pAutoAuditArchive]

[Audit].[pAutoAudit]

[Audit].[pAutoAuditRebuild]

[Audit].[pAutoAuditRebuildAll]

[Audit].[pAutoAuditDrop]

[Audit].[pAutoAuditAll]

[Audit].[pAutoAuditSetTriggerState]

[Audit].[pAutoAuditSetTriggerStateAll]

[Audit].[pAutoAuditDropAll]

[Audit].[GenerateIt]

[Audit].[SuspendIt]

[Audit].[ReactivateIt]

[Audit].[RemoveIt]

[Audit].[ProcessExpiredDataAudits]

[Audit].[ProcessDataAuditsCleanup]

[Audit].[ProcessDataAuditsCleanupForAllDb]

 

 


[Audit].[AuditSettings]是部署消息表,关于AudoAudit的部署音讯都放在该表。若是要询问、学习AudoAudit这多少个项目,那么必须询问这多少个参数。参数具体职能可以参加脚本注释信息.

 

 

SELECT
* FROM [Audit].[AuditSettings]

 

 

此时,你查询[Audit].[SchemaAudit],发现数据库DDL触发器已经抓获了您创制视图、存储过程等等的DDL脚本

 

图片 2

 

 

下面我们来测试一下AutoAudit的效用吗,
如下所示,我们清空[Audit].[SchemaAudit]下数据,然后创造、修改TEST表,具体测试脚本如下,数据库的DDL触发器会捕获相关DDL SQL,当然DML操作是不会被抓获的。

 

TRUNCATE
TABLE [Audit].[SchemaAudit]

 

GO

 

CREATE
TABLE TEST(ID INT ,NAME VARCHAR(12));

GO

ALTER
TABLE TEST ADD SEX BIT;

GO

ALTER
TABLE TEST DROP COLUMN SEX;

GO

 

 

图片 3

 

 

 

 

应用账号tmp登录数据库,执行上面一批脚本

 

 

USE
YourSQLDba;

GO

CREATE
TABLE TEST(ID INT, NAME VARCHAR(24));

GO

ALTER
TABLE TEST ADD SEX BIT;

GO

INSERT
INTO TEST

SELECT
1000, ‘KERRY’, 1 ;

GO

DROP
TABLE TEST;

GO

 

 

一般来说所示,Schema奥迪tDDLTrigger触发器会捕获DDL相关脚本记录到表下[Audit].[SchemaAudit],XML伊芙(Eve)ntData里面含有了实际、详细的消息。例如脚本执行的岁月、ServerName等等。

 

图片 4

 

 

那么接下去,大家想审计、跟踪某个具体表,例如,我们想审计、跟踪[Maint].[DataBaseSizeDtl_Day]表,那么可以透过存储过程Audit.pAutoAudit去贯彻

EXEC Audit.pAutoAudit @SchemaName = 'Maint', -- sysname

    @TableName = 'DataBaseSizeDtl_Day', -- sysname

    @ColumnNames = '<All>', -- varchar(max)

    @StrictUserContext = 1, -- bit

    @LogSQL = 1, -- bit

    @BaseTableDDL = 0, -- bit

    @LogInsert = 2, -- tinyint

    @LogUpdate = 2, -- tinyint

    @LogDelete = 2 -- tinyint

 

 

储存过程是按照参数,动态变化被审计表的相干触发器,如下所示,

 

ALTER PROC [Audit].[pAutoAudit] 

(

@SchemaName            sysname       = 'dbo',--this is the default schema name for the tables getting AutoAudit added

@TableName             sysname,              --enter the name of the table to add AutoAudit to.

@ColumnNames           varchar(max)= '<All>',--columns to include when logging details (@Log...=2). Default = '<All>'. Format: '[Col1],[Col2],...'

@StrictUserContext     bit           = 1,    -- 2.00 if 0 then permits DML setting of Created, CreatedBy, Modified, ModifiedBy

@LogSQL                bit           = 0,    -- 0 = Don't log SQL statement in AuditHeader, 1 = log the SQL statement

@BaseTableDDL          bit           = 0,    -- 0 = don't add audit columns to base table, 1 = add audit columns to base table

@LogInsert            tinyint        = 2,    -- 0 = nothing, 1 = header only, 2 = header and detail

@LogUpdate            tinyint        = 2,    -- 0 = nothing, 1 = header only, 2 = header and detail

@LogDelete            tinyint        = 2     -- 0 = nothing, 1 = header only, 2 = header and detail

) 

 

 

留神,对应参数的不等值会影响触发器以及审计内容以及数据。

 

@SchemaName 审计表的Schema

@TableName  审计表的表名

@ColumnNames 表的字段,假若只为<All>那么在AuditDetail记录所有字段的变化值,也得以只记录某个或一些字段的值。

@StrictUserContext
默认为1,假若为0,那么会在跟踪升级表上平添字段[AutoAudit_CreatedDate]、[AutoAudit_CreatedBy]等。

@LogSQL          
0代表不会奥迪tHeader中记录DML操作的SQL,1象征在AuditHeader中记录DML操作的SQL

@BaseTableDDL     0表示在基表不扩张审计字段,1意味在基表扩充审计字段

@LogInsert  0表示不审计任何INSERT,1意味着只会记录SQL等音讯到表AuditHeader,2意味不仅抓获SQL等消息,还会捕获详细数据变化值到AuditDetail

@LogUpdate  0表示不审计任何UPDATE,1意味只会记录SQL等音讯到表AuditHeader,2表示不仅抓获SQL等消息,还会捕获详细数据变化值到AuditDetail

    @LogDelete  0代表不审计任何DELETE,1表示只会记录SQL等音信到表AuditHeader,2象征不仅抓获SQL等信息,还会捕获详细数据变化值到AuditDetail

 

 

图片 5

 

就会在’DataBaseSizeDtl_Day’上边生成六个触发器DataBaseSizeDtl_Day_Audit_Delete、DataBaseSizeDtl_Day_Audit_Insert、DataBaseSizeDtl_Day_Audit_Update,然后大家往这些表插入数据(启动作业YourSQLDba_Monitor_Database_Daily_Growth)即可,那么接下去,查看[Audit].[AuditHeader]表,就能来看什么HostName、SysUser、Application、Table、Operation、SQLStatement等等具体音信

 

 

SELECT
* FROM [Audit].[AuditHeader]

 

 

图片 6

 

 

SELECT
* FROM AUDIT.AuditDetail

 

 

图片 7

 

 

 

Auto奥迪t的优缺点

 

AudtoAudit配置简单、灵活,能够在成功基本审核或精美审核,完全可以依据需要定制审计粒度音讯;
其它,代码中有广大地点值得大家上学、借鉴的地点。不过测试Auto奥迪t的过程中也发现了有些Bug,近日该项目终止更新,需要自己解决这么些题材,例如,当主键的字段不是率先列并且指定具体列@ColumnNames时,那么在利用Audit.pAutoAudit
创造连锁触发器时就会报错,如下截图所示

 

 

USE
YourSQLDba;

GO

 

SELECT
* INTO db_objects FROM sys.objects;

 

ALTER
TABLE db_objects
ADD CONSTRAINT pk_db_objects
PRIMARY KEY(OBJECT_ID);

 

GO

 

 

EXEC
Audit.pAutoAudit @SchemaName = ‘dbo’,

    @TableName
= ‘db_objects’,

    @ColumnNames
= ‘[type_desc],[create_date]’,

   
@StrictUserContext = 1,

    @LogSQL = 1,

    @BaseTableDDL
= 0,

    @LogInsert
= 1,

    @LogUpdate
= 1,

    @LogDelete
= 1

 

 

 

图片 8

 

自然也还有一些别样bug,如下所:

 

EXEC
Audit.pAutoAudit @SchemaName = ‘dbo’,

    @TableName
= ‘db_objects’,

    @ColumnNames
= ‘<all>’,

   
@StrictUserContext = 1,

    @LogSQL = 1,

    @BaseTableDDL
= 0,

    @LogInsert
= 1,

    @LogUpdate
= 1,

    @LogDelete
= 1

 

图片 9

 

 

以此都急需你调试代码,找出荒谬的原由,修改相关代码(重要都是动态生成SQL时,有局部SQL语法错误,例如多了一个,等等),近期。已经自己早已意识并修改了该代码多处地点的小Bug。

 

终极跟踪审计,假如没有必要的话,最好不要开启,除非是为寻找定位一些题目,因为毕竟这几个依旧有无数额外的特性开销、损耗,尤其是这种应用触发器来跟踪、审计,AutoAudit也明确提议那些会影响属性:“Adding
AutoAudit to your tables will impact performance.”

 

 

参考资料:

 

https://autoaudit.codeplex.com/

http://sqlblog.com/blogs/paul_nielsen/archive/2007/01/15/codegen-to-create-fixed-audit-trail-triggers.aspx

 

相关文章