SQLSERVER2008新增的審核/審計功能

標籤：blog   http   io   ar   os   使用   for   sp   strong   

很多時候我們都需要對資料庫或者資料庫伺服器執行個體進行審核/審計

例如對失敗的登入次數進行審計，某個資料庫上的DDL語句進行審計，某個資料庫表裡面的delete語句進行審計

事實上，我們這些審計的需求基本上都是為了一個目的：防駭客

 

上面的這些審計需求無非就是看一下有哪些人試圖入侵資料庫伺服器，入侵了之後是否有drop表，是否有delete資料

在SQLSERVER2008及以前版本可以選擇的方案有

1、伺服器層級DDL觸發器和資料庫層級的DDL觸發器（SQL2005及以上版本） 以及DML觸發器

2、從交易記錄裡讀取操作記錄，權威的書都會說交易記錄不是審核工具，一般大型資料庫都會設定為簡單模式，交易記錄截斷

3、依靠SQLSERVER ERRORLOG來檢查登入審核，導致SQLSERVER ERRORLOG login相關的日誌泛濫 導致SQL排錯造成困難

4、事件通知：http://www.cnblogs.com/gaizai/p/3473553.html
5、變更追蹤：http://www.cnblogs.com/gaizai/p/3482579.html
6、變更資料擷取（CDC）：http://www.cnblogs.com/gaizai/p/3479731.html

 

我們一般都會把C2 審核跟蹤和登入審核裡面只限成功的登入，以防止SQL ERRORLOG日誌泛濫，因為伺服器是很久才重啟一次的，如果不做修改很容易造成磁碟爆滿

--禁用C2 審核跟蹤和只限成功的登入EXEC sys.sp_configure N‘c2 audit mode‘, N‘0‘GORECONFIGURE WITH OVERRIDEGOUSE [master]GOEXEC xp_instance_regwrite N‘HKEY_LOCAL_MACHINE‘, N‘Software\Microsoft\MSSQLServer\MSSQLServer‘, N‘AuditLevel‘, REG_DWORD, 1GO

 

SQLSERVER2008新增的審核功能

在sqlserver2008新增了審核功能，可以對伺服器層級和資料庫層級的操作進行審核/審計，本人覺得審核功能是對上面眾多解決方案的統一

我們看一下審核的使用方法

 

 

審核對象

步驟一：建立審核對象，審核對象是跟儲存路徑關聯的，所以如果你需要把審核動作記錄儲存到不同的路徑就需要建立不同的審核對象

我們把審核動作記錄儲存在檔案系統裡，在建立之前我們還要在相關路徑先建立好儲存的檔案夾，我們在D盤先建立sqlaudits檔案夾，然後執行下面語句

--建立審核對象之前需要切換到master資料庫USE [master]GOCREATE SERVER AUDIT MyFileAudit TO FILE(FILEPATH=‘D:\sqlaudits‘) --這裡指定檔案夾不能指定檔案，組建檔案都會儲存在這個檔案夾GO

 

實際上，我們在建立審核對象的同時可以指定審核選項，下面是相關指令碼

把日誌放在磁碟的好處是可以使用新增的TVF:sys.[fn_get_audit_file] 來過濾和排序審核心數據，如果把審核心數據儲存在Windows 事件記錄裡查詢起來非常麻煩

USE [master]GOCREATE SERVER AUDIT MyFileAudit TO FILE(FILEPATH=‘D:\sqlaudits‘,MAXSIZE=4GB,MAX_ROLLOVER_FILES=6) WITH (ON_FAILURE=CONTINUE,QUEUE_DELAY=1000);ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)

MAXSIZE：指明每個稽核線索檔案的最大大小是4GB

MAX_ROLLOVER_FILES：指明滾動檔案數目，類似於SQL ERRORLOG，達到多少個檔案之後刪除前面的曆史檔案，這裡是6個檔案

ON_FAILURE：指明當審核心數據發生錯誤時的操作，這裡是繼續進行審核，如果指定shutdown，那麼將會shutdown整個執行個體

queue_delay：指明審核心數據寫入的延遲時間，這裡是1秒，最小值也是1秒，如果指定0表示是即時寫入，當然效能也有一些影響

STATE：指明啟動審核功能，STATE這個選項不能跟其他選項共用，所以只能單獨一句

 

 

在修改審核選項的時候，需要先禁用審核，再開啟審核

ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)ALTER SERVER AUDIT MyFileAudit WITH(QUEUE_DELAY =1000)ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)

 

審核規範

在SQLSERVER審核裡面有審核規範的概念，一個審核對象只能綁定一個審核規範，而一個審核規範可以綁定到多個審核對象

