2024年09月29日玄貓（BlackCat）

MySQL 觸發器實戰：權限控制與稽核追蹤的完整實作

深入探討 MySQL 觸發器的實戰應用，從 BEFORE 觸發器的權限控制到 AFTER 觸發器的稽核追蹤機制。透過完整的程式碼範例展示如何建構安全可靠的資料庫系統，確保只有授權使用者能修改敏感資料，並記錄所有資料變更的完整歷史。

資料庫技術 SQL 程式設計系統安全

MySQL 觸發器 BEFORE TRIGGER AFTER TRIGGER 權限控制稽核追蹤資料安全資料完整性自動化機制

在企業級資料庫系統中，資料安全與可追溯性是兩個不可忽視的核心需求。一方面需要確保只有授權的使用者才能修改敏感資料，避免未經授權的變更或刪除操作。另一方面需要記錄所有資料變更的完整歷史，包括誰在什麼時間做了什麼操作，以滿足合規要求與問題追查的需要。這兩個看似獨立的需求，都可以透過 MySQL 觸發器機制來優雅地實現。

觸發器作為資料庫層的自動化機制，在資料操作發生時自動執行預定義的邏輯。BEFORE 觸發器在資料實際寫入前執行，可以進行權限檢查與資料驗證，不符合條件時可以阻止操作繼續。AFTER 觸發器在資料成功寫入後執行，適合記錄變更歷史或更新相關資料。這種在資料庫層實施的控制機制，相較於應用程式層的驗證更為可靠，因為無論資料變更來自哪個應用程式或工具，觸發器都會被一致地執行。

本文將以投票系統為例，展示如何使用 BEFORE 觸發器實現細緻的權限控制，確保選民資料、競選項目與候選人資訊只能由特定的管理員修改。接著說明如何使用 AFTER 觸發器建立完整的稽核追蹤機制，記錄所有資料表的插入、更新與刪除操作。透過這些實際的程式碼範例，讀者將了解如何在自己的系統中應用觸發器來提升資料安全與管理效率。

使用 BEFORE 觸發器實現權限控制

在投票系統中，選民資料、競選項目與候選人資訊都是高度敏感的資料，不應該允許一般的操作人員隨意修改。這類資料應該只能由特定的管理員，例如選務委員會秘書來進行維護。雖然 MySQL 本身提供了使用者權限系統，但有時候需要更細緻的控制，例如允許某個使用者查詢資料但不能修改，或是允許修改但要記錄完整的稽核日誌。BEFORE 觸發器提供了這種靈活性。

BEFORE 觸發器的關鍵優勢在於它可以在資料寫入前檢查各種條件，如果不符合要求可以透過 SIGNAL 語句拋出錯誤來阻止操作。這種機制確保了只有符合條件的操作才能執行，不符合條件的操作會被立即拒絕並回傳錯誤訊息給使用者。在權限控制的場景中，我們可以在觸發器中檢查當前執行操作的使用者身份，如果不是授權的管理員就拋出錯誤。

-- 競選項目資料表的 BEFORE UPDATE 觸發器
-- 這個觸發器確保只有授權的管理員才能更新競選項目資料

-- 變更分隔符號為 //
-- 因為觸發器定義中會使用分號，需要臨時變更分隔符號
DELIMITER //

CREATE TRIGGER tr_race_bu
BEFORE UPDATE ON race
FOR EACH ROW
BEGIN
    -- 檢查當前執行操作的使用者
    -- USER() 函數回傳格式為 'username@hostname' 的字串
    -- 使用 NOT LIKE 進行模式比對
    -- 'secretary_of_state%' 表示允許從任何主機連線的 secretary_of_state 使用者
    IF USER() NOT LIKE 'secretary_of_state%' THEN
        -- SIGNAL 語句用於拋出自訂錯誤
        -- SQLSTATE '45000' 是通用的使用者定義錯誤代碼
        -- MESSAGE_TEXT 設定錯誤訊息，會顯示給執行操作的使用者
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '權限不足：只有選務委員會秘書才能更新競選項目資料';
    END IF;
    
    -- 如果權限檢查通過，觸發器正常結束
    -- UPDATE 操作會繼續執行
END//

-- 恢復預設的分隔符號
DELIMITER ;

