[創意料理] 用 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
   ,N'TableName' AS local_table_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 時寫一筆記錄。

-- Suspect Table Create Script
CREATE TABLE [dbo].[Suspect] (
    [id] [INT] IDENTITY (1, 1) NOT NULL
   ,[host_name] [NVARCHAR](128) NULL
   ,[program_name] [NVARCHAR](128) NULL
   ,[client_net_address] [NVARCHAR](48) NULL
   ,[client_tcp_port] [INT] NULL
   ,[local_net_address] [NVARCHAR](48) NULL
   ,[local_tcp_port] [INT] NULL
   ,[local_database_name] [NVARCHAR](128) NULL
   ,[local_table_name] [NVARCHAR](128) NULL
   ,[login_name] [NVARCHAR](128) NULL
   ,[sql_text] [NTEXT] NULL
   ,[created_time] [datetime] NULL
   ,CONSTRAINT [PK_Suspect] PRIMARY KEY CLUSTERED
    (
    [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Suspect] ADD CONSTRAINT [DF_Suspect_created_time] DEFAULT (GETDATE()) FOR [created_time]
GO

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

觸發程序是包含在整個 INSERT、UPDATE、DELETE 開始到結束的行程裡面,所以如果觸發程序發生錯誤也會導致原本要執行的 SQL 語句失敗,除此之外它也會拉長回應時間,慎用!

參考資料

相關資源

C# 指南
ASP.NET 教學
ASP.NET MVC 指引
Azure SQL Database 教學
SQL Server 教學
Xamarin.Forms 教學