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

by Derrick 10. 五月 2012 17:00

前言

隨著企業開始正視資訊安全的重要性,存取資料的稽核紀錄將變得重要。在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)」

如果資料庫動作導致發生稽核的事件,但卻發生稽核記錄寫入失敗時,這些動作就會失敗。也就是說,稽核事件的動作無法繼續進行,也不會發生稽核的事件。

稽核會繼續嘗試記錄事件,而且如果失敗狀況已解決,就會恢復稽核。這項功能,適用於當維持完整稽核是比資料庫系統的運行作業更重要時,請使用此選項。

這是以交易的方式來進行,若是因故發生無法寫入稽核記錄的事件,系統將會回復此交易。但是,這只包含有使用「稽核」組態的對象,若有未受到稽核的對象,則不受到交易回復的影響,仍將繼續執行。

舉例來說:以客戶、訂單兩個資料表為例,因為客戶資料表包含了機密資料,你僅在客戶資料表上設定了「稽核」。此選項能夠以交易的方式回復客戶資料表,但是因為沒有在訂單資料表上設定「稽核」,所以,仍是可以對訂單資料表繼續作業。

01_3_建立「伺服器稽核」與設定存放稽核結果的目的地,比較SQL Server 2008與2012版本

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

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

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

圖4:檢視已經建立的「伺服器稽核」

任務二:建立伺服器稽核規格


在伺服器層級的稽核動作群組上,使用的是:DATABASE_CHANGE_GROUP,這是指每當建立、改變或卸除任何資料庫時,就會引發這個事件。

步驟 1. 使用「物件總管」,展開指定的伺服器「安全性」\「伺服器稽核規格」節點。

步驟 2. 在「伺服器稽核規格」節點,滑鼠右鍵選擇「新增伺服器稽核規格」。

步驟 3. 在「建立伺服器稽核規格」視窗,輸入以下的參數:

  • 在「名稱」方塊輸入:監視每當發生建立、改變或卸除任何資料庫的事件。
  • 在「稽核」方塊選取:稽核資料庫的異動_UNC。
  • 在「稽核動作類型」方塊選擇:DATABASE_CHANGE_GROUP。
  • 點選「確定」完成設定。

請參考下圖所示:

05_建立伺服器稽核規格 [1]

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

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

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

圖9:使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容

結語

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

Tags:

SQL Server資料庫 | 陳俊宇Derrick Chen

評論 (1) -

Rebon
Rebon Taiwan
2014/1/31 下午 04:00:40 #

首先

感謝有這篇好文 使人能清楚理解對於資料庫稽核 ^^

想請問 sql server 2014 CTP2 版本是否也能使用 "資料庫層級稽核(Database Level Audit)" 呢?

小弟不才 查了好一陣子 一直都查不到有沒有支援 Frown

安裝了之後發現只有 "伺服器層級稽核(Server Level Audit)"

是否能夠透過更新的方式新增 "資料庫層級稽核(Database Level Audit)" 功能呢?

還是只能回頭使用 2012 版本 ><"

萬分感謝!!!!!!!



回覆

新增評論




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






NET Magazine國際中文電子雜誌

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