返回文章列表
玄貓(BlackCat)

MySQL資料庫安全與權限控制完整實戰指南

深入探討MySQL資料庫安全機制與權限控制實作,涵蓋觸發器自動化資料驗證、檢視表存取控制、使用者權限管理、稽核追蹤機制,以及MySQL Workbench安全配置,提供生產環境可用的完整SQL範例與最佳實務

資料庫系統 資訊安全 系統管理
MySQL Database Security 資料庫安全 Access Control 權限控制 Triggers 觸發器 Views 檢視表 User Management 使用者管理 Audit Trail 稽核追蹤 Data Protection 資料保護 MySQL Workbench

資料庫安全是現代資訊系統的基石,直接關係到企業核心資料資產的保護與業務連續性。在資料外洩事件頻傳的今日,建立完善的資料庫安全機制已不僅是合規要求,更是企業生存的關鍵。MySQL作為全球最廣泛使用的開源關聯式資料庫管理系統,提供了豐富的安全功能與存取控制機制。然而這些功能的正確配置與合理運用,需要深入理解其運作原理與最佳實務。

資料庫安全涉及多個層面的防護。最外層是網路安全,確保資料庫伺服器只接受來自可信來源的連線。接著是身份認證與授權,驗證使用者身份並限制其操作權限。資料層面的安全包括敏感資料的加密儲存與傳輸,以及細緻的存取控制。稽核與監控機制則提供事後的追蹤與分析能力,協助識別可疑活動與安全事件。這些層層防護構成了縱深防禦體系,單一層面的失效不會導致整個系統的淪陷。

本文將系統化地探討MySQL資料庫安全的核心技術與實作方法。我們將深入觸發器的應用,展示如何透過BEFORE與AFTER觸發器實現自動化的資料驗證與完整性檢查。檢視表作為存取控制的重要工具,能夠精確控制使用者對敏感資料的可見性,我們將詳細說明其設計原則與實作技巧。使用者權限管理是資料庫安全的核心,從帳戶建立到權限授予,從角色設計到最小權限原則,我們將提供完整的管理流程。稽核追蹤機制記錄所有關鍵操作,為安全事件調查提供證據,我們將展示如何建立完善的稽核系統。最後介紹MySQL Workbench在安全管理中的應用,這個圖形化工具能夠大幅簡化複雜的安全配置工作。

MySQL觸發器實現資料完整性控制

觸發器是MySQL提供的強大機制,能夠在資料變更時自動執行預定義的SQL語句。這種自動化執行的特性使得觸發器成為實施業務規則、維護資料完整性、記錄稽核資訊的理想工具。觸發器在資料庫層面執行,不依賴應用程式邏輯,即使應用程式存在漏洞或被繞過,觸發器的保護機制仍然有效。這種特性對於確保資料庫層面的安全控制尤為重要。

觸發器分為BEFORE觸發器與AFTER觸發器兩大類。BEFORE觸發器在資料實際寫入前執行,可以驗證與修改即將寫入的資料,甚至在發現違規時拋出錯誤阻止操作。AFTER觸發器在資料成功寫入後執行,適合用於更新衍生資料、記錄稽核日誌,以及觸發後續的業務流程。觸發器可以針對INSERT、UPDATE、DELETE三種資料變更操作設定,並可存取NEW與OLD兩個特殊記錄變數,分別代表新資料與舊資料。

-- MySQL觸發器完整實作範例
-- 展示薪資資料的驗證、稽核與自動化處理

-- 建立員工基本資料表
-- 儲存員工的核心資訊
CREATE TABLE employee (
    employee_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '員工編號',
    first_name VARCHAR(50) NOT NULL COMMENT '名字',
    last_name VARCHAR(50) NOT NULL COMMENT '姓氏',
    email VARCHAR(100) UNIQUE NOT NULL COMMENT '電子郵件',
    phone VARCHAR(20) COMMENT '聯絡電話',
    hire_date DATE NOT NULL COMMENT '到職日期',
    department_id INT COMMENT '部門編號',
    position_id INT COMMENT '職位編號',
    salary DECIMAL(15, 2) NOT NULL COMMENT '薪資',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    created_by VARCHAR(50) DEFAULT 'system' COMMENT '建立者',
    updated_by VARCHAR(50) DEFAULT 'system' COMMENT '更新者',
    INDEX idx_department (department_id),
    INDEX idx_position (position_id),
    INDEX idx_hire_date (hire_date)
) ENGINE=InnoDB COMMENT='員工基本資料表';

