前言
隨著企業開始正視資訊安全的重要性,存取資料的稽核紀錄將變得重要。在SQL Server 2008版本開始導入一套全新的稽核系統:SQL Server Audit,讓管理人員不但可以精確地紀錄所需要之稽核資訊,而且利用「擴充事件(Extended Event)」來監視系統。
所謂的「擴充事件」,是一種耗用資源少的輕量型效能監視系統,這也是在SQL Server 2008版本開始提供。如今在SQL Server 2012版本,增加了更多彈性與自訂的功能。
伺服器稽核新增強的功能
以下整理了SQL Server 2012版本在「伺服器稽核(SQL Server Audit)」上新增強的功能:
- 所有SQL Server版本都支援「伺服器層級稽核(Server Level Audit)」。但是「資料庫層級稽核(Database Level Audit)」,則限於Enterprise、Developer以及Evaluation版本。
- 強化對稽核檔案記錄寫入失敗的處理,並且提供了繼續、關閉伺服器以及失敗作業等選項。
- 新增加對稽核記錄檔的篩選。例如,在CREATE SERVER AUDIT可以搭配使用WHERE條件式,篩選符合條件式的資料才能存放到稽核記錄檔上。
- 在先前版本中,稽核記錄可能具有不定數目的記錄檔,或在預先定義的數目之後換用。現在,已經導入了新的選項,可設定稽核檔案的數目上限而不換用,讓客戶能夠控制所收集的稽核資訊數量,而不會遺失稽核記錄。
- 新增「稽核動作群組」:USER_DEFINED_AUDIT_GROUP。它讓你可以自定使用者所需的稽核事件。使用系統預存程序:sp_audit_write,則可以將使用者定義稽核事件加入至 USER_DEFINED_AUDIT_GROUP。
- 提供了T-SQL堆疊(Stack) 框架資訊與動作相關聯的T-SQL堆疊資訊,這是以XML格式顯示。可以利用系統預存函數「sys.fn_get_audit_file」來查詢。
- 支援監視「自主資料庫」的資料庫使用者。
稽核記錄失敗時的處理
舉例來說:如果寫入的目標目錄是位於遠端共用上,若是發生網路中斷事件,「伺服器稽核」可以設定為能夠於恢復網路連接後,繼續執行服務。此外,也導入發生寫入記錄目標失敗時,讓此作業失敗的新功能。
有關於強化稽核檔案寫入失敗的處理,以下提供更進一步的說明:
「繼續(CONTINUE)」
若發生稽核記錄寫入失敗時,系統不會保留稽核記錄。「伺服器稽核」是會繼續嘗試記錄事件,而且之後,如果失敗狀況已解決,就會恢復稽核作業。
使用此選項可能會違反安全性原則,因為可能會有遺漏未稽核到的活動。這項功能,適用於資料庫系統的運行作業比維持完整稽核更重要時,請使用此選項。
「關閉伺服器(SHUTDOWN)」
這是在SQL Server 2008版本就已經提供的功能。若是發生稽核記錄寫入失敗時,系統將會強制伺服器關閉。發出此內容的登入必須具有SHUTDOWN權限。
如果登入的帳戶沒有此權限,這個功能將會失敗,而且會引發錯誤訊息,不會發生稽核的事件。這項功能,適用於當稽核失敗可能危害系統的安全性或完整性時,請使用此選項。
「失敗作業(FAIL OPERATION)」
如果資料庫動作導致發生稽核的事件,但卻發生稽核記錄寫入失敗時,這些動作就會失敗。也就是說,稽核事件的動作無法繼續進行,也不會發生稽核的事件。
稽核會繼續嘗試記錄事件,而且如果失敗狀況已解決,就會恢復稽核。這項功能,適用於當維持完整稽核是比資料庫系統的運行作業更重要時,請使用此選項。
這是以交易的方式來進行,若是因故發生無法寫入稽核記錄的事件,系統將會回復此交易。但是,這只包含有使用「稽核」組態的對象,若有未受到稽核的對象,則不受到交易回復的影響,仍將繼續執行。
舉例來說:以客戶、訂單兩個資料表為例,因為客戶資料表包含了機密資料,你僅在客戶資料表上設定了「稽核」。此選項能夠以交易的方式回復客戶資料表,但是因為沒有在訂單資料表上設定「稽核」,所以,仍是可以對訂單資料表繼續作業。

