第十一篇 SQL Server安全性稽核

標籤：

本篇文章是SQL Server安全系列的第十一篇，詳細內容請參考原文。

SQL Server審核
SQL Server審核是指你可以在資料庫或伺服器執行個體監控事件。稽核線索包含你選擇捕獲的事件的列表，在伺服器上產生資料庫和伺服器對象、主體和操作的活動記錄。你幾乎可以捕獲任何發生的事情的資料，包括成功和不成功的登入，讀、更新、刪除的資料，管理工作，以及更多。審核可以深入到資料庫和伺服器。
這似乎有點奇怪存在辦法回頭追溯那些已經發生的事件。但稽核線索往往是你用於檢測攻擊的第一和最佳的資源，特別是當攻擊只探測資料庫，但尚未成功訪問資料或損害資料。當發生這樣的攻擊，監管人員對你施加壓力，稽核線索可能會協助你找出發生了什麼，什麼資料被訪問，以及攻擊的來源。如果沒有日誌，在你可以確定發生了什麼之前，你必須先捕捉到攻擊者，然後再回複他們。
SQL Server很久以前就提供支援審核功能，但是直到SQL Server 2008審核才真正成為一個核心功能，有內建對象、T-SQL支援，並在SSMS有使用者介面。微軟從初始版本學到了很多並在SQL Server 2012做了一些好的改進。其中最大的變化是，微軟讓SQL Server的所有版本都可以使用審核，而不只是企業版。這意味著你現在可以在任何版本中建立一個跨資料庫的審核規範。但這一訊息是混合的：個別的資料庫審核僅在企業版、開發版和評估版適用。所以，如果你正在測試或評估生產環境最終使用的SQL Server版本，確保你所選擇的有你需要的功能。
SQL Server審核對象
無論你是使用T-SQL還是SSMS使用者介面來管理審核，你會操作下面三個對象：
->伺服器審核：伺服器審核對象是審核的頂級容器，你將始終使用該對象進行審核。通常，你將建立一個伺服器審核匯總一個或多個用於特定目的(比如compliance或特定一組伺服器/資料庫物件)的審核規範。在這個對象你可以組態稽核的名稱、稽核線索儲存路徑、審核檔案最大限制、稽核線索失敗時的操作。你還可以定義篩選來控制事件記錄的寫入。
->伺服器審核規範：在這個對象你可以定義服務等級的事件以捕獲寫入稽核線索。這個規範需要與你之前建立的伺服器審核關聯。就是在這裡定義你想記錄哪些對象上的哪些事件。
->資料庫審核規範：這個對象和伺服器審核規範相似，你用它來捕獲單獨資料庫上的事件。它也要關聯到伺服器審核。
通常你會使用一個伺服器審核，依據你想要捕獲事件的類型結合一種或兩種其他對象。如果所有的事件都發生在一個資料庫中，使用資料庫審核規範對象。否則，如果事件跨越兩個或多個資料庫，或是伺服器層級事件，則使用伺服器審核規範對象。
當你建立一個伺服器審核時，稽核線索有三種儲存位置：檔案、安全日誌、應用程式記錄檔。你應該很小心的選擇審核目標(稽核線索儲存位置)，因為它可以很容易的包含敏感資訊，如社會保險號碼、信用卡號、工資、企業財務資料等。因此，你應該使用未經授權的使用者不能訪問的位置。這意味著，應用程式記錄檔可能不是一個好選擇，因為預設情況下使用者不需要提高許可權就可以查看它。但安全日誌可以是一個很好的選擇，因為訪問需要admin許可權。檔案也可以是一個很好的選擇，因為你可以使用系統內建的安全特性保護檔案夾或檔案夾中的檔案。
建立伺服器審核
你可以使用SSMS圖形介面或T-SQL語句建立審核。在SSMS下建立是非常容易的，我們即將這樣操作。開啟SSMS串連到本機資料庫執行個體。物件總管->安全性->審核，右擊審核選擇"建立審核"，開啟建立審核對話方塊。
你可以使用對話方塊設定伺服器審核的各種屬性：
->審核名稱根據日期和時間自動初始化成Audit-20150908-143152這種格式，當然你可以自行修改為任何你喜歡的。我設定成Sample Audit更好識別審核。
->下一步，你可以設定隊列延遲(毫秒)，預設是設定為1秒，代表最大等待日誌寫入的時間。這個值在效能和安全性之間的折衷：一個較短的時間限制將使其更可能在災難故障前記錄到關鍵事件，但可能會影響伺服器的效能。我讓伺服器審核保持預設設定，這意味著，在最壞的情況下，我可能會失去一秒的條目。作為樣本審核這是可以接受的！
->下一步，設定在稽核線索失敗時的操作，當SQL Server不能寫入一個稽核線索條件時，比如沒有可用磁碟空間。預設是繼續，只是在Windows事件記錄產生一個錯誤，審核繼續執行。The Shut down server option is available when auditing is crucial and neither the operation nor server execution should continue in the face of an audit failure.這是一個嚴酷的選擇(關閉伺服器)，審核是至關重要時它是必要的。操作失敗選項是介於前面兩個極端之間的某個地方，讓失敗的交易復原。我還是將此項保持預設。
->剩餘需要你選擇的是審核目標。有三個選擇：檔案、安全日誌、應用程式記錄檔。如果你選擇後面兩個Windows事件記錄，對話方塊中剩餘選項就都會禁用，其他選項只適用於審核目標為檔案。
如果你選擇了檔案選項，你就得選擇一個路徑。本例子中我選擇的是D:\SQL2012，一個可以隨時清理以騰出空間的檔案夾。你可以設定任何你喜歡的路徑，不同的分區或者網路磁碟。
檔案審核目標的其他選項讓你設定檔案大小和檔案數量。你可以設定最大換用檔案數為一個最大值或者無限制。對於最大換用檔案數選項，一旦檔案夾下的檔案數達到最大值，SQL Server會用新檔案重寫最舊的檔案。對於最大檔案數選項，一旦達到最大值，稽核線索就會寫入失敗。如果你選擇了關聯的無限制選項，那麼換用檔案數將不受限制。你同樣可以設定最大檔案大小或大小無限制。對話方塊中最後一個選項——保留磁碟空間，告訴SQL Server預分配等於你指定的最大檔案大小的磁碟空間，如果有，只有當最大檔案大小的無限制選項沒有選中時才適用。因此，你有足夠的選項來控制你的審核檔案在指定的磁碟機上的空間量。
圖11.1顯示我建立名叫Sample Audit的審核

