在保密你的服務器和數據，防備當前復雜的攻擊，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用來保護世界上一些最嚴格的監管組織，行業和數據。

SQL Server審核對象

不管你使用T-SQL還是SSMS的用戶界面操作審核，你會與三個對象打交道。

• 服務器審核：對于你的審核，服務器審核對象是最高級別的容器；你經常為一個審核使用這個對象。通常你會為特定的目的，創建服務器審核到組，包含一個或多個審核規范，例如承諾，或為一系列的服務器或數據庫對象。你會使用這個對象到特定的審核名稱，那里用來保存審核日志，限于日志，和在審核失敗事件里發生了什么。你也可以定義篩選來給你在事件日志里顆粒級別的控制。
  
• 服務器審核規格：使用這個對象在審核日志里來定義特定服務器級別的事件來捕獲。規格與你剛才創建的具體服務器審核相關。這里是你定義的地方，即你在想要記錄的對象上的事件。
• 數據庫審核規格：這個對象和服務器審核規格類似，除了你會在單個數據庫里用它來捕獲事件。它也和一個服務器審核對象關聯。

你會經常用到服務器審核對象，連同一個或其它對象，取決于在日志里你想要捕獲的事件類型。如果你想要記錄的所有事件在單個數據庫，使用數據庫審核規格對象。否則，如果事件跨越兩個或更多數據庫，或是服務器級別的事件，使用服務器審核規格對象。

當你創建一個服務器審核時，你可以為審核日志選擇三個位置之一：Windows應用程序日志，Windows安全日志，或文件系統里的一個文件。對你寫入審核數據的位置你要非常仔細，因為它很可能包含像社會保障號，信用卡號，工資，企業財務數據等敏感信息。因此，你要使用未授權用戶不能訪問的位置。這就是說Windows應用程序日志不是個好選擇，因為默認情況下，用戶不需要更高的許可來查看它。但安全日志可以是個好選擇，因為訪問需要管理員許可。文件夾中的文件也可以是個好選擇，因為你可以安全化文件夾或它里面的文件使用Windows內建的安全系統。

創建一個服務器審核

你可以使用SSMS里的圖形話界面工具或T-SQL語句創建SQL Server審核。使用SSMS的審核的圖形界面更加簡單，以為我們從這里開始。打開SSMS連接到本地SQL Server實例。在對象瀏覽器里，切換到最高層的【安全性】節點——不是數據庫里的【安全性】節點——展開節點，你會看到審核。

提示：這里我使用的是SQL Server 2012和AdventureWorks2012樣本數據庫。

你可以使用這個對話框來設置你創建的服務器審核對象的各種屬性：

• 審核名稱：SQLServer默認以當前的日期和時間自動生成，如Audit-20160430-084903格式，你也可以設置為你需要的名稱。我設置為Sample Audit來更好的區分。
• 隊列延遲：接下來你可以用毫秒來設置隊列延遲，默認是1秒，這表示你愿意等待系統寫入日志條目的最大時間是1秒。這個值是性能和完全之間的權衡：更短的時間限制，在災難故障前，更容易記錄關鍵事件，但會影響到服務器性能。對于這服務器審核，我保持默認設置，意味著最壞的情況我會丟失一秒的條目。對于范例審核，完全可以接受。
• 接下來的設置決定在審核日志失敗時會發生什么，當SQL Server不能寫入一條審核日志條目時，可能因為沒有可用的磁盤空間。默認是【繼續】，在系統事件日志里有會有一條錯誤，但潛在的操作還是繼續。當審核至關重要，面對審核失敗，不管操作還是服務器執行都不應該繼續，可以使用【關閉服務器】選項。這是一個嚴厲的操作——關閉SQL Server服務——但審核至關重要時這個是必須的。【操作失敗】是在這2個極端之間，引起造成失敗的事務回滾。這個設置我也保持默認。
• 對話框上剩下的選項與你選擇的審計目標有關。有3個選項：文件，安全日志，和應用程序日志。如果你選擇了后2個系統事件日志選項，沒有可用的其它選項，剩下的選項只針對【文件】.
• 如果你選擇了【文件】選項，你需要指定一個文件路徑。對于這個例子，我選擇D:\Junk。你可以把審核文件放在你想要的任何地方，例如其它盤甚至在網絡硬盤上。
• 其它選項與存儲日志文件有關，讓你在數據量和文件輸上有限制。你可以指定【最大滾動更新文件數】，也可以指定【最大文件數】。使用【最大滾動更新文件數】選項，一旦文件夾里文件數達到最大數，SQL Server開始用最新的文件覆蓋最老的文件。使用【最大文件數】選項，一旦達到指定的文件數，寫入審核日志會失敗。如果你勾選了關聯的【無限制】選項，對于滾動更新數和文件數沒有限制。你也可以指定【最大文件大小】，也可以設置【無限制】選項。對話框上的最后一個選項，【保留磁盤空間】，告訴SQL Server預留磁盤空間到你指定的最大文件大小，對于最大文件大小，只有在選擇了【無限制】的情況下才會有。因此對于指定硬盤上的空間量，你有大量的控制選項。