圖1:建立「伺服器稽核」與設定存放稽核結果的目的地,比較SQL Server 2008與2012版本
設定最大換用檔案與最大檔案數目
請參考以下表格的整理:
選項 | 說明 |
最大換用檔案(Maximum rollover files) | 此選項用於設定除了目前的檔案外,還要保留多少份的檔案數量之上限。對「檔案大小上限」所輸入的參數值必須要是整數,預設是無限制,系統會自動填入:2147483647。 每當「稽核」重新啟動(例如:執行個體重新啟動或是對「稽核」設定停用後再度啟用),或者是,檔案數量已經達到「檔案大小上限」所設定的空間時,系統就會評估此參數。在評估「最大換用檔案」時,如果檔案的數量已經超過其所設定值時,系統會自動刪除最舊版本的檔案。 如果將「最大換用檔案」設定為0,則每次在評估「最大換用檔案」的設定時,系統都會建立新的檔案。每次評估「最大換用檔案」的設定值時,系統只會自動刪除一個檔案。 |
最大檔案數目(MAX FILES) | 在SQL Server 2012版本上新增加了「最大檔案數目」選項,可以指定可建立的最大稽核檔案之數目。若是達到此限制,系統不會換用最初的第一個檔案。 因此,當達到MAX_FILES選項限制時,任何會被觸發進而產生稽核事件的動作,都會遭遇到失敗並接收到錯誤訊息。 |
表格1:使用參數:最大換用檔案以及最大檔案數目
由表格1來看,在設定「最大換用檔案」後,搭配使用「最大檔案數目」選項,可以避免發生被人惡意洗稽核記錄的情形。
認識稽核檔案的存取權
這要提醒你的是:系統會將「稽核」的結果傳送到事先所定義的「目標」內存放,若將「目標」組態為二進位檔案,這是使用SQL Server服務啟動帳戶的身分來寫入與讀取。因此,請確認SQL Server服務啟動帳戶對於目標的檔案位置有足夠的讀取和寫入權限。若是權限不足,在建立「伺服器稽核」時,就會遭遇到以下的錯誤訊息,以及下圖所示:
訊息 33072,層級 16,狀態 1,行 2
稽核記錄檔路徑無效。
![02_稽核記錄檔案路徑無效 [1] 02_稽核記錄檔案路徑無效 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=02_%E7%A8%BD%E6%A0%B8%E8%A8%98%E9%8C%84%E6%AA%94%E6%A1%88%E8%B7%AF%E5%BE%91%E7%84%A1%E6%95%88%20%5B1%5D_thumb.png)
圖2:SQL Server服務啟動帳戶對於稽核記錄檔路徑沒有存取權限
若要將SQL Server Audit的目標記錄檔案直接寫入到網路共用資料夾,需要進行以下的設定:
- SQL Server服務的服務啟動帳戶,需要使用指定的登入帳戶。
- 此登入帳戶需要對於遠端的網路共用資料夾,具備寫入與讀取的權限。
- 請使用「通用命名慣例(Universal Naming Convention,UNC)」命名格式來存取。
實作練習一:將SQL Server Audit的目標記錄檔案寫入到網路共用資料夾
在本次練習將一併介紹SQL Server Audit新增強的功能。
準備工作
1. 準備兩台伺服器:一台安裝SQL Server的資料庫伺服器,一台伺服器提供網路共用。在本次實作練習環境中,使用OPM擔任資料庫伺服器,使用OP1擔任網路共用伺服器。
2. 建立共用的登入帳戶,請擇一執行即可:
- 若在網域環境,請建立一個網域登入帳戶:advop,密碼為:P@ssw0rd。
- 若非網域環境,替代方案是可以在兩台伺服器上,建立相同名稱、密碼的本機登入帳戶:advop,密碼為:P@ssw0rd。
3. 將此登入帳戶:advop,加入到以下本機Administrators群組內。
4. 將此網域登入帳戶:advop,加入到SQL Server執行個體的伺服器角色:sysadmin內。
5. 在擔任網路共用的伺服器上,建立資料夾:C:\myAdmin\Audit_Logs。並設定此網域登入帳戶:advop對此資料夾的「共用使用權限」為「完全控制」。
6. 設定使用此advop網域登入帳戶來當做SQL Server服務啟動帳戶。
任務一:建立SQL Server Audit伺服器稽核,將目標記錄檔案寫入到遠端的網路共用資料內
步驟 1. 使用SSMS管理工具,連線到目標伺服器,使用「物件總管」,展開指定的伺服器「安全性」\「稽核」節點。
步驟 2. 在「稽核」節點上,滑鼠右鍵選擇「新增稽核」。
步驟 3. 在「建立稽核」視窗填入以下的參數:
- 在「稽核名稱」方塊輸入:稽核資料庫的異動_UNC。
- 在「佇列延遲(以毫秒為單位)」方塊輸入:1000。
- 在「於稽核記錄失敗」方塊點選:「繼續」。
- 在「稽核目的地」方塊選擇:「檔案」。
- 在「檔案路徑」方塊輸入:\\OP1\Audit_Logs。
- 點選「確定」,完成設定。
請參考下圖所示:
![03_組態伺服器稽核 [1] 03_組態伺服器稽核 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=03_%E7%B5%84%E6%85%8B%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%A8%BD%E6%A0%B8%20%5B1%5D_thumb.png)
圖3:建立SQL Server Audit「伺服器稽核」
步驟 4. 在「稽核」物件:稽核資料庫的異動_UNC上,滑鼠右鍵,選擇「啟用稽核」。
步驟 5. 在「啟用稽核」視窗,點選「關閉」。
或是,使用以下的範例程式碼來建立與啟用SQL Server Audit「伺服器稽核」。請參考下圖所示:
-- 01_建立「伺服器稽核」,將目標記錄檔案寫入到遠端的網路共用資料內
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
CREATE SERVER AUDIT [稽核資料庫的異動_UNC]
TO FILE
( FILEPATH = N'\\OP1\Audit_Logs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
-- 02_啟用此「伺服器稽核」:稽核資料庫的異動_UNC
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
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目錄檢視,檢視「伺服器稽核」物件的目前狀態
SELECT name N'稽核',type_desc N'稽核類型', on_failure_desc N'寫入動作失敗時',
is_state_enabled N'啟用',queue_delay N'寫入磁碟前等候時間(毫秒)',
create_date N'建立日期時間', modify_date '修改日期時間'
FROM sys.server_audits
GO
範例程式碼1:建立與啟用伺服器稽核,將目標記錄檔案寫入到遠端的網路共用資料內
![04_檢視已經建立的「伺服器稽核」 [1] 04_檢視已經建立的「伺服器稽核」 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=04_%E6%AA%A2%E8%A6%96%E5%B7%B2%E7%B6%93%E5%BB%BA%E7%AB%8B%E7%9A%84%E3%80%8C%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%A8%BD%E6%A0%B8%E3%80%8D%20%5B1%5D_thumb.png)
圖4:檢視已經建立的「伺服器稽核」
任務二:建立伺服器稽核規格
在伺服器層級的稽核動作群組上,使用的是:DATABASE_CHANGE_GROUP,這是指每當建立、改變或卸除任何資料庫時,就會引發這個事件。
步驟 1. 使用「物件總管」,展開指定的伺服器「安全性」\「伺服器稽核規格」節點。
步驟 2. 在「伺服器稽核規格」節點,滑鼠右鍵選擇「新增伺服器稽核規格」。
步驟 3. 在「建立伺服器稽核規格」視窗,輸入以下的參數:
- 在「名稱」方塊輸入:監視每當發生建立、改變或卸除任何資料庫的事件。
- 在「稽核」方塊選取:稽核資料庫的異動_UNC。
- 在「稽核動作類型」方塊選擇:DATABASE_CHANGE_GROUP。
- 點選「確定」完成設定。
請參考下圖所示:
![05_建立伺服器稽核規格 [1] 05_建立伺服器稽核規格 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=05_%E5%BB%BA%E7%AB%8B%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%A8%BD%E6%A0%B8%E8%A6%8F%E6%A0%BC%20%5B1%5D_thumb.png)
圖5:建立伺服器稽核規格
步驟 4. 在「伺服器稽核規格」物件:當建立、改變或卸除伺服器主體,滑鼠右鍵選擇「啟用伺服器稽核規格」。
步驟 5. 在「啟用伺服器稽核規格」視窗,點選「關閉」。請參考下圖所示:
圖6:檢視已經建立的伺服器稽核以及對應的伺服器稽核規格
或是,使用以下的範例程式碼來建立與啟用伺服器稽核規格。請參考下圖所示:
-- 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 [稽核資料庫的異動_UNC]
ADD (DATABASE_CHANGE_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
範例程式碼2:建立與啟用伺服器稽核規格
![07_檢視已經建立的伺服器稽核規格 [1] 07_檢視已經建立的伺服器稽核規格 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=07_%E6%AA%A2%E8%A6%96%E5%B7%B2%E7%B6%93%E5%BB%BA%E7%AB%8B%E7%9A%84%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%A8%BD%E6%A0%B8%E8%A6%8F%E6%A0%BC%20%5B1%5D_thumb.png)
圖7:檢視已經建立的伺服器稽核規格
任務三:建立、修改與刪除資料庫與檢視稽核記錄
步驟 1. 使用「物件總管」,建立、修改與刪除資料庫,或是執行以下的範例程式碼:
-- 01_建立資料庫:DB_Audit02
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB_Audit02')
DROP DATABASE [DB_Audit02]
GO
CREATE DATABASE DB_Audit02
GO
-- 02_啟用資料庫屬性:AUTO_SHRINK
ALTER DATABASE [DB_Audit02]
SET AUTO_SHRINK ON WITH NO_WAIT
GO
-- 03_刪除此資料庫:DB_Audit02
DROP DATABASE DB_Audit02
GO
範例程式碼3:建立、修改與刪除資料庫
步驟 2. 在「物件總管」,展開指定的伺服器「安全性」\「稽核」節點。
步驟 3. 在「稽核」物件:稽核資料庫的異動_UNC上,滑鼠右鍵選擇「檢視稽核紀錄」。這裡面記錄了詳盡的資訊。
![08_使用記錄檔檢視器來檢視稽核記錄 [1] 08_使用記錄檔檢視器來檢視稽核記錄 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=08_%E4%BD%BF%E7%94%A8%E8%A8%98%E9%8C%84%E6%AA%94%E6%AA%A2%E8%A6%96%E5%99%A8%E4%BE%86%E6%AA%A2%E8%A6%96%E7%A8%BD%E6%A0%B8%E8%A8%98%E9%8C%84%20%5B1%5D_thumb.png)
圖8:使用記錄檔檢視器來檢視稽核記錄
步驟 4. 點選「關閉」,完成檢視稽核目標記錄。
或是,使用以下的範例程式碼來檢視稽核目標記錄:
-- 01_使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容
-- 請留意:event_time資料行存放的是格林威治標準時間(GMT)。
USE master
GO
SELECT *
FROM sys.fn_get_audit_file ('\\OP1\Audit_Logs\\*',default,default)
ORDER BY 1 DESC;
GO
-- 02_移轉為伺服器所在地時區
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 陳述式'
FROM sys.fn_get_audit_file ('\\OP1\Audit_Logs\\*',default,default)
ORDER BY 1 DESC;
GO
範例程式碼4:使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容
![09_使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容[1] 09_使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容[1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=09_%E4%BD%BF%E7%94%A8fn_get_audit_file%E7%B3%BB%E7%B5%B1%E5%AE%89%E5%85%A8%E5%87%BD%E6%95%B8%E4%BE%86%E6%AA%A2%E8%A6%96%E7%A8%BD%E6%A0%B8%E6%AA%94%E6%A1%88%E7%9A%84%E5%85%A7%E5%AE%B9%5B1%5D_thumb.png)
圖9:使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容
結語
在本期文章中,介紹了伺服器稽核新增強的功能、稽核記錄失敗時的處理、設定最大換用檔案與最大檔案數目、認識稽核檔案的存取權、將SQL Server Audit的目標記錄檔案寫入到網路共用資料夾等主題。