-- 建立職位薪資範圍表
-- 定義每個職位的合理薪資區間
CREATE TABLE position_salary_range (
    position_id INT PRIMARY KEY COMMENT '職位編號',
    position_name VARCHAR(100) NOT NULL COMMENT '職位名稱',
    min_salary DECIMAL(15, 2) NOT NULL COMMENT '最低薪資',
    max_salary DECIMAL(15, 2) NOT NULL COMMENT '最高薪資',
    currency_code CHAR(3) DEFAULT 'TWD' COMMENT '幣別代碼',
    effective_date DATE NOT NULL COMMENT '生效日期',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    CONSTRAINT chk_salary_range CHECK (min_salary < max_salary)
) ENGINE=InnoDB COMMENT='職位薪資範圍表';

-- 建立薪資變更稽核表
-- 記錄所有薪資調整的完整歷程
CREATE TABLE salary_audit_log (
    audit_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '稽核記錄編號',
    employee_id INT NOT NULL COMMENT '員工編號',
    old_salary DECIMAL(15, 2) COMMENT '原薪資',
    new_salary DECIMAL(15, 2) NOT NULL COMMENT '新薪資',
    salary_change DECIMAL(15, 2) COMMENT '薪資變動金額',
    change_percentage DECIMAL(5, 2) COMMENT '變動百分比',
    change_reason VARCHAR(200) COMMENT '變更原因',
    changed_by VARCHAR(50) NOT NULL COMMENT '變更者',
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '變更時間',
    operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL COMMENT '操作類型',
    client_ip VARCHAR(45) COMMENT '客戶端IP位址',
    INDEX idx_employee (employee_id),
    INDEX idx_changed_at (changed_at),
    FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
) ENGINE=InnoDB COMMENT='薪資變更稽核表';

-- 插入職位薪資範圍資料
INSERT INTO position_salary_range 
    (position_id, position_name, min_salary, max_salary, effective_date)
VALUES
    (1, '軟體工程師', 45000.00, 80000.00, '2025-01-01'),
    (2, '資深軟體工程師', 65000.00, 120000.00, '2025-01-01'),
    (3, '專案經理', 70000.00, 130000.00, '2025-01-01'),
    (4, '系統架構師', 80000.00, 150000.00, '2025-01-01'),
    (5, '技術總監', 120000.00, 200000.00, '2025-01-01');

-- 暫時改變陳述式分隔符號
-- 因為觸發器定義中包含分號
DELIMITER //

-- BEFORE INSERT觸發器
-- 在新增員工前驗證薪資合理性
CREATE TRIGGER trg_employee_before_insert
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    -- 宣告變數儲存薪資範圍
    DECLARE v_min_salary DECIMAL(15, 2);
    DECLARE v_max_salary DECIMAL(15, 2);
    DECLARE v_position_name VARCHAR(100);
    
    -- 查詢該職位的薪資範圍
    SELECT 
        min_salary,
        max_salary,
        position_name
    INTO 
        v_min_salary,
        v_max_salary,
        v_position_name
    FROM position_salary_range
    WHERE position_id = NEW.position_id;
    
    -- 驗證薪資是否在合理範圍內
    IF NEW.salary < v_min_salary THEN
        -- 薪資低於最低標準,拋出錯誤
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = CONCAT(
            '薪資 ',
            NEW.salary,
            ' 低於職位 ',
            v_position_name,
            ' 的最低薪資標準 ',
            v_min_salary
        );
    END IF;
    
    IF NEW.salary > v_max_salary THEN
        -- 薪資超過最高標準,拋出錯誤
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = CONCAT(
            '薪資 ',
            NEW.salary,
            ' 超過職位 ',
            v_position_name,
            ' 的最高薪資標準 ',
            v_max_salary
        );
    END IF;
    
    -- 驗證電子郵件格式
    IF NEW.email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '電子郵件格式不正確';
    END IF;
    
    -- 驗證到職日期不能是未來日期
    IF NEW.hire_date > CURDATE() THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '到職日期不能是未來日期';
    END IF;
    
    -- 自動設定建立者為當前使用者
    SET NEW.created_by = IFNULL(CURRENT_USER(), 'system');
