新手學SQL Server 2012稽核SQL Server Audit新增強的功能(2)

by Derrick 3. 十月 2012 22:23

前言

在前一篇文章中,介紹了伺服器稽核新增強的功能、稽核記錄失敗時的處理、設定最大換用檔案與最大檔案數目、認識稽核檔案的存取權、將SQL Server Audit的目標記錄檔案寫入到網路共用資料夾等主題。

在本次文章中,將繼續討論調整稽核記錄失敗時的處理、調整稽核檔案數目上限、認識對稽核記錄檔案的篩選以及認識使用者定義稽核群組等主題。

任務四:調整稽核記錄失敗時的處理

選擇:「繼續」模式

步驟 1. 在擔任網路共用伺服器上,關閉先前分享的資料夾。

步驟 2. 回到資料庫伺服器上,使用「物件總管」,建立、修改與刪除資料庫,或是執行先前的範例程式碼3。

因為事先關閉了網路分享資料夾,這將導致觸發的事件無法寫入到稽核記錄檔案內。但先前在建立「伺服器稽核」時,在「於稽核記錄失敗時」區域,預設所組態的是「繼續」。因此,系統仍是可以正常執行,但是在Windows事件檢視器上將可以觀察到以下的錯誤訊息,請參考下圖所示:

-- Windows事件檢視器的應用程式記錄
SQL Server Audit 無法寫入檔案 '\\OP1\Audit_Logs\稽核資料庫的異動_UNC_F378D9C3-4CD7-41E9-B37C-D483C62935FA.sqlaudit'。

image

圖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'.

image

圖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檢視稽核檔案的內容

image

圖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
這個命令要求必須停用 稽核。請停用 稽核 後,再重新執行這個命令。

image

圖13:尚未停用伺服器稽核,是無法變更伺服器稽核的屬性

在本次實作練習中,若是沒有先啟用遠端的網路分享資料夾功能,導致無法存取稽核目標檔案,將遭遇到以下的錯誤訊息,請參考下圖所示:

訊息 33222,層級 16,狀態 1,行 1
稽核 '稽核資料庫的異動_UNC' 無法進行 start。
如需詳細資訊,請參閱 SQL Server 錯誤記錄檔。
您還可以查詢 sys.dm_os_ring_buffers,其中 ring_buffer_type = 'RING_BUFFER_XE_LOG'。

SNAGHTML16e8e8

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

image

圖15:檢視SQL Server 錯誤記錄檔,無法建立稽核目標檔案,錯誤識別碼:33206

步驟 5. 使用「物件總管」,建立、修改與刪除資料庫,或是,執行先前的範例程式碼3。
步驟 6. 使用「物件總管」,檢視稽核記錄,或是,執行範例程式碼4來檢視稽核記錄。

步驟 7. 在擔任網路共用伺服器上,關閉先前分享的資料夾。
步驟 8. 回到資料庫伺服器上,使用「物件總管」,建立、修改與刪除資料庫,或是執行先前的範例程式碼3。

因為事先關閉了網路分享資料夾,這將導致觸發的事件無法寫入到稽核記錄檔案內。但先前在建立「伺服器稽核」時,在「於稽核記錄失敗時」區域,預設所組態的是「失敗作業」。因此,這將導致建立、修改與刪除資料庫等作業都會失敗,無法執行。

 

訊息 15247,層級 16,狀態 15,行 1
使用者沒有執行此動作的權限。

image

圖16:建立資料庫失敗,使用者沒有執行此動作的權限

在SQL Server錯誤記錄檔上將可以觀察到以下的錯誤訊息,請參考下圖所示:

錯誤: 33239,嚴重性: 16,狀態: 1。
An error occurred while auditing this operation. Fix the error in the audit and then retry this operation.

image

圖17:SQL Server錯誤記錄檔:無法存取稽核目標檔案,導致「失敗作業」,錯誤識別碼:33239

Windows事件檢視器上將可以觀察到以下的錯誤訊息,請參考下圖所示:

稽核此作業時發生錯誤。請修正核此中的錯誤,然後重試此作業。

image

圖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 服務非預期地結束.

SNAGHTML1b363c

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

image

圖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:變更伺服器稽核的屬性:稽核檔案數目上限、失敗作業

image

圖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'。

SNAGHTML238e1c

圖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'。

請參考下圖所示,或是,執行以下的範例程式碼:

image

圖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:建立伺服器稽核:稽核備份或還原作業

image

圖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'。請參考下圖所示:

 

image

圖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字句,加入篩選了這兩個資料庫。請參考下圖所示:

image

圖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系統預存程序時,就會引發這個事件。請參考下圖所示:

image

圖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:執行巢狀式呼叫使用者預存程序

 

image

圖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系統安全函數查詢使用者定義稽核事件

image

圖29:使用「物件總管」,檢視稽核記錄

 image
圖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:停用以及刪除伺服器稽核與伺服器稽核規格

結語

在本期文章中,介紹了調整稽核記錄失敗時的處理、調整稽核檔案數目上限、認識對稽核記錄檔案的篩選以及認識使用者定義稽核群組等主題。

Tags:

SQL Server資料庫 | 陳俊宇Derrick Chen

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List