圖11.1 在SSMS建立伺服器審核
你也可以使用T-SQL語句建立審核。代碼11.1使用CREATE SERVER AUDIT建立相同設定的Sample Audit.我通過建立審核對話方塊中的指令碼按鈕產生相關指令碼。

USE master;GOCREATE SERVER AUDIT [Sample Audit]TO FILE (    FILEPATH = N‘D:\SQL2012‘    ,MAXSIZE = 0 MB    ,MAX_ROLLOVER_FILES = 2147483647    ,RESERVE_DISK_SPACE = OFF)WITH(    QUEUE_DELAY = 1000    ,ON_FAILURE = CONTINUE);GO

代碼11.1 T-SQL代碼建立Sample Audit
代碼11.2顯示建立一個伺服器審核，將日誌寫入到應用程式記錄檔而不是檔案。因為沒有其他額外選項，所以代碼比前面的要簡潔。

CREATE SERVER AUDIT SQLServerAudit    TO APPLICATION_LOG    WITH ( QUEUE_DELAY = 1000,  ON_FAILURE = CONTINUE);GO

代碼11.2 建立寫入到應用程式記錄檔的審核
現在在物件總管->安全性->審核節點，你會看到有兩個對象，11.2所示。注意，在放大鏡表徵圖上都疊加有一個紅色的向下箭頭表徵圖。這表明，審核沒有啟用，這是建立時的預設狀態。你可以通過按右鍵審核對象，從彈出的菜單中選擇啟用審核，或者使用代碼11.3中的ALTER SERVER AUDIT語句啟用審核。一旦你啟用了審核，紅色的箭頭就會消失，雖然你可能需要再次重新整理物件總管。如果你是跟蹤著SSMS介面操作，繼續向前，啟用Sample Audit審核，接下來我們將會使用。

