SQL Server 2008 参数化查询 --可重用的缓存计划
我将讨论如果一个查询可以被参数化,那么SQL Server优化器怎样尝试将其参数化,以及你可以怎样建立你自己的参数化查询.
1.什么是参数化查询?
一个简单理解参数化查询的方式是把它看做只是一个T-SQL查询,它接受控制这个查询返回什么的参数.通过使用不同的参数,一个参数化查询返回不同的结果.要获得一个参数化查询,你需要以一种特定的方式来编写你的代码,或它需要满足一组特定的标准.
有两种不同的方式来创建参数化查询.第一个方式是让查询优化器自动地参数化你的查询.另一个方式是通过以一个特定方式来编写你的T-SQL代码,并将它传递给sp_executesql系统存储过程,从而编程一个参数化查询.这篇文章的后面部分将介绍这个方法.
参数化查询的关键是查询优化器将创建一个可以重用的缓存计划.通过自动地或编程使用参数化查询,SQL
Server可以优化类似T-SQL语句的处理.这个优化消除了对使用高贵资源为这些类似T-SQL语句的每一次执行创建一个缓存计划的需求.而且通过创建一个可重用计划,SQL
Server还减少了存放过程缓存中类似的执行计划所需的内存使用.
2.现在让我们看看使得SQL
Server创建参数化查询的不同方式.
参数化查询是怎样自动创建的?
微软编写查询优化器代码的人竭尽全力地优化SQL Server处理你的T-SQL命令的方式.我想这是查询优化器名称的由来.这些尽量减少资源和最大限度地提高查询优化器执行性能的方法之一是查看一个T-SQL语句并确定它们是否可以被参数化.要了解这是如何工作的,让我们看看下面的T-SQL语句:
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader
WHERE SalesOrderID = 56000;
在这里,你可以看到这个命令有两个特点.第一它简单,第二它在WHERE谓词中包含一个用于SalesOrderID值的指定值.查询优化器可以识别这个查询比较简单以及SalesOrderID有一个参数("56000").因此,查询优化器可以自动地参数化这个查询.
如果你使用下面的SELECT语句来查看一个只包含用于上面语句的缓存计划的,干净的缓冲池,那么你会看到查询优化器将T-SQL查询重写为一个参数化T-SQL语句:
SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], [sql].[text]
AS [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle
当我在一个SQL Server 2008实例上运行这个命令时,我得到下面的输出,(注意,输出被重新格式化了,以便它更易读):
如果你看看上面输出中的plan_text字段,你会看到它不像原来的T-SQL文本.如前所述,查询优化器将这个查询重新编写为一个参数化T-SQL语句.在这里,你可以看到它现在有一个数据类型为(int)的变量(@1),它在之前的SELECT语句中被定义的.另外在plan_text的末尾,值"56000"被替换为变量@1.既然这个T-SQL语句被重写了,而且被存储为一个缓存计划,那么如果未来一个T-SQL命令和它大致相同,只有SalesOrderID字段被赋的值不同的话,它就可以被用于重用.让我们在动作中看看它.
如果我在我的机器上运行下面的命令:
DBCC FREEPROCCACHE
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader
WHERE SalesOrderID = 56000;
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader
WHERE SalesOrderID = 56001;
SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], [sql].[text]
AS [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle
我从最后的SELECT语句得到下面的输出,(注意,输出被重新格式化以便它更易读):
在这里,我首先释放过程缓存,然后我执行两个不同、但却类似的非参数化查询来看看查询优化器是会创建两个不同的缓存计划还是创建用于这两个查询的一个缓存计划.在这里,你可以看到查询优化器事实上很聪明,它参数化第一个查询并缓存了计划.然后当第二个类似、但有一个不同的SalesOrderID值的查询发送到SQL
Server时,优化器可以识别已经缓存了一个计划,然后重用它来处理第二个查询.你可以这么说是因为"cnt"字段现在表明这个计划被用了两次.
3.数据库配置选项PARAMETERIZATION可以影响T-SQL语句怎样被自动地参数化.对于这个选项有两种不同的设置,SIMPLE和FORCED.当PARAMETERIZATION设置被设置为SIMPLE时,只有简单的T-SQL语句才会被参数化.要介绍这个,看下下面的命令:
SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56000
这个查询类似于我前面的示例,除了在这里我添加了一个额外的JOIN标准.当数据库AdventureWorks的PARAMETERIZATION选项被设置为SIMPLE时,这个查询不会被自动地参数化.SIMPLE
PARAMETERIZATION设置告诉查询优化器只参数化简单的查询.但是当选项PARAMETERIZATION被设置为FORCED时,这个查询将被自动地参数化.
当你设置数据库选项为使用FORCE PARAMETERIZATION时,查询优化器试图参数化所有的查询,而不仅仅是简单的查询.你可能会认为这很好.但是在某些情况下,当数据库设置PARAMETERIZATION为FORCED时,查询优化器将选择不是很理想的查询计划.当数据库设置PARAMETER为FORCED时,它改变查询中的字面常量.这可能导致当查询中涉及计算字段时索引和索引视图不被选中参与到执行计划中,从而导致一个无效的计划.FORCED
PARAMETERIZATION选项可能是改进具有大量类似的、传递过来的参数稍有不同的查询的数据库性能的一个很好的解决方案.一个在线销售应用程序,它的客户对你的产品执行大量的类似搜索,
产品值不同,这可能是一个能够受益于FORCED PARAMETERIZATION的很好的应用程序类型.
不是所有的查询从句都会被参数化.例如查询的TOP、TABLESAMPLE、
HAVING、GROUP BY、ORDER BY、OUTPUT...INTO或FOR XML从句不会被参数化.
4.使用sp_execute_sql来参数化你的T-SQL
你不需要依赖于数据库的PARAMETERIZATION选项来使得查询优化器参数化一个查询.你可以参数化你自己的查询.你通过重新编写你的T-SQL语句并使用”sp_executesql”系统存储过程执行重写的语句来实现.正如已经看到的,上面包括一个"JOIN"从句的SELECT语句在数据库的PARAMETERIZATION设置为SIMPLE时没有被自动参数化.让我重新编写这个查询以便查询优化器将创建一个可重用的参数化查询执行计划.
为了说明,让我们看两个类似的、不会被自动参数化的T-SQL语句,并创建两个不同的缓存执行计划.然后我将重新编写这两个查询使得它们都使用相同的缓存参数化执行计划.
让我们看看这个代码:
DBCC FREEPROCCACHE
SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56000
SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = 56001
SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], [sql].[text]
AS [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle
在这里,我释放了过程缓存,然后运行这两个包含一个JOIN的、不同的非简单的T-SQL语句.然后我将检查缓存计划.这是这个使用DMV
的SELECT语句的输出(注意,输出被重新格式化了,以便它更易读):
正如你从这个输出看到的,这两个SELECT语句没有被查询优化器参数化.优化器创建了两个不同的缓存执行计划,每一个都只被执行了一次.我们可以通过使用sp_executesql系统存储过程来帮助优化器为这两个不同的SELECT语句创建一个参数化执行计划.
下面是上面的代码被重新编写来使用sp_executesql
系统存储过程:
DBCC FREEPROCCACHE
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal
FROM AdventureWorks.Sales.SalesOrderHeader H
JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID
SELECT stats.execution_count AS cnt, p.size_in_bytes AS [size], [sql].[text]
AS [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle
如同你所看到的,我重新编写了这两个SELECT语句,使它们通过使用”EXEC
sp_executesql”语句来执行.对这些EXEC语句中的每一个,我都传递三个不同的参数.第一个参数是基本的SELECT语句,但是我将SalesOrderID的值用一个变量(@SalesOrderID)替代.在第二个参数中,我确定了@SalesOrderID的数据类型,在这个例子中它是一个integer.然后在最后一个参数中,我传递了SalesOrderID的值.这个参数将控制我的SELECT根据SalesOrderID值所生成的结果.sp_executesql的每次执行中前两个参数都是一样的.但是第三个参数不同,因为每个都有不同的SalesOrderID值.
现在当我运行上面的代码时,我从DMV
SELECT语句得到下面的输出(注意,输出被重新格式化了,以便它更易读):
从这个输出,你可以看出,我有一个参数化缓存计划,它被执行了两次,为每个EXEC语句各执行了一次.
使用参数化查询来节省资源和优化性能
在语句可以被执行之前,每个T-SQL语句都需要被评估,而且需要建立一个执行计划.创建执行计划会占用宝贵的CPU资源.当执行计划被创建后,它使用内存空间将它存储在过程缓存中.降低CPU和内存使用的一个方法是利用参数化查询.尽管数据库可以被设置为对所有查询FORCE参数化,但是这不总是最好的选择.通过了解你的哪些T-SQL语句可以被参数化然后使用sp_executesql存储过程,你可以帮助SQL
Server节省资源并优化你的查询的性能.
没有更多推荐了，
加入CSDN，享受更精准的内容推荐，与500万程序员共同成长！SQLSERVER2008新增的审核/审计功能
很多时候我们都需要对数据库或者数据库服务器实例进行审核/审计
例如对失败的登录次数进行审计，某个数据库上的DDL语句进行审计，某个数据库表里面的delete语句进行审计
事实上，我们这些审计的需求基本上都是为了一个目的：防黑客
上面的这些审计需求无非就是看一下有哪些人试图入侵数据库服务器，入侵了之后是否有drop表，是否有delete数据
在SQLSERVER2008及以前版本可以选择的方案有
1、和数据库级别的DDL触发器（SQL2005及以上版本） 以及DML触发器
2、自己手工从事务日志里读取操作记录，权威的书都会说事务日志不是审核工具，一般大型数据库都会设置为简单模式，事务日志截断
3、依靠SQLSERVER ERRORLOG来检查登录审核，导致SQLSERVER ERRORLOG login相关的日志泛滥 导致SQL排错造成困难
4、事件通知：5、更改跟踪：6、变更数据捕获（CDC）：
我们一般都会把C2 审核跟踪和登录审核里面只限成功的登录，以防止SQL ERRORLOG日志泛滥，因为服务器是很久才重启一次的，如果不做修改很容易造成磁盘爆满
--禁用C2 审核跟踪和只限成功的登录
EXEC sys.sp_configure N'c2 audit mode', N'0'
RECONFIGURE WITH OVERRIDE
USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 1
SQLSERVER2008新增的审核功能
在sqlserver2008新增了审核功能，可以对服务器级别和数据库级别的操作进行审核/审计，事实上，事件通知、更改跟踪、变更数据捕获（CDC）
都不是用来做审计的，只是某些人乱用这些功能，也正因为乱用这些功能导致踩坑
：性能跟踪
更改跟踪：用Sync Services来构建偶尔连接的系统
变更数据捕获（CDC）：数据仓库的ETL 中的数据抽取（背后使用logreader）
而审核是SQLSERVER专门针对数据库安全的进行的审核，记住，他是专门的！
我们看一下审核的使用方法&
步骤一：创建审核对象，审核对象是跟保存路径关联的，所以如果你需要把审核操作日志保存到不同的路径就需要创建不同的审核对象
我们把审核操作日志保存在文件系统里，在创建之前我们还要在相关路径先创建好保存的文件夹，我们在D盘先创建sqlaudits文件夹，然后执行下面语句
--创建审核对象之前需要切换到master数据库
USE [master]
CREATE SERVER AUDIT MyFileAudit TO FILE(FILEPATH='D:\sqlaudits') --这里指定文件夹不能指定文件，生成文件都会保存在这个文件夹
实际上，我们在创建审核对象的同时可以指定审核选项，下面是相关脚本
把日志放在磁盘的好处是可以使用新增的TVF:sys.[fn_get_audit_file] 来过滤和排序审核数据，如果把审核数据保存在Windows 事件日志里查询起来非常麻烦
USE [master]
CREATE SERVER AUDIT MyFileAudit TO FILE(
FILEPATH='D:\sqlaudits',
MAXSIZE=4GB,
MAX_ROLLOVER_FILES=6)
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)
CREATE SERVER AUDIT MyAppAudit TO APPLICATION_LOG
ALTER SERVER AUDIT MyAppAudit WITH(STATE =ON)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyAppAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
我们创建一个服务器级别的审核规范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)
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileB
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
--消息 33230，级别 16，状态 1，第 86 行
--审核 'MyFileAudit' 的审核规范已经存在。
这里要说一下 ：审核对象和审核规范的修改 ，无论是审核对象还是审核规范，在修改他们的相关参数之前，他必须要先禁用，后修改，再启用
--禁用审核对象
ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)
--禁用服务器级审核规范
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
--禁用数据库级审核规范
ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=OFF)
--相关修改选项操作
--启用审核对象
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
--启用服务器级审核规范
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=ON)
--启用数据库级审核规范
ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=ON)
审核服务器级别事件
审核服务级别事件，我们一般用得最多的就是审核登录失败的事件，下面的脚本就是审核登录成功事件和登录失败事件
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
修改审核规范
--跟审核对象一样，更改审核规范时必须将其禁用
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE =OFF)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
ADD (login_change_password_gourp),
DROP (successful_login_group)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE =ON)
审核操作组
每个审核操作组对应一种操作，在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
相关脚本如下：
--创建审核对象
USE [master]
CREATE SERVER AUDIT MyDBFileAudit TO FILE(FILEPATH='D:\sqldbaudits')
SERVER AUDIT
MyDBFileAudit WITH (STATE=ON)
--创建数据库级别审核规范
CREATE DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog
FOR SERVER AUDIT MyDBFileAudit
ADD (database_object_change_group),
ADD (SELECT ,INSERT,UPDATE,DELETE ON schema::dbo
BY PUBLIC)
WITH (STATE =ON)
我们先在D盘创建sqldbaudits文件夹
第一个操作组对数据库中所有对象的DDL语句create，alter，drop等进行记录第二个语句监视由任何public用户（也就是所有用户）对dbo架构的任何对象所做的DML操作
创建完毕之后可以在SSMS里看到相关的审核
数据库审核规范
服务器审核规范和审核对象
查看审核事件
被记录到文件系统的审核文件不是存储在可以利用记事本打开的文本文件中，而是采用二进制文件的方式
这里说一个，当磁盘空间不足的时候是可以直接删除这些SQLAUDIT文件
如果使用DDL触发器的方法：
一般都会在数据库里头创建一张表来保存审计数据，但是当表数据量达到很多的时候，DBA也需要去维护这张表
工作量又增加了，可能你会说，我需要审计的项目不多，所以审计的数据也不会太多，但对于某些大公司来说
他们要审计的数据是非常多的，有些需要归档，而有些不需要归档
对于不需要归档审计数据的情况，我比较喜欢这种方式，当磁盘容量不够的时候把最老的那个审计文件删除掉
当然，你可以把整个sqlaudits文件夹或某个sqlaudit文件进行备份，放到备份磁盘上，然后删除一些较老的sqlaudit文件
备份了之后以后就有机会对之前的审计数据进行翻查，都比较灵活
我们有两种方法查看审核日志
方法一：对象资源管理器-》安全性-》审核-》选中某个审核对象-》右键-》查看审核日志
审核项目包括有：日期、时间戳记、服务器实例名称、操作ID、类类型、序列号、成功或失败、列权限、数据库主体ID、服务器主体名称、
服务器主体SID、被执行的（或尝试）的实际语句等等
方法二：使用新的表值函数sys.[fn_get_audit_file]()
此函数接受一个或多个审核文件的参数（使用通配符模式匹配）
并利用另外两个附加参数可以指定要处理的起始文件，以及开始读取审核的已知偏移位置
这两个参数都是可选的，但依然必须使用关键字default指定，此函数随后从文件中读取二进制数据，并将格式化这些审核项目
服务器级别审核
根据最近时间的那个sqlaudit文件，查询这个文件里面的信息
[event_time] AS '触发审核的日期和时间' ,
sequence_number AS '单个审核记录中的记录顺序' ,
action_id AS '操作的 ID' ,
succeeded AS '触发事件的操作是否成功' ,
permission_bitmask AS '权限掩码' ,
is_column_permission AS '是否为列级别权限' ,
session_id AS '发生该事件的会话的 ID' ,
server_principal_id AS '执行操作的登录上下文 ID' ,
database_principal_id AS '执行操作的数据库用户上下文 ID' ,
target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
class_type AS '可审核实体的类型' ,
session_server_principal_name AS '会话的服务器主体' ,
server_principal_name AS '当前登录名' ,
server_principal_sid AS '当前登录名 SID' ,
database_principal_name AS '当前用户' ,
target_server_principal_name AS '操作的目标登录名' ,
target_server_principal_sid AS '目标登录名的 SID' ,
target_database_principal_name AS '操作的目标用户' ,
server_instance_name AS '审核的服务器实例的名称' ,
database_name AS '发生此操作的数据库上下文' ,
schema_name AS '此操作的架构上下文' ,
object_name AS '审核的实体的名称' ,
statement AS 'TSQL 语句（如果存在）' ,
additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
file_name AS '记录来源的审核日志文件的路径和名称' ,
audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量' ,
user_defined_event_id AS '作为 sp_audit_write 参数传递的用户定义事件 ID' ,
user_defined_information AS '于记录用户想要通过使用 sp_audit_write 存储过程记录在审核日志中的任何附加信息'
sys.[fn_get_audit_file]('D:\sqlaudits\MyFileAudit_F0BCDC6F-0A89-459D-B345-9DDEB036CC39_0_220000.sqlaudit',
DEFAULT, DEFAULT)
[event_time] BETWEEN ' 11:02:00'
' 11:18:00'
数据库级别审核
先执行下面脚本查询一些数据
SELECT * FROM [dbo].[nums]
[event_time] AS '触发审核的日期和时间' ,
sequence_number AS '单个审核记录中的记录顺序' ,
action_id AS '操作的 ID' ,
succeeded AS '触发事件的操作是否成功' ,
permission_bitmask AS '权限掩码' ,
is_column_permission AS '是否为列级别权限' ,
session_id AS '发生该事件的会话的 ID' ,
server_principal_id AS '执行操作的登录上下文 ID' ,
database_principal_id AS '执行操作的数据库用户上下文 ID' ,
target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
class_type AS '可审核实体的类型' ,
session_server_principal_name AS '会话的服务器主体' ,
server_principal_name AS '当前登录名' ,
server_principal_sid AS '当前登录名 SID' ,
database_principal_name AS '当前用户' ,
target_server_principal_name AS '操作的目标登录名' ,
target_server_principal_sid AS '目标登录名的 SID' ,
target_database_principal_name AS '操作的目标用户' ,
server_instance_name AS '审核的服务器实例的名称' ,
database_name AS '发生此操作的数据库上下文' ,
schema_name AS '此操作的架构上下文' ,
object_name AS '审核的实体的名称' ,
statement AS 'TSQL 语句（如果存在）' ,
additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
file_name AS '记录来源的审核日志文件的路径和名称' ,
audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量' ,
user_defined_event_id AS '作为 sp_audit_write 参数传递的用户定义事件 ID' ,
user_defined_information AS '于记录用户想要通过使用 sp_audit_write 存储过程记录在审核日志中的任何附加信息'
sys.[fn_get_audit_file]('D:\sqldbaudits\MyDBFileAudit_698BA060-CC40-4A3C-B19D-12B_920000.sqlaudit',
DEFAULT, DEFAULT)
将审核日志保存到文件系统的好处就是可以使用TVP里通过where 和order by对审核数据进行筛选和排序
和审核相关的视图
--查询审核相关视图
SELECT * FROM sys.[server_file_audits]
SELECT * FROM sys.[server_audit_specifications]
SELECT * FROM sys.[server_audit_specification_details]
SELECT * FROM sys.[database_audit_specifications]
SELECT * FROM sys.[database_audit_specification_details]
SELECT * FROM sys.[dm_server_audit_status]
SELECT * FROM sys.[dm_audit_actions]
SELECT * FROM sys.[dm_audit_class_type_map]
删除相关对象
--删除顺序
--删除数据库审核规范
ALTER DATABASE AUDIT SPECIFICATION [CaptureDBActionToEventLog] WITH (STATE=OFF)
DROP DATABASE AUDIT SPECIFICATION [CaptureDBActionToEventLog]
--删除服务器审核规范
USE [master]
ALTER SERVER
AUDIT SPECIFICATION [CaptureLoginsToFile] WITH (STATE=OFF)
DROP SERVER AUDIT SPECIFICATION [CaptureLoginsToFile]
--删除审核对象
ALTER SERVER AUDIT [MyFileAudit] WITH (STATE=OFF)
ALTER SERVER AUDIT [MyAppAudit] WITH (STATE=OFF)
ALTER SERVER AUDIT [MyEventLogAudit] WITH (STATE=OFF)
DROP SERVER AUDIT [MyAppAudit]
DROP SERVER AUDIT [MyFileAudit]
DROP SERVER AUDIT [MyEventLogAudit]
要查看是否打开了审核引擎使用下面SQL语句
&XE engine initialized已经初始化&表示审核引擎已经打开，扩展事件可以捕获审核事件
SELECT * FROM sys.[dm_os_ring_buffers] WHERE [ring_buffer_type]='RING_BUFFER_XE_LOG'
&Record id = "0" type ="RING_BUFFER_XE_LOG" time ="106735"&
&XE_LogRecord message="XE engine initialized"&&/XE_LogRecord&&/Record&
本文概括介绍了SQLSERVER2008新增的审核功能，在SQLSERVER相关论坛里面&审核&这个话题是大家问得比较多的
希望通过这篇文章，能让大家认识新增的审核功能，在生产环境里面遇到问题也可以互相交流
而审核功能最大的好处是：你使用自建审计表来保存审计数据，如果聪明的黑客攻破你的数据库实例，他自然可以把你的那个审计表
drop掉，你同样查不出黑客的任何蛛丝马迹，而审核不同，他把审核数据放在SQLSERVER外面，除非你们公司的SA和DBA的安全意识
很弱，黑客有机会把磁盘文件删除掉，否则依然有可能查出黑客的蛛丝马迹进行预防！！
msdn相关文章：
如有不对的地方，欢迎大家拍砖o(&_&)o&
补充：搬迁数据库后，数据库审核规范是不会删除的，你可以在新的数据库实例上再创建审核对象并把审核对象绑定到原来的数据库审核规范
--附加数据库，模拟搬迁数据库
USE [master]
CREATE DATABASE [MacFilter] ON
( FILENAME = N'E:\DataBase\MacFilter.mdf' ),
( FILENAME = N'E:\DataBase\MacFilter_log.ldf' )
FOR ATTACH
--在新实例上重新创建审核对象
CREATE SERVER AUDIT MyFileAudit TO FILE(
FILEPATH='E:\sqlaudits',
MAXSIZE=4GB,
MAX_ROLLOVER_FILES=6)
ON_FAILURE=CONTINUE,
QUEUE_DELAY=1000);
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
--修改原来的数据库级别审核规范
USE [MacFilter]
ALTER DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog WITH (STATE =OFF)
--重新绑定
ALTER DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog
FOR SERVER AUDIT MyFileAudit
WITH (STATE =ON)
--更新测试
UPDATE [Mac_Table] SET [MAC]=22 WHERE [PcName]='android_3eb5ee'
--查询审核数据
[event_time] AS '触发审核的日期和时间' ,
sequence_number AS '单个审核记录中的记录顺序' ,
action_id AS '操作的 ID' ,
succeeded AS '触发事件的操作是否成功' ,
permission_bitmask AS '权限掩码' ,
is_column_permission AS '是否为列级别权限' ,
session_id AS '发生该事件的会话的 ID' ,
server_principal_id AS '执行操作的登录上下文 ID' ,
database_principal_id AS '执行操作的数据库用户上下文 ID' ,
target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
class_type AS '可审核实体的类型' ,
session_server_principal_name AS '会话的服务器主体' ,
server_principal_name AS '当前登录名' ,
server_principal_sid AS '当前登录名 SID' ,
database_principal_name AS '当前用户' ,
target_server_principal_name AS '操作的目标登录名' ,
target_server_principal_sid AS '目标登录名的 SID' ,
target_database_principal_name AS '操作的目标用户' ,
server_instance_name AS '审核的服务器实例的名称' ,
database_name AS '发生此操作的数据库上下文' ,
schema_name AS '此操作的架构上下文' ,
object_name AS '审核的实体的名称' ,
statement AS 'TSQL 语句（如果存在）' ,
additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
file_name AS '记录来源的审核日志文件的路径和名称' ,
audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量'
sys.[fn_get_audit_file]('e:\sqlaudits\MyFileAudit_-C5D9--AF0E13CED504_0_250000.sqlaudit',
DEFAULT, DEFAULT)
--查看审核结果
----查询服务器审核事件
fgaf.[event_time] AS '触发审核的日期和时间' ,
fgaf.sequence_number AS '单个审核记录中的记录顺序' ,
fgaf.action_id AS '操作的 ID' ,
fgaf.succeeded AS '触发事件的操作是否成功' ,
fgaf.permission_bitmask AS '权限掩码' ,
fgaf.is_column_permission AS '是否为列级别权限' ,
fgaf.session_id AS '发生该事件的会话的 ID' ,
fgaf.server_principal_id AS '执行操作的登录上下文 ID' ,
fgaf.database_principal_id AS '执行操作的数据库用户上下文 ID' ,
fgaf.target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
fgaf.target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
fgaf.object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
fgaf.class_type AS '可审核实体的类型' ,
--[dactm].[class_type_desc] AS '可审核实体的类型描述' ,
fgaf.session_server_principal_name AS '会话的服务器主体' ,
fgaf.server_principal_name AS '当前登录名' ,
fgaf.server_principal_sid AS '当前登录名 SID' ,
fgaf.database_principal_name AS '当前用户' ,
fgaf.target_server_principal_name AS '操作的目标登录名' ,
fgaf.target_server_principal_sid AS '目标登录名的 SID' ,
fgaf.target_database_principal_name AS '操作的目标用户' ,
fgaf.server_instance_name AS '审核的服务器实例的名称' ,
fgaf.database_name AS '发生此操作的数据库上下文' ,
fgaf.schema_name AS '此操作的架构上下文' ,
fgaf.object_name AS '审核的实体的名称' ,
fgaf.statement AS 'TSQL 语句（如果存在）' ,
fgaf.additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
fgaf.file_name AS '记录来源的审核日志文件的路径和名称' ,
fgaf.audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量'
sys.[fn_get_audit_file]('E:\DataBaseAudit\ServerAudit\ServerFileAudit_9E770D97-AD49-466A-200F8_0_880000.sqlaudit',
DEFAULT, DEFAULT) AS fgaf
INNER JOIN sys.[dm_audit_class_type_map] AS dactm ON [dactm].[class_type] = [fgaf].[class_type]
fgaf.[event_time] BETWEEN ''
ORDER BY fgaf.[event_time]
----查询某个数据库的审核事件
fgaf.[event_time] AS '触发审核的日期和时间' ,
fgaf.sequence_number AS '单个审核记录中的记录顺序' ,
fgaf.action_id AS '操作的 ID' ,
fgaf.succeeded AS '触发事件的操作是否成功' ,
fgaf.permission_bitmask AS '权限掩码' ,
fgaf.is_column_permission AS '是否为列级别权限' ,
fgaf.session_id AS '发生该事件的会话的 ID' ,
fgaf.server_principal_id AS '执行操作的登录上下文 ID' ,
fgaf.database_principal_id AS '执行操作的数据库用户上下文 ID' ,
fgaf.target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
fgaf.target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
fgaf.object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
fgaf.class_type AS '可审核实体的类型' ,
[dactm].[class_type_desc] AS '可审核实体的类型描述' ,
fgaf.session_server_principal_name AS '会话的服务器主体' ,
fgaf.server_principal_name AS '当前登录名' ,
fgaf.server_principal_sid AS '当前登录名 SID' ,
fgaf.database_principal_name AS '当前用户' ,
fgaf.target_server_principal_name AS '操作的目标登录名' ,
fgaf.target_server_principal_sid AS '目标登录名的 SID' ,
fgaf.target_database_principal_name AS '操作的目标用户' ,
fgaf.server_instance_name AS '审核的服务器实例的名称' ,
fgaf.database_name AS '发生此操作的数据库上下文' ,
fgaf.schema_name AS '此操作的架构上下文' ,
fgaf.object_name AS '审核的实体的名称' ,
fgaf.statement AS 'TSQL 语句（如果存在）' ,
fgaf.additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
fgaf.file_name AS '记录来源的审核日志文件的路径和名称' ,
fgaf.audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量'
sys.[fn_get_audit_file]('E:\DataBaseAudit\DBAudit_db\DBFileAudit_db%8O%BC4B9--BDD9-F24D8DA1CE70_0_960000.sqlaudit',
DEFAULT, DEFAULT) AS fgaf
INNER JOIN sys.[dm_audit_class_type_map] AS dactm ON [dactm].[class_type] = [fgaf].[class_type]
fgaf.[event_time] BETWEEN ''
ORDER BY fgaf.[event_time]
--服务器审核规范详细
SELECT * FROM sys.[server_audit_specification_details]
--数据库审核规范详细
SELECT * FROM sys.[database_audit_specification_details]
SELECT * FROM sys.[dm_audit_actions]
阅读(...) 评论()