在保密你的伺服器和數據，防備當前複雜的攻擊，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 SQLServerAudit
    TO APPLICATION_LOG
    WITH ( 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
BEGIN
    SET NOCOUNT ON;

    SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix
        FROM Person.Person
    ORDER 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;
GO

ALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec
    WITH (STATE = OFF);
GO

ALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec
    ADD (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;
GO

CREATE SERVER AUDIT FilterAudit
    TO 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/