圖11.2 物件總管下的伺服器審核

ALTER SERVER AUDIT SQLServerAudit WITH (STATE = ON);GO

代碼11.3 T-SQL啟用SQLServerAudit審核
建立伺服器審核規範
一旦你建立了一個伺服器審核，你可以定義你想寫的審核事件。伺服器審核本質上是審核規範的一個容器。記住，有兩種類型的審核規範：伺服器和資料庫審核規範。
我將建立伺服器審核規範用於記錄SQL Server登入失敗的資訊到日誌。這是一個資料庫範圍外的伺服器層級的操作，所以我需要在伺服器層級上建立一個規範。使用SSMS->安全性->伺服器審核規範，右擊伺服器審核規範，選擇建立伺服器審核規範，開啟建立伺服器審核規範對話方塊。
你可以接受自動產生的正式名稱，但我命名為TestSQLServerAuditSpec.然後從審核下拉式清單中選擇伺服器審核，列表中包含執行個體上所有已存在的伺服器審核。本例中我選擇使用Sample Audit,意味著稽核線索條目會儲存到檔案中。
然後，你可以使用對話方塊的操作部分來定義你想記錄的事件或事件組。可用的操作類型列表有非常多，11.3所示。本例選擇FAILED_LOGIN_GROUP類型。

圖11.3 選擇審核操作類型
FAILED_LOGIN_GROUP操作類型的其餘列是禁用的，因為該類型沒有其他可用選項。但其他類型讓你將操作與各種伺服器對象關聯起來。對話方塊應該看起來像圖11.4，當你單擊“確定”按鈕時，它將建立規範。

圖11.4 定義一個新的伺服器審核規範
在物件總管下的伺服器審核規範節點會出現新建立的伺服器審核規範，預設是禁用的。你可以右擊規範，然後選擇啟用伺服器審核規範。
你也可以使用代碼11.4中的T-SQL語句來建立新的伺服器審核規範。代碼使用一個WITH子句選項來接收狀態參數——啟用或禁用規範。如果你省略這個子句，狀態會預設設定為OFF.

CREATE SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec    FOR SERVER AUDIT [Sample Audit] ADD (FAILED_LOGIN_GROUP)    WITH (STATE = ON);GO

代碼11.4 T-SQL建立伺服器審核規範
為了測試審核，串連到SQL Server執行個體，然後嘗試用一個錯誤的密碼登入資料庫。你可以另外開啟一個SSMS或者使用物件總管下的連線按鍵來操作。
然後右擊Sample Audit，選擇查看稽核線索，開啟記錄檔查看器對話方塊。它可能需要點時間來從磁碟檔案載入到的日誌條目列表，但最終你會看到11.5所示的登入失敗資訊。每一行包含了相當多的有關事件的資訊，使其向右滾動查看所有資料變得笨重。但當你選擇一行時，該資料會出現在視窗下方以便於閱讀。但不幸的是，如你在圖中所看到的各列沒有很好的格式。

