SQLSERVER2008新增的审核/审计功能

发布时间：2020-12-12 13:09:42 所属栏目：MsSql教程来源：网络整理

导读：SQLSERVER2008新增的审核/审计功能很多时候我们都需要对数据库或者数据库服务器实例进行审核/审计例如对失败的登录次数进行审计，某个数据库上的DDL语句进行审计，某个数据库表里面的delete语句进行审计事实上，我们这些审计的需求基本上都是为了一个目的

SQLSERVER2008新增的审核/审计功能

很多时候我们都需要对数据库或者数据库服务器实例进行审核/审计

例如对失败的登录次数进行审计，某个数据库上的DDL语句进行审计，某个数据库表里面的delete语句进行审计

事实上，我们这些审计的需求基本上都是为了一个目的：防黑客

上面的这些审计需求无非就是看一下有哪些人试图入侵数据库服务器，入侵了之后是否有drop表，是否有delete数据

在SQLSERVER2008及以前版本可以选择的方案有

1、服务器级别DDL触发器和数据库级别的DDL触发器（SQL2005及以上版本）以及DML触发器

2、自己手工从事务日志里读取操作记录，权威的书都会说事务日志不是审核工具，一般大型数据库都会设置为简单模式，事务日志截断

3、依靠SQLSERVER ERRORLOG来检查登录审核，导致SQLSERVER ERRORLOG login相关的日志泛滥?导致SQL排错造成困难

4、事件通知：http://www.cnblogs.com/gaizai/p/3473553.html
5、更改跟踪：http://www.cnblogs.com/gaizai/p/3482579.html
6、变更数据捕获（CDC）：http://www.cnblogs.com/gaizai/p/3479731.html

我们一般都会把C2 审核跟踪和登录审核里面只限成功的登录，以防止SQL ERRORLOG日志泛滥，因为服务器是很久才重启一次的，如果不做修改很容易造成磁盘爆满

--禁用C2 审核跟踪和只限成功的登录
EXEC sys.sp_configure N'c2 audit mode',N0'
GO
RECONFIGURE WITH OVERRIDE
GO

USE [master]
EXEC xp_instance_regwrite NHKEY_LOCAL_MACHINESoftwareMicrosoftMSSQLServerMSSQLServerAuditLevel1
GO

SQLSERVER2008新增的审核功能

在sqlserver2008新增了审核功能，可以对服务器级别和数据库级别的操作进行审核/审计，事实上，事件通知、更改跟踪、变更数据捕获（CDC）

都不是用来做审计的，只是某些人乱用这些功能，也正因为乱用这些功能导致踩坑

事件通知：性能跟踪

更改跟踪：用Sync Services来构建偶尔连接的系统

变更数据捕获（CDC）：数据仓库的ETL 中的数据抽取（背后使用logreader）

而审核是SQLSERVER专门针对数据库安全的进行的审核，记住，他是专门的！

我们看一下审核的使用方法?

审核对象

步骤一：创建审核对象，审核对象是跟保存路径关联的，所以如果你需要把审核操作日志保存到不同的路径就需要创建不同的审核对象

我们把审核操作日志保存在文件系统里，在创建之前我们还要在相关路径先创建好保存的文件夹，我们在D盘先创建sqlaudits文件夹，然后执行下面语句

创建审核对象之前需要切换到master数据库 CREATE SERVER AUDIT MyFileAudit TO FILE(FILEPATH='D:sqlaudits') 这里指定文件夹不能指定文件，生成文件都会保存在这个文件夹实际上，我们在创建审核对象的同时可以指定审核选项，下面是相关脚本

把日志放在磁盘的好处是可以使用新增的TVF:sys.[fn_get_audit_file] 来过滤和排序审核数据，如果把审核数据保存在Windows 事件日志里查询起来非常麻烦

FILE( FILEPATHD:sqlaudits',MAXSIZE=4GB,MAX_ROLLOVER_FILES=6) WITH ( ON_FAILURE=CONTINUE,QUEUE_DELAY1000); ALTER SERVER AUDIT MyFileAudit WITH(STATE ON)

MAXSIZE：指明每个审核日志文件的最大大小是4GB

MAX_ROLLOVER_FILES：指明滚动文件数目，类似于SQL ERRORLOG，达到多少个文件之后删除前面的历史文件，这里是6个文件

ON_FAILURE：指明当审核数据发生错误时的操作，这里是继续进行审核，如果指定shutdown，那么将会shutdown整个实例

queue_delay：指明审核数据写入的延迟时间，这里是1秒，最小值也是1秒，如果指定0表示是实时写入，当然性能也有一些影响

STATE：指明启动审核功能，STATE这个选项不能跟其他选项共用，所以只能单独一句

在修改审核选项的时候，需要先禁用审核，再开启审核

OFF) WITH(QUEUE_DELAY 1000) 审核规范

在SQLSERVER审核里面有审核规范的概念，一个审核对象只能绑定一个审核规范，而一个审核规范可以绑定到多个审核对象

