SQLSERVER2008新增的審核/審計(jì)功能

很多時(shí)候我們都需要對(duì)數(shù)據(jù)庫(kù)或者數(shù)據(jù)庫(kù)服務(wù)器實(shí)例進(jìn)行審核/審計(jì)

例如對(duì)失敗的登錄次數(shù)進(jìn)行審計(jì)，某個(gè)數(shù)據(jù)庫(kù)上的DDL語(yǔ)句進(jìn)行審計(jì)，某個(gè)數(shù)據(jù)庫(kù)表里面的delete語(yǔ)句進(jìn)行審計(jì)

事實(shí)上，我們這些審計(jì)的需求基本上都是為了一個(gè)目的：防黑客

上面的這些審計(jì)需求無非就是看一下有哪些人試圖入侵?jǐn)?shù)據(jù)庫(kù)服務(wù)器，入侵了之后是否有drop表，是否有delete數(shù)據(jù)

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

1、服務(wù)器級(jí)別DDL觸發(fā)器和數(shù)據(jù)庫(kù)級(jí)別的DDL觸發(fā)器（SQL2005及以上版本） 以及DML觸發(fā)器

2、自己手工從事務(wù)日志里讀取操作記錄，權(quán)威的書都會(huì)說事務(wù)日志不是審核工具，一般大型數(shù)據(jù)庫(kù)都會(huì)設(shè)置為簡(jiǎn)單模式，事務(wù)日志截?cái)?/p>

3、依靠SQLSERVER ERRORLOG來檢查登錄審核，導(dǎo)致SQLSERVER ERRORLOG login相關(guān)的日志泛濫 導(dǎo)致SQL排錯(cuò)造成困難

4、事件通知

5、更改跟蹤

6、變更數(shù)據(jù)捕獲（CDC）

我們一般都會(huì)把C2 審核跟蹤和登錄審核里面只限成功的登錄，以防止SQL ERRORLOG日志泛濫，因?yàn)榉?wù)器是很久才重啟一次的，如果不做修改很容易造成磁盤爆滿

--禁用C2?審核跟蹤和只限成功的登錄 EXEC?sys.sp_configure?N'c2?audit?mode',?N'0' GO RECONFIGURE?WITH?OVERRIDE GO USE?[master] GO EXEC?xp_instance_regwrite?N'HKEY_LOCAL_MACHINE',?N'Software\Microsoft\MSSQLServer\MSSQLServer',?N'AuditLevel',?REG_DWORD,?1 GO

SQLSERVER2008新增的審核功能

在sqlserver2008新增了審核功能，可以對(duì)服務(wù)器級(jí)別和數(shù)據(jù)庫(kù)級(jí)別的操作進(jìn)行審核/審計(jì)，事實(shí)上，事件通知、更改跟蹤、變更數(shù)據(jù)捕獲（CDC）

都不是用來做審計(jì)的，只是某些人亂用這些功能，也正因?yàn)閬y用這些功能導(dǎo)致踩坑

事件通知：性能跟蹤

更改跟蹤：用Sync Services來構(gòu)建偶爾連接的系統(tǒng)

變更數(shù)據(jù)捕獲（CDC）：數(shù)據(jù)倉(cāng)庫(kù)的ETL 中的數(shù)據(jù)抽取（背后使用logreader）

而審核是SQLSERVER專門針對(duì)數(shù)據(jù)庫(kù)安全的進(jìn)行的審核，記住，他是專門的！

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

審核對(duì)象

步驟一：創(chuàng)建審核對(duì)象，審核對(duì)象是跟保存路徑關(guān)聯(lián)的，所以如果你需要把審核操作日志保存到不同的路徑就需要?jiǎng)?chuàng)建不同的審核對(duì)象

我們把審核操作日志保存在文件系統(tǒng)里，在創(chuàng)建之前我們還要在相關(guān)路徑先創(chuàng)建好保存的文件夾，我們?cè)贒盤先創(chuàng)建sqlaudits文件夾，然后執(zhí)行下面語(yǔ)句

--創(chuàng)建審核對(duì)象之前需要切換到master數(shù)據(jù)庫(kù) USE?[master] GO CREATE?SERVER?AUDIT?MyFileAudit?TO?FILE(FILEPATH='D:\sqlaudits')?--這里指定文件夾不能指定文件，生成文件都會(huì)保存在這個(gè)文件夾 GO