插圖11.1顯示了對于Sample Audit完整的創建審核對話框信息。

插圖11.1：在SSMS里，使用創建審核對話框創建一個服務器審核對象。

你也可以使用T-SQL代碼創建審核。代碼11.1顯示了創建剛才一樣配置的審核，使用CREATE SERVER AUDIT語句。我使用腳本按鈕生成了這個代碼，在創建審核對話框上，我修改了我想要的配置后，我還修改了一些代碼。

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

代碼11.1:你可以用來創建Sample Audit的T-SQL代碼

代碼11.2向你展示了如何創建一個服務器審核，寫入日志到系統應用程序事件日志，而不是文件。沒有其它可以設置選項，因此語句比剛才的例子小很多。

CREATE SERVER AUDIT SQLServerAuditTO APPLICATION_LOGWITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE); GO

代碼11.2：創建寫入系統應用程序日志的SQL Server 審核的代碼

在創建這2個審核后，可以看下對象瀏覽器的【安全性】節點里的【審核】節點（你要刷新下節點內容來查看它們），如插圖11.2所示。看下，在放大鏡圖標上獨有一個向下的紅色箭頭。這表示審核還沒啟用，創建后默認都沒啟用。你可以在對象瀏覽器里右擊審核，在彈出菜單里選擇【啟用審核】來啟用它們，或者用代碼11.3使用ALTER SERVER AUDIT語句來修改審核。一旦啟用審計，紅色向下箭頭會消失，在對象瀏覽器里你要刷新下來才可以看到。如果你在SSMS里和我們一起來操作，啟用它們吧，一會我們要用到。

插圖11.2：在對象瀏覽器里新建但沒啟用的服務器審核

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

代碼11.3：啟用SQLServerAudit審核的代碼

創建一個服務器審核規范

一旦你創建了一個服務器審核，你可以定義你想要寫入的審核時間。一個服務器審核基本上是審核規范的容器。記住有2類審核規范：服務器和數據庫審核規范。

這里我創建的服務器審核規范會記錄所有SQL Server失敗的登陸。這個是服務器層級操作，不是單個數據庫，因此我需要在服務器層級創建一個規范，而不是單個數據庫層級。在SSMS里使用圖形化用戶界面，在服務器層級【安全性】節點里，右擊【服務器審核規范】，從彈出菜單里選擇【新建服務器審核規范】來打開【Create Server Audit Specification】對話框。

你可以使用默認生成的名稱，但我這里命名為TestSQLServerAuditSpec。然后從【審核】下拉列表里選擇【Sample Audit】，這里包含了SQL Server實例里已存在的所有服務器審核，選擇【Sample Audit】意味著審核日志項目會進入審核里定義的文件系統。

然后你可以使用對話框里【審核操作類型】來定義你想要記錄的事件或事件組。可用操作類型很豐富，如插圖11.3所示。對于這個規范，選擇【FAILED_LOGIN_GROUP】類型。

插圖11.3：你可以選擇記錄的服務器操作審核類型很多

對于FAILED_LOGIN_GROUP操作類型剩下的列都停用了，因為對于這個類型沒有其它可用選項。但其它類型會讓你關聯多個服務器對象。對話框如插圖11.4所示，但你點擊【確定】的時候會創建規范。

