Auditing SQL Server SQL Statements

“谁把我的表给删拉”，”谁删了整个表阿”…碰到这种棘手的情况，你如果没有预先做好准备，真的是头都要急炸了。那怎么能抓出这个”凶手”呢？SQL Trace, SQL Profile，SQL Trigger，Extended Events等着伺候你呢，更别说CLR，Service Broker等重量级武器了，别急！

工具虽好，但是也得用得贴合场景才能发挥作用，要不然跟你的SQL Server抢IO，那就得不偿失了。比如高频的OLTP场合，你还用TRIGGER把大量的DML语句都写到当前数据库去，那不是给数据库增加一倍工作量么。当然控制好权限，做好测试也能保证你的数据安全。这不妨碍我们讨论auditing SQL。

SQL Profiler是基于SQL Trace的，而SQL Trace是会被更高版本的SQL Server给逐渐摒弃的，所以我们就只讨论一个就可以了。一开始我知道 SQL Profiler是可以可视化监控即时的SQL Server活动的，但是缺点是不能保存或者自动执行，需要人工干预。经过研究，它是可以自动保存截取结果的。

让我们认识下“源”：想要抓“破坏分子”，首先要知道“破环分子”的标示有哪些，漏抓，错抓，都是失手的表现。访问SQL SERVER的方式有很多种，有ADO.NET, JDBC, SQL SERVER Management Studio, ODBC等各种方法，也有 Ad-hoc SQL, Stored Procedure等表现形式，是否每一种的方式都有各自不同的格式呢？最好的方法就是针对每种方式都来做个例子验证下。

首先最简单的SSMS方式，我们将SQL Profiler限制到某一个数据库，lenistest4。 在SSMS里面输入：

use lenistest4go

SQL Profiler显示的是SQL:BatchCompleted。

这里与有没有go无关。当你选中一块SQL区域并执行，其中如果有go那就有关系了，比如：

select top 10 * from sys.tablesgoselect top 10 * from dbo.regiongo

这里一个go分割了一个batch。最后一个go没有意义，我们提交了一段代码，这段代码相当于是一个大batch，如果中间有go，那这个go 就代表了一个子batch。

那么用动态语句，会有什么Trace格式呢：

declare @sqlstatement nvarchar(max) = N'select top 10 * from dbo.region' ;exec sp_executesql @sqlstatement

这里有SQL:Batch*, SQL:Stmt*,SP:Stmt， SQL:Stmt*代表的是一系列的SQL命令，比如declare, set等；SP:Stmt*代表了stored procedure的范围内语句。

declare @sqlstatement nvarchar(max) = N'declare @regionName varchar(20) = ''China''select top 10 * from dbo.regionwhere regionName = @regionName' ;exec sp_executesql @sqlstatement

上面这段SQL，验证了SQL命令select和declare是不是都被看作是SP:Stmt* ? 其实在一个batch里面，SQL命令里面DML语句也是被当作Statement来处理的。

如果在动态SQL里面有go是不是也会有SP:Batch*? 我们接着往下看：

declare @sqlstatement nvarchar(max) = N'declare @regionName varchar(20) = ''China''select top 10 * from dbo.regionwhere regionName = @regionName;godeclare @regionName2 varchar(20) = ''England''select top 10 * from dbo.regionwhere regionName = @regionName2' ;exec sp_executesql @sqlstatement

Msg 102, Level 15, State 1, Line 12

Incorrect syntax near ‘go’.

可以看到go是不能用在动态语句里面的。

为了验证SP:Stmt*是不是指的是存储过程里面的语句，我们先创建一个stored procedure,然后再执行它：

create procedure dbo.getRegionNameasbegindeclare @regionName varchar(20) = 'China'select top 10 * from dbo.regionwhere regionName = @regionNamedeclare @regionNamex varchar(20) = 'England'select top 10 * from dbo.regionwhere regionName = @regionNamexendexec dbo.getRegionName

正是如此 ！综上所述， SQL:Batch* ， 这里的batch相当于是个scope，一个大的执行空间，里面的所有 SQL 语句都是statement，包括DML，DDL等一系列 T-SQL语句 ；而SP:Stmt*又是存储过程的执行空间，里面所有的 T-SQL语句都是statement。

下面看段c#调用这个stored procedure，看看trace是如何识别的：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;using System.Data.SqlTypes;using System.Data;namespace AccessLenistest4DB{    class Program    {        static void Main(string[] args)        {            SqlConnection ICONN = new SqlConnection(@"data source = (localhost);initial catalog = lenistest4 ;user id = sa; password = sas;");            SqlCommand icmd = new SqlCommand();            icmd.Connection = ICONN;            icmd.CommandType = System.Data.CommandType.StoredProcedure;            icmd.CommandText = "dbo.getRegionName";            SqlDataAdapter ida = new SqlDataAdapter(icmd);            DataSet localds = new DataSet();            try            {                ICONN.Open();                ida.Fill(localds);            }            catch(SqlException se)            {                Console.Write(se.ToString());            }        }    }}

