前言
在前一篇文章中，介紹了伺服器稽核新增強的功能、稽核記錄失敗時的處理、設定最大換用檔案與最大檔案數目、認識稽核檔案的存取權、將SQL Server Audit的目標記錄檔案寫入到網路共用資料夾等主題。
在本次文章中，將繼續討論調整稽核記錄失敗時的處理、調整稽核檔案數目上限、認識對稽核記錄檔案的篩選以及認識使用者定義稽核群組等主題。
任務四：調整稽核記錄失敗時的處理
選擇：「繼續」模式
步驟 1. 在擔任網路共用伺服器上，關閉先前分享的資料夾。
步驟 2. 回到資料庫伺服器上，使用「物件總管」，建立、修改與刪除資料庫，或是執行先前的範例程式碼3。
因為事先關閉了網路分享資料夾，這將導致觸發的事件無法寫入到稽核記錄檔案內。但先前在建立「伺服器稽核」時，在「於稽核記錄失敗時」區域，預設所組態的是「繼續」。因此，系統仍是可以正常執行，但是在Windows事件檢視器上將可以觀察到以下的錯誤訊息，請參考下圖所示：
-- Windows事件檢視器的應用程式記錄
SQL Server Audit 無法寫入檔案 '\\OP1\Audit_Logs\稽核資料庫的異動_UNC_F378D9C3-4CD7-41E9-B37C-D483C62935FA.sqlaudit'。
圖10：檢視Windows事件檢視器的應用程式記錄，事件識別碼：33202
在SQL Server錯誤記錄檔上將可以觀察到以下的錯誤訊息，請參考下圖所示：
-- SQL Server錯誤記錄檔
訊息
Error: 33202, Severity: 17, State: 1.
訊息
SQL Server Audit could not write to file '\\OP1\Audit_Logs\稽核資料庫的異動_UNC_F378D9C3-4CD7-41E9-B37C-D483C62935FA.sqlaudit'.
圖11：檢視SQL Server錯誤記錄檔，錯誤識別碼：33202
或是，執行以下的範例程式碼來查詢「伺服器稽核」的運行狀態，並請參考下圖5所示：
-- 01_使用sys.dm_server_audit_status動態管理檢視，檢視「伺服器稽核」SQL Server Audit的狀態
SELECT audit_id N'稽核的識別碼', name N'稽核的名稱', status_desc N'伺服器稽核狀態',
status_time N'上一次狀態變更的時間戳記', audit_file_size N'稽核檔案大小(KB)',
audit_file_path N'稽核檔案目標的完整路徑名稱'
FROM sys.dm_server_audit_status;
GO
範例程式碼5：使用fn_get_audit_file檢視稽核檔案的內容
圖12：伺服器稽核的狀態是：RUNTIIME_FAILED
選擇：「失敗作業」模式
步驟 3. 在擔任網路共用伺服器上，再度分享先前的資料夾。
步驟 4. 回到資料庫伺服器上，使用「物件總管」，在「伺服器稽核」物件：稽核資料庫的異動_UNC上，先停用此「伺服器稽核」，在「於稽核記錄失敗」區域，設定為「失敗作業」，再重新啟用此「伺服器稽核」。
或是，執行以下的範例程式碼：
-- 01_停用此「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF);
GO
-- 02_在「於稽核記錄失敗」參數，設定為「失敗作業」
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH( ON_FAILURE = FAIL_OPERATION)
GO
-- 03_啟用此「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = ON);
GO
範例程式碼6：變更伺服器稽核的屬性為：失敗作業
若是沒有先停用此「伺服器稽核」，就試圖去變更其屬性，將遭遇到以下的錯誤訊息，請參考下圖所示：
訊息 33071，層級 16，狀態 1，行 2
這個命令要求必須停用 稽核。請停用 稽核 後，再重新執行這個命令。
圖13：尚未停用伺服器稽核，是無法變更伺服器稽核的屬性
在本次實作練習中，若是沒有先啟用遠端的網路分享資料夾功能，導致無法存取稽核目標檔案，將遭遇到以下的錯誤訊息，請參考下圖所示：
訊息 33222，層級 16，狀態 1，行 1
稽核 '稽核資料庫的異動_UNC' 無法進行 start。
如需詳細資訊，請參閱 SQL Server 錯誤記錄檔。
您還可以查詢 sys.dm_os_ring_buffers，其中 ring_buffer_type = 'RING_BUFFER_XE_LOG'。
圖14：若是無法存取稽核目標檔案，也是無法啟用此伺服器稽核，錯誤識別碼：33222
在SQL Server錯誤記錄檔上將可以觀察到以下的錯誤訊息，請參考下圖所示：
錯誤: 33206，嚴重性: 17，狀態: 1。
SQL Server Audit failed to create the audit file '\\OP1\Audit_Logs\稽核資料庫的異動_UNC_F0F90B89-9EFC-4593-A07F-6B6FA9CD65BE.sqlaudit'.
Make sure that the disk is not full and that the SQL service account has the required permissions to create and write to the file.
圖15：檢視SQL Server 錯誤記錄檔，無法建立稽核目標檔案，錯誤識別碼：33206
步驟 5. 使用「物件總管」，建立、修改與刪除資料庫，或是，執行先前的範例程式碼3。
步驟 6. 使用「物件總管」，檢視稽核記錄，或是，執行範例程式碼4來檢視稽核記錄。
步驟 7. 在擔任網路共用伺服器上，關閉先前分享的資料夾。
步驟 8. 回到資料庫伺服器上，使用「物件總管」，建立、修改與刪除資料庫，或是執行先前的範例程式碼3。
因為事先關閉了網路分享資料夾，這將導致觸發的事件無法寫入到稽核記錄檔案內。但先前在建立「伺服器稽核」時，在「於稽核記錄失敗時」區域，預設所組態的是「失敗作業」。因此，這將導致建立、修改與刪除資料庫等作業都會失敗，無法執行。
訊息 15247，層級 16，狀態 15，行 1
使用者沒有執行此動作的權限。
圖16：建立資料庫失敗，使用者沒有執行此動作的權限
在SQL Server錯誤記錄檔上將可以觀察到以下的錯誤訊息，請參考下圖所示：
錯誤: 33239，嚴重性: 16，狀態: 1。
An error occurred while auditing this operation. Fix the error in the audit and then retry this operation.
圖17：SQL Server錯誤記錄檔：無法存取稽核目標檔案，導致「失敗作業」，錯誤識別碼：33239
Windows事件檢視器上將可以觀察到以下的錯誤訊息，請參考下圖所示：
稽核此作業時發生錯誤。請修正核此中的錯誤，然後重試此作業。
圖18：Windows事件檢視器：無法存取稽核目標檔案，導致「失敗作業」，錯誤識別碼：33239
選擇：「關閉伺服器」模式
步驟 9. 在擔任網路共用伺服器上，再度分享先前的資料夾。
步驟10. 回到資料庫伺服器上，使用「物件總管」，在「伺服器稽核」：稽核資料庫的異動_UNC上，先停用此「伺服器稽核」，在「於稽核記錄失敗」區域，設定為「關閉伺服器」，再重新啟用此「伺服器稽核」。
或是，執行以下的範例程式碼：
-- 01_停用此「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF);
GO
-- 02_在「於稽核記錄失敗」參數，設定為「關閉伺服器」
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH( ON_FAILURE = SHUTDOWN)
GO
-- 03_啟用此「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = ON);
GO
範例程式碼7：變更伺服器稽核的屬性為：關閉伺服器
步驟11. 使用「物件總管」，建立、修改與刪除資料庫，或是執行先前的範例程式碼3。
步驟12. 使用「物件總管」，檢視稽核記錄，或是，執行範例程式碼4來檢視稽核記錄。
步驟13. 在擔任網路共用伺服器上，關閉先前分享的資料夾。
步驟14. 回到資料庫伺服器上，使用「物件總管」，建立、修改與刪除資料庫，或是執行先前的範例程式碼3。
因為事先關閉了網路分享資料夾，這將導致觸發的事件無法寫入到稽核記錄檔案內。但先前在建立「伺服器稽核」時，在「於稽核記錄失敗時」區域，預設所組態的是「關閉伺服器」。因此，這將導致此執行個體自動關機，停止服務。在Windows事件檢視器上將可以觀察到以下的錯誤訊息，請參考下圖所示：
事件識別碼：33219
伺服器已停止，因為 SQL Server Audit '稽核資料庫的異動_UNC' 設定為於失敗時關閉。
如果要對此問題進行疑難排解，請在伺服器啟動時使用 -m 旗標 (單一使用者模式)，以略過稽核所產生的關機。
--
事件識別碼：19019
MSSQLSERVER 服務非預期地結束.
圖19：Windows事件檢視器：SQL Server執行個體已經被非預期地結束
在圖19中，此執行個體已經被強迫關機，停止服務，你之後也無法啟動此執行個體。若沒有修復讓其能夠存取稽核目標檔案，那就只能使用-m旗標(單一使用者模式)方式來啟動執行個體，修改「伺服器稽核」的屬性。
步驟15. 在擔任網路共用伺服器上，再度分享先前的資料夾。
步驟16. 重新啟動此執行個體。
步驟17. 檢視SQL Server錯誤記錄檔將可以觀察到以下的錯誤訊息，請參考下圖所示：
The server was stopped because SQL Server Audit '稽核資料庫的異動_UNC' is configured to shut down on failure.
To troubleshoot this issue, use the -m flag (Single User Mode) to bypass Audit-generated shutdowns when the server is starting.
圖20：SQL Server錯誤記錄檔：因為伺服器稽核失敗，執行個體也被關閉
任務五：調整稽核檔案數目上限
步驟 1. 在擔任網路共用伺服器上，先將分享資料夾內稽核目標檔案，除了目前正在使用的無法刪除外，其餘的請全部都刪除掉。
步驟 2. 回到資料庫伺服器上，使用「物件總管」，在「伺服器稽核」：稽核資料庫的異動_UNC上，先停用此「伺服器稽核」。設定此「伺服器稽核」以下的屬性：
- 在「於稽核記錄失敗」區域，設定為「失敗作業」。
- 在「稽核檔案數目上限」區域，點選：「最大檔案數目」，在「檔案數目」方塊，輸入：2。
- 再重新啟用此稽核。或是，執行以下的範例程式碼。請參考下圖所示：
-- 01_停用此「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF);
GO
-- 02_在「於稽核記錄失敗」參數，設定為「失敗作業」
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH( ON_FAILURE = FAIL_OPERATION)
GO
-- 03_設定「最大檔案數目」的數量為：2
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
TO FILE (MAX_FILES = 2)
GO
-- 04_啟用此「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = ON);
GO
範例程式碼8：變更伺服器稽核的屬性：稽核檔案數目上限、失敗作業
圖21：設定稽核檔案數目上限、於稽核記錄失敗時的組態
步驟 2. 使用「物件總管」，在「稽核」物件：稽核資料庫的異動_UNC上，先停用此「伺服器稽核」，在啟用此「伺服器稽核」，前述的動作，請重複兩次。或是，執行以下的範例程式碼：
-- 01_停用此「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF);
GO
-- 02_啟用此「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = ON);
GO
範例程式碼9：對伺服器稽核先停用後再度啟用
應該會遭遇到啟用「伺服器稽核」失敗的錯誤訊息，請參考圖14與15，以及下圖所示。
訊息 33222，層級 16，狀態 1，行 1
稽核 '稽核資料庫的異動_UNC' 無法進行 start。如需詳細資訊，請參閱 SQL Server 錯誤記錄檔。
您還可以查詢 sys.dm_os_ring_buffers，其中 ring_buffer_type = 'RING_BUFFER_XE_LOG'。
圖22：啟用伺服器稽核卻失敗：超過最大檔案數目
在圖22中，我們在「伺服器稽核」上，在「稽核檔案數目上限」區域，特別設定其「最大檔案數目」為：2。
再多次的先停用此「伺服器稽核」與啟用此「伺服器稽核」。因為每次啟用「伺服器稽核」，系統就會自動產生一份新的稽核目標檔案，如此作法，就會超過當初在稽核檔案數目上所設定的上限值，導致無法啟用此「伺服器稽核」。
步驟 3. 使用「物件總管」，先停用後刪除此「伺服器稽核」以及其「伺服器稽核規格」。或是，執行以下的範例程式碼：
-- 01_停用以及刪除「伺服器稽核」：稽核資料庫的異動_UNC
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核資料庫的異動_UNC')
BEGIN
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF)
DROP SERVER AUDIT [稽核資料庫的異動_UNC]
END
GO
-- 02_停用以及刪除「伺服器稽核規格」：監視每當發生建立、改變或卸除任何資料庫的事件
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視每當發生建立、改變或卸除任何資料庫的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視每當發生建立、改變或卸除任何資料庫的事件]
WITH (STATE = OFF)
DROP SERVER AUDIT SPECIFICATION [監視每當發生建立、改變或卸除任何資料庫的事件]
END
GO
範例程式碼10：對伺服器稽核與伺服器稽核規格分別執行：停用及刪除
實作練習二：認識對稽核記錄檔案的篩選
準備工作
請事先在本機伺服器上建立以下的資料夾：
- C:\myAdmin\Device。
- C:\myAdmin\Audit_Logs。
任務一：建立伺服器稽核與加入WHERE子句
步驟 1. 使用SSMS管理工具，建立「伺服器稽核」，在「建立稽核」視窗，設定以下的屬性：
- 「伺服器稽核」命名為：稽核備份或還原資料庫作業。
- 點選左邊窗格的「篩選」，右邊窗格，輸入WHERE子句：database_name = 'Northwind'。
請參考下圖所示，或是，執行以下的範例程式碼：
圖23：在伺服器稽核上，設定篩選條件式
在上圖23中，若要篩選稽核記錄的內容，能夠使用的資料行名稱是英文名稱，可以藉由執行sys.fn_get_audit_file系統預存函數來查詢可以使用哪些資料行的名稱。
-- 01_建立「伺服器稽核」：稽核備份或還原資料庫作業，WHERE子句篩選式：Northwind
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核備份或還原資料庫作業')
BEGIN
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = OFF);
DROP SERVER AUDIT [稽核備份或還原資料庫作業]
END
GO
CREATE SERVER AUDIT [稽核備份或還原資料庫作業]
TO FILE
( FILEPATH = N'C:\myAdmin\Audit_Logs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF )
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE )
WHERE database_name = 'Northwind' -- WHERE子句篩選式：Northwind
GO
-- 02_啟用此「伺服器稽核」：稽核備份或還原資料庫作業
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = ON);
GO
-- 03_使用sys.dm_server_audit_status動態管理檢視，檢視「伺服器稽核」SQL Server Audit的狀態
SELECT audit_id N'稽核的識別碼', name N'稽核的名稱', status_desc N'伺服器稽核狀態',
status_time N'上一次狀態變更的時間戳記', audit_file_size N'稽核檔案大小(KB)',
audit_file_path N'稽核檔案目標的完整路徑名稱',*
FROM sys.dm_server_audit_status;
GO
-- 04_使用sys.server_audits目錄檢視，檢視「伺服器稽核」物件的目前狀態，包含：WHERE子句篩選式
SELECT name N'稽核', type_desc N'稽核類型', on_failure_desc N'寫入動作失敗時',
predicate N'述詞運算式', is_state_enabled N'啟用', queue_delay N'寫入磁碟前等候時間(毫秒)',
create_date N'建立日期時間', modify_date '修改日期時間'
FROM sys.server_audits
GO
範例程式碼11：建立伺服器稽核：稽核備份或還原作業
圖24：檢視有設定WHERE子句篩選式的伺服器稽核
任務二：建立伺服器稽核規格
步驟 1. 使用「物件總管」，建立「伺服器稽核規格」，在「建立伺服器稽核規格」視窗，輸入以下的參數：
- 將「伺服器稽核規格」命名為：監視每當執行備份或是還原資料庫的事件。
- 在「稽核動作群組類型」部分，選擇：BACKUP_RESTORE_GROUP。
在伺服器層級的稽核動作群組上，使用的是：BACKUP_RESTORE_GROUP，這是指每當發出執行備份或是還原資料庫命令時，就會引發這個事件。
或是，執行以下的範例程式碼：
-- 01_建立「伺服器稽核規格」：監視每當執行備份或是還原資料庫的事件
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視每當執行備份或是還原資料庫的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
END
GO
CREATE SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
FOR SERVER AUDIT [稽核備份或還原資料庫作業]
ADD (BACKUP_RESTORE_GROUP)
GO
-- 02_啟用此「伺服器稽核規格」：監視每當執行備份或是還原資料庫的事件
ALTER SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
WITH (STATE = ON);
GO
-- 03_檢視「伺服器稽核規格」物件
SELECT name N'伺服器稽核規格', is_state_enabled N'是否已啟用',
create_date N'建立日期時間', modify_date N'修改日期時間'
FROM sys.server_audit_specifications
GO
範例程式碼12：建立與啟用伺服器稽核規格：監視每當執行備份或是還原資料庫的事件
任務三：測試備份資料庫與檢視稽核記錄
步驟 1. 使用SSMS管理工具，對pubs、Northwind、master以及msdb等資料庫執行備份作業。或是，執行以下的範例程式碼：
-- 備份資料庫：pubs、Northwind、master以及msdb
USE master
GO
BACKUP DATABASE [pubs]
TO DISK = N'C:\myAdmin\Device\pubs.bak'
WITH FORMAT, NAME = N'pubs-完整 資料庫 備份', COMPRESSION
GO
BACKUP DATABASE [Northwind]
TO DISK = N'C:\myAdmin\Device\Northwind.bak'
WITH FORMAT, NAME = N'Northwind-完整 資料庫 備份', COMPRESSION
GO
BACKUP DATABASE [master]
TO DISK = N'C:\myAdmin\Device\master.bak'
WITH FORMAT, NAME = N'master-完整 資料庫 備份', COMPRESSION
GO
BACKUP DATABASE [msdb]
TO DISK = N'C:\myAdmin\Device\msdb.bak'
WITH FORMAT, NAME = N'msdb-完整 資料庫 備份', COMPRESSION
GO
範例程式碼13：備份資料庫pubs、Northwind、master以及msdb
步驟 2. 再重複執行步驟1的動作。
步驟 3. 檢查此稽核的稽核記錄，應該僅能看到Northwind資料庫的備份稽核記錄，卻無法看到其他資料庫的備份稽核記錄，這是因為我們先前在「伺服器稽核」上加入了WHERE字句僅篩選了此資料庫。
步驟 4. 先停用「伺服器稽核」：稽核備份或還原資料庫作業，修改此「伺服器稽核」，設定以下的屬性：
- 點選左邊窗格的「篩選」，右邊窗格，輸入WHERE子句：database_name = 'Northwind' OR database_name = 'pubs'。請參考下圖所示：
圖25：在伺服器稽核上，修改篩選條件式
完成後，再度啟用此「伺服器稽核」。或是，執行以下的範例程式碼：
-- 01_停用此「伺服器稽核」：稽核備份或還原資料庫作業
USE master
GO
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = OFF);
GO
-- 02_修改WHERE子句篩選式，包含：Northwind與pubs資料庫
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WHERE database_name = 'Northwind' OR database_name = 'pubs'
GO
-- 03_啟用此「伺服器稽核」：稽核備份或還原資料庫作業
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = ON);
GO
範例程式碼14：修改伺服器稽核的WHERE子句篩選式，包含：Northwind與pubs資料庫
在範例程式碼14中，經過測試好像不能使用IN邏輯運算子。
步驟 5. 再重複執行步驟1的動作。檢查此稽核的稽核記錄，已經可以看到Northwind以及pubs資料庫的備份稽核記錄，卻無法看到其他資料庫的備份稽核記錄，這是因為我們先前在「伺服器稽核」上修改了WHERE字句，加入篩選了這兩個資料庫。請參考下圖所示：
圖26：檢視篩選過的稽核記錄
步驟 6. 先停用「伺服器稽核」：稽核備份或還原資料庫作業，移除「篩選」窗格的內容，再度啟用此「伺服器稽核」。或是，執行以下的範例程式碼：
-- 01_停用此「伺服器稽核」：稽核備份或還原資料庫作業
USE master
GO
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = OFF);
GO
-- 02_移除WHERE子句篩選式
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
REMOVE WHERE;
GO
-- 03_啟用此「伺服器稽核」：稽核備份或還原資料庫作業
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = ON);
GO
範例程式碼15：移除WHERE子句篩選式
步驟 7. 再重複執行步驟1的動作。檢查此稽核的稽核記錄。應該可以看記錄了每個資料庫的備份稽核記錄。
步驟 8. 停用以及刪除「伺服器稽核」與「伺服器稽核規格」。或是，執行以下的範例程式碼：
-- 01_停用以及刪除「伺服器稽核」：稽核備份或還原資料庫作業
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核備份或還原資料庫作業')
BEGIN
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = OFF)
DROP SERVER AUDIT [稽核備份或還原資料庫作業]
END
GO
-- 02_停用以及刪除「伺服器稽核規格」：監視每當發生建立、改變或卸除任何資料庫的事件
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視每當執行備份或是還原資料庫的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
WITH (STATE = OFF)
DROP SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
END
GO
範例程式碼16：停用及刪除：對伺服器稽核與伺服器稽核規格分別執行
實作練習三：認識使用者定義稽核群組
在SQL Server 2012版本上新增加了「使用者定義稽核群組」，讓你可以自行在指定的層級上設定所需的稽核群組，所包含了兩個層級，分別是：「伺服器稽核規格」或是「資料庫稽核規格」。
在設計好「使用者定義稽核群組」後，再利用sp_audit_write系統預存程序於指定的情況時執行，此系統預存程序可以將使用者所定義的稽核事件傳遞觸發到此「使用者定義稽核群組」上。
由於是讓使用者可以設計所需的稽核事件，傳送的資料也都可以自訂。所以，在稽核目標記錄上，額外提供了T-SQL堆疊(Stack) 框架資訊以及與動作相關聯的T-SQL堆疊資訊，這是以XML格式顯示，一樣可以使用sys.fn_get_audit_file系統預存函數來查詢。
任務一：建立伺服器稽核
步驟 1. 使用SSMS管理工具，建立「伺服器稽核」，在「建立稽核」視窗，設定以下的屬性：
或是，執行以下的範例程式碼：
-- 01_建立「伺服器稽核」：稽核使用者定義稽核群組
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核使用者定義稽核群組')
BEGIN
ALTER SERVER AUDIT [稽核使用者定義稽核群組]
WITH (STATE = OFF);
DROP SERVER AUDIT [稽核使用者定義稽核群組]
END
GO
CREATE SERVER AUDIT [稽核使用者定義稽核群組]
TO FILE
( FILEPATH = N'C:\myAdmin\Audit_Logs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF )
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE )
GO
-- 02_啟用此「伺服器稽核」：稽核使用者定義稽核群組
ALTER SERVER AUDIT [稽核使用者定義稽核群組]
WITH (STATE = ON);
GO
-- 03_使用sys.dm_server_audit_status動態管理檢視，檢視「伺服器稽核」SQL Server Audit的狀態
SELECT audit_id N'稽核的識別碼', name N'稽核的名稱', status_desc N'伺服器稽核狀態',
status_time N'上一次狀態變更的時間戳記', audit_file_size N'稽核檔案大小(KB)',
audit_file_path N'稽核檔案目標的完整路徑名稱',*
FROM sys.dm_server_audit_status;
GO
-- 04_使用sys.server_audits目錄檢視，檢視「伺服器稽核」物件的目前狀態，包含：WHERE子句篩選式
SELECT name N'稽核', type_desc N'稽核類型', on_failure_desc N'寫入動作失敗時',
predicate N'述詞運算式', is_state_enabled N'啟用', queue_delay N'寫入磁碟前等候時間(毫秒)',
create_date N'建立日期時間', modify_date '修改日期時間'
FROM sys.server_audits
GO
範例程式碼17：建立與啟用伺服器稽核：稽核使用者定義稽核群組
任務二：建立伺服器稽核規格
步驟 1. 使用「物件總管」，建立「伺服器稽核規格」，在「建立伺服器稽核規格」視窗，輸入以下的參數：
- 將「伺服器稽核規格」命名為：監視sp_audit_write所引發的事件。
- 在「稽核動作群組類型」部分，選擇：USER_DEFINED_AUDIT_GROUP。
在伺服器層級的稽核動作群組上，使用的是：USER_DEFINED_AUDIT_GROUP，這是指每當執行sp_audit_write系統預存程序時，就會引發這個事件。請參考下圖所示：
圖27：在「稽核動作群組類型」部分，選擇：USER_DEFINED_AUDIT_GROUP
或是，執行以下的範例程式碼：
-- 01_建立「伺服器稽核規格」：監視sp_audit_write所引發的事件
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視sp_audit_write所引發的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
END
GO
CREATE SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
FOR SERVER AUDIT [稽核使用者定義稽核群組]
ADD (USER_DEFINED_AUDIT_GROUP)
GO
-- 02_啟用此「伺服器稽核規格」：監視sp_audit_write所引發的事件
ALTER SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
WITH (STATE = ON);
GO
-- 03_檢視「伺服器稽核規格」物件
SELECT name N'伺服器稽核規格', is_state_enabled N'是否已啟用',
create_date N'建立日期時間', modify_date N'修改日期時間'
FROM sys.server_audit_specifications
GO
範例程式碼18：建立與啟用伺服器稽核規格：監視sp_audit_write所引發的事件
任務三：測試觸發使用者定義稽核群組與檢視稽核記錄
步驟 1. 執行以下的範例程式碼來建立巢狀式呼叫的使用者預存程序：
-- 01_建立使用者預存程序：nest1
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nest1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[nest1]
GO
CREATE PROCEDURE nest1
AS
DECLARE @nestlevel nvarchar(19)
SET @nestlevel =N'存取被監控的物件，巢狀層級：'+CAST(@@NESTLEVEL AS nvarchar(10))
SELECT @nestlevel
EXEC sp_audit_write
@user_defined_event_id = 11, @succeeded = 0 , @user_defined_information = @nestlevel;
GO
-- 02_建立使用者預存程序：nest2
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nest2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[nest2]
GO
CREATE PROCEDURE nest2
AS
DECLARE @nestlevel nvarchar(19)
SET @nestlevel =N'存取被監控的物件，巢狀層級：'+CAST(@@NESTLEVEL AS nvarchar(10))
SELECT @nestlevel
EXEC sp_audit_write
@user_defined_event_id = 12, @succeeded = 0 , @user_defined_information = @nestlevel;
EXEC nest1
GO
範例程式碼19：建立巢狀式呼叫使用者預存程序
在範例程式碼19中，我們建立了兩個使用者預存程序：nest1與nest2。在nest2使用者預存程序內，會再執行nest2使用者預存程序，形成所謂的巢狀式呼叫使用者預存程序。
在nest1與nest2使用者預存程序中，都呼叫執行了sp_audit_write系統預存程序，以下是其使用的參數之說明：
- @user_defined_event_id：由使用者定義並且記錄在稽核記錄之 user_defined_event_id 資料行中的參數，其使用的資料類型是smallint。
- @succeeded：由使用者傳遞的參數，用於指出事件是否成功。這會顯示在稽核記錄的succeeded資料行中，其使用的資料類型是bit。
- @user_defined_information：由使用者自行定義所要記錄的訊息。這會顯示在稽核記錄的user_defined_information資料行中，其使用的資料類型是nvarchar(4000)。
此系統預存程序也有傳回碼值可以使用，0表示成功，1表示失敗，若發生輸入參數錯誤，或是無法寫入目標稽核記錄都會造成失敗。
在此範例程式中，我們在@user_defined_information參數內，搭配使用了@@NESTLEVEL組態函數，來傳回本機伺服器中執行目前預存程序的巢狀層級(最初值是0)。
步驟 2. 執行以下的範例程式碼來執行先前建立的使用者預存程序，請參考下圖來檢視其執行結果：
-- 01_執行巢狀式呼叫預存程序：nest2
USE tempdb
GO
EXEC nest2
GO
-- 02_直接執行系統預存程序：sp_audit_write
EXEC sp_audit_write
@user_defined_event_id = 88 , @succeeded = 0 ,
@user_defined_information = N'直接執行系統預存程序：sp_audit_write' ;
GO
範例程式碼20：執行巢狀式呼叫使用者預存程序
圖28：檢視使用者預存程序的執行結果
在圖28中，可以觀察到由@@NESTLEVEL組態函數，所傳回本機伺服器中執行目前預存程序的巢狀層級。
步驟 3. 檢視「伺服器稽核」的稽核目標記錄，或是，執行以下的範例程式碼，請參考下圖所示：
-- 01_使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容
-- 移轉為伺服器所在地時區，增加資料行：additional_information、user_defined_event_id、user_defined_information
SELECT SWITCHOFFSET (CAST(event_time AS datetimeoffset), datepart(TZoffset,sysdatetimeoffset())) N'引發時的日期時間(伺服器所在地時區)',
server_principal_name N'登入帳戶', database_principal_name N'資料庫使用者',
database_name N'資料庫', object_name N'物件名稱', statement N'TSQL 陳述式',
additional_information N'其他資訊', user_defined_event_id N'使用者定義事件識別碼',
user_defined_information N'使用者定義資訊'
FROM sys.fn_get_audit_file ('C:\myAdmin\Audit_Logs\*',default,default)
ORDER BY 1 DESC;
GO
範例程式碼21：使用fn_get_audit_file系統安全函數查詢使用者定義稽核事件
圖29：使用「物件總管」，檢視稽核記錄
圖30：使用fn_get_audit_file系統安全函數，檢視稽核記錄
以下是執行nest2使用者預存程序後，在稽核目標記錄內所產生的資料：
其他資訊：<tsql_stack><frame nest_level = '2' database_name = 'tempdb' schema_name = 'dbo' object_name = 'nest2'/></tsql_stack>
使用者定義事件識別碼：12
使用者定義資訊：存取被監控的物件，巢狀層級：1
以下是nest2使用者預存程序呼叫nest1使用者預存程序後，在稽核目標記錄內所產生的資料：
其他資訊：<tsql_stack><frame nest_level = '3' database_name = 'tempdb' schema_name = 'dbo' object_name = 'nest1'/></tsql_stack>
使用者定義事件識別碼：11
使用者定義資訊：存取被監控的物件，巢狀層級：2
以下是直接執行sp_audit_write系統預存程序後，在稽核目標記錄內所產生的資料：
其他資訊：
使用者定義事件識別碼：88
使用者定義資訊：直接執行系統預存程序：sp_audit_write
在additional_informatio(其他資訊)資料行部分，其輸出的資料格式為XML，請參考如下：
<tsql_stack><frame nest_level = '%u' database_name = '%.*s' schema_name = '%.*s' object_name = '%.*s' /></tsql_stack>
其中Frame nest_level表示框架的目前巢狀層級。而模組名稱，會以三部分格式表示(database_name、schema_name以及object_name)。如果輸出的模組名稱包含了無效的XML字元，系統在剖析時會自動將這些字元逸出為格式：_xHHHH_，其中HHHH 代表字元的四位數十六進位UCS-2碼。無效的XML字元，例如有：'<'、'>'、'/' 和 '_x'等。
步驟 4. 使用「物件總管」，停用以及刪除「伺服器稽核」以及「伺服器稽核規格」，或是，執行以下的範例程式碼：
-- 01_停用以及刪除「伺服器稽核」：稽核使用者定義稽核群組
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核使用者定義稽核群組')
BEGIN
ALTER SERVER AUDIT [稽核使用者定義稽核群組]
WITH (STATE = OFF);
DROP SERVER AUDIT [稽核使用者定義稽核群組]
END
GO
-- 02_停用以及刪除「伺服器稽核規格」：監視sp_audit_write所引發的事件
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視sp_audit_write所引發的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
END
GO
範例程式碼22：停用以及刪除伺服器稽核與伺服器稽核規格
結語
在本期文章中，介紹了調整稽核記錄失敗時的處理、調整稽核檔案數目上限、認識對稽核記錄檔案的篩選以及認識使用者定義稽核群組等主題。
訂閱