我們來看一下指令碼

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileFOR SERVER AUDIT MyFileAuditADD (failed_login_group),ADD (successful_login_group)WITH (STATE=ON)GOCREATE SERVER AUDIT MyAppAudit TO APPLICATION_LOGGOALTER SERVER AUDIT MyAppAudit WITH(STATE =ON)ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)GOALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFileFOR SERVER AUDIT MyAppAuditADD (failed_login_group),ADD (successful_login_group)WITH (STATE=ON)GO

我們建立一個伺服器層級的審核規範CaptureLoginsToFile，然後再建立多一個審核對象MyAppAudit ，這個審核對象會把稽核線索儲存到Windows事件記錄的應用程式記錄檔裡

我們禁用審核規範CaptureLoginsToFile，修改審核規範CaptureLoginsToFile屬於審核對象MyAppAudit ，修改成功

 

而如果要把多個審核規範綁定到同一個審核對象則會報錯

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileAFOR SERVER AUDIT MyFileAuditADD (failed_login_group),ADD (successful_login_group)WITH (STATE=ON)GOCREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileBFOR SERVER AUDIT MyFileAuditADD (failed_login_group),ADD (successful_login_group)WITH (STATE=ON)GO--訊息 33230，層級 16，狀態 1，第 86 行--審核 ‘MyFileAudit‘ 的審核規範已經存在。

 

這裡要說一下 ：審核對象和審核規範的修改 ，無論是審核對象還是審核規範，在修改他們的相關參數之前，他必須要先禁用，後修改，再啟用

--禁用審核對象ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)--禁用伺服器級審核規範ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)GO--禁用資料庫級審核規範ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=OFF)GO--相關修改選項操作--啟用審核對象ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)--啟用伺服器級審核規範ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=ON)GO--啟用資料庫級審核規範ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=ON)GO

 

審核伺服器層級事件

審核服務等級事件，我們一般用得最多的就是審核登入失敗的事件，下面的指令碼就是審核登入成功事件和登入失敗事件

CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileFOR SERVER AUDIT MyFileAuditADD (failed_login_group),ADD (successful_login_group)WITH (STATE=ON)GO

 

修改審核規範

--跟審核對象一樣，更改審核規範時必須將其禁用ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE =OFF)ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFileADD (login_change_password_gourp),DROP (successful_login_group)ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE =ON)GO

 

審核操作組

每個審核操作組對應一種操作，在SQLSERVER2008裡一共有35個操作組，包括備份與還原操作，資料庫所有權的更改，從伺服器和資料庫角色中添加或刪除登入使用者

添加審核操作組的只需在審核規範裡使用ADD，下面語句添加了登入使用者修改密碼操作的操作組

ADD (login_change_password_gourp)

 

 

 

審核心數據庫層級事件 

資料庫審核規範存在於他們的資料庫中，不能審核tempdb中的資料庫操作

CREATE DATABASE AUDIT SPECIFICATION和ALTER DATABASE AUDIT SPECIFICATION

工作方式跟伺服器審核規範一樣

在SQLSERVER2008裡一共有15個資料庫層級的操作組
7個資料庫層級的審核操作是：select ,insert,update,delete,execute,receive,references

 

相關指令碼如下：

--建立審核對象USE [master]GOCREATE SERVER AUDIT MyDBFileAudit TO FILE(FILEPATH=‘D:\sqldbaudits‘) GOALTER  SERVER AUDIT  MyDBFileAudit WITH (STATE=ON)GO--建立資料庫層級審核規範USE [sss]GOCREATE DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLogFOR SERVER AUDIT MyDBFileAuditADD (database_object_change_group),ADD (SELECT ,INSERT,UPDATE,DELETE ON schema::dbo   BY PUBLIC)WITH (STATE =ON)

 

我們先在D盤建立sqldbaudits檔案夾

第一個操作組對資料庫中所有對象的DDL語句create，alter，drop等進行記錄
第二個語句監視由任何public使用者（也就是所有使用者）對dbo架構的任何對象所做的DML操作

 

建立完畢之後可以在SSMS裡看到相關的審核

資料庫審核規範

伺服器審核規範和審核對象

查看審核事件

被記錄到檔案系統的審核檔案不是儲存在可以利用記事本開啟的文字檔中，而是採用二進位檔案的方式

 

 

這裡說一個，當磁碟空間不足的時候是可以直接刪除這些SQLAUDIT檔案

如果使用DDL觸發器的方法：http://www.cnblogs.com/gaizai/p/3363220.html?ADUIN=1815357042&ADSESSION=1387155615&ADTAG=CLIENT.QQ.5275_.0&ADPUBNO=26274

一般都會在資料庫裡頭建立一張表來儲存審計資料，但是當表資料量達到很多的時候，DBA也需要去維護這張表