實(shí)際上，我們?cè)趧?chuàng)建審核對(duì)象的同時(shí)可以指定審核選項(xiàng)，下面是相關(guān)腳本

把日志放在磁盤的好處是可以使用新增的TVF:sys.[fn_get_audit_file] 來過濾和排序?qū)徍藬?shù)據(jù)，如果把審核數(shù)據(jù)保存在Windows 事件日志里查詢起來非常麻煩

USE?[master] GO CREATE?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：指明每個(gè)審核日志文件的最大大小是4GB

MAX_ROLLOVER_FILES：指明滾動(dòng)文件數(shù)目，類似于SQL ERRORLOG，達(dá)到多少個(gè)文件之后刪除前面的歷史文件，這里是6個(gè)文件

ON_FAILURE：指明當(dāng)審核數(shù)據(jù)發(fā)生錯(cuò)誤時(shí)的操作，這里是繼續(xù)進(jìn)行審核，如果指定shutdown，那么將會(huì)shutdown整個(gè)實(shí)例

queue_delay：指明審核數(shù)據(jù)寫入的延遲時(shí)間，這里是1秒，最小值也是1秒，如果指定0表示是實(shí)時(shí)寫入，當(dāng)然性能也有一些影響

STATE：指明啟動(dòng)審核功能，STATE這個(gè)選項(xiàng)不能跟其他選項(xiàng)共用，所以只能單獨(dú)一句

在修改審核選項(xiàng)的時(shí)候，需要先禁用審核，再開啟審核

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

審核規(guī)范

在SQLSERVER審核里面有審核規(guī)范的概念，一個(gè)審核對(duì)象只能綁定一個(gè)審核規(guī)范，而一個(gè)審核規(guī)范可以綁定到多個(gè)審核對(duì)象

我們來看一下腳本

CREATE?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFile FOR?SERVER?AUDIT?MyFileAudit ADD?(failed_login_group), ADD?(successful_login_group) WITH?(STATE=ON) GO CREATE?SERVER?AUDIT?MyAppAudit?TO?APPLICATION_LOG GO ALTER?SERVER?AUDIT?MyAppAudit?WITH(STATE?=ON) ALTER?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFile?WITH?(STATE=OFF) GO ALTER?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFile FOR?SERVER?AUDIT?MyAppAudit ADD?(failed_login_group), ADD?(successful_login_group) WITH?(STATE=ON) GO

我們創(chuàng)建一個(gè)服務(wù)器級(jí)別的審核規(guī)范CaptureLoginsToFile，然后再創(chuàng)建多一個(gè)審核對(duì)象MyAppAudit ，這個(gè)審核對(duì)象會(huì)把審核日志保存到Windows事件日志的應(yīng)用程序日志里

我們禁用審核規(guī)范CaptureLoginsToFile，修改審核規(guī)范CaptureLoginsToFile屬于審核對(duì)象MyAppAudit ，修改成功

而如果要把多個(gè)審核規(guī)范綁定到同一個(gè)審核對(duì)象則會(huì)報(bào)錯(cuò)

CREATE?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFileA FOR?SERVER?AUDIT?MyFileAudit ADD?(failed_login_group), ADD?(successful_login_group) WITH?(STATE=ON) GO CREATE?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFileB FOR?SERVER?AUDIT?MyFileAudit ADD?(failed_login_group), ADD?(successful_login_group) WITH?(STATE=ON) GO --消息?33230，級(jí)別?16，狀態(tài)?1，第?86?行 --審核?'MyFileAudit'?的審核規(guī)范已經(jīng)存在。

審核服務(wù)器級(jí)別事件

審核服務(wù)級(jí)別事件，我們一般用得最多的就是審核登錄失敗的事件，下面的腳本就是審核登錄成功事件和登錄失敗事件

CREATE?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFile FOR?SERVER?AUDIT?MyFileAudit ADD?(failed_login_group), ADD?(successful_login_group) WITH?(STATE=ON) GO

修改審核規(guī)范

--跟審核對(duì)象一樣，更改審核規(guī)范時(shí)必須將其禁用 ALTER?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFile?WITH?(STATE?=OFF) ALTER?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFile ADD?(login_change_password_gourp), DROP?(successful_login_group) ALTER?SERVER?AUDIT?SPECIFICATION?CaptureLoginsToFile?WITH?(STATE?=ON) GO

審核操作組

