SQL Server審計(jì)功能入門(mén)：SQL Server審核 (SQL Server Audit)
介紹
??? Audit是SQL Server 2008之后才有的功能，它能告訴你“誰(shuí)什么時(shí)候做了什么事情”。具體是指審核SQL Server 數(shù)據(jù)庫(kù)引擎實(shí)例或單獨(dú)的數(shù)據(jù)庫(kù)涉及到跟蹤和記錄數(shù)據(jù)庫(kù)引擎中發(fā)生的事件。它的底層是基于擴(kuò)展事件(Extented Event)，所以其性能和靈活性相對(duì)較好。審核數(shù)據(jù)可以輸出到審核文件、Windows安全日志和應(yīng)用程序日志。
??? Audit都需要?jiǎng)?chuàng)建一個(gè)實(shí)例級(jí)的“SQL Server審核”，然后可以創(chuàng)建從屬于它“服務(wù)器審核規(guī)范”和“數(shù)據(jù)庫(kù)審核規(guī)范”。可以理解“SQL Server審核”是審核的頂級(jí)容器，這兩個(gè)“規(guī)范”是定義要審核的具體內(nèi)容。
創(chuàng)建和使用審核的一般過(guò)程：
1. 創(chuàng)建審核并定義目標(biāo)。
2. 創(chuàng)建映射到審核的服務(wù)器審核規(guī)范或數(shù)據(jù)庫(kù)審核規(guī)范，并啟用審核規(guī)范。
3. 啟用審核。
4. 通過(guò)使用 Windows“事件查看器”、“日志文件查看器”或 fn_get_audit_file 函數(shù)來(lái)讀取審核事件。
?
實(shí)現(xiàn)
創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)和數(shù)據(jù)：
use?master go create?database?AuditTest go use?AuditTest go create?table?tb(ID?int?primary?key?,name?varchar(20),weight?decimal(10,2)); insert?into?tb?values(1,'John',70.2),(2,'Ted',80.8),(3,'Clark',75.1) go
1.?創(chuàng)建審核和定義目標(biāo)。
創(chuàng)建審核可以用SSMS或者語(yǔ)句實(shí)現(xiàn)。所謂目標(biāo)，就是審核輸出存到哪里，與擴(kuò)展事件的Target差不多。
對(duì)應(yīng)的語(yǔ)句創(chuàng)建：
--create?Server?Audit USE?[master] GO CREATE?SERVER?AUDIT?[Audit-AuditTest] TO?FILE (?FILEPATH?=?N'D:share' ?,MAXSIZE?=?50?MB ?,MAX_FILES?=?10 ?,RESERVE_DISK_SPACE?=?ON ) WITH (?QUEUE_DELAY?=?1000 ?,ON_FAILURE?=?CONTINUE ?--AUDIT_GUID=uniqueidentifier? ) --WHERE?object_name='tb' GO --Enable?Server?Audit alter?server?audit?[Audit-AuditTest]?with?(state=on)
簡(jiǎn)單說(shuō)明一下相關(guān)參數(shù)：
TO FILE:指定輸出到審核文件，也可以指定為Security Log和Application Log。?
FILEPATH：審核文件的目錄地址。
MaxSize：?jiǎn)蝹€(gè)審核文件的最大容量。?
MAXSIZE：類(lèi)似于Trace,指定Rollover允許最多文件數(shù)。?
RESERVE_DISK_SPACE：預(yù)先分配審核文件到MaxSize。個(gè)人推薦啟用。?
QUEUE_DELAY：指定事件發(fā)生到被強(qiáng)制審核的毫秒間隔。指定為0則為同步審核。?
ON_FAILURE ：當(dāng)審核向上檔寫(xiě)入數(shù)據(jù)失敗時(shí)，接下來(lái)會(huì)采取的行為：CONTINUE | SHUTDOWN | FAIL_OPERATION。?
AUDIT_GUID：用于數(shù)據(jù)庫(kù)鏡像。類(lèi)似Login的SID作用，鏡像會(huì)話的主庫(kù)如果有審核，則在鏡像庫(kù)創(chuàng)建對(duì)應(yīng)的審核需要指定同樣GUID。
WHERE：相當(dāng)于擴(kuò)展事件中Predicate，用于指定過(guò)濾條件。
2.?創(chuàng)建數(shù)據(jù)庫(kù)審核規(guī)范并啟用之
指定從屬于哪個(gè)SQL Server審核和定義出要審核的內(nèi)容。可以通過(guò)SSM或者語(yǔ)句創(chuàng)建之。下面針對(duì)測(cè)試表tb創(chuàng)建審核規(guī)范：任何人對(duì)tb表的DML和表結(jié)構(gòu)修改操作都被審核。
對(duì)應(yīng)的語(yǔ)句：
--Create?and?enable?Database?Audit?Specification USE?[AuditTest] GO CREATE?DATABASE?AUDIT?SPECIFICATION?[DatabaseAuditSpecification-AuditTest] FOR?SERVER?AUDIT?[Audit-AuditTest] ADD?(SCHEMA_OBJECT_CHANGE_GROUP), ADD?(SELECT,DELETE,INSERT,UPDATE?ON?OBJECT::[dbo].[tb]?BY?[public]) WITH?(STATE=ON) GO
對(duì)照SSMS，參數(shù)的意義就很明白了。更詳細(xì)內(nèi)容參考：CREATE DATABASE AUDIT SPECIFICATION
審核活動(dòng)類(lèi)型(Audit Action Type)參考：SQL Server 審核操作組和操作
3.?啟用審核
??? SQL Server在創(chuàng)建審核和審核規(guī)范時(shí)，默認(rèn)是不啟用，需要顯式啟用。在前面兩步，我已經(jīng)顯式啟用了。
4.?測(cè)試和查看審核數(shù)據(jù)
use?AuditTest go select?*?from?dbo.tb where?ID=1; update?dbo.tb set?name='Ted_New' where?ID=2; alter?table?dbo.tb?alter?column?name?varchar(30); alter?table?dbo.tb?add?newCol?varchar(20); go
在前面目標(biāo)定義的文件中會(huì)生成一個(gè)審核文件，文件名：[SQL Server審核名稱(chēng)]_[審核的GUID]_*.sqlaudit。
這個(gè)文件也可以用其它文字編輯器打，但是不便閱讀。通常使用系統(tǒng)函數(shù)sys.fn_get_audit_file讀取它。
SELECT?event_time
????,?action_id
????,?succeeded
????,?session_id
????,?session_server_principal_name
????,?object_name
????,?statement
????,?file_name
????,?audit_file_offset
from?sys.fn_get_audit_file('d:share*',default,default)從結(jié)果可以得到：誰(shuí)在什么時(shí)候做了什么。Select和Alter語(yǔ)句只有一條記錄。Update有兩條記錄，一條Select和一條Update。將audit_file_offset的值傳遞給fn_get_audit_file作為第三個(gè)參數(shù)值，可以實(shí)現(xiàn)從指定的offset讀取審核文件。注意：event_time輸出為UTC時(shí)間了。
當(dāng)然也可以直接查看：
5.?嘗試服務(wù)器審核規(guī)范
審核創(chuàng)建和刪除登錄，并將審核內(nèi)容記錄到應(yīng)用程序日志。
USE?[master] GO CREATE?SERVER?AUDIT?[Audit-AuditTest] TO?APPLICATION_LOG WITH (?QUEUE_DELAY?=?1000 ,ON_FAILURE?=?CONTINUE ) GO Alter?SERVER?AUDIT?[Audit-AuditTest]?With(State=On) go Create?Server?Audit?Specification?SAS_CreateDropLogin For?Server?Audit?[Audit-AuditTest] Add?(SERVER_PRINCIPAL_CHANGE_GROUP) With?(State=On) go create?login?xx?with?password='P@ssW0rd'; drop?login?xx?;
通過(guò)Windows的事件查看器，可以查看到33205事件。個(gè)人覺(jué)得在查看數(shù)據(jù)詳細(xì)事件時(shí)，使用XML格式更好理解一些。
總結(jié) SQL Server審核相對(duì)而言性能影響較少，審核粒度也非常靈活。審核是針對(duì)“事件“，回答”誰(shuí)什么時(shí)候干了什么“，但對(duì)數(shù)據(jù)本身變化的跟蹤力度較弱。審核的目標(biāo)結(jié)果，無(wú)論是審核文件或者應(yīng)用程序日志，都需要另外處理和分析才能得想要的內(nèi)容。
下載文檔
