介紹

??? Audit是SQL Server 2008之后才有的功能，它能告訴你“誰什么時候做了什么事情”。具體是指審核SQL Server 數(shù)據(jù)庫引擎實例或單獨的數(shù)據(jù)庫涉及到跟蹤和記錄數(shù)據(jù)庫引擎中發(fā)生的事件。它的底層是基于擴展事件(Extented Event)，所以其性能和靈活性相對較好。審核數(shù)據(jù)可以輸出到審核文件、Windows安全日志和應(yīng)用程序日志。

??? Audit都需要創(chuàng)建一個實例級的“SQL Server審核”，然后可以創(chuàng)建從屬于它“服務(wù)器審核規(guī)范”和“數(shù)據(jù)庫審核規(guī)范”。可以理解“SQL Server審核”是審核的頂級容器，這兩個“規(guī)范”是定義要審核的具體內(nèi)容。

創(chuàng)建和使用審核的一般過程：

1. 創(chuàng)建審核并定義目標(biāo)。

2. 創(chuàng)建映射到審核的服務(wù)器審核規(guī)范或數(shù)據(jù)庫審核規(guī)范，并啟用審核規(guī)范。

3. 啟用審核。

4. 通過使用 Windows“事件查看器”、“日志文件查看器”或 fn_get_audit_file 函數(shù)來讀取審核事件。

?

實現(xiàn)

創(chuàng)建測試數(shù)據(jù)庫和數(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或者語句實現(xiàn)。所謂目標(biāo)，就是審核輸出存到哪里，與擴展事件的Target差不多。

對應(yīng)的語句創(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)

簡單說明一下相關(guān)參數(shù)：

TO FILE:指定輸出到審核文件，也可以指定為Security Log和Application Log。?
FILEPATH：審核文件的目錄地址。

MaxSize：單個審核文件的最大容量。?
MAXSIZE：類似于Trace,指定Rollover允許最多文件數(shù)。?
RESERVE_DISK_SPACE：預(yù)先分配審核文件到MaxSize。個人推薦啟用。?
QUEUE_DELAY：指定事件發(fā)生到被強制審核的毫秒間隔。指定為0則為同步審核。?
ON_FAILURE ：當(dāng)審核向上檔寫入數(shù)據(jù)失敗時，接下來會采取的行為：CONTINUE | SHUTDOWN | FAIL_OPERATION。?
AUDIT_GUID：用于數(shù)據(jù)庫鏡像。類似Login的SID作用，鏡像會話的主庫如果有審核，則在鏡像庫創(chuàng)建對應(yīng)的審核需要指定同樣GUID。

WHERE：相當(dāng)于擴展事件中Predicate，用于指定過濾條件。

2.?創(chuàng)建數(shù)據(jù)庫審核規(guī)范并啟用之

指定從屬于哪個SQL Server審核和定義出要審核的內(nèi)容?？梢酝ㄟ^SSM或者語句創(chuàng)建之。下面針對測試表tb創(chuàng)建審核規(guī)范：任何人對tb表的DML和表結(jié)構(gòu)修改操作都被審核。

對應(yīng)的語句：

--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

對照SSMS，參數(shù)的意義就很明白了。更詳細內(nèi)容參考：CREATE DATABASE AUDIT SPECIFICATION

審核活動類型(Audit Action Type)參考：SQL Server 審核操作組和操作

3.?啟用審核

??? SQL Server在創(chuàng)建審核和審核規(guī)范時，默認(rèn)是不啟用，需要顯式啟用。在前面兩步，我已經(jīng)顯式啟用了。

4.?測試和查看審核數(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)定義的文件中會生成一個審核文件，文件名：[SQL Server審核名稱]_[審核的GUID]_*.sqlaudit。

這個文件也可以用其它文字編輯器打，但是不便閱讀。通常使用系統(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é)果可以得到：誰在什么時候做了什么。Select和Alter語句只有一條記錄。Update有兩條記錄，一條Select和一條Update。將audit_file_offset的值傳遞給fn_get_audit_file作為第三個參數(shù)值，可以實現(xiàn)從指定的offset讀取審核文件。注意：event_time輸出為UTC時間了。

當(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?;

通過Windows的事件查看器，可以查看到33205事件。個人覺得在查看數(shù)據(jù)詳細事件時，使用XML格式更好理解一些。

總結(jié) SQL Server審核相對而言性能影響較少，審核粒度也非常靈活。審核是針對“事件“，回答”誰什么時候干了什么“，但對數(shù)據(jù)本身變化的跟蹤力度較弱。審核的目標(biāo)結(jié)果，無論是審核文件或者應(yīng)用程序日志，都需要另外處理和分析才能得想要的內(nèi)容。