使用SQL Server Audit記錄資料庫變更

最近工作中有一個需求就是某一個比較重要的業務表經常被莫名其妙的變更。在SQL Server中這類工作如果不事前捕獲記錄的話無法做到。對于捕獲變更來說可以考慮的選擇包括TraceCDC。但Trace的成本比較大對于負載量較高的系統并不合适而CDC需要影響業務庫是以SQL Server Audit就是一個比較好的選擇。

    在SQL Server中如果隻是希望獲得表的更新時間隻需要看表的聚集索引的最後更新時間即可代碼如下

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,* 

FROM sys.dm_db_index_usage_stats 

WHERE database_id = DB_ID( 'DateBaseName') 

AND OBJECT_ID=OBJECT_ID('TableName')

    但這種方式并不能看到由某人在某個時間修改了某個表在此使用Server Audit。Server Audit底層采用的是擴充事件且存儲結構可以以單獨檔案獨立于使用者庫是以不僅性能較好也不會對使用者庫産生影響。

    下面是啟用稽核的T-SQL代碼

USE master 

CREATE SERVER AUDIT audit1 TO FILE (FILEPATH='D:\SQLAudit') 

USE AdventureWorks2012 

CREATE DATABASE AUDIT SPECIFICATION SerialPic FOR SERVER AUDIT audit1 

ADD(UPDATE,INSERT,DELETE ON Person.Address by dbo)

   上述代碼首先建立伺服器級别的稽核并存入D\SQLAudit中然後對應建立資料庫級别的稽核。在資料庫級别的稽核中跟蹤Person.Address表的UpdateInsertDelete操作。

    接下來嘗試修改資料庫Person.Address在安全-稽核下檢視稽核日志如圖1所示。

image

圖1.檢視稽核日志

    結果如圖2所示。

圖2.資料庫稽核記錄

    這樣就可以看到誰在什麼時間曾經對該表做過哪些修改。當然除了UI方式也可以通過T-SQL方式檢視稽核記錄。

SELECT * FROM 

fn_get_audit_file('D:\SQLAudit\audit1_B8A7821A-D735-446D-B6FA-DF582AB80375_0_130648999540780000.sqlaudit', default, default)

分類: SQL Server安全

本文轉自CareySon部落格園部落格原文連結http://www.cnblogs.com/CareySon/p/4204027.html如需轉載請自行聯系原作者