每個(gè)審核操作組對(duì)應(yīng)一種操作，在SQLSERVER2008里一共有35個(gè)操作組，包括備份和還原操作，數(shù)據(jù)庫(kù)所有權(quán)的更改，從服務(wù)器和數(shù)據(jù)庫(kù)角色中添加或刪除登錄用戶

添加審核操作組的只需在審核規(guī)范里使用ADD，下面語(yǔ)句添加了登錄用戶修改密碼操作的操作組

ADD?(login_change_password_gourp)

這里說一下服務(wù)器審核的內(nèi)部實(shí)際上使用的是SQL2008新增的擴(kuò)展事件里面的其中一個(gè)package：SecAudit package，當(dāng)然他內(nèi)部也是使用擴(kuò)展事件來收集服務(wù)器信息

審核數(shù)據(jù)庫(kù)級(jí)別事件

數(shù)據(jù)庫(kù)審核規(guī)范存在于他們的數(shù)據(jù)庫(kù)中，不能審核tempdb中的數(shù)據(jù)庫(kù)操作

CREATE DATABASE AUDIT SPECIFICATION和ALTER DATABASE AUDIT SPECIFICATION

工作方式跟服務(wù)器審核規(guī)范一樣

在SQLSERVER2008里一共有15個(gè)數(shù)據(jù)庫(kù)級(jí)別的操作組

7個(gè)數(shù)據(jù)庫(kù)級(jí)別的審核操作是：select ,insert,update,delete,execute,receive,references

相關(guān)腳本如下：

--創(chuàng)建審核對(duì)象 USE?[master] GO CREATE?SERVER?AUDIT?MyDBFileAudit?TO?FILE(FILEPATH='D:\sqldbaudits')? GO ALTER??SERVER?AUDIT??MyDBFileAudit?WITH?(STATE=ON) GO --創(chuàng)建數(shù)據(jù)庫(kù)級(jí)別審核規(guī)范 USE?[sss] GO CREATE?DATABASE?AUDIT?SPECIFICATION?CaptureDBActionToEventLog FOR?SERVER?AUDIT?MyDBFileAudit ADD?(database_object_change_group), ADD?(SELECT?,INSERT,UPDATE,DELETE?ON?schema::dbo???BY?PUBLIC) WITH?(STATE?=ON)

查看審核事件

被記錄到文件系統(tǒng)的審核文件不是存儲(chǔ)在可以利用記事本打開的文本文件中，而是采用二進(jìn)制文件的方式

我們有兩種方法查看審核日志

方法一：對(duì)象資源管理器-》安全性-》審核-》選中某個(gè)審核對(duì)象-》右鍵-》查看審核日志

審核項(xiàng)目包括有：日期、時(shí)間戳記、服務(wù)器實(shí)例名稱、操作ID、類類型、序列號(hào)、成功或失敗、列權(quán)限、數(shù)據(jù)庫(kù)主體ID、服務(wù)器主體名稱、

服務(wù)器主體SID、被執(zhí)行的（或嘗試）的實(shí)際語(yǔ)句等等

方法二：使用新的表值函數(shù)sys.[fn_get_audit_file]()

此函數(shù)接受一個(gè)或多個(gè)審核文件的參數(shù)（使用通配符模式匹配）

并利用另外兩個(gè)附加參數(shù)可以指定要處理的起始文件，以及開始讀取審核的已知偏移位置

這兩個(gè)參數(shù)都是可選的，但依然必須使用關(guān)鍵字default指定，此函數(shù)隨后從文件中讀取二進(jìn)制數(shù)據(jù)，并將格式化這些審核項(xiàng)目

服務(wù)器級(jí)別審核

根據(jù)最近時(shí)間的那個(gè)sqlaudit文件，查詢這個(gè)文件里面的信息