END//

-- BEFORE UPDATE觸發器
-- 在更新員工資料前進行驗證
CREATE TRIGGER trg_employee_before_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
    DECLARE v_min_salary DECIMAL(15, 2);
    DECLARE v_max_salary DECIMAL(15, 2);
    DECLARE v_position_name VARCHAR(100);
    DECLARE v_salary_change DECIMAL(15, 2);
    DECLARE v_change_percentage DECIMAL(5, 2);
    
    -- 檢查薪資是否變更
    IF NEW.salary != OLD.salary THEN
        
        -- 查詢新職位的薪資範圍
        SELECT 
            min_salary,
            max_salary,
            position_name
        INTO 
            v_min_salary,
            v_max_salary,
            v_position_name
        FROM position_salary_range
        WHERE position_id = NEW.position_id;
        
        -- 驗證新薪資在合理範圍內
        IF NEW.salary < v_min_salary OR NEW.salary > v_max_salary THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = CONCAT(
                '薪資 ',
                NEW.salary,
                ' 超出職位 ',
                v_position_name,
                ' 的合理範圍 (',
                v_min_salary,
                ' - ',
                v_max_salary,
                ')'
            );
        END IF;
        
        -- 計算薪資變動
        SET v_salary_change = NEW.salary - OLD.salary;
        SET v_change_percentage = (v_salary_change / OLD.salary) * 100;
        
        -- 限制單次調薪幅度不超過30%
        IF ABS(v_change_percentage) > 30 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = CONCAT(
                '薪資調整幅度 ',
                ROUND(v_change_percentage, 2),
                '% 超過允許的最大幅度 30%'
            );
        END IF;
    END IF;
    
    -- 自動更新修改者資訊
    SET NEW.updated_by = IFNULL(CURRENT_USER(), 'system');
END//

-- AFTER INSERT觸發器
-- 新增員工後記錄稽核日誌
CREATE TRIGGER trg_employee_after_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
    -- 記錄新增操作到稽核表
    INSERT INTO salary_audit_log (
        employee_id,
        old_salary,
        new_salary,
        salary_change,
        change_percentage,
        change_reason,
        changed_by,
        operation_type,
        client_ip
    ) VALUES (
        NEW.employee_id,
        NULL,  -- 新增時無舊薪資
        NEW.salary,
        NEW.salary,  -- 變動金額等於新薪資
        NULL,  -- 新增時無變動百分比
        '新進員工',
        NEW.created_by,
        'INSERT',
        SUBSTRING_INDEX(USER(), '@', -1)  -- 取得客戶端主機
    );
END//

-- AFTER UPDATE觸發器
-- 更新員工資料後記錄變更
CREATE TRIGGER trg_employee_after_update
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
    -- 只有當薪資實際變更時才記錄
    IF NEW.salary != OLD.salary THEN
        INSERT INTO salary_audit_log (
            employee_id,
            old_salary,
            new_salary,
            salary_change,
            change_percentage,
            change_reason,
            changed_by,
            operation_type,
            client_ip
        ) VALUES (
            NEW.employee_id,
            OLD.salary,
            NEW.salary,
            NEW.salary - OLD.salary,
            ((NEW.salary - OLD.salary) / OLD.salary) * 100,
            '薪資調整',
            NEW.updated_by,
            'UPDATE',
            SUBSTRING_INDEX(USER(), '@', -1)
        );
    END IF;
