SQL Server 審計（Audit）

悅光陰發表於2021-04-25
SQLServer

審計（Audit）用於追蹤和記錄SQL Server例項，或者單個資料庫中發生的事件（Event），審計運作的機制是通過捕獲事件（Event），把事件包含的資訊寫入到事件日誌（Event Log）或審計檔案（Audit File）中，為review提供最真實詳細的資料。

審計主要包含伺服器審計物件（Server Audit，簡稱審計物件）、伺服器級別的審計規範（Server Audit Specification）、資料庫級別的審計規範（Database Audit Specification）和目標（Target）。

  • 審計物件是在伺服器級別上建立的物件，必須啟用，用於指定審計資料的儲存方式和儲存路徑，並提供工具檢視審計資料。
  • 伺服器級別的審計規範用於記錄伺服器級別的事件（Event），
  • 資料庫級別的審計規範用於記錄資料庫級別的事件，
  • Target是指用於儲存Audit資料的媒介，可以是File、Windows Security Event Log 或 Windows Application Event Log，常用的Target是File。

SQL Server使用 Extended Events來幫助建立審計，也就是說，審計是在擴充套件事件的基礎上設計的功能，專門用於稽核資料庫的安全。為了啟用審計，首先需要建立一個SQL Server 例項級的審計物件，然後建立從屬於它的“伺服器審計規範”或“資料庫審計規範”，審計輸出的結果資料可以儲存到審計檔案（File）、安全日誌（Security Log）和應用程式日誌（Application Log）中。

一，審計物件的構成

構成審計的成分主要有4大類：審計物件、伺服器級別的審計規範、資料庫級別的審計規範和目標（Target）。

  • 審計物件可以包含一個或多個審計規範，用於指定輸出結果儲存的路徑。審計物件建立時預設的狀態是disable。
  • 伺服器級別的審計規範，從屬於審計物件，它包含伺服器級別的審計動作組（Audit Action Group）或單個Audit Action，這些動作組或動作由Extended Events觸發。
  • 資料庫級別的審計規範，也從屬於審計物件，它包含資料庫級別的審計動作組（Audit Action Group）或單個Audit Action，這些動作組或動作由Extended Events觸發。
  • Target（也稱作Audit Destination）用於儲存審計的結果，Target可以是一個file，Windows Security Event log 或者Windows application event log.

Action Group 是預定義的一組Action，每一個Action（也稱作Action Event）都是一個原子事件，因此，Action Group也是由Action Event構成的組合。當Event（也稱作Action）發生時，Event被髮送到審計物件（audit）中，SQL Server把資料記錄到Target中。關於審計的動作組和動作的詳細資訊，請閱讀官方文件：SQL Server Audit Action Groups and Actions

二，審計物件

審計監控SQL Server例項，從伺服器級別或資料庫級別的Action或Action Group中收集這些Action發生時產生的資料。

當定義一個審計物件時，需要指定儲存審計輸出結果的路徑，即audit的Target（審計的目的，audit destination）。新建的Audit預設處於disable狀態，不能自動追蹤和記錄（審計）任何audit action。當Audit啟用後，audit destination會接收從審計輸出的資料。

建立審計物件的語法：

