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

標籤：

    最近工作中有一個需求，就是某一個比較重要的業務表經常被莫名其妙的變更。在SQL Server中這類工作如果不事前捕獲記錄的話，無法做到。對於捕獲變更來說，可以考慮的選擇包括Trace，CDC。但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)

 

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表的Update，Insert，Delete操作。

 

    接下來嘗試修改資料庫Person.Address，在安全-審核下查看稽核線索，1所示。

圖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 Audit記錄資料庫變更