SELECT??[event_time]?AS?'觸發(fā)審核的日期和時(shí)間'?, ????????sequence_number?AS?'單個(gè)審核記錄中的記錄順序'?, ????????action_id?AS?'操作的?ID'?, ????????succeeded?AS?'觸發(fā)事件的操作是否成功'?, ????????permission_bitmask?AS?'權(quán)限掩碼'?, ????????is_column_permission?AS?'是否為列級(jí)別權(quán)限'?, ????????session_id?AS?'發(fā)生該事件的會(huì)話的?ID'?, ????????server_principal_id?AS?'執(zhí)行操作的登錄上下文?ID'?, ????????database_principal_id?AS?'執(zhí)行操作的數(shù)據(jù)庫(kù)用戶上下文?ID'?, ????????target_server_principal_id?AS?'執(zhí)行?GRANT/DENY/REVOKE?操作的服務(wù)器主體'?, ????????target_database_principal_id?AS?'執(zhí)行?GRANT/DENY/REVOKE?操作的數(shù)據(jù)庫(kù)主體'?, ????????object_id?AS?'發(fā)生審核的實(shí)體的?ID(服務(wù)器對(duì)象，DB,數(shù)據(jù)庫(kù)對(duì)象，架構(gòu)對(duì)象)'?, ????????class_type?AS?'可審核實(shí)體的類型'?, ????????session_server_principal_name?AS?'會(huì)話的服務(wù)器主體'?, ????????server_principal_name?AS?'當(dāng)前登錄名'?, ????????server_principal_sid?AS?'當(dāng)前登錄名?SID'?, ????????database_principal_name?AS?'當(dāng)前用戶'?, ????????target_server_principal_name?AS?'操作的目標(biāo)登錄名'?, ????????target_server_principal_sid?AS?'目標(biāo)登錄名的?SID'?, ????????target_database_principal_name?AS?'操作的目標(biāo)用戶'?, ????????server_instance_name?AS?'審核的服務(wù)器實(shí)例的名稱'?, ????????database_name?AS?'發(fā)生此操作的數(shù)據(jù)庫(kù)上下文'?, ????????schema_name?AS?'此操作的架構(gòu)上下文'?, ????????object_name?AS?'審核的實(shí)體的名稱'?, ????????statement?AS?'TSQL?語(yǔ)句（如果存在）'?, ????????additional_information?AS?'單個(gè)事件的唯一信息，以?XML?的形式返回'?, ????????file_name?AS?'記錄來源的審核日志文件的路徑和名稱'?, ????????audit_file_offset?AS?'包含審核記錄的文件中的緩沖區(qū)偏移量'?, ????????user_defined_event_id?AS?'作為?sp_audit_write?參數(shù)傳遞的用戶定義事件?ID'?, ????????user_defined_information?AS?'于記錄用戶想要通過使用?sp_audit_write?存儲(chǔ)過程記錄在審核日志中的任何附加信息' 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'

和審核相關(guān)的視圖

--查詢審核相關(guān)視圖 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]

刪除相關(guān)對(duì)象

--刪除順序 --刪除數(shù)據(jù)庫(kù)審核規(guī)范 USE?[sss] GO ALTER?DATABASE?AUDIT?SPECIFICATION?[CaptureDBActionToEventLog]?WITH?(STATE=OFF) GO DROP?DATABASE?AUDIT?SPECIFICATION?[CaptureDBActionToEventLog] GO --刪除服務(wù)器審核規(guī)范 USE?[master] GO ALTER?SERVER??AUDIT?SPECIFICATION?[CaptureLoginsToFile]?WITH?(STATE=OFF) GO DROP?SERVER?AUDIT?SPECIFICATION?[CaptureLoginsToFile] GO --刪除審核對(duì)象 ALTER?SERVER?AUDIT?[MyFileAudit]?WITH?(STATE=OFF) GO ALTER?SERVER?AUDIT?[MyAppAudit]?WITH?(STATE=OFF) GO ALTER?SERVER?AUDIT?[MyEventLogAudit]?WITH?(STATE=OFF) GO DROP?SERVER?AUDIT?[MyAppAudit] GO DROP?SERVER?AUDIT?[MyFileAudit] GO DROP?SERVER?AUDIT?[MyEventLogAudit] GO

總結(jié)

本文概括介紹了SQLSERVER2008新增的審核功能，在SQLSERVER相關(guān)論壇里面“審核”這個(gè)話題是大家問得比較多的

希望通過這篇文章，能讓大家認(rèn)識(shí)新增的審核功能，在生產(chǎn)環(huán)境里面遇到問題也可以互相交流

而審核功能最大的好處是：你使用自建審計(jì)表來保存審計(jì)數(shù)據(jù)，如果聰明的黑客攻破你的數(shù)據(jù)庫(kù)實(shí)例，他自然可以把你的那個(gè)審計(jì)表

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

很弱，黑客有機(jī)會(huì)把磁盤文件刪除掉，否則依然有可能查出黑客的蛛絲馬跡進(jìn)行預(yù)防！！

SQL 數(shù)據(jù)庫(kù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。