[創意料理] 用 SQL Server 的 Trigger 搭配 DMV 來抓增刪改資料表的兇手

系統中的程序(Applicaion、SQL Agent Job、Stored Procedure、...)一多,尤其大部分中小企業的資料庫增刪改並沒有得到良好的管理,重複的操作一直在做,資料若是正確的那大家相安無事,如果資料發生錯誤的時候,開發人員就雞飛狗跳了,眾多程序重複增刪改相同的資料,讓追查錯誤來源變成一件苦差事,萬一錯誤的資料又跟錢有關,那更是壓力山大,還好科技始終來自於人性,利用 SQL Server 的 Trigger 加上 DMV(Dynamic Management Views)可以幫助我們找到線索。

Trigger(觸發程序)

建立 Trigger 的方式很簡單,只要在它上面按右鍵選擇「新增觸發程序」,SSMS 就會幫我們把產生 Trigger 的語法開好。

接下來我們輸入「Trigger 名稱」、「目標資料表名稱」、「Trigger 的事件」,這樣 Trigger 就會在 TriggerTest 這個資料表被 INSERT、UPDATE、DELETE 之後被觸發。

利用 DMV 來撒網

DMV 可以幫助我們取得當前的伺服器狀態,我們利用 sys.dm_exec_connectionssys.dm_exec_sessionssys.dm_exec_requests 這三個 View,以及 sys.dm_exec_input_buffer() 這個 Function 並帶入 @@SPID 參數來幫我們找到嫌疑犯的機器名稱客戶端名稱來源位址登入的 SQL 帳號執行增刪改的 SQL 語句、...等線索。

SELECT
    s.[host_name]
   ,s.program_name
   ,c.client_net_address
   ,c.client_tcp_port
   ,c.local_net_address
   ,c.local_tcp_port
   ,d.[Name] AS local_database_name
   ,s.login_name
   ,(SELECT
            ib.event_info
        FROM sys.dm_exec_input_buffer(r.session_id, r.request_id) ib)
    AS sql_text
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
    ON s.session_id = c.session_id
INNER JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
INNER JOIN sys.databases d
    ON r.database_id = d.database_id
WHERE s.session_id = @@spid;

鎖定被害人

由於 Trigger 是針對資料表的增刪改做觸發,所以如果是特定的資料列,我們必須撰寫篩選的邏輯來鎖定被害人,因此我鎖定的是 TriggerTest 資料表內 Id = 1 的 Name 欄位,當它被 UPDATE 時寫一筆記錄。

之後我就可以從搜集到的訊息記錄中,去縮小搜索範圍,相當有幫助。

參考資料

 


   
  如果這篇文章值得你請我喝飲料
可以使用街口支付斗內我一杯