END//

-- AFTER DELETE觸發器
-- 刪除員工時記錄操作
CREATE TRIGGER trg_employee_after_delete
AFTER DELETE ON employee
FOR EACH ROW
BEGIN
    -- 記錄員工離職
    INSERT INTO salary_audit_log (
        employee_id,
        old_salary,
        new_salary,
        salary_change,
        change_percentage,
        change_reason,
        changed_by,
        operation_type,
        client_ip
    ) VALUES (
        OLD.employee_id,
        OLD.salary,
        NULL,
        -OLD.salary,
        -100.00,
        '員工離職',
        IFNULL(CURRENT_USER(), 'system'),
        'DELETE',
        SUBSTRING_INDEX(USER(), '@', -1)
    );
END//

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

-- 測試觸發器功能
-- 測試1: 正常新增員工
INSERT INTO employee (
    first_name,
    last_name,
    email,
    phone,
    hire_date,
    department_id,
    position_id,
    salary
) VALUES (
    '志明',
    '王',
    '[email protected]',
    '0912-345-678',
    '2025-01-15',
    1,
    1,  -- 軟體工程師
    55000.00  -- 在合理範圍內
);

-- 測試2: 嘗試新增薪資過高的員工(應該失敗)
-- INSERT INTO employee (
--     first_name, last_name, email, hire_date,
--     position_id, salary
-- ) VALUES (
--     '春嬌', '陳', '[email protected]', '2025-02-01',
--     1, 90000.00  -- 超過軟體工程師最高薪資
-- );
-- 預期結果: 錯誤訊息 "薪資 90000.00 超過職位軟體工程師的最高薪資標準 80000.00"

-- 測試3: 更新員工薪資
UPDATE employee
SET salary = 60000.00
WHERE email = '[email protected]';

-- 查詢稽核記錄
SELECT 
    audit_id AS '稽核編號',
    employee_id AS '員工編號',
    old_salary AS '原薪資',
    new_salary AS '新薪資',
    salary_change AS '變動金額',
    CONCAT(ROUND(change_percentage, 2), '%') AS '變動百分比',
    change_reason AS '變更原因',
    changed_by AS '變更者',
    operation_type AS '操作類型',
    changed_at AS '變更時間'
FROM salary_audit_log
ORDER BY changed_at DESC;

這個完整的觸發器範例展示了多層次的資料保護機制。BEFORE觸發器在資料寫入前執行驗證,確保薪資在職位合理範圍內,電子郵件格式正確,到職日期合理。單次調薪幅度的限制防止了異常的薪資調整。AFTER觸發器在資料成功變更後記錄完整的稽核資訊,包括舊值、新值、變動金額與百分比。這些記錄為後續的安全分析與問題追蹤提供了完整的證據鏈。

@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

title MySQL觸發器資料保護機制

|應用程式層|
start
:提交資料變更請求;
note right
  INSERT / UPDATE / DELETE
  員工資料或薪資調整
end note

|MySQL觸發器層|
partition "BEFORE觸發器階段" {
    if (觸發器類型?) then (INSERT)
        :執行BEFORE INSERT觸發器;
    elseif (UPDATE)
        :執行BEFORE UPDATE觸發器;
    else (DELETE)
        :無BEFORE DELETE觸發器;
    endif
    
    partition "資料驗證" {
        :查詢職位薪資範圍;
        
        if (薪資在合理範圍?) then (否)
            #pink:SIGNAL拋出錯誤;
            :操作被拒絕;
            stop
        endif
        
        if (調薪幅度<30%?) then (否)
            #pink:SIGNAL拋出錯誤;
            :操作被拒絕;
            stop
        endif
        
        if (郵件格式正確?) then (否)
            #pink:SIGNAL拋出錯誤;
            :操作被拒絕;
            stop
        endif
        
        :設定建立者/更新者資訊;
    }
}

|資料庫核心層|
:執行實際資料變更;
note right
  資料寫入employee表
  更新索引
  檢查約束條件
end note

