近在项目中有需求对SQL Server的操作进行审计(Audit),主要是跟踪谁对数据库或者表在什么时间进行了什么操作，需求比较直接也比较简单，SQL server有很多方案可以实现这个功能，但每种实现都有不同的优缺点，而且不是所有功能都能在所有SQL server版本下面实现。下面将研究的方案，包括触发器，SQL Profier，数据库日志分析，SQL审计以及Extended Events整理成文档，并分析了他们的优缺点，并详细实现了如何分析Extended Event的log内容，希望以后遇到这种需求对大家有所帮助。
触发器(trigger)
使用trigger来实现审计也是最简单的实现方式，但这种实现方式缺点也很明显，需要对所有关注的表建立触发器，因此配置比较麻烦,而且在监控太多表时性能比较差，参考示例如下。
create trigger AuditTrigger on Shift
after update, insert, delete
as
begin
--可以增加log来记录此事件
end
go优点：
方法简单，容易理解
适用于SQL Server的所有版本
缺点：
配置比较麻烦，需要为每个table建立触发器
监控对象多时性能较差
只有Database-Level的审计，没有Server-Level的审计
基于数据库级别的技术，一旦有数据库创建或者删除操作，需要做相应的适配，所以维护成本也相对高
说明：Server-Level的审计包括数据库安全操作（login，logout，权限管理），数据库备份与恢复，数据库Schema的更新等。Database-Level的审计是对单个数据库上的表，视图等进行SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, and REFERENCES等。
SQL Profier
SQL Profiler是一个图形界面和一组系统存储过程，其作用如下：
图形化监视SQL Server查询；
在后台收集查询信息；
分析性能；
诊断像死锁之类的问题；
调试T-SQL语句；
模拟重放SQL Server活动；
Profier跟踪，捕捉SQL Server实例上执行的活动。
因此，从功能完整性角度来说，我们完全可以使用SQL Profiler来实现就数据库实例级别的审计日志的功能，而且SQL Profier提供了图形化配置工具来完成日志审计的配置。
图形化配置方法如下：
Step1, 打开SQL Server Management Studio，在菜单中，找到“工具”菜单，找到SQL Server Profiler菜单项,点击进入。
Step3, 点击Events Selection标签页，选择需要跟踪的事件，并自定义一些列。如下图，比如我们需要跟踪发起操作的登陆用户，可以在Filter中选择LoginName，并通过like设置过滤的用户。
优点
图形化配置比较简单
适用于大部分版本SQL Server
基于实例级别的技术实现，数据库的改动不会对审计有影响，可维护性好
可以完成Server-Level（比如Security方面的login，logout）审计和Database-Level的审计
缺点
性能比较低，开销比较大
数据库日志分析
SQL Server数据库事务日志中，记录了每个事务的数据变更操作的详细信息，包含谁在哪个时间点做了什么操作。所以，这个功能完全满足我们的需求，基于SQL Server数据库事务日志的分析，来获取数据变更的详细审计日志信息，而且代价最小，不需要任何的配置。我也花了很多时间在这个功能上，但最后悲剧的是log的内容是16进制存储，而且是特定的格式，很难简单的解析出log内容，有可能不同版本的SQL Server事务日志格式存在差异，必须要对每个版本的事务日志解析做相应的适配，导致维护成本极高，产品功能延续性存在极大风险和挑战。有个商业的软件ApexSQL的公司，他们的产品ApexSQL Log就是通过数据库事务日志来实现审计日志功能的产品，界面如下：
除了使用商业工具，我们也可以从fn_dblog中获取审计日志，如下脚本，但不能获取操作内容，如果需要获取用户信息，可以通过SUSER_SNAME([Transaction SID])获取谁执行了此操作。获取的方法如下图：
SELECT [Current LSN],[Transaction ID],AllocUnitName,[Begin Time], [Transaction Name],[Transaction SID],[End Time],SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
GO
优点
基于Transaction log，性能比较高，开销较小
适用于所有的SQL SERVER版本
缺点
分析Log内容比较复杂，只能借助商业软件
没有UI界面，只能通过查询工具来具体分析某一个表的某一个操作，比较复杂
没有商业软件的支持的情况下，Log的可读性差
数据库级别的技术方案，可维护性差
SQL审计
SQL审计是SQL Server基于Extended Events上开发的一个SQL审计功能，功能比较全，可以执行Database-Level的审计和Server-Level的审计，使用比较简单，日志也易懂。但SQL审计在不同的SQL Server的版本下可以完成的功能也不一样，如下：
EditionSQL Server 2008 and 2008 R2SQL Server 2012 and 2014EnterpriseServer and database-levelServer and database-levelEvaluationServer and database-levelServer and database-levelDeveloperServer and database-levelServer and database-levelDatacenterNoneServer-levelBusiness IntelligenceServer and database-levelServer and database-levelStandardNoneServer-levelWebNoneServer-levelExpressNoneServer-level
第一步，创建实例级别的Audit
首先创建服务器级别的审计对象，展开"Security"，右击Audits，通过“New Audit”，打开“Create Audit”窗体开始创建审计对象，审计输出的数据保存到“Audit destination”中，本文选择File，把数据存储到审计文件中，其他类型是：Security Log和Application Log。Queue delay是指数据写入到审计文件的延迟，默认是1s。审计对象的作用是指定审计数据保存的路径，以及写入数据的延迟和数据文件的大小，审计对象主要是存储审计规范的数据。
根据硬盘空间的限制，设置审计对象的属性 Audit File maximum Limit、Maximum File size、以及Reserve disk space，控制审计文件的大小，管理硬盘空间的使用，避免硬盘爆掉。
第二步，启用审计对象
选择刚创建的Audit对象，右键选择Enable Audit，如下图。
第三步，创建数据库级别的审计规范
在数据库的Security中右击“Database Audit Specifications”，除了数据库级别的审计动作（Database-Level Audit Actions），在数据库对象上发生以下操作（Action）时，记录事件的信息：
SELECT
UPDATE
INSERT
DELETE
EXECUTE
REFERENCES
数据库级别的审计操作追踪和记录的是数据库对象（schema，objects）上发生的事件，因此必须配置Object Class、Object Schema，Object Name 和 Principal Name字段，如下图，
第四步，查看审计数据
查看审计数据，选中Server级别的审计对象，通过”View Audit Logs“查看记录的日志数据，如下图
使用SQL审计的方式优缺点如下：
优点
性能比较高，开销较小
支持Server-level和database-level的审计
缺点
支持的版本有限
基于数据库级别的方案，可维护性差。
Extended Event
SQL Server产品长期的规划是逐渐使用Extended Event来替换SQL Profiler工具，因为Extended Event更加轻量级，性能消耗比SQL Profiler大幅降低，因此对用户系统性能影响也大幅减轻。在审计日志的应用场景中，只需要在实例级别创建一个Extended Event Session对象，然后启用即可。既满足了功能性的需求，又能够做到很好后期维护，不需要为某一个数据库创建相应对象，对实例的性能消耗大幅降低到5%左右。
第一步，创建Extended Event Session
首先创建Extended Event Session，展开"Management"，右击Extend Events下面的Sessions，打开“New Session Wizard”窗体开始创建Session。
按照创建向导一步步的创建，会提示输入Session Name，例如，sql_statement_audit,并勾选Start the event session at server startup，如下图，
下一步，向导会提示选择模板，可以选择do not use a template，如下图，
下一步，向导会提示Select Events to Capture，这里面会选择需要捕获的事件，
下一步，向导会提示Capture Global Fields，选择捕获的事件字段，如下图
这些步骤可以通过脚本实现，脚本如下：
CREATE EVENT SESSION [SQL_Statement_Audit] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'SQL_Statement_Audit')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO第二步，启用Extend Event Session
启用Extend Event Session，选中刚才创建的Extend Session，比如SQL_Statement_Audit,右键Start Session,如下图，
第一步和第二步可以结合成一个脚本如下
-- =============================================
-- Create date: 08/01/2019
-- Description: 创建Extended Event Session,名字指定为SQL_Statement_Audit
-- Remarks:设置服务启动时自动启动此Session，创建后立即启动此Session
-- =============================================
USE master
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='SQL_Statement_Audit')
DROP EVENT session SQL_Statement_Audit ON SERVER;
GO
CREATE EVENT SESSION [SQL_Statement_Audit] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'SQL_Statement_Audit')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
-- We need to enable event session to capture event and event data
ALTER EVENT SESSION [SQL_Statement_Audit]
ON SERVER STATE = START;GO第三步，查看审计Log
选中创建的Event Session下面的log file，右键选择View Target Data，即可查看审计日志，如下图，
Log的内容以XML方式存储，因此必须分析xml的内容，xml文件的位置存储在指定的目录下，而且是rollback方式存储，以下脚本可以获取.
SELECT name, target_name, CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'SQL_Statement_Audit';显示结果将包含一个xml文件，该文件包含xel文件的存储位置,比如我的路径配置在S:\MSSQL12.MSSQLSERVER\MSSQL\Log目录下，下面会有很多的xel文件。
<EventFileTarget truncated="0">
<Buffers logged="114" dropped="0" />
<File name="S:\MSSQL12.MSSQLSERVER\MSSQL\Log\SQL_Statement_Audit_0_131913202717420000.xel" />
</EventFileTarget>xml文件内容可读性比较差，下面的脚本实现了一个存储过程来查询并分析Log的内容，此脚本会查询log的路径并分析指定的更新操作。
-- =============================================
-- Create date: 08/01/2019
-- Description: 查询数据库更新日志，包括所有site库，configuration库，操作包括delete，update，alter，truncate，delete
-- Parameters:
-- - @userName -> 指定sql用户,默认值为SA
-- - @startTime -> 查询log的起始时间，默认为2019-01-08
-- - @endTime -> 查询log的结束时间，默认为2030-12-31
-- =============================================
USE master
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF (OBJECT_ID('query_audit_log', 'P') is not null)
DROP PROC query_audit_log
GO
CREATE PROC query_audit_log
@userName nvarchar(200) = 'sa',
@startTime varchar(100) = '2019-01-08T00:00:00',
@endTime varchar(100) = '2030-12-31T00:00:00'
AS
--获取XEL Log的存储路径
DECLARE @XELPath VARCHAR(256) = CONVERT(VARCHAR(256),SERVERPROPERTY('ErrorLogFileName'));
SET @XELPath = LEFT(@XELPath,
LEN(@XELPath) - CHARINDEX('\', REVERSE(@XELPath)) + 1)
IF OBJECT_ID('tempdb..#tempdbname') IS NOT NULL
DROP TABLE #tempdbname
SELECT
event_xml.value('(./@name)', 'varchar(1000)') as event_name,
event_xml.value('(./@timestamp)', 'varchar(1000)') as timestamp,
event_xml.value('(./action[@name="sql_text"]/value)[1]', 'varchar(4000)') as sql_text,
event_xml.value('(./action[@name="username"]/value)[1]', 'varchar(4000)') as username,
event_xml.value('(./action[@name="database_name"]/value)[1]', 'varchar(4000)') as databasename
into #tempdbname
FROM (SELECT CAST(event_data AS XML) xml_event_data
FROM sys.fn_xe_file_target_read_file( @XELPath + N'SQL_Statement_Audit_*.xel', NULL, NULL, NULL)) AS event_table
CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
SELECT * from #tempdbname WHERE
(LOWER(databasename) LIKE '%site%' OR
LOWER(databasename) LIKE '%configuration%') AND
timestamp >= @startTime AND
timestamp <= @endTime AND
( LOWER(sql_text) LIKE '%update%' OR LOWER(sql_text) LIKE '%delete%' OR LOWER(sql_text) LIKE '%alter%' OR LOWER(sql_text) LIKE '%truncate%')
GO执行此存储过程，可以查询指定日期范围内，指定用户对我们的数据库进行了插入，删除，更新等操作，脚本如下：
exec query_audit_log上面的脚本中Site1是获取Site1下的更新操作。结果显示如下：
使用Extended Event方式实现Audit log，相比于其他的三种技术，使用Extended Event实现审计日志方法是最优选择。
优点
性能比较高，开销较小
适用于大部分SQL SERVER版本
基于实例的Audit，可维护性比较高
缺点
Log内容需要自己分析xml，不如sql audit和profier直观
写在最后
本文比较了SQL Audit的五种实现方案，触发器，SQL Profier，SQL审计，Extended Event和基于事物日志的优缺点，详细描述了每种方案的详细配置步骤，最后无论从可维护性，可靠性，性能，易用性方面比较，Extend Event是最优的选择
网友评论