工作量又增加了，可能你會說，我需要審計的項目不多，所以審計的資料也不會太多，但對於某些大公司來說

他們要審計的資料是非常多的，有些需要歸檔，而有些不需要歸檔

 

對於不需要歸檔審計資料的情況，我比較喜歡這種方式，當磁碟容量不夠的時候把最老的那個審計檔案刪除掉

 

我們有兩種方法查看稽核線索

方法一：物件總管-》安全性-》審核-》選中某個審核對象-》右鍵-》查看稽核線索

 

審核項目包括有：日期、時間戳記、伺服器執行個體名稱、操作ID、類類型、序號、成功或失敗、列許可權、資料庫主體ID、伺服器主體名稱、

伺服器主體SID、被執行的（或嘗試）的實際語句等等

 

方法二：使用新的資料表值函式sys.[fn_get_audit_file]()

此函數接受一個或多個審核檔案的參數（使用萬用字元模式比對）

並利用另外兩個附加參數可以指定要處理的起始檔案，以及開始讀取審核的已知位移位置

這兩個參數都是可選的，但依然必須使用關鍵字default指定，此函數隨後從檔案中讀取位元據，並將格式化這些審核項目

 

伺服器層級審核

根據最近時間的那個sqlaudit檔案，查詢這個檔案裡面的資訊

SELECT  [event_time] AS ‘觸發審核的日期和時間‘ ,        sequence_number AS ‘單個審核記錄中的記錄順序‘ ,        action_id AS ‘操作的 ID‘ ,        succeeded AS ‘觸發事件的操作是否成功‘ ,        permission_bitmask AS ‘許可權掩碼‘ ,        is_column_permission AS ‘是否為列層級許可權‘ ,        session_id AS ‘發生該事件的會話的 ID‘ ,        server_principal_id AS ‘執行操作的登入內容識別碼‘ ,        database_principal_id AS ‘執行操作的資料庫使用者內容識別碼‘ ,        target_server_principal_id AS ‘執行 GRANT/DENY/REVOKE 操作的伺服器主體‘ ,        target_database_principal_id AS ‘執行 GRANT/DENY/REVOKE 操作的資料庫主體‘ ,        object_id AS ‘發生審核的實體的 ID(伺服器對象，DB,資料庫物件，架構對象)‘ ,        class_type AS ‘可審核實體的類型‘ ,        session_server_principal_name AS ‘會話的伺服器主體‘ ,        server_principal_name AS ‘當前登入名稱‘ ,        server_principal_sid AS ‘當前登入名稱 SID‘ ,        database_principal_name AS ‘目前使用者‘ ,        target_server_principal_name AS ‘操作的目標登入名稱‘ ,        target_server_principal_sid AS ‘目標登入名稱的 SID‘ ,        target_database_principal_name AS ‘操作的目標使用者‘ ,        server_instance_name AS ‘審核的伺服器執行個體的名稱‘ ,        database_name AS ‘發生此操作的資料庫上下文‘ ,        schema_name AS ‘此操作的架構上下文‘ ,        object_name AS ‘審核的實體的名稱‘ ,        statement AS ‘TSQL 陳述式（如果存在）‘ ,        additional_information AS ‘單個事件的唯一資訊，以 XML 的形式返回‘ ,        file_name AS ‘記錄來源的稽核線索檔案的路徑和名稱‘ ,        audit_file_offset AS ‘包含審核記錄的檔案中的緩衝區位移量‘ ,        user_defined_event_id AS ‘作為 sp_audit_write 參數傳遞的使用者定義事件 ID‘ ,        user_defined_information AS ‘於記錄使用者想要通過使用 sp_audit_write 預存程序記錄在稽核線索中的任何附加資訊‘FROM    sys.[fn_get_audit_file](‘D:\sqlaudits\MyFileAudit_F0BCDC6F-0A89-459D-B345-9DDEB036CC39_0_130595725124220000.sqlaudit‘,                                DEFAULT, DEFAULT)WHERE   [event_time] BETWEEN ‘2014-11-04 11:02:00‘                     AND     ‘2014-11-04 11:18:00‘ 

 

 

資料庫層級審核

先執行下面指令碼查詢一些資料

USE [sss]GOSELECT * FROM [dbo].[nums]