我们来看一下脚本

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFile FOR SERVER AUDIT MyFileAudit ADD (failed_login_group),ADD (successful_login_group) WITH (STATEON) CREATE SERVER AUDIT MyAppAudit TO APPLICATION_LOG ALTER SERVER AUDIT MyAppAudit ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile FOR SERVER AUDIT MyAppAudit 我们创建一个服务器级别的审核规范CaptureLoginsToFile，然后再创建多一个审核对象MyAppAudit ，这个审核对象会把审核日志保存到Windows事件日志的应用程序日志里

我们禁用审核规范CaptureLoginsToFile，修改审核规范CaptureLoginsToFile属于审核对象MyAppAudit ，修改成功

而如果要把多个审核规范绑定到同一个审核对象则会报错

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileA CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileB 消息 33230，级别 16，状态 1，第 86 行 --审核 'MyFileAudit' 的审核规范已经存在。

这里要说一下：审核对象和审核规范的修改，无论是审核对象还是审核规范，在修改他们的相关参数之前，他必须要先禁用，后修改，再启用

禁用审核对象禁用服务器级审核规范禁用数据库级审核规范 ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile 相关修改选项操作启用审核对象启用服务器级审核规范启用数据库级审核规范审核服务器级别事件

审核服务级别事件，我们一般用得最多的就是审核登录失败的事件，下面的脚本就是审核登录成功事件和登录失败事件

修改审核规范

跟审核对象一样，更改审核规范时必须将其禁用 WITH (STATE ADD (login_change_password_gourp),255)">DROP (successful_login_group) 审核操作组

每个审核操作组对应一种操作，在SQLSERVER2008里一共有35个操作组，包括备份和还原操作，数据库所有权的更改，从服务器和数据库角色中添加或删除登录用户

添加审核操作组的只需在审核规范里使用ADD，下面语句添加了登录用户修改密码操作的操作组

ADD (login_change_password_gourp)

这里说一下服务器审核的内部实际上使用的是SQL2008新增的扩展事件里面的其中一个package：SecAudit package，当然他内部也是使用扩展事件来收集服务器信息

审核数据库级别事件?

数据库审核规范存在于他们的数据库中，不能审核tempdb中的数据库操作

CREATE DATABASE AUDIT SPECIFICATION和ALTER DATABASE AUDIT SPECIFICATION

工作方式跟服务器审核规范一样

在SQLSERVER2008里一共有15个数据库级别的操作组
7个数据库级别的审核操作是：select,insert,update,delete,execute,receive,references

相关脚本如下：

创建审核对象 CREATE SERVER AUDIT MyDBFileAudit D:sqldbaudits') ALTER SERVER AUDIT MyDBFileAudit 创建数据库级别审核规范 sssCREATE DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog FOR SERVER AUDIT MyDBFileAudit ADD (database_object_change_group),255)">ADD (SELECT,INSERT,255)">UPDATE,255)">DELETE ON schema::dbo BY PUBLIC) 我们先在D盘创建sqldbaudits文件夹

第一个操作组对数据库中所有对象的DDL语句create，alter，drop等进行记录
第二个语句监视由任何public用户（也就是所有用户）对dbo架构的任何对象所做的DML操作

创建完毕之后可以在SSMS里看到相关的审核

数据库审核规范

服务器审核规范和审核对象

查看审核事件

被记录到文件系统的审核文件不是存储在可以利用记事本打开的文本文件中，而是采用二进制文件的方式

这里说一个，当磁盘空间不足的时候是可以直接删除这些SQLAUDIT文件

如果使用DDL触发器的方法：http://www.cnblogs.com/gaizai/p/3363220.html?ADUIN=xxx2&ADSESSION=1387155615&ADTAG=CLIENT.QQ.5275_.0&ADPUBNO=26274

一般都会在数据库里头创建一张表来保存审计数据，但是当表数据量达到很多的时候，DBA也需要去维护这张表

工作量又增加了，可能你会说，我需要审计的项目不多，所以审计的数据也不会太多，但对于某些大公司来说

他们要审计的数据是非常多的，有些需要归档，而有些不需要归档

对于不需要归档审计数据的情况，我比较喜欢这种方式，当磁盘容量不够的时候把最老的那个审计文件删除掉

当然，你可以把整个sqlaudits文件夹或某个sqlaudit文件进行备份，放到备份磁盘上，然后删除一些较老的sqlaudit文件

备份了之后以后就有机会对之前的审计数据进行翻查，都比较灵活

我们有两种方法查看审核日志

方法一：对象资源管理器-》安全性-》审核-》选中某个审核对象-》右键-》查看审核日志

审核项目包括有：日期、时间戳记、服务器实例名称、操作ID、类类型、序列号、成功或失败、列权限、数据库主体ID、服务器主体名称、

服务器主体SID、被执行的（或尝试）的实际语句等等

方法二：使用新的表值函数sys.[fn_get_audit_file]()

此函数接受一个或多个审核文件的参数（使用通配符模式匹配）

并利用另外两个附加参数可以指定要处理的起始文件，以及开始读取审核的已知偏移位置

这两个参数都是可选的，但依然必须使用关键字default指定，此函数随后从文件中读取二进制数据，并将格式化这些审核项目

服务器级别审核

