SQL Server Audit监控触发器状态

  1. 云栖社区>
  2. 博客>
  3. 正文

SQL Server Audit监控触发器状态

杰克.陈 2018-02-01 15:53:00 浏览1273
展开阅读全文
原文:SQL Server Audit监控触发器状态

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 实现代码(SQL Codes)
  4. 注意事项(Attention)
  5. 疑问(Questions)
  6. 参考文献(References)

二.背景(Contexts)

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

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

wps_clip_image-18803

(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)

wps_clip_image-31207

(Figure2:操作记录)

四.注意事项(Attention)

1. 审核必须已存在,才能为它创建服务器审核规范。 服务器审核规范在创建之后处于禁用状态。

2. CREATE SERVER AUDIT 语句位于事务范围内。 如果对事务进行回滚,也将对该语句进行回滚。

3. 经过审核的事件可以写入事件日志或审核文件。

4. 定义审核时,将指定结果的输出位置。 这是审核的目标位置。 审核是在禁用状态下创建的,因此不会自动审核任何操作。 启用审核后,审核目标将从审核接收数据,目标可以是文件、Windows 安全事件日志或 Windows 应用程序事件日志。

5. 通过使用 Windows“事件查看器”、“日志文件查看器”或 fn_get_audit_file 函数来读取审核事件。

6. 如果在启动审核期间出现问题,则服务器将不会启动。 在这种情况下,可以在命令行中使用 –f 选项来启动服务器。

五.疑问(Questions)

1. 如何对*.sqlaudit这些日志进行归档或者删除内容呢?

2. 使用sys.fn_get_audit_file获取到的event_time跟当前的时间不对应,什么问题?

3. 使用sys.fn_get_audit_file为什么获取到的记录有空的呢?如果说database_name为空到是还好解释,因为有些操作可能是服务器级别的,这个字段为空到是说的过去,但是[object_name]和[statement]为什么没有内容呢?

wps_clip_image-9127

(Figure3:空值)

4. 如果我创建表失败了,为什么返回的记录中还是显示成功的呢?,执行下面的SQL时返回了下面的错误信息,但是记录的succeeded字段显示为1。

--测试 CREATE TABLE dbo.TestAudit(Id INT) ; GO

消息2714,级别16,状态6,第2 行

数据库中已存在名为'TestAudit' 的对象。

wps_clip_image-19201

(Figure4:创建表失败)

5. Audit与C2是什么关系呢?

6. C2禁用的时候不能马上生效,需要重启数据库服务,这是为什么呢?

7. 如何修改C2日志文件的路径?

解答:要想修改C2日志文件的路径,那就先要知道这个默认的路径在哪里?在数据库属性中设置数据库的默认位置,开启C2开关,就能把类似audittrace20130710105730.trc的文件保存到下图设置的路径当中。

wps_clip_image-9557

(Figure5:重设C2记录文件的保存路径)

8. 为什么修改了路径之后的显示是这样的?Path Field Limits

wps_clip_image-10913

(Figure6:路径)

六.参考文献(References)

使用SQLServer Audit来监控触发器的启用、禁用情况

创建服务器审核和服务器审核规范

SQL Server 审核(数据库引擎)

SQL Server 审核操作组和操作

CREATE SERVER AUDIT (Transact-SQL)

SQL 跟踪

SQL Server Audit (Database Engine)

网友评论

登录后评论
0/500
评论
杰克.陈
+ 关注