圖11.5 查看伺服器稽核線索
注意伺服器審核自動捕獲修改審核事件，在我啟用伺服器審核時。圖中是捕獲的第二行資訊。
提示：你需提前啟用伺服器審核和伺服器審核規範才能捕獲並寫入事件。通常，你會想建立他們的時候就啟用。
建立資料庫審核規範
建立資料庫審核規範和建立伺服器審核規範非常相似。主要不同是能夠捕獲到日誌的事件範圍。資料庫審核規範能夠捕獲發生在單一資料庫內的事件，比如通過T-SQL語句訪問資料，修改結構或資料庫物件的許可權，或者執行預存程序。另一重要的區別是，你在資料庫下建立資料庫審核規範，而不是在安全性節點。
如果你想建立一個審核記錄任何使用者或程式執行一個SELECT語句從AdventureWorks2012.Person.Person表中擷取資料。展開 物件總管->資料庫->AdventureWorks2012->安全性，右擊資料庫審核規範節點，選擇建立資料庫審核規範，開啟建立資料庫審核規範對話方塊，同樣它會自動產生名稱，你可以隨意修改。
圖11.6顯示了在Person.Person表上的SELECT審核操作類型，關聯到之前建立的審核Sample Audit.本例中你要選擇OBJECTS對象類(另外的選項是DATABASE和SCHEMA),Person架構，Person表名，以及主體名稱。不幸的是，你不能直接鍵入對象和主體名稱。你必須點擊旁邊的省略按鈕，會開啟選擇對象對話方塊。

圖11.6 建立資料庫審核規範
你可以將主體名稱設定為任何資料庫使用者或角色，包括Public資料庫角色以覆蓋所有訪問資料庫的使用者。類似伺服器審核規範，你可以增加任何喜歡的操作到規範。
一旦建立了規範，記得啟用它。
你也可以使用代碼11.5中的T-SQL語句建立這個規範，帶上WITH子句選項啟用規範。

USE AdventureWorks2012;GOCREATE DATABASE AUDIT SPECIFICATION [Person Data Access]    FOR SERVER AUDIT [Sample Audit]    ADD (SELECT ON OBJECT::Person.Person BY public)    WITH (STATE = ON);GO

代碼11.5 T-SQL代碼建立資料庫審核規範
然後你可以通過執行表上的一個SELECT語句測試審核規範。一旦你做了此操作，重新整理記錄檔查看器，11.7。正如你所看到的，日誌中包含一個非常完整的事件資訊，包括引起它的SQL語句。

圖11.7 查詢Person.Person後的稽核線索
注意圖片中的其他資訊列沒有任何內容，這是因為產生事件直接是SELECT語句。但是當SELECT語句是在預存程序或其他代碼模組下執行，其他資訊會包含T-SQL堆棧資訊的XML代碼塊。這可以用於區分查詢是以查詢語句還是代碼模組執行的。
我們建立一個名叫tempPerson的預存程序，包含圖片中語句列的SELECT語句。在你執行過預存程序後，你會得到了圖11.8所示的結果，同樣出現因為有人在Person.Person表上執行了SELECT語句。但是這次注意其他資訊列。

圖11.8 執行封裝SELECT語句的預存程序
其他資訊列包含XML代碼，包括一些引起稽核線索的代碼資訊，最重要的是預存程序的架構和名稱。
編寫自訂審核資訊
你不局限於僅捕獲每個事件的預設資訊。你還可以建立使用者自訂的審核事件，它允許你將任何你希望的日誌寫入到稽核線索。代碼11.6中示範了如何?這個。只有當審核規範(不管是伺服器還是資料庫)被禁用時，才能修改它，所以代碼首先禁用規範。然後添加USER_DEFINED_AUDIT_GROUP操作類型到TestSQLServerAuditSpec規範，並立刻啟用它。最後一行代碼使用sp_audit_write系統預存程序往稽核線索中寫入一些有趣東西。

USE master;GOALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec    WITH (STATE = OFF);GOALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec    ADD (USER_DEFINED_AUDIT_GROUP)    WITH (STATE = ON);GO-- Write some custom audit informationEXEC sp_audit_write 9999, 1, N‘Something in SQL Server succeeded!‘