SELECT  [event_time] AS ‘觸發審核的日期和時間‘ ,        sequence_number AS ‘單個審核記錄中的記錄順序‘ ,        action_id AS ‘操作的 ID‘ ,        succeeded AS ‘觸發事件的操作是否成功‘ ,        permission_bitmask AS ‘許可權掩碼‘ ,        is_column_permission AS ‘是否為列層級許可權‘ ,        session_id AS ‘發生該事件的會話的 ID‘ ,        server_principal_id AS ‘執行操作的登入內容識別碼‘ ,        database_principal_id AS ‘執行操作的資料庫使用者內容識別碼‘ ,        target_server_principal_id AS ‘執行 GRANT/DENY/REVOKE 操作的伺服器主體‘ ,        target_database_principal_id AS ‘執行 GRANT/DENY/REVOKE 操作的資料庫主體‘ ,        object_id AS ‘發生審核的實體的 ID(伺服器對象，DB,資料庫物件，架構對象)‘ ,        class_type AS ‘可審核實體的類型‘ ,        session_server_principal_name AS ‘會話的伺服器主體‘ ,        server_principal_name AS ‘當前登入名稱‘ ,        server_principal_sid AS ‘當前登入名稱 SID‘ ,        database_principal_name AS ‘目前使用者‘ ,        target_server_principal_name AS ‘操作的目標登入名稱‘ ,        target_server_principal_sid AS ‘目標登入名稱的 SID‘ ,        target_database_principal_name AS ‘操作的目標使用者‘ ,        server_instance_name AS ‘審核的伺服器執行個體的名稱‘ ,        database_name AS ‘發生此操作的資料庫上下文‘ ,        schema_name AS ‘此操作的架構上下文‘ ,        object_name AS ‘審核的實體的名稱‘ ,        statement AS ‘TSQL 陳述式（如果存在）‘ ,        additional_information AS ‘單個事件的唯一資訊，以 XML 的形式返回‘ ,        file_name AS ‘記錄來源的稽核線索檔案的路徑和名稱‘ ,        audit_file_offset AS ‘包含審核記錄的檔案中的緩衝區位移量‘ ,        user_defined_event_id AS ‘作為 sp_audit_write 參數傳遞的使用者定義事件 ID‘ ,        user_defined_information AS ‘於記錄使用者想要通過使用 sp_audit_write 預存程序記錄在稽核線索中的任何附加資訊‘FROM    sys.[fn_get_audit_file](‘D:\sqldbaudits\MyDBFileAudit_698BA060-CC40-4A3C-B19D-12B370712404_0_130595753193920000.sqlaudit‘,                                DEFAULT, DEFAULT)

 

將稽核線索儲存到檔案系統的好處就是可以使用TVP裡通過where 和order by對審核心數據進行篩選和排序

 

和審核相關的視圖

--查詢審核相關視圖SELECT * FROM sys.[server_file_audits]SELECT * FROM sys.[server_audit_specifications]SELECT * FROM sys.[server_audit_specification_details]SELECT * FROM sys.[database_audit_specifications]SELECT * FROM sys.[database_audit_specification_details]SELECT * FROM sys.[dm_server_audit_status]SELECT * FROM sys.[dm_audit_actions]SELECT * FROM sys.[dm_audit_class_type_map]

 

刪除相關對象

--刪除順序--刪除資料庫審核規範USE [sss]GOALTER DATABASE AUDIT SPECIFICATION [CaptureDBActionToEventLog] WITH (STATE=OFF)GODROP DATABASE AUDIT SPECIFICATION [CaptureDBActionToEventLog]GO--刪除伺服器審核規範USE [master]GOALTER SERVER  AUDIT SPECIFICATION [CaptureLoginsToFile] WITH (STATE=OFF)GODROP SERVER AUDIT SPECIFICATION [CaptureLoginsToFile]GO--刪除審核對象ALTER SERVER AUDIT [MyFileAudit] WITH (STATE=OFF)GOALTER SERVER AUDIT [MyAppAudit] WITH (STATE=OFF)GOALTER SERVER AUDIT [MyEventLogAudit] WITH (STATE=OFF)GODROP SERVER AUDIT [MyAppAudit]GODROP SERVER AUDIT [MyFileAudit]GODROP SERVER AUDIT [MyEventLogAudit]GO

 

總結

本文概括介紹了SQLSERVER2008新增的審核功能，在SQLSERVER論壇裡面“審核”這個話題是大家問得比較多的

希望通過這篇文章，能讓大家認識新增的審核功能，在生產環境裡面遇到問題也可以互相交流

 

而審核功能最大的好處是：你使用自建審計表來儲存審計資料，如果聰明的駭客攻破你的資料庫執行個體，他自然可以把你的那個審計表

drop掉，你同樣查不出駭客的任何蛛絲馬跡，而審核不同，他把審核心數據放在SQLSERVER外面，除非你們公司的SA和DBA的安全意識

很弱，駭客有機會把磁碟檔案刪除掉，否則依然有可能查出駭客的蛛絲馬跡進行預防！！

SQLSERVER2008新增的審核/審計功能