CREATE SERVER AUDIT audit_name  
{  
    TO { [ FILE (<file_options> [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR }  
    [ WITH ( <audit_options> [ , ...n ] ) ]   
    [ WHERE <predicate_expression> ]  
}  [ ; ]  
  
<file_options>::=  
{  
        FILEPATH = 'os_file_path'  
    [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]  
    [ , { MAX_ROLLOVER_FILES = { integer | UNLIMITED } } | { MAX_FILES = integer } ]  
    [ , RESERVE_DISK_SPACE = { ON | OFF } ]   
}  
  
<audit_options>::=  
{  
    [   QUEUE_DELAY = integer ]  
    [ , ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION } ]  
    [ , AUDIT_GUID = uniqueidentifier ]  
}

主要引數註釋：

  • FILEPATH ='os_file_path'：指定審計日誌儲存的目錄，目錄中包含的檔案是基於審計名稱和審計GUID產生的。
  • MAXSIZE = { max_size }：指定一個審計檔案的最大容量，
  • MAX_ROLLOVER_FILES ={ integer | UNLIMITED }：指定審計包含的審計檔案的最大數量，當檔案數量達到限制的數量時，SQL Server會自動刪除建立時間最早的審計檔案。
  • MAX_FILES =integer：是指審計只能最多包含的審計檔案的數量，當檔案數量達到限制的數量時，不會刪除最老的審計檔案，當產生更多的審計資料時，SQL Server會丟擲錯誤，並失敗。
  • RESERVE_DISK_SPACE = { ON | OFF }：在Disk上為審計預先分配MAXSIZE的空間
  • QUEUE_DELAY =integer：單位是毫秒（千分之一秒），用於指定一個Audit Action從發生到被強制處理可以經過的時間間隔，預設值是1000，即1秒，這也是可以甚至的最小查詢延遲。值為0表示同步傳送，
  • ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }：當Target不能繼續寫入審計日誌時，審計物件的動作：
    • 　　CONTINUE，是指不保留審計記錄，審計繼續嘗試記錄事件，如果解決了故障情況，則繼續進行審計。
    • 　　SHUTDOWN：如果SQL Server由於任何原因不能寫入審計記錄，那麼強制關閉SQL Server例項
    • 　　FAIL_OPERATION：如果資料庫操作導致審計事件的發生，那麼執行的資料庫操作將失敗；不會導致審計事件發生的操作，將繼續執行。

注意：審計物件（Server Audit）的create、alter、或 drop，需要ALTER ANY SERVER AUDIT或CONTROL SERVER的許可權。當儲存Audit Log時，為了保證審計資料的安全，需要限制無關人員的許可權，禁止無關人員訪問審計檔案的儲存目錄。

舉個例子，建立審計物件：

CREATE SERVER AUDIT [AuditMonitorQuery]
TO FILE 
(    FILEPATH = N'G:\AuditFiles\MonitorQuery\'
    ,MAXSIZE = 1 GB
    ,MAX_ROLLOVER_FILES = 128
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'xxx'
)
ALTER SERVER AUDIT [AuditMonitorQuery] WITH (STATE = ON)
GO

三，伺服器級別的審計規範

伺服器級別的審計規範屬於Server Audit，伺服器界別的審計規範從伺服器級別的action group中收集資料。

CREATE SERVER AUDIT SPECIFICATION audit_specification_name  
FOR SERVER AUDIT audit_name  
{  
    { ADD ( { audit_action_group_name } )   
    } [, ...n]  
    [ WITH ( STATE = { ON | OFF } ) ]  
}

注意：擁有ALTER ANY SERVER AUDIT許可權的使用者可以建立伺服器級別的審計規範，擁有CONTROL SERVER許可權或sysadmin可以檢視審計資料。

舉個例子：建立伺服器級別的審計規範

CREATE SERVER AUDIT SPECIFICATION HIPAA_Audit_Specification  
FOR SERVER AUDIT HIPAA_Audit  
    ADD (FAILED_LOGIN_GROUP)  
    WITH (STATE=ON);

四，資料庫級別的審計規範

資料庫級別的審計規範屬於一個Server Audit， 資料庫級別的審計規範從資料庫級別的action group，或者audit event中收集資料。action group是預先定義的一組audit event，這些action產生的資訊都會被髮送到Audit，被儲存到Target中。

CREATE DATABASE AUDIT SPECIFICATION audit_specification_name  
{  
    FOR SERVER AUDIT audit_name   
        [ { ADD ( { <audit_action_specification> | audit_action_group_name } ) } [, ...n] ]  
    [ WITH ( STATE = { ON | OFF } ) ]  
} [ ; ]  

<audit_action_specification>::= action [ ,...n ] ON [ class :: ] securable BY principal [ ,...n ]

重要引數註釋：

  • action：資料庫級別的action的名，
  • class：securable的class name
  • securable：資料庫中審計作用（應用audit action 或audit action group）的table、view 等安全物件（Securable Object）。
  • principal：資料庫中審計作用（應用audit action 或audit action group）的principal，如果審計所有的principal，請使用public。

注意：擁有 ALTER ANY DATABASE AUDIT 許可權的使用者可以建立資料庫級別的審計規範，當資料庫級別的審計規範被建立之後，可以被CONTROL SERVER和sysadmin產看。

舉個例子：

CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables  
FOR SERVER AUDIT HIPAA_Audit  
ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY public )  
WITH (STATE = ON) ;

五，Target

審計輸出的結果會被髮送到Target，實際上，Target的作用是儲存審計資料，可以是一個檔案，Windows Security event log或Windows Application event log。

任何授權的使用者都可以讀寫Windows Application event log，它的安全級別比Windows Security event log要低。通常情況下，審計資料都會儲存到單獨的檔案中。

當把審計資訊儲存到一個file，為了保證審計資料的安全，可以通過以下方式限制對檔案位置的訪問：

  • SQL Server Service Account必須同時具有讀寫許可權
  • Audit 管理員需要有審計檔案的讀寫許可權
  • Audit Reader：具有讀取審計檔案的許可權

因為資料庫引擎可以訪問審計檔案，擁有CONTROL SERVER許可權的SQL Server Login都可以使用資料庫引擎來訪問審計檔案。為了記錄正在讀取審計檔案的使用者，需要定義一個審計，追蹤和記錄使用者對函式master.sys.fn_get_audit_file的呼叫記錄，這個審計記錄了擁有CONTROL SERVER許可權的Login通過SQL Server訪問審計檔案的記錄。

如果審計管理員（Audit Admin）把檔案複製到其他位置（處於存檔等目的），那麼需要將新位置上的許可權降級為：

  • Audit Administrator - Read / Write
  • Audit Reader - Read

注意：為了保護審計檔案，應該對審計檔案進行加密，對儲存審計檔案的目錄使用Windows BitLocker Drive Encryption或Windows Encrypting File System，從而防止未經授權的訪問。

 

 

參考文件：

SQL Server Audit (Database Engine)

相關文章