插圖11.4：使用SSMS定義新的服務器審核規范

新定義的服務器審核規范會在對象瀏覽器里的【服務器審核規范】節點里出現，默認是停用的。你可以右擊這個規范從彈出菜單里選擇【啟用服務器審核規范】來啟用它。

你也可以使用代碼11.4來創建新的服務器審核規范。這個代碼使用WITH子句來接受用ON或OFF的STATE參數值，來啟用或停用規范。如果你忽略這個子句，狀態默認是OFF。

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

代碼11.4：使用可選的WITH子句來創建新的立即生效服務器審核規范。

測試審核，連接到SQL Server實例，嘗試用不正確的登陸來連接實例。你可以用另一個的SSMS實例，也可以在對象瀏覽器的工具欄新開一個。

然后在對象瀏覽器里右擊【安全性】【審核】下的【Sample Audit】，從彈出菜單里選擇【查看審核日志】。你會看到如插圖11.5的里列表。每行包含事件的一些信息，你可以拉伸下窗體看到更多的欄目信息。但當你點擊一條記錄時，數據會顯示在窗體下方，更容易閱讀。遺憾的是，列的格式并不好，如你在圖中所示。

插圖11.5：對Sample Audit查看它的審核日志

注意服務器審核自動捕獲修改審核日志的事件，這里是我啟用服務器審核的事件。即在圖里表顯示的第二行。

提示：

在它們捕獲并寫入事件前，你要啟用服務器審核和服務器審核規范。通常，你在創建它們的時候就會啟用它們。

創建一個數據庫審核規范

創建一個數據庫審核規范和創建服務器審核規范基本一樣。最大的區別是在日志里你可以捕獲的事件范圍。數據庫審核規范只能在單個數據庫捕獲事件，例如通過T-SQL語句訪問數據，修改數據庫對象的結構或許可，或執行存儲過程。其它最大的區別是你在數據庫對象里創建規范，而不是在對象瀏覽器里的服務器層級里創建。

假設你想創建審核記錄什么時候，什么用戶或進程在AdventureWorks2012數據庫里對Person.Person表執行了SELECT語句。在數據庫里，找到AdventureWorks2012數據庫，展開節點，在【安全性】【數據庫審核規范】右擊，從彈出菜單里選擇【新建數據庫審核規范】，這會打開【創建數據庫審核規范】對話框，修改自動生成的【名稱】為Person Data Access，【審核】里選擇Sample Audit，在操作里選擇如插圖11.6的內容。

插圖11.6：創建數據庫審核規范記錄在AdventureWorks2012數據庫里什么時候什么用戶查詢了Person.Person表。

你可以設置主體名稱為任何數據庫用戶或角色，包括公共數據庫角色來包含訪問數據庫的所有人。和服務器審核規范一樣，你可以增加多個操作。

一旦你創建了規范，記得啟用它。

同樣，你也可以使用T-SQL代碼來創建這個規范，有自動啟用的選項WITH子句。

1 USE AdventureWorks2014; 2 GO 3 4 CREATE DATABASE AUDIT SPECIFICATION [Person Data Access] 5 FOR SERVER AUDIT [Sample Audit] 6 ADD (SELECT ON OBJECT::Person.Person BY public) 7 WITH (STATE = ON); 8 GO

代碼11.5：創建數據庫審核規范記錄Person.Person被查詢

接下來，你可以通過執行對表的SELECT操作測試審核規范。一旦這樣做后，再次檢查下Sample Audit的審核日志，如插圖11.7所示。如你所見，日志包含了事件的完整信息，包括引起它的SQL語句。

插圖11.7：當查詢Person.Persion表時的審核日志

注意在圖中的【其它信息】是空的。這是因為生成日志條目的事件是直接的SELECT語句。但當SELECT語句在存儲過程里或其它代碼模塊里執行時，其它信息會包含T-SQL堆棧信息，以XML格式。這個用來識別查詢是語句還是代碼模塊實行非常有用。

-- Test it using a stored procedure CREATE PROCEDURE tempPerson AS BEGINSET NOCOUNT ON;SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, SuffixFROM Person.PersonORDER BY LastName, FirstName, MiddleName; END GO EXEC dbo.tempPerson;