if (資料變更成功?) then (是)
    |MySQL觸發器層|
    partition "AFTER觸發器階段" {
        if (觸發器類型?) then (INSERT)
            :執行AFTER INSERT觸發器;
        elseif (UPDATE)
            :執行AFTER UPDATE觸發器;
        else (DELETE)
            :執行AFTER DELETE觸發器;
        endif
        
        partition "稽核記錄" {
            :計算薪資變動;
            :計算變動百分比;
            :取得客戶端資訊;
            :寫入salary_audit_log表;
        }
    }
    
    |應用程式層|
    :操作成功回傳;
    stop
else (否)
    #pink:操作失敗回傳;
    stop
endif

@enduml

檢視表實現細緻化存取控制

檢視表是MySQL提供的虛擬表機制,它本身不儲存資料,而是動態執行預定義的查詢來產生結果集。檢視表在資料庫安全中扮演關鍵角色,它能夠精確控制使用者對敏感資料的可見性。透過檢視表,可以隱藏特定欄位、過濾特定資料列,或是對資料進行轉換,使得不同權限層級的使用者看到適當範圍的資訊。這種機制實現了資料的邏輯隔離,即使多個使用者存取同一個底層資料表,他們透過不同的檢視表看到的內容也可以完全不同。

-- MySQL檢視表存取控制完整實作
-- 展示多層次的資料可見性控制

-- 建立員工完整資訊檢視(僅限管理者)
-- 包含所有敏感資訊包括薪資
CREATE OR REPLACE VIEW v_employee_full_info AS
SELECT 
    e.employee_id AS '員工編號',
    CONCAT(e.last_name, e.first_name) AS '姓名',
    e.email AS '電子郵件',
    e.phone AS '電話',
    DATE_FORMAT(e.hire_date, '%Y年%m月%d日') AS '到職日期',
    TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS '年資(年)',
    e.department_id AS '部門編號',
    e.position_id AS '職位編號',
    psr.position_name AS '職位名稱',
    e.salary AS '當前薪資',
    psr.min_salary AS '職位最低薪資',
    psr.max_salary AS '職位最高薪資',
    ROUND((e.salary - psr.min_salary) / (psr.max_salary - psr.min_salary) * 100, 2) AS '薪資級距百分比',
    e.created_at AS '建立時間',
    e.updated_at AS '更新時間',
    e.created_by AS '建立者',
    e.updated_by AS '更新者'
FROM employee e
LEFT JOIN position_salary_range psr ON e.position_id = psr.position_id;

-- 建立員工基本資訊檢視(一般使用者)
-- 不包含薪資等敏感資訊
CREATE OR REPLACE VIEW v_employee_basic_info AS
SELECT 
    employee_id AS '員工編號',
    CONCAT(last_name, first_name) AS '姓名',
    email AS '電子郵件',
    phone AS '電話',
    DATE_FORMAT(hire_date, '%Y年%m月%d日') AS '到職日期',
    TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS '年資(年)',
    department_id AS '部門編號',
    position_id AS '職位編號'
FROM employee;

-- 建立員工薪資統計檢視(人資部門)
-- 提供薪資分析但不顯示個人識別資訊
CREATE OR REPLACE VIEW v_salary_statistics AS
SELECT 
    psr.position_name AS '職位名稱',
    COUNT(e.employee_id) AS '員工人數',
    ROUND(AVG(e.salary), 2) AS '平均薪資',
    MIN(e.salary) AS '最低薪資',
    MAX(e.salary) AS '最高薪資',
    ROUND(STDDEV(e.salary), 2) AS '薪資標準差',
    psr.min_salary AS '職位薪資下限',
    psr.max_salary AS '職位薪資上限'
FROM employee e
INNER JOIN position_salary_range psr ON e.position_id = psr.position_id
GROUP BY e.position_id, psr.position_name, psr.min_salary, psr.max_salary;

