SQL Server Audit监控触发器状态

2021-10-03 03:05:05

原文:SQL Server Audit监控触发器状态

一.本文所涉及的内容（Contents）

本文所涉及的内容（Contents）
背景（Contexts）
实现代码（SQL Codes）
注意事项（Attention）
疑问（Questions）
参考文献（References）

二.背景（Contexts）

　　DML触发器一般会运用在业务系统的逻辑处理上，在你对数据库用户权限控制不当的时候，这些DML触发器很可能莫名的被禁用或者删除了，你作为数据库管理员你想做冤大头嘛？背黑锅嘛？如果不想，下面给出4种解决方案：

1. 使用DDL触发器对服务级别或者数据库级别进行监控，可以参考文档：SQL Server DDL触发器运用，效果如下图所示：

（Figure1：触发器的日志）

2. 也可以使用数据库DDL事件通知，可以参考文档：SQL Server 事件通知（Event notifications），效果如上图所示；

3. 第三种方案就是变更数据捕获（CDC），可以参考文档：SQL Server 变更数据捕获（CDC）监控表数据

4. 第四种方案就是这篇文章要讲到的新特性：SQL Server Audit，可以使用DATABASE AUDIT SPECIFICATION来捕获这些事件。

三.实现代码（SQL Codes）

(一) 创建服务器级别的审核，注意设置FILEPATH为对应的路径，显示设置审核状态，创建审核的时候只能是服务器的；

USE master
GO
--创建服务器级别审核
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'F:\AuditLog\', MAXSIZE = 256 MB)
WITH (ON_FAILURE = CONTINUE);
GO
--开启审核
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

(二) 创建服务器级别的审核规范，这里指定相对应的AUDIT（审核）接收、保存跟踪和记录的数据，可以同时多个审核规范指向同一个审核。

SCHEMA_OBJECT_CHANGE_GROUP，针对架构执行 CREATE、ALTER 或 DROP 操作时将引发此事件。任何数据库的任何架构发生更改时，均将引发此事件。审核操作组的其它值可以参考：SQL Server 审核操作组和操作

--创建服务器级别审核规范
USE master
GO
CREATE SERVER AUDIT SPECIFICATION Schema_Change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

(三) 手动产生对象更改事件

--测试
CREATE TABLE dbo.TestAudit(Id INT)
GO
CREATE TRIGGER dbo.TestAudit_Trigger ON dbo.TestAudit
    FOR INSERT
AS
    BEGIN
        SELECT * FROM dbo.TestAudit;
    END
GO

(四) 查询Audit的记录，这里针对审核ServerAudit的内容进行查询：

--查询
SELECT  database_name,
        event_time,
        succeeded,
        server_principal_name,
        [object_name],
        [statement]
FROM sys.fn_get_audit_file('F:\AuditLog\ServerAudit_*.sqlaudit', default, default)