重新運行后，我們再次來看日志。

插圖11.8：當通過存儲過程運行SELECT語句，會包含T-SQL堆棧信息

其它信息包含XML片段，包含引起審核日志條目的一些信息，架構和存儲過程名。

寫自定義審核信息

對每類事件，你不限于把默認信息寫入日志條目。你也可以創建用戶自定義審核事件，它讓你寫入任何你想寫的東西。代碼11.6展示了這個方法。當審核規范（不管服務器還是數據庫）被停用時，你才可以修改它。因此代碼首先停用它，然后它添加使用USER_DEFINED_AUDIT_GROUP的TestSQLServerAuditSpec到審核規范，并立即啟用它。最后一行代碼使用sp_audit_write系統存儲過程來寫入一些信息到審核日志。

USE master; GOALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpecWITH (STATE = OFF); GOALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpecADD (USER_DEFINED_AUDIT_GROUP)WITH (STATE = ON); GO-- Write some custom audit information EXEC sp_audit_write 9999, 1, N'Something in SQL Server succeeded!'

代碼11.6：使用USER_DEFINED_AUDIT_GROUP操作類型來寫自定義審核信息到服務器審核規范

當然，你也可以用SSMS的可視化用戶界面來修改服務器審核規范。

如果你執行剛才的代碼并查看日志，你會看到如插圖11.9的信息。你還是會看到審核事件的上下文，包括引起事件的T-SQL語句，但在底部，你會看到用戶定義的信息。

插圖11.9：包含用戶自定義信息的審核日志條目

　　提示：如果USER_DEFINED_AUDIT_GROUP服務器審核規范停用的話，SQL Server會忽略sp_audit_write。

篩選審核事件

SQL Server也包含篩選寫入審核日志事件的能力，這個和擴展事件使用同樣的篩選機制。篩選給你顆粒度的控制，SQL Server寫入審核日志的內容。但是SQL Server還是為你在規范里定義的事件生成所有的日志條目，然后使用篩選來覺得是否寫入事件到日志，理解這個非常重要。因此你還是有大量的日志事件生成，即使這些條目不寫入日志。這就是說還是為特定的對象創建審核事件，而不是篩選它們。

現在假設你想創建一個審核，記錄特定類型的所有事件，除用特定登陸關聯之外。代碼11.7第一部分在AdventureWorks2012數據庫里創建carol登錄和登錄的用戶。然后使用SUSER_ID方法獲得新用戶的主體ID（這里是267）。然后第二部分創建一個服務器審核使用WHERE子句篩選主體ID不為267的事件，并啟用服務器審核。現在carol在數據庫里有自由領域，在服務器審核事件里是安全的。

CREATE 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 307 based on SUSER_ID from previous statement USE master; GOCREATE SERVER AUDIT FilterAuditTO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000,ON_FAILURE = CONTINUE ) WHERE server_principal_id <> 267;ALTER SERVER AUDIT FilterAudit WITH (STATE = ON); GO

代碼11.7：在AdventureWorks2012數據庫里創建一個登錄和用戶，然后創建除這個用戶外的服務器審核

你也可以只用審核屬性對話框篩選事件，在對象瀏覽器里右擊服務器審核，在彈出菜單里選擇屬性。選擇【Filter】頁，如插圖11.10所示。注意，這里沒有WHERE語句。

插圖11.10：使用審核屬性對話框來添加篩選到服務器審核

小結

SQL Server的審核功能，讓你為數據庫實例和數據庫范圍里的各個不同事件，記錄日志。你可以使用SSMS里的用戶圖形界面或T-SQL代碼來創建不同審核對象，有很多選項來完成，例如篩選記錄，寫自定義信息到日志，和查看T-SQL的堆棧信息。審核是SQL Server一個重要的安全功能，在你的數據庫里幫你找出可疑的活動，這樣的話你可以消除掉你數據的安全漏洞。

原文鏈接

http://www.sqlservercentral.com/articles/Stairway+Series/130498/

總結

以上是生活随笔為你收集整理的SQL Server安全（11/11）：审核（Auditing）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。