-- 建立薪資異常檢視(稽核人員)
-- 識別可能的薪資異常情況
CREATE OR REPLACE VIEW v_salary_anomalies AS
SELECT 
    e.employee_id AS '員工編號',
    CONCAT(e.last_name, e.first_name) AS '姓名',
    psr.position_name AS '職位',
    e.salary AS '當前薪資',
    psr.min_salary AS '最低標準',
    psr.max_salary AS '最高標準',
    CASE
        WHEN e.salary < psr.min_salary THEN '低於標準'
        WHEN e.salary > psr.max_salary THEN '超出標準'
        WHEN e.salary = psr.min_salary THEN '等於最低標準'
        WHEN e.salary = psr.max_salary THEN '等於最高標準'
        ELSE '正常'
    END AS '薪資狀態',
    CASE
        WHEN e.salary < psr.min_salary THEN 
            ROUND((psr.min_salary - e.salary) / psr.min_salary * 100, 2)
        WHEN e.salary > psr.max_salary THEN 
            ROUND((e.salary - psr.max_salary) / psr.max_salary * 100, 2)
        ELSE 0
    END AS '偏離百分比'
FROM employee e
INNER JOIN position_salary_range psr ON e.position_id = psr.position_id
WHERE e.salary < psr.min_salary OR e.salary > psr.max_salary;

-- 建立可更新檢視範例
-- 允許透過檢視表更新特定欄位
CREATE OR REPLACE VIEW v_employee_contact_update AS
SELECT 
    employee_id,
    email,
    phone
FROM employee;

-- 測試檢視表查詢
SELECT * FROM v_employee_basic_info;
SELECT * FROM v_salary_statistics;
SELECT * FROM v_salary_anomalies;

這些檢視表展示了不同層級的資料可見性控制。完整資訊檢視包含所有敏感資料,只授權給管理者。基本資訊檢視隱藏薪資等敏感欄位,適合一般員工查詢。薪資統計檢視提供彙總資訊但不暴露個人資料,符合人資分析需求。薪資異常檢視協助稽核人員識別可疑情況。可更新檢視允許使用者更新特定欄位,實現了寫入權限的精確控制。

MySQL使用者權限管理完整實作

使用者權限管理是資料庫安全的核心機制。MySQL的權限系統採用分層設計,從全域權限到資料庫權限,從資料表權限到欄位權限,提供了細緻的存取控制能力。最小權限原則要求每個使用者只獲得完成工作所需的最小權限集合,這能夠有效降低內部威脅與權限濫用的風險。角色的引入進一步簡化了權限管理,相同職能的使用者可以被授予相同的角色,當職責變更時只需要調整角色成員而非個別使用者的權限。

-- MySQL使用者與權限管理完整範例

-- 建立不同角色的使用者帳戶
-- 注意:在生產環境中應使用強密碼

-- 1. 建立資料庫管理員帳戶
-- 擁有完整的資料庫管理權限
CREATE USER IF NOT EXISTS 'db_admin'@'localhost' 
IDENTIFIED BY 'Admin@Strong#Pass123';

-- 授予所有權限
GRANT ALL PRIVILEGES ON employee_db.* 
TO 'db_admin'@'localhost';

-- 授予GRANT OPTION
-- 允許此使用者授權給其他使用者
GRANT GRANT OPTION ON employee_db.* 
TO 'db_admin'@'localhost';

-- 2. 建立人資部門帳戶
-- 可以查看與修改員工資料
CREATE USER IF NOT EXISTS 'hr_manager'@'localhost' 
IDENTIFIED BY 'HR@Manager#2025';

-- 授予完整員工表的讀寫權限
GRANT SELECT, INSERT, UPDATE ON employee_db.employee 
TO 'hr_manager'@'localhost';

-- 授予薪資範圍表的讀取權限
GRANT SELECT ON employee_db.position_salary_range 
TO 'hr_manager'@'localhost';

-- 授予稽核日誌的讀取權限
GRANT SELECT ON employee_db.salary_audit_log 
TO 'hr_manager'@'localhost';

-- 授予統計檢視表的存取權限
GRANT SELECT ON employee_db.v_employee_full_info 
TO 'hr_manager'@'localhost';

