黃昏的甘蔗

 當有異常存取資料庫的時後，中控台會發出警報聲，並迅速的找到入侵者所在的位置，然後警衛就會火速到達與入侵者搏鬥，直到入侵者跳下大樓或從水漩渦中逃離....Mission Impossible 都是這麼演的。但實際上的資料庫被異常存取（入侵者用合法使用者的權限存取）通常都是毫無知覺的，系統即使留下記錄，大多數也是用來事後稽查與復原。

很多人都說資料庫要開「稽核 Audit」於是去玩了一下，卻發現 SQL2008 沒辦法用、SQL2012 只能用部分大項的稽核，沒辦法監督資料表被異常存取，原因是.... 標準版 Standard 沒這功能

SQL 2008 Std - 此版本的 SQL Server 不提供稽核功能


SQL 2012 Std - 此版本的 SQL Server 不提供更細微的稽核功能

MSSQL 2016  各版本的差異   https://www.microsoft.com/zh-tw/server-cloud/products/sql-server-editions/overview.aspx 

 

--------------------------------------------------------------------------------

才發現不夠錢買 Ent. 版的是沒辦法開啟資料表的稽核 Audit ，只好轉向使用 Trigger 觸發程序來做，針對特定的資料表來進行保護

假設：本例是以 SQL 層去做，AP層要 programmer 多寫記錄程式片段
要被監督的資料表：Account
Trigger 記錄的資料表：Tr_Account  （也就是上圖寫的 log Table）

因為：被監督資料表與欲寫入的記錄資料表欄位必須相同，不然會得到底下的錯誤 （註：Tr_Account_changed 是 Trigger 的名稱）

訊息 213，層級 16，狀態 1，程序 Tr_Account_changed，行 7
資料行名稱或提供的數值數量與資料表定義不相符。

-----------------------------------------------------------------------------

Step 1：複製一份監督資料表（Account）的內容到記錄資料表（Tr_Account），我會習慣前面都冠個 Tr_ 以跟原本的資料表做區分，也好集中在一起
SELECT  *  into  Tr_Account  from  Account 

 

Step 2：建立 Trigger 觸發程序 ------------------------------------------

直接以例子來做介紹，雙--線後為註解

USE test    -- 使用資料表 test
GO
CREATE TRIGGER Tr_Account_changed ON Account     -- 建立 Trigger，觸發程序名稱 Tr_Account_changed 作用在資料表 Account
FOR UPDATE,INSERT,DELETE                         -- 動作 Update、Insert、Delete 都觸發，另外 FOR 可以換成 AFTER 是當正常的資料庫異動完後才處理此 Trigger
AS
IF (UPDATE(password))                            -- 判斷 Account 資料表中的 password 欄位有異動時
BEGIN
INSERT INTO Tr_Account SELECT * FROM inserted    -- 將異動的該筆資料寫入到記錄資料表 Tr_Account
END
-- inserted 代表 insert 的資料 或 update 「後」的資料
-- deleted 代表 delete 的資料 或 update 「前」的資料
-- inserted 和 deleted  都有資料表示為 UPDATE ，可以使用 IF EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)  來判斷

 

Step 3：測試 Trigger 動作是否正常  -------------------------------------

針對監督的 Account 資料表做修改，修改 username 與新增另一筆

新增的一筆也增加到了記錄的資料表，但....變更的 username 卻沒記錄到

這是因為我們在 Trigger 中觸發的條件為「IF (UPDATE(password)) 」所以修改了 username 並不會有動作

想要偵測多欄位可以使用  OR 判斷式「IF (UPDATE(username) OR UPDATE(password))」，下圖被異動了兩次，所以多了兩筆

 

 

Step 4：觀察資料庫中已存在的 Trigger 觸發程序  ------------------------
查看所有 Trigger
select * from sys.triggers
查看 Trigger 對應的處理事件，以 object_id 做關連
select * from sys.trigger_events

或使用 Derrick 大的查詢方式
-- 資料來源：http://sharedderrick.blogspot.tw/2013/08/dml-trigger.html
-- 查詢：現行資料庫內，有哪些DML 觸發程序
SELECT SCHEMA_NAME(tb.schema_id) N'結構描述', OBJECT_NAME(t.parent_id) N'資料表', t.name N'觸發程序名稱', parent_class_desc N'觸發程序父類別的描述',  t.type_desc N'物件類型的描述',  tEV.type_desc '引發觸發程序的每個事件', is_instead_of_trigger N'是否為INSTEAD OF 觸發程序' FROM sys.triggers t INNER JOIN sys.trigger_events tEV ON t.object_id = tEV.object_id  INNER JOIN sys.tables tb ON t.parent_id = tb.object_id

Step 5：刪除存在的 Trigger 觸發程序  -------------------------------------
直接以 trigger name 做刪除
drop  trigger   Trigger_name;
例： drop  trigger  Tr_Account_changed

~End