一. SQL Server審計(jì)功能介紹

SQL Server審計(jì)功能（Audit）是SQL Server 2008之后才有的功能，審計(jì)(Audit)用于追蹤和記錄SQL Server實(shí)例，或者單個(gè)數(shù)據(jù)庫中發(fā)生的事件(Event)，審計(jì)運(yùn)作的機(jī)制是通過捕獲事件(Event)，把事件包含的信息寫入到事件日志(Event Log)或?qū)徲?jì)文件(Audit File)中，為review提供最真實(shí)詳細(xì)的數(shù)據(jù)。

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

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

1. 創(chuàng)建審核并啟用。
2. 創(chuàng)建映射到審核的服務(wù)器審核規(guī)范或數(shù)據(jù)庫審核規(guī)范，并啟用審核規(guī)范。
3. 通過使用 Windows“事件查看器”、“日志文件查看器”或 fn_get_audit_file 函數(shù)來讀取審核事件。

官方手冊參考：https://learn.microsoft.com/zh-cn/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16

二. 啟用審核功能

下面以SQL Server2019為例，啟用審核功能，具體配置過程如下：

1.創(chuàng)建審核并啟用

“SQL Server 審核” 對象收集單個(gè)服務(wù)器實(shí)例或數(shù)據(jù)庫級操作和操作組以進(jìn)行監(jiān)視。 這種審核處于 SQL Server 實(shí)例級別。 每個(gè) SQL Server 實(shí)例可以具有多個(gè)審核。

定義審核時(shí)，將指定結(jié)果的輸出位置，這是審核的目標(biāo)位置。目標(biāo)位置支持三種，分別是文件、windows安全事件日志、windows應(yīng)用程序事件日志。 審核是在 禁用 狀態(tài)下創(chuàng)建的，因此不會自動審核任何操作。 啟用審核后，審核目標(biāo)將從審核接收數(shù)據(jù)。

創(chuàng)建審核可以通過SSMS界面化配置，也可通過執(zhí)行SQL語句實(shí)現(xiàn)。

（1）創(chuàng)建一個(gè)審核，名為 Audit-logfile，目標(biāo)位置為文件（審核結(jié)果文件存放在c:\dbaudit路徑中），通過SSMS界面化配置方法如下：
用管理員賬號登錄SQL Server管理工具（SQL Server Management Studio），在“安全性”-“審核”菜單上，右鍵-“新建審核”，如下圖：

新建審核界面如下圖：

點(diǎn)擊“確定”即可創(chuàng)建一個(gè)審核，創(chuàng)建后在“安全性”-“審核”下面多了一個(gè)名為“Audit-logfile”的審核配置，默認(rèn)是未啟用狀態(tài)（紅色的叉號表示未啟用，若想啟用審核，可右鍵點(diǎn)擊，選擇啟用審核即可），如下圖：

對應(yīng)的通過SQL語句方式創(chuàng)建審核的語句如下：

USE [master]
GO

CREATE SERVER AUDIT [Audit-logfile]
TO FILE 
(	FILEPATH = N'C:\dbaudit\'
	,MAXSIZE = 1024 MB
	,MAX_FILES = 100
	,RESERVE_DISK_SPACE = ON
) 
WITH 
(	QUEUE_DELAY = 1000, 
	ON_FAILURE = CONTINUE
)

ALTER SERVER AUDIT [Audit-logfile] WITH (STATE = OFF)
GO

如下圖，執(zhí)行sql語句后，會在左側(cè)“安全性”-“審核”下面生成一個(gè)名為“Audit-logfile”的配置：

SQL語句中相關(guān)參數(shù)說明如下：

• TO FILE:指定輸出到審核文件，也可以指定為SECURITY_LOG和APPLICATION_LOG。
• FILEPATH：審核文件的目錄地址。
• MAXSIZE：單個(gè)審核文件的大容量。
• MAX_FILES：類似于Trace,指定Rollover允許多文件數(shù)。
• RESERVE_DISK_SPACE：預(yù)先分配審核文件到MAXSIZE，推薦啟用。
• QUEUE_DELAY：指定事件發(fā)生到被強(qiáng)制審核的毫秒間隔。指定為0則為同步審核。
• ON_FAILURE ：當(dāng)審核向上檔寫入數(shù)據(jù)失敗時(shí)，接下來會采取的行為：CONTINUE | SHUTDOWN | FAIL_OPERATION。
• ALTER SERVER AUDIT [Audit-logfile] WITH (STATE = OFF) # 該語句是是否啟用審核，默認(rèn)是創(chuàng)建時(shí)未啟用（STATE = OFF），也可以創(chuàng)建的時(shí)候直接啟用（STATE = ON）

啟用審核后，在服務(wù)器 C:\dbaudit 路徑下會生成一個(gè) 審計(jì)日志文件（Audit-logfile），如下圖：

文件為sqlaudit類型（不可讀，后面需要通過sql語句進(jìn)行查詢：select * from sys.fn_get_audit_file(‘C:\dbaudit*’,default,default)）