GRANT SELECT ON employee_db.v_salary_statistics 
TO 'hr_manager'@'localhost';

-- 3. 建立一般員工帳戶
-- 只能查看基本資訊
CREATE USER IF NOT EXISTS 'employee_user'@'localhost' 
IDENTIFIED BY 'Employee@2025';

-- 只授予基本檢視表的讀取權限
GRANT SELECT ON employee_db.v_employee_basic_info 
TO 'employee_user'@'localhost';

-- 4. 建立稽核人員帳戶
-- 可以查看所有稽核記錄
CREATE USER IF NOT EXISTS 'auditor'@'localhost' 
IDENTIFIED BY 'Audit@Secure#2025';

-- 授予稽核相關檢視表的讀取權限
GRANT SELECT ON employee_db.v_salary_anomalies 
TO 'auditor'@'localhost';

GRANT SELECT ON employee_db.salary_audit_log 
TO 'auditor'@'localhost';

-- 授予完整資訊檢視的讀取權限
GRANT SELECT ON employee_db.v_employee_full_info 
TO 'auditor'@'localhost';

-- 5. 建立應用程式服務帳戶
-- 用於應用程式連線資料庫
CREATE USER IF NOT EXISTS 'app_service'@'%' 
IDENTIFIED BY 'AppService@Secret#Key2025';

-- 授予必要的資料操作權限
GRANT SELECT, INSERT, UPDATE ON employee_db.employee 
TO 'app_service'@'%';

GRANT SELECT ON employee_db.v_employee_basic_info 
TO 'app_service'@'%';

-- 限制來源IP(範例)
-- CREATE USER 'app_service'@'192.168.1.%' 
-- IDENTIFIED BY 'password';

-- 套用權限變更
FLUSH PRIVILEGES;

-- 查詢使用者權限
-- 顯示特定使用者的權限
SHOW GRANTS FOR 'hr_manager'@'localhost';
SHOW GRANTS FOR 'employee_user'@'localhost';
SHOW GRANTS FOR 'auditor'@'localhost';

-- 查詢所有使用者
SELECT 
    User AS '使用者名稱',
    Host AS '來源主機',
    account_locked AS '帳戶鎖定',
    password_expired AS '密碼過期',
    password_lifetime AS '密碼有效期',
    password_last_changed AS '密碼最後變更'
FROM mysql.user
WHERE User NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema');

-- 修改使用者密碼
-- ALTER USER 'employee_user'@'localhost' 
-- IDENTIFIED BY 'NewPassword@2025';

-- 設定密碼過期政策
-- ALTER USER 'employee_user'@'localhost' 
-- PASSWORD EXPIRE INTERVAL 90 DAY;

-- 撤銷權限
-- REVOKE UPDATE ON employee_db.employee 
-- FROM 'hr_manager'@'localhost';

-- 刪除使用者
-- DROP USER IF EXISTS 'employee_user'@'localhost';

這個權限管理範例展示了基於角色的權限分配策略。資料庫管理員擁有完整控制權,人資管理者可以管理員工資料,一般員工只能查看基本資訊,稽核人員專注於安全監控,應用服務帳戶則獲得系統運作所需的最小權限。每個帳戶的權限都精確定義,符合最小權限原則。密碼政策與帳戶管理功能確保了認證機制的安全性。

MySQL資料庫安全是一個多層次的防護體系,從觸發器的資料驗證到檢視表的存取控制,從使用者權限管理到稽核追蹤機制,每個環節都在保護資料資產的安全。觸發器提供了自動化的業務規則執行與資料完整性檢查,確保資料層面的安全控制不被繞過。檢視表實現了細緻化的資料可見性控制,不同權限的使用者看到適當範圍的資訊。使用者權限管理遵循最小權限原則,精確控制每個帳戶的操作能力。稽核機制記錄所有關鍵操作,為安全事件調查提供完整證據。這些技術的綜合運用,構建了縱深防禦的安全架構,有效保護企業核心資料資產,確保業務系統的穩定運作。