同樣的模式可以應用於 INSERT 與 DELETE 操作，以及其他需要保護的資料表。對於候選人資料表，我們也需要建立相應的權限控制觸發器。這些觸發器的邏輯結構基本相同，都是先檢查使用者身份，不符合條件就拋出錯誤。

-- 候選人資料表的權限控制觸發器

DELIMITER //

-- BEFORE UPDATE 觸發器
CREATE TRIGGER tr_candidate_bu
BEFORE UPDATE ON candidate
FOR EACH ROW
BEGIN
    -- 檢查使用者權限
    IF USER() NOT LIKE 'secretary_of_state%' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '權限不足：只有選務委員會秘書才能更新候選人資料';
    END IF;
END//

-- BEFORE DELETE 觸發器
CREATE TRIGGER tr_candidate_bd
BEFORE DELETE ON candidate
FOR EACH ROW
BEGIN
    -- 檢查使用者權限
    IF USER() NOT LIKE 'secretary_of_state%' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '權限不足：只有選務委員會秘書才能刪除候選人資料';
    END IF;
    
    -- 即使有權限，刪除候選人資料也需要特別謹慎
    -- 如果該候選人已有相關的投票記錄，刪除可能影響資料完整性
    -- 在實務中，可能需要先檢查是否有相關的 ballot_candidate 記錄
END//

DELIMITER ;

這種權限控制機制的優勢在於它是在資料庫層實施的，無論使用者透過何種方式存取資料庫，都必須遵守這些規則。即使有人繞過應用程式直接使用 MySQL 命令列工具或其他資料庫管理工具，觸發器仍然會執行檢查。這提供了一層額外的安全保障，防止意外或惡意的資料變更。

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 100

|使用者|
start

:發出 UPDATE 語句;
note right
  修改競選項目或
  候選人資料
end note

|MySQL 伺服器|
:接收 SQL 語句;

:執行 BEFORE 觸發器;

|觸發器邏輯|
:取得當前使用者\nUSER() 函數;

if (使用者是 secretary_of_state?) then (是)
  :權限檢查通過;
  
  |MySQL 伺服器|
  :執行 UPDATE 操作;
  
  :更新資料成功;
  
  |使用者|
  :收到成功訊息;
  
  stop
else (否)
  :權限檢查失敗;
  
  :SIGNAL 拋出錯誤;
  note right
    SQLSTATE '45000'
    權限不足錯誤訊息
  end note
  
  |MySQL 伺服器|
  :回復操作;
  
  |使用者|
  :收到錯誤訊息;
  
  stop
endif

@enduml

建立稽核追蹤的資料表結構

在實施稽核追蹤機制之前，需要先建立專門的稽核資料表來儲存變更記錄。稽核資料表的設計需要能夠記錄操作的時間、執行操作的使用者，以及變更的詳細內容。一個良好的設計是為每個需要追蹤的業務資料表建立對應的稽核資料表，這樣可以保持資料的組織性並簡化查詢。

稽核資料表通常包含幾個核心欄位：操作時間記錄何時發生變更、操作使用者記錄誰執行了操作、變更內容記錄具體的變更資訊。變更內容的記錄方式可以有多種選擇，最簡單的是使用字串拼接記錄主要欄位的值，更進階的方式是使用 JSON 格式儲存完整的記錄資料。