根据最近时间的那个sqlaudit文件，查询这个文件里面的信息

SELECT event_time] AS 触发审核的日期和时间单个审核记录中的记录顺序操作的 ID触发事件的操作是否成功权限掩码是否为列级别权限发生该事件的会话的 ID执行操作的登录上下文 ID执行操作的数据库用户上下文 ID执行 GRANT/DENY/REVOKE 操作的服务器主体执行 GRANT/DENY/REVOKE 操作的数据库主体object_id 发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)可审核实体的类型会话的服务器主体当前登录名当前登录名 SID当前用户操作的目标登录名目标登录名的 SID操作的目标用户审核的服务器实例的名称发生此操作的数据库上下文此操作的架构上下文object_name 审核的实体的名称TSQL 语句（如果存在）单个事件的唯一信息，以 XML 的形式返回file_name 记录来源的审核日志文件的路径和名称包含审核记录的文件中的缓冲区偏移量作为 sp_audit_write 参数传递的用户定义事件 ID于记录用户想要通过使用 sp_audit_write 存储过程记录在审核日志中的任何附加信息FROM sys.fn_get_audit_file](D:sqlauditsMyFileAudit_F0BCDC6F-0A89-459D-B345-9DDEB036CC39_0_130595725124220000.sqlauditDEFAULT,255)">DEFAULT) WHERE ] BETWEEN 2014-11-04 11:02:00' AND 2014-11-04 11:18:00'

数据库级别审核

先执行下面脚本查询一些数据

SELECT * FROM dbo].nums] D:sqldbauditsMyDBFileAudit_698BA060-CC40-4A3C-B19D-12B370712404_0_130595753193920000.sqlauditDEFAULT)

将审核日志保存到文件系统的好处就是可以使用TVP里通过where 和order by对审核数据进行筛选和排序

和审核相关的视图

查询审核相关视图 FROM sys.server_file_auditsserver_audit_specificationsserver_audit_specification_detailsdatabase_audit_specificationsdatabase_audit_specification_detailsdm_server_audit_statusdm_audit_actionsdm_audit_class_type_map]

删除相关对象

删除顺序删除数据库审核规范 DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLogDROP 删除服务器审核规范 ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFileDROP SERVER AUDIT SPECIFICATION 删除审核对象 ALTER SERVER AUDIT MyFileAuditMyAppAuditMyEventLogAuditDROP SERVER AUDIT 总结

本文概括介绍了SQLSERVER2008新增的审核功能，在SQLSERVER相关论坛里面“审核”这个话题是大家问得比较多的

希望通过这篇文章，能让大家认识新增的审核功能，在生产环境里面遇到问题也可以互相交流

而审核功能最大的好处是：你使用自建审计表来保存审计数据，如果聪明的黑客攻破你的数据库实例，他自然可以把你的那个审计表

drop掉，你同样查不出黑客的任何蛛丝马迹，而审核不同，他把审核数据放在SQLSERVER外面，除非你们公司的SA和DBA的安全意识

很弱，黑客有机会把磁盘文件删除掉，否则依然有可能查出黑客的蛛丝马迹进行预防！！

msdn相关文章：

CREATE SERVER AUDIT (Transact-SQL)

SQL Server Audit Action Groups and Actions

sys.fn_get_audit_file (Transact-SQL)

如有不对的地方，欢迎大家拍砖o(∩_∩)o?

补充：搬迁数据库后，数据库审核规范是不会删除的，你可以在新的数据库实例上再创建审核对象并把审核对象绑定到原来的数据库审核规范

附加数据库，模拟搬迁数据库 DATABASE MacFilterON ( FILENAME = NE:DataBaseMacFilter.mdf' ),( FILENAME E:DataBaseMacFilter_log.ldf' ) FOR ATTACH GO 在新实例上重新创建审核对象 E:sqlauditsON) 修改原来的数据库级别审核规范 DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog 重新绑定 ON) 更新测试 UPDATE Mac_TableSET MAC]22 WHERE PcNameandroid_3eb52207eb8b89ee' 查询审核数据 ' e:sqlauditsMyFileAudit_149774D4-C5D9-4700-9FA1-AF0E13CED504_0_130595854334250000.sqlaudit ?

脚本更新

查看审核结果 --查询服务器审核事件 SELECT fgaf.[dactm].[class_type_desc] AS '可审核实体的类型描述', fgaf.session_server_principal_name E:DataBaseAuditServerAuditServerFileAudit_9E770D97-AD49-466A-8367-7885850200F8_0_130596271711880000.sqlauditDEFAULT) AS fgaf INNER JOIN sys.AS dactm ON dactmclass_type= fgafWHERE fgaf.2014-11-04' 2014-11-06ORDER BY fgaf.] --查询某个数据库的审核事件 class_type_desc可审核实体的类型描述E:DataBaseAuditDBAudit_dbDBFileAudit_db%8O%89095_794BC4B9-4126-4CF7-BDD9-F24D8DA1CE70_0_130596271711960000.sqlaudit服务器审核规范详细数据库审核规范详细 ] ]

--本篇文章转自：SQLSERVER2008新增的审核/审计功能

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!