代碼11.6 編寫自訂審核資訊
當然，你也可以通過SSMS圖形介面修改伺服器審核規範。
如果你執行代碼11.6，然後查看稽核線索，你會看到條目出現在圖11.9的頂部。你仍然能得到很多審核事件的背景資訊，包括事件的T-SQL語句，但是你會發現在底部的使用者定義的資訊項是我們定義內容。

圖11.9 稽核線索包含使用者定義的資訊
提示：如果USER_DEFINED_AUDIT_GROUP伺服器審核規範禁用，資料庫會忽略sp_audit_write.
過濾審核事件
SQL Server還包括過濾事件寫入稽核線索的能力，它使用和擴充事件相同的過濾機制。過濾為你提供細粒度的控制什麼會被SQL Server寫入稽核線索。但重要的是，伺服器仍然會為你在規範中定義的事件產生所有的日誌條目，然後使用過濾來決定是否將事件寫入日誌。所以，即使條目沒有寫入日誌，建置事件條目還是需要開銷。這意味著，建立特定對象的審核事件通常比過濾要好。
你想建立一個審核，記錄一個特定類型的所有事件，除了特定登入名稱的資訊。代碼11.7的第一部分建立carol登入名稱並映射到AdventureWorks2012資料庫。然後使用SUSER_ID方法檢索新使用者的主體ID(我這是288)。然後，第二部分建立一個帶WHERE子句過濾掉主體ID為288的伺服器審核，並啟用伺服器審核。Now carol has free reign in the database, safe from server audit events!

-- Part 1: Create the login and database userUSE AdventureWorks2012;GOCREATE LOGIN carol WITH PASSWORD = ‘GEP2zYDt+5Cqw‘;CREATE USER carol FOR LOGIN carol;SELECT SUSER_ID(‘carol‘);-- Part 2: Create the server audit-- Change principal id from 288 based on SUSER_ID from previous statementUSE master;GOCREATE SERVER AUDIT FilterAudit    TO APPLICATION_LOGWITH(    QUEUE_DELAY = 1000    ,ON_FAILURE = CONTINUE)WHERE server_principal_id <> 288;ALTER SERVER AUDIT FilterAudit WITH (STATE = ON);GO

代碼11.7 建立一個登入，然後建立一個排除此使用者的審核
你也可以使用“審核屬性”對話方塊來篩選伺服器審核事件，按右鍵伺服器審核選擇屬性。選擇“過濾”頁，11.10所示。注意，文字框只包含用於建立伺服器審核語句中的WHERE子句謂詞，括在括弧中。沒有必要包含WHERE關鍵字。
 
圖11.10 用審核屬性添加過濾

-- *** Clean up ***-- ****************USE AdventureWorks2012;GODROP USER carol;--清理資料庫審核規範ALTER DATABASE AUDIT SPECIFICATION [Person Data Access] WITH (STATE = OFF);DROP DATABASE AUDIT SPECIFICATION [Person Data Access];DROP PROCEDURE dbo.tempPerson;USE master;GO--清理伺服器審核規範ALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec WITH (STATE = OFF);DROP SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec;--清理伺服器審核ALTER SERVER AUDIT [Sample Audit] WITH (STATE = OFF);DROP SERVER AUDIT [Sample Audit];ALTER SERVER AUDIT SQLServerAudit WITH (STATE = OFF);DROP SERVER AUDIT SQLServerAudit;ALTER SERVER AUDIT FilterAudit WITH (STATE = OFF);DROP SERVER AUDIT FilterAudit;--刪除登入DROP LOGIN carol;

View Code

總結
SQL Server審核功能讓你在伺服器執行個體和資料庫範圍記錄各種事件的日誌條目。你可以使用SSMS圖形介面或T-SQL語句建立審核對象，可以結合大量的選項比如過濾條目、編寫自訂資訊、查看T-SQL堆棧資訊。審核是SQL Server的一個重要的安全功能，可以協助你找到資料庫中可疑的活動，這樣你就可以消除你的資料安全性漏洞。

第十一篇 SQL Server安全性稽核