-- 選民資料稽核表
-- 記錄 voter 資料表的所有變更操作
CREATE TABLE voter_audit (
    -- 稽核記錄的唯一識別碼
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- 操作發生的時間
    -- 使用 DATETIME 類型精確記錄到秒
    audit_datetime DATETIME NOT NULL,
    
    -- 執行操作的使用者
    -- 格式為 'username@hostname'
    audit_user VARCHAR(100) NOT NULL,
    
    -- 變更內容的描述
    -- 使用較大的 VARCHAR 儲存詳細資訊
    audit_change VARCHAR(1000) NOT NULL,
    
    -- 建立索引以加速按時間查詢
    INDEX idx_audit_datetime (audit_datetime),
    
    -- 建立索引以加速按使用者查詢
    INDEX idx_audit_user (audit_user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 競選項目稽核表
CREATE TABLE race_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    audit_datetime DATETIME NOT NULL,
    audit_user VARCHAR(100) NOT NULL,
    audit_change VARCHAR(1000) NOT NULL,
    INDEX idx_audit_datetime (audit_datetime),
    INDEX idx_audit_user (audit_user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 候選人稽核表
CREATE TABLE candidate_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    audit_datetime DATETIME NOT NULL,
    audit_user VARCHAR(100) NOT NULL,
    audit_change VARCHAR(1000) NOT NULL,
    INDEX idx_audit_datetime (audit_datetime),
    INDEX idx_audit_user (audit_user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 選票稽核表
CREATE TABLE ballot_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    audit_datetime DATETIME NOT NULL,
    audit_user VARCHAR(100) NOT NULL,
    audit_change VARCHAR(1000) NOT NULL,
    INDEX idx_audit_datetime (audit_datetime),
    INDEX idx_audit_user (audit_user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 選票候選人關聯稽核表
CREATE TABLE ballot_candidate_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    audit_datetime DATETIME NOT NULL,
    audit_user VARCHAR(100) NOT NULL,
    audit_change VARCHAR(1000) NOT NULL,
    INDEX idx_audit_datetime (audit_datetime),
    INDEX idx_audit_user (audit_user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

這些稽核資料表採用統一的結構設計，便於維護與查詢。索引的建立考慮了常見的查詢場景，例如查詢特定時間範圍內的變更，或是查詢特定使用者執行的所有操作。使用 InnoDB 引擎確保了交易的完整性，即使在高並發環境下也能正確記錄稽核資訊。

使用 AFTER 觸發器記錄資料變更

有了稽核資料表的基礎，接下來就可以建立 AFTER 觸發器來自動記錄資料變更。AFTER 觸發器在資料成功寫入後執行，此時可以確定操作已經完成，適合記錄最終的變更結果。對於每個業務資料表，通常需要建立三個 AFTER 觸發器，分別對應 INSERT、UPDATE 與 DELETE 操作。

在 AFTER INSERT 觸發器中，可以存取 NEW 偽記錄來取得新插入的資料。觸發器將這些資料組合成描述性的字串，連同操作時間與使用者資訊一起插入到稽核資料表中。這樣當有新的選民註冊或候選人登記時，系統會自動記錄完整的資訊。

-- 選民資料表的 AFTER INSERT 觸發器
-- 記錄新增選民的操作

DELIMITER //

CREATE TRIGGER tr_voter_ai
AFTER INSERT ON voter
FOR EACH ROW
BEGIN
    -- 將新增的記錄資訊插入稽核表
    INSERT INTO voter_audit (
        audit_datetime,
        audit_user,
        audit_change
    )
    VALUES (
        -- NOW() 函數取得當前的日期與時間
        NOW(),
        
        -- USER() 函數取得當前執行操作的使用者
        USER(),
        
        -- CONCAT() 函數組合變更內容的描述
        -- NEW 關鍵字存取新插入的記錄
        CONCAT(
            '新增選民 - ',
            'ID: ', NEW.voter_id, ', ',
            '姓名: ', NEW.voter_name, ', ',
            '地址: ', IFNULL(NEW.voter_address, '(未提供)'), ', ',
            '註冊號碼: ', NEW.voter_registration_num
        )
    );
END//

DELIMITER ;

AFTER UPDATE 觸發器的邏輯稍微複雜一些，因為需要記錄變更前後的值。透過 OLD 與 NEW 兩個偽記錄，可以比較資料的變化。為了避免記錄沒有實際變更的更新操作，觸發器可以先檢查關鍵欄位是否真的有改變。

-- 選民資料表的 AFTER UPDATE 觸發器
-- 記錄選民資料的更新操作

DELIMITER //

CREATE TRIGGER tr_voter_au
AFTER UPDATE ON voter
FOR EACH ROW
BEGIN
    -- 只有在資料實際變更時才記錄
    -- 檢查關鍵欄位是否有改變
    IF (OLD.voter_name != NEW.voter_name
        OR IFNULL(OLD.voter_address, '') != IFNULL(NEW.voter_address, '')
        OR OLD.voter_registration_num != NEW.voter_registration_num) THEN
        
        INSERT INTO voter_audit (
            audit_datetime,
            audit_user,
            audit_change
        )
        VALUES (
            NOW(),
            USER(),
            CONCAT(
                '更新選民 - ',
                'ID: ', NEW.voter_id, ', ',
                '變更: ',
                -- 記錄變更前後的關鍵資訊
                CASE 
                    WHEN OLD.voter_name != NEW.voter_name 
                    THEN CONCAT('姓名從「', OLD.voter_name, '」改為「', NEW.voter_name, '」 ')
                    ELSE ''
                END,
                CASE 
                    WHEN IFNULL(OLD.voter_address, '') != IFNULL(NEW.voter_address, '')
                    THEN CONCAT('地址從「', IFNULL(OLD.voter_address, '(空)'), '」改為「', IFNULL(NEW.voter_address, '(空)'), '」 ')
                    ELSE ''
                END,
                CASE 
                    WHEN OLD.voter_registration_num != NEW.voter_registration_num
                    THEN CONCAT('註冊號碼從「', OLD.voter_registration_num, '」改為「', NEW.voter_registration_num, '」')
                    ELSE ''
                END
            )
        );
    END IF;
END//

DELIMITER ;

AFTER DELETE 觸發器記錄刪除的資料，使用 OLD 偽記錄來存取被刪除的記錄內容。這確保了即使資料被刪除，仍然可以從稽核記錄中查詢到歷史資訊。

-- 選民資料表的 AFTER DELETE 觸發器
-- 記錄選民資料的刪除操作

DELIMITER //

CREATE TRIGGER tr_voter_ad
AFTER DELETE ON voter
FOR EACH ROW
BEGIN
    INSERT INTO voter_audit (
        audit_datetime,
        audit_user,
        audit_change
    )
    VALUES (
        NOW(),
        USER(),
        CONCAT(
            '刪除選民 - ',
            'ID: ', OLD.voter_id, ', ',
            '姓名: ', OLD.voter_name, ', ',
            '地址: ', IFNULL(OLD.voter_address, '(未提供)'), ', ',
            '註冊號碼: ', OLD.voter_registration_num
        )
    );
END//

DELIMITER ;

同樣的觸發器模式可以應用於其他資料表。對於競選項目資料表，稽核記錄應該包含項目名稱、類型與選舉日期等關鍵資訊。對於候選人資料表，則需要記錄候選人姓名、所屬政黨與競選項目。

-- 競選項目資料表的稽核追蹤觸發器

DELIMITER //

-- AFTER INSERT 觸發器
CREATE TRIGGER tr_race_ai
AFTER INSERT ON race
FOR EACH ROW
BEGIN
    INSERT INTO race_audit (audit_datetime, audit_user, audit_change)
    VALUES (
        NOW(),
        USER(),
        CONCAT(
            '新增競選項目 - ',
            'ID: ', NEW.race_id, ', ',
            '名稱: ', NEW.race_name, ', ',
            '類型: ', IFNULL(NEW.race_type, '(未指定)'), ', ',
            '選舉日期: ', IFNULL(NEW.election_date, '(未定)')
        )
    );
END//

-- AFTER UPDATE 觸發器
CREATE TRIGGER tr_race_au
AFTER UPDATE ON race
FOR EACH ROW
BEGIN
    INSERT INTO race_audit (audit_datetime, audit_user, audit_change)
    VALUES (
        NOW(),
        USER(),
        CONCAT(
            '更新競選項目 - ',
            'ID: ', NEW.race_id, ', ',
            '名稱: ', NEW.race_name
        )
    );
END//

-- AFTER DELETE 觸發器
CREATE TRIGGER tr_race_ad
AFTER DELETE ON race
FOR EACH ROW
BEGIN
    INSERT INTO race_audit (audit_datetime, audit_user, audit_change)
    VALUES (
        NOW(),
        USER(),
        CONCAT(
            '刪除競選項目 - ',
            'ID: ', OLD.race_id, ', ',
            '名稱: ', OLD.race_name
        )
    );
END//

DELIMITER ;

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 100

|使用者|
start

:執行資料操作\n(INSERT/UPDATE/DELETE);

|MySQL 伺服器|
:執行資料變更;

:資料成功寫入;

:觸發 AFTER 觸發器;

|觸發器邏輯|
:收集操作資訊;
note right
  - 操作時間 (NOW())
  - 操作使用者 (USER())
  - 變更內容 (NEW/OLD)
end note

if (是 INSERT 操作?) then (是)
  :使用 NEW 取得\n新插入的資料;
elseif (是 UPDATE 操作?) then (是)
  :比較 OLD 與 NEW\n判斷實際變更;
else (DELETE)
  :使用 OLD 取得\n被刪除的資料;
endif

:組合稽核訊息;
note right
  使用 CONCAT 函數
  建立描述性文字
end note

|稽核資料表|
:插入稽核記錄;

:記錄成功儲存;

|MySQL 伺服器|
:完成整個交易;

|使用者|
:收到操作成功訊息;

stop

@enduml

稽核記錄的查詢與分析

建立了完整的稽核追蹤機制後，可以透過查詢稽核資料表來了解系統的操作歷史。這對於問題追查、安全審計與合規檢查都非常有價值。以下是一些常見的稽核查詢範例。

-- 查詢最近的選民資料變更
-- 這個查詢顯示最新的 20 筆選民資料操作記錄
SELECT 
    audit_datetime AS 操作時間,
    audit_user AS 操作使用者,
    audit_change AS 變更內容
FROM voter_audit
ORDER BY audit_datetime DESC
LIMIT 20;

-- 查詢特定使用者的所有操作
-- 這可以追蹤特定管理員執行了哪些操作
SELECT 
    audit_datetime AS 操作時間,
    audit_change AS 變更內容
FROM voter_audit
WHERE audit_user LIKE 'secretary_of_state%'
ORDER BY audit_datetime DESC;

-- 查詢特定時間範圍內的變更
-- 例如查詢今天所有的資料變更
SELECT 
    audit_datetime AS 操作時間,
    audit_user AS 操作使用者,
    audit_change AS 變更內容
FROM voter_audit
WHERE DATE(audit_datetime) = CURDATE()
ORDER BY audit_datetime DESC;

-- 統計每個使用者的操作次數
-- 這可以了解系統的使用情況
SELECT 
    audit_user AS 使用者,
    COUNT(*) AS 操作次數,
    MIN(audit_datetime) AS 首次操作,
    MAX(audit_datetime) AS 最後操作
FROM voter_audit
GROUP BY audit_user
ORDER BY 操作次數 DESC;

-- 查詢特定選民的完整變更歷史
-- 假設選民 ID 為 voter_id
-- 這需要解析 audit_change 欄位中的 ID
SELECT 
    audit_datetime AS 操作時間,
    audit_user AS 操作使用者,
    audit_change AS 變更內容
FROM voter_audit
WHERE audit_change LIKE '%ID: 123,%'
ORDER BY audit_datetime;

這些查詢提供了多角度審視資料變更歷史的能力。透過時間、使用者或特定記錄的維度，可以快速定位到需要調查的操作。在安全事件發生時，稽核記錄成為了重要的證據來源，可以協助追蹤問題的根源。

從實務角度來看，觸發器提供了一種在資料庫層實施安全策略與稽核機制的有效方式。BEFORE 觸發器的權限控制確保了敏感資料只能由授權人員修改，這種在資料庫層的保護比單純依賴應用程式層的驗證更為可靠。AFTER 觸發器的稽核追蹤則建立了完整的操作歷史記錄，無論資料變更來自何處，都會被自動記錄下來。

然而在使用觸發器時也需要注意一些限制與最佳實務。觸發器會在每次資料操作時執行，如果觸發器邏輯過於複雜或包含耗時的操作，可能會影響資料庫的整體效能。稽核記錄的儲存也會持續增長，需要定期歸檔或清理舊的記錄。此外觸發器的除錯相對困難，因為它是自動執行的，錯誤訊息可能不如應用程式代碼那樣直觀。

展望未來，隨著系統規模的擴大與合規要求的提高，稽核機制可能需要進一步強化。例如可以考慮使用 JSON 格式儲存更結構化的稽核資料，便於進階的查詢與分析。也可以整合專門的稽核分析工具，自動偵測異常的操作模式。對於超大規模的系統，可能需要將稽核資料分離到專用的資料庫，避免影響主要業務系統的效能。無論採用何種方案，觸發器都將繼續在資料庫安全與稽核追蹤中扮演重要角色。