（2）創(chuàng)建一個(gè)審核，名為 Audit-log，目標(biāo)位置為windows應(yīng)用程序日志（審核結(jié)果寫在windows應(yīng)用程序事件中），通過SSMS界面化配置方法如下：

通過sql語句創(chuàng)建如下：

USE [master]
GO

CREATE SERVER AUDIT [Audit-log]
TO APPLICATION_LOG 
WITH (
QUEUE_DELAY = 1000, 
ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit-log] WITH (STATE = ON)  --創(chuàng)建時(shí)啟用
GO

上面創(chuàng)建了兩個(gè)審核，Audit-logfile 和 Audit-log（審核目標(biāo)位置不同），介紹了目標(biāo)位置為不同方式的兩種創(chuàng)建方法，實(shí)際運(yùn)用中可選擇其中一種即可。

2. 創(chuàng)建審核規(guī)范并啟用

審核規(guī)范可以是服務(wù)器級別的審核規(guī)范，也可以是數(shù)據(jù)庫級別的審核規(guī)范，二者創(chuàng)建一種即可。

如果是服務(wù)器級別的審核規(guī)范，則審核規(guī)范對所有數(shù)據(jù)庫都生效，如果是數(shù)據(jù)庫級別的審核規(guī)范，則需要對每一個(gè)具體的數(shù)據(jù)庫定義審核規(guī)范。

下面分別介紹兩種審核規(guī)范的創(chuàng)建方式。

2.1 創(chuàng)建服務(wù)器審核規(guī)范

“服務(wù)器審核規(guī)范” 對象屬于審核。 您可以為每個(gè)審核創(chuàng)建一個(gè)服務(wù)器審核規(guī)范，因?yàn)樗鼈兌际窃?SQL Server 實(shí)例范圍內(nèi)創(chuàng)建的。

服務(wù)器審核規(guī)范可收集許多由擴(kuò)展事件功能引發(fā)的服務(wù)器級操作組。 您可以在服務(wù)器審核規(guī)范中包括“審核操作組” 。 審核操作組是預(yù)定義的操作組，它們是 數(shù)據(jù)庫引擎中發(fā)生的原子事件。 這些操作將發(fā)送到審核，審核將它們記錄到目標(biāo)中。

如為上一步創(chuàng)建的審核（Audit-logfile） 創(chuàng)建一個(gè)服務(wù)器審核規(guī)范，下面分別介紹兩種方式：通過SSMS界面配置和通過SQL語句創(chuàng)建。

（1）通過SSMS界面創(chuàng)建服務(wù)器審核規(guī)范
右鍵“安全性”-“服務(wù)器審核規(guī)范”：

點(diǎn)擊“新建服務(wù)器審核規(guī)范”：

如上圖：名稱自定義，如Server-audit，審核對象選擇 Audit-logfile,審核操作類型可選擇需要審計(jì)的類型：

部分審計(jì)類型含義如下：

• AUDIT_CHANGE_GROUP：當(dāng)Audit被創(chuàng)建、修改和刪除時(shí)，觸發(fā)該事件
• BATCH_COMPLETED_GROUP：當(dāng)任何batch、sp或事務(wù)完成執(zhí)行時(shí)，觸發(fā)該事件，并記錄下執(zhí)行的命令的文本。
• BATCH_STARTED_GROUP：當(dāng)任何batch、sp或事務(wù)開始執(zhí)行時(shí)，觸發(fā)該事件，并記錄下執(zhí)行的命令的文本。
• DATABASE_OBJECT_CHANGE_GROUP：當(dāng)任何數(shù)據(jù)庫中的對象執(zhí)行create、alter或drop命令時(shí)，觸發(fā)該事件，可能會產(chǎn)生巨大的審計(jì)日志
• FAILED_LOGIN_GROUP：當(dāng)一個(gè)principal嘗試登錄SQL Server，并失敗時(shí)，觸發(fā)該事件
• FAILED_DATABASE_AUTHENTICATION_GROUP：當(dāng)一個(gè)Principal嘗試登錄數(shù)據(jù)庫，并失敗時(shí)，觸發(fā)該事件
• SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP：當(dāng)一個(gè)principal成功登錄SQL Server中的數(shù)據(jù)庫時(shí)，觸發(fā)該事件
• SUCCESSFUL_LOGIN_GROUP：當(dāng)一個(gè)principal成功登錄SQL Server時(shí)，觸發(fā)該事件

（2）通過SQL語句創(chuàng)建，執(zhí)行sql語句如下：

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Server-audit]
FOR SERVER AUDIT [Audit-logfile]
ADD (FAILED_LOGIN_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON)
GO

執(zhí)行成功后，會在左側(cè)“安全性”-“服務(wù)器審核規(guī)范”下面生成一條配置，名為 Server-audit，如下圖

2.2 創(chuàng)建數(shù)據(jù)庫審核規(guī)范

“數(shù)據(jù)庫審核規(guī)范” 對象也屬于 SQL Server 審核。 針對每個(gè)審核，您可以為每個(gè) SQL Server 數(shù)據(jù)庫創(chuàng)建一個(gè)數(shù)據(jù)庫審核規(guī)范。

