使用SQLServerAudit記錄資料庫變更

範大腳腳發表於2017-12-20
SQLServer資料庫
最近工作中有一個需求就是某一個比較重要的業務表經常被莫名其妙的變更。在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)
 
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)
 
   上述程式碼首先建立伺服器級別的稽核並存入DSQLAudit中然後對應建立資料庫級別的稽核。在資料庫級別的稽核中跟蹤Person.Address表的UpdateInsertDelete操作。
 
    接下來嘗試修改資料庫Person.Address在安全-稽核下檢視稽核日誌如圖1所示。
image
圖1.檢視稽核日誌
 
    結果如圖2所示。
image
圖2.資料庫稽核記錄
 
    這樣就可以看到誰在什麼時間曾經對該表做過哪些修改。當然除了UI方式也可以通過T-SQL方式檢視稽核記錄。
SELECT * FROM 
fn_get_audit_file(`D:SQLAuditaudit1_B8A7821A-D735-446D-B6FA-DF582AB80375_0_130648999540780000.sqlaudit`, default, default)
分類: SQL Server安全
本文轉自CareySon部落格園部落格原文連結http://www.cnblogs.com/CareySon/p/4204027.html如需轉載請自行聯絡原作者


相關文章