sql server 审核

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

例如对失败的登录次数进行审计，某个数据库上的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', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 1
GO

 

---

SQLSERVER2008新增的审核功能

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

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

事件通知：性能跟踪

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

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

 

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

 

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

审核对象

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

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

--创建审核对象之前需要切换到master数据库
USE [master]
GO
CREATE SERVER AUDIT MyFileAudit TO FILE(FILEPATH='D:\sqlaudits') --这里指定文件夹不能指定文件，生成文件都会保存在这个文件夹
GO

 

实际上，我们在创建审核对象的同时可以指定审核选项，下面是相关脚本

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

USE [master]
GO
CREATE SERVER AUDIT MyFileAudit TO FILE(
FILEPATH='D:\sqlaudits',
MAXSIZE=4GB,
MAX_ROLLOVER_FILES=6) 
WITH (
ON_FAILURE=CONTINUE,
QUEUE_DELAY=1000);

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这个选项不能跟其他选项共用，所以只能单独一句

 

 

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

ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)
ALTER SERVER AUDIT MyFileAudit WITH(QUEUE_DELAY =1000)
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)

 

---

审核规范

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

我们来看一下脚本

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO

CREATE SERVER AUDIT MyAppAudit TO APPLICATION_LOG
GO
ALTER SERVER AUDIT MyAppAudit WITH(STATE =ON)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
GO
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyAppAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO

我们创建一个服务器级别的审核规范CaptureLoginsToFile，然后再创建多一个审核对象MyAppAudit ，这个审核对象会把审核日志保存到Windows事件日志的应用程序日志里

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

 

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

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileA
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileB
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO

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

 

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

--禁用审核对象
ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)
--禁用服务器级审核规范
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
GO
--禁用数据库级审核规范
ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=OFF)
GO

--相关修改选项操作



--启用审核对象
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
--启用服务器级审核规范
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=ON)
GO
--启用数据库级审核规范
ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=ON)
GO

 

审核服务器级别事件