數(shù)據(jù)庫審核規(guī)范可收集由擴(kuò)展事件功能引發(fā)的數(shù)據(jù)庫級審核操作。 你可以向數(shù)據(jù)庫審核規(guī)范添加審核操作組或?qū)徍耸录?審核事件 是可以由 SQL Server 引擎審核的原子操作。 “審核操作組”是預(yù)定義的操作組。 它們都位于 SQL Server 數(shù)據(jù)庫作用域。 這些操作將發(fā)送到審核，審核將它們記錄到目標(biāo)中。

下面分別介紹兩種方式：通過SSMS界面創(chuàng)建和通過執(zhí)行SQL語句創(chuàng)建數(shù)據(jù)庫審核規(guī)范，針對測試環(huán)境數(shù)據(jù)庫mydb創(chuàng)建數(shù)據(jù)庫審核規(guī)范。

（1）通過SSMS界面創(chuàng)建數(shù)據(jù)庫審核規(guī)范

選擇“數(shù)據(jù)庫”-mydb-“安全性”-“數(shù)據(jù)庫審核規(guī)范”，右鍵如下圖：

點(diǎn)擊“新建數(shù)據(jù)庫審核規(guī)范”，如下圖：

如上圖：名稱自定義，如Db-Audit，審核對象選擇 Audit-logfile,審核操作類型可選擇需要審計(jì)的事件操作類型等：

（2）通過執(zhí)行SQL語句創(chuàng)建數(shù)據(jù)庫審核規(guī)范

use [mydb]
GO

CREATE DATABASE AUDIT SPECIFICATION [Db-Audit]
FOR SERVER AUDIT [Audit-logfile]
ADD (DELETE,UPDATE,SELECT,INSERT ON DATABASE::[mydb] BY [public])
WITH (STATE = ON)
GO

執(zhí)行如下圖：

執(zhí)行成功后，會在左側(cè) “數(shù)據(jù)庫”-mydb-“安全性”-“數(shù)據(jù)庫審核規(guī)范” 下面生成一個(gè)名為 Db-Audit 的配置項(xiàng)。

數(shù)據(jù)庫級別的審核規(guī)范，審計(jì)動作組部分含義如下：

• BATCH_COMPLETED_GROUP
• BATCH_STARTED_GROUP
• DATABASE_OBJECT_CHANGE_GROUP：當(dāng)對數(shù)據(jù)庫對象執(zhí)行CREATE、ALTER、 或 DROP命令時(shí)，觸發(fā)該事件
• DATABASE_OBJECT_PERMISSION_CHANGE_GROUP：當(dāng)數(shù)據(jù)庫對象的權(quán)限(執(zhí)行GRANT, REVOKE, or DENY)發(fā)生變化時(shí)，觸發(fā)該事件
• DATABASE_PRINCIPAL_CHANGE_GROUP：當(dāng)數(shù)據(jù)庫中的user，role等發(fā)生變化時(shí)，觸發(fā)該事件
• SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP：當(dāng)一個(gè)Schema中的對象的權(quán)限(執(zhí)行GRANT, REVOKE, or DENY)發(fā)生變化時(shí)，觸發(fā)該事件
• SCHEMA_OBJECT_CHANGE_GROUP：當(dāng)對Schema中的對象執(zhí)行CREATE、ALTER、 或 DROP命令時(shí)，觸發(fā)該事件
• SCHEMA_OBJECT_ACCESS_GROUP：當(dāng)schema中的對象的權(quán)限發(fā)生變化時(shí)，觸發(fā)該事件

數(shù)據(jù)庫級別的審核規(guī)范，單個(gè)審計(jì)動作如下：

數(shù)據(jù)庫級別的審計(jì)動作(Audit Action)，分別在執(zhí)行查詢、修改、插入、刪除、執(zhí)行、等命令時(shí)觸發(fā)

• SELECT ：發(fā)出 SELECT 語句時(shí)將引發(fā)此事件。
• UPDATE： 發(fā)出 UPDATE 語句時(shí)將引發(fā)此事件。
• INSERT： 發(fā)出 INSERT 語句時(shí)將引發(fā)此事件。
• DELETE： 發(fā)出 DELETE 語句時(shí)將引發(fā)此事件。
• EXECUTE： 發(fā)出 EXECUTE 語句時(shí)將引發(fā)此事件。
• RECEIVE： 發(fā)出 RECEIVE 語句時(shí)將引發(fā)此事件。
• REFERENCES： 檢查 REFERENCES 權(quán)限時(shí)將引發(fā)此事件。

3. 查看審核事件日志

3.1 通過SSMS日志文件查看器查看

右鍵 “安全性”-“審核”-“Audit-logfile”, 選擇 “查看審核日志”：

日志如下圖所示：

3.2 針對目標(biāo)位置為文件的審核日志

通過SQL查詢：

select * from 
sys.fn_get_audit_file('C:\dbaudit\*',default,default)

如下圖：

總結(jié) 

到此這篇關(guān)于SQLServer審計(jì)功能配置的文章就介紹到這了,更多相關(guān)SQLServer審計(jì)功能配置內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論