一. SQL Server審計功能介紹

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

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

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

1. 創(chuàng)建審核并啟用。
2. 創(chuàng)建映射到審核的服務器審核規(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 審核” 對象收集單個服務器實例或數(shù)據(jù)庫級操作和操作組以進行監(jiān)視。 這種審核處于 SQL Server 實例級別。 每個 SQL Server 實例可以具有多個審核。

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

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

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

新建審核界面如下圖：

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

對應的通過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語句后，會在左側“安全性”-“審核”下面生成一個名為“Audit-logfile”的配置：

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

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

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

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

（2）創(chuàng)建一個審核，名為 Audit-log，目標位置為windows應用程序日志（審核結果寫在windows應用程序事件中），通過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)建時啟用
GO

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

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

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

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

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

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

“服務器審核規(guī)范” 對象屬于審核。 您可以為每個審核創(chuàng)建一個服務器審核規(guī)范，因為它們都是在 SQL Server 實例范圍內創(chuàng)建的。

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

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

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

點擊“新建服務器審核規(guī)范”：

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

部分審計類型含義如下：

• AUDIT_CHANGE_GROUP：當Audit被創(chuàng)建、修改和刪除時，觸發(fā)該事件
• BATCH_COMPLETED_GROUP：當任何batch、sp或事務完成執(zhí)行時，觸發(fā)該事件，并記錄下執(zhí)行的命令的文本。
• BATCH_STARTED_GROUP：當任何batch、sp或事務開始執(zhí)行時，觸發(fā)該事件，并記錄下執(zhí)行的命令的文本。
• DATABASE_OBJECT_CHANGE_GROUP：當任何數(shù)據(jù)庫中的對象執(zhí)行create、alter或drop命令時，觸發(fā)該事件，可能會產(chǎn)生巨大的審計日志
• FAILED_LOGIN_GROUP：當一個principal嘗試登錄SQL Server，并失敗時，觸發(fā)該事件
• FAILED_DATABASE_AUTHENTICATION_GROUP：當一個Principal嘗試登錄數(shù)據(jù)庫，并失敗時，觸發(fā)該事件
• SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP：當一個principal成功登錄SQL Server中的數(shù)據(jù)庫時，觸發(fā)該事件
• SUCCESSFUL_LOGIN_GROUP：當一個principal成功登錄SQL Server時，觸發(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í)行成功后，會在左側“安全性”-“服務器審核規(guī)范”下面生成一條配置，名為 Server-audit，如下圖

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

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

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

下面分別介紹兩種方式：通過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ī)范”，右鍵如下圖：

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

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

（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í)行成功后，會在左側 “數(shù)據(jù)庫”-mydb-“安全性”-“數(shù)據(jù)庫審核規(guī)范” 下面生成一個名為 Db-Audit 的配置項。

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

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

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

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

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

3. 查看審核事件日志

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

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

日志如下圖所示：

3.2 針對目標位置為文件的審核日志

通過SQL查詢：

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

如下圖：

總結 

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

最新評論