• AutoAudit研究学习


     

    AutoAudit介绍

     

    AutoAudit这个是Paul Nielsen写的一个开源的审计跟踪的脚本项目,项目位于https://autoaudit.codeplex.com/上,Paul Nielsen的一篇博客CodeGen to Create Fixed Audit Trail Triggers上也介绍了他创建这个项目的前因后果。目前最新的版本为3.30a,官方文档写着支持SQL Server 2005, 2008, 2012。根据我的测试,SQL Server 2014也是支持的。看了这个项目最后一次更新时间为2012年,也就是说那个时候SQL Server 2014SQL Server 2016SQL Server 2017都还没有发布。可以这样说,AudoAudit这个项目自2012年后,已经停止更新了。

     

     

    那么AutoAudit这个脚本能做那些跟踪审计方面的工作呢?下面笼统的总结了一下AutoAudit的功能:

     

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

     

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

     

    2: 审计DML(INSERTUPDATEDELETE)的脚本以及数据变化。

     

       可以灵活配置、动态监控某个表、或一批表、或者所有表的DML操作,例如INSERTUPDATEDELTE操作,可以只捕获SQL语句,也可以捕获相关值的变化(记录修改前的值与修改后的值)。可以设置参数、在被监控的表上动态的生成触发器等。

     

     

    AutoAudit部署

     

    AutoAudit的部署是非常简单的事情,你从官方下载一个AutoAuditSQL脚本,例如当前版本为AutoAudit 3.30a.sql,你可以修改一些变量或不做修改,在你需要做审计跟踪的数据库执行脚本就轻松部署了。当然,如果你要搞懂、弄透的话,还得花费一番功夫研究一下脚本。AutoAudit 3.30a.sql的脚本大概6000多行,里面有大量的注释,代码非常优雅、简洁。个人拜读起来,感觉就是一件精雕细琢的艺术品,爽心悦目。下面来简单介绍一下这个开源项目吧,估计认真看代码的没有几个人。

     

    clip_image001

     

     

    如果你没有修改任何参数,执行完这个脚本后,就会在当前数据库上创建下面一些对象:

     

    1:数据库DDL触发器SchemaAuditDDLTrigger

     

    2:创建一些数据库对象,如果没有修改参数@AuditSchema,默认创建在Audit这个Schema下面。

     

    Table8个)

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

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

    [Audit].[AuditDetail]            记录表数据INSERTUPDATEDELETE变化前后的值(可以记录全部字段或部分字段)

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

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

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

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

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

     

     

    View5个)

     

    [Audit].[vAudit]

    [Audit].[vAuditAll]

    [Audit].[vAuditArchive]

    [Audit].[vAuditDetailAll]

    [Audit].[vAuditHeaderAll]

     

     

    Procedure16个)

     

    [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脚本

     

    clip_image002

     

     

    下面我们来测试一下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

     

     

    clip_image003

     

     

     

     

    使用账号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

     

     

    如下所示,SchemaAuditDDLTrigger触发器会捕获DDL相关脚本记录到表下[Audit].[SchemaAudit]XMLEventData里面包含了具体、详细的信息。例如脚本执行的时间、ServerName等等。

     

    clip_image004

     

     

    那么接下来,我们想审计、跟踪某个具体表,例如,我们想审计、跟踪[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表示不会AuditHeader中记录DML操作的SQL1表示在AuditHeader中记录DML操作的SQL

    @BaseTableDDL     0表示在基表不增加审计字段,1表示在基表增加审计字段

    @LogInsert  0表示不审计任何INSERT1表示只会记录SQL等信息到表AuditHeader2表示不仅捕获SQL等信息,还会捕获详细数据变化值到AuditDetail

    @LogUpdate  0表示不审计任何UPDATE1表示只会记录SQL等信息到表AuditHeader2表示不仅捕获SQL等信息,还会捕获详细数据变化值到AuditDetail

        @LogDelete  0表示不审计任何DELETE1表示只会记录SQL等信息到表AuditHeader2表示不仅捕获SQL等信息,还会捕获详细数据变化值到AuditDetail

     

     

    clip_image005

     

    就会在'DataBaseSizeDtl_Day'下面生成三个触发器DataBaseSizeDtl_Day_Audit_DeleteDataBaseSizeDtl_Day_Audit_InsertDataBaseSizeDtl_Day_Audit_Update,然后我们往这个表插入数据(启动作业YourSQLDba_Monitor_Database_Daily_Growth)即可,那么接下来,查看[Audit].[AuditHeader]表,就能看到什么HostNameSysUserApplicationTableOperationSQLStatement等等具体信息

     

     

    SELECT * FROM [Audit].[AuditHeader]

     

     

    clip_image006

     

     

    SELECT * FROM AUDIT.AuditDetail

     

     

    clip_image007

     

     

     

    AutoAudit的优缺点

     

    AudtoAudit配置简单、灵活,可以在做到基本审核或精细审核,完全可以根据需要定制审计粒度信息; 另外,代码中有许多地方值得我们学习、借鉴的地方。但是测试AutoAudit的过程中也发现了一些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

     

     

     

    clip_image008

     

    当然也还有一些其他bug,如下所:

     

    EXEC Audit.pAutoAudit @SchemaName = 'dbo',

        @TableName = 'db_objects',

        @ColumnNames = '<all>',

        @StrictUserContext = 1,

        @LogSQL = 1,

        @BaseTableDDL = 0,

        @LogInsert = 1,

        @LogUpdate = 1,

        @LogDelete = 1

     

    clip_image009

     

     

    这个都需要你调试代码,找出错误的原因,修改相关代码(主要都是动态生成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

     

  • 相关阅读:
    简单介绍ps切片工具切图技巧
    ps选区工具应用
    【Photoshop基本概念知识】
    jQuery上传插件Uploadify使用详解
    PS非主流头发效果
    C#正则表达式整理备忘
    C#中利用正则表达式实现字符串搜索
    PS快捷键大全
    PS操作速查
    Photoshop CS2 菜单入门介绍
  • 原文地址:https://www.cnblogs.com/kerrycode/p/7687234.html
Copyright © 2020-2023  润新知