这儿多了个RPC， remote procedure call。其他都一样，所以RPC可以看作是一种命名空间，用阿里区别访问协议。这里要区别的是我们调用的是stored procedure，所以会RPC。所以如果我们是用纯SQL来访问数据库，那会不会有 RPC标示呢：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;using System.Data.SqlTypes;using System.Data;namespace AccessLenistest4DB{    class Program    {        static void Main(string[] args)        {            SqlConnection ICONN = new SqlConnection(@"data source = (localhost);initial catalog = lenistest4 ;user id = sa; password = sas;");            SqlCommand icmd = new SqlCommand();            icmd.Connection = ICONN;            icmd.CommandType = System.Data.CommandType.Text;            icmd.CommandText = "select * from dbo.region";            SqlDataAdapter ida = new SqlDataAdapter(icmd);            DataSet localds = new DataSet();            try            {                ICONN.Open();                ida.Fill(localds);            }            catch(SqlException se)            {                Console.Write(se.ToString());            }        }    }}

并没有RPC。 事实证明 RPC只出现在客户端语言调用存储过程的例子。

Trace的手段 ：“破坏分子”的特征被识别了，接下来就是怎么去抓捕的手段问题了。可以有即时的GUI工具，比如SQL Profiler，Extended Event(SSMS自带)，也可以用脚本去抓，并放在特定存储里面供稍后分析使用。

SQL Profiler 是即时的GUI工具很好用，可以保存结果，也可以自定义模板，缺点在于你必须开一个额外的窗口去跟踪，有时候数据量太大，还会影响传输，对多太SQL Server做监控就不怎么容易了，一个一个手工去开窗口，是不是很麻烦 ?

这里有Extended Events可以帮我们用脚本的形式去捕捉这些T-SQL语句，这里有个简单的例子：

1. 通过 Extended Events ，我们可以监控一段时间内的的 SQL Completed 情况，简要介绍下：

1.1 Extended Events 概念：由一系列自动触发的 event 产生性能数据，经过 event engine 的收集，存放到指定的输出文件，以供后续的分析。

1.2 XE 涉及到的动态管理试图 : sys.dm_xe_packages; sys.dm_xe_objects;sys.dm_xe_sessions

1.3 基本用法:

2.3.1 创建一个 Event Session

create event session capture_sql_eventson serveradd event sqlserver.sql_statement_completed(action(sqlserver.sql_text))add target package0.event_file(set filename = 'E:\data_bu\capture_sql_events.xel', metadatafile = 'E:\data_bu\capture_sql_event.xem')go

2.3.2 启用这个 Event session 来收集数据

alter event session capture_sql_eventson serverSTATE = startgo

2.3.3 停用这个 Event session

alter event session capture_sql_eventson serverstate = stopgo

2.3.4 修改一个 session 来增加或者删除对 Event 的监控

alter event session capture_sql_events on serveradd event sqlserver.sql_batch_completed (action(sqlserver.sql_text))go

2.3.5 查看正在运行的 Event Session

select * from sys.dm_xe_sessionsselect * from sys.dm_xe_session_events

2.3.6 查看收集到的统计数据

select top 10 * from dbo.regiongoselect * , cast(event_data as xml) as event_data_xmlfrom sys.fn_xe_file_target_read_file('E:\data_bu\capture_sql_events*.xel',null,null,null)where event_data like N'%region%'

从结果集我们可以看到，sql_statement_completed这个Event抓到的结果中，包含了Action中我们指定的内容，还包含了其他的一些统计信息：

<event name="sql_statement_completed" package="sqlserver" timestamp="2016-05-06T03:29:48.868Z"><data name="duration"><value>0</value></data><data name="cpu_time"><value>0</value></data><data name="physical_reads"><value>0</value></data><data name="logical_reads"><value>2</value></data><data name="writes"><value>0</value></data><data name="row_count"><value>6</value></data><data name="last_row_count"><value>6</value></data><data name="line_number"><value>1</value></data><data name="offset"><value>0</value></data><data name="offset_end"><value>62</value></data><data name="statement"><value>select top 10 * from dbo.region</value></data><data name="parameterized_plan_handle"><value /></data><action name="sql_text" package="sqlserver"><value>select top 10 * from dbo.region</value></action></event>

我们在Action里面加入对database_name,和plan_handle的捕捉，可以从结果看到又多出来两个元素：

create event session capture_sql_eventson serveradd event sqlserver.sql_statement_completed(action(sqlserver.sql_text,sqlserver.database_name,sqlserver.plan_handle))add target package0.event_file(set filename = 'E:\data_bu\capture_sql_events.xel', metadatafile = 'E:\data_bu\capture_sql_event.xem')

<event name="sql_statement_completed" package="sqlserver" timestamp="2016-05-06T03:52:02.524Z"><data name="duration"><value>0</value></data><data name="cpu_time"><value>0</value></data><data name="physical_reads"><value>0</value></data><data name="logical_reads"><value>2</value></data><data name="writes"><value>0</value></data><data name="row_count"><value>6</value></data><data name="last_row_count"><value>6</value></data><data name="line_number"><value>1</value></data><data name="offset"><value>0</value></data><data name="offset_end"><value>62</value></data><data name="statement"><value>select top 10 * from dbo.region</value></data><data name="parameterized_plan_handle"><value /></data><action name="plan_handle" package="sqlserver"><value>06002000448a700f00d62b7a0300000001000000000000000000000000000000000000000000000000000000</value></action><action name="database_name" package="sqlserver"><value>lenistest4</value></action><action name="sql_text" package="sqlserver"><value>select top 10 * from dbo.region</value></action></event>