MySQL 使用者與權限管理

權限等級與安全性管理

簡介

在任何資料庫系統中，使用者與權限管理是確保資料安全的第一道防線。MySQL 作為最廣泛使用的開源關聯式資料庫，提供了細緻且彈性的權限機制，讓管理者可以依照不同的業務需求，將資料存取權限精確切分到資料庫、資料表、甚至欄位層級。

如果權限設定不當，常見的風險包括資料外洩、惡意刪除、以及資源濫用等，這些問題不僅會影響系統的穩定性，更可能帶來嚴重的商業與法律損失。因此，了解 MySQL 的權限等級（global、database、table、column、routine）以及如何在實務中安全地管理它們，是每位資料庫管理員（DBA）與開發者必備的基礎能力。

本篇文章將從概念說明、實作範例、常見陷阱與最佳實踐，帶你一步步建立 安全、可維護 的 MySQL 權限架構，適合剛入門的初學者，也能為中級開發者提供實務參考。

核心概念

1. 權限層級概述

MySQL 的權限分為五個主要層級：

層級	說明	典型使用情境
全域 (GLOBAL)	作用於整個 MySQL 伺服器，使用 `GRANT ... ON .`	管理員、備份腳本
資料庫 (DATABASE)	只對特定資料庫有效，使用 `GRANT ... ON db_name.*`	應用程式僅能存取自己的資料庫
資料表 (TABLE)	限制於單一資料表，使用 `GRANT ... ON db_name.table_name`	只允許讀取報表表格
欄位 (COLUMN)	更細緻地控制欄位存取，使用 `GRANT ... (col1, col2) ON db_name.table_name`	隱私欄位僅限特定角色
例行程序 (ROUTINE)	針對 stored procedure、function，使用 `GRANT ... ON PROCEDURE db_name.proc_name`	限制執行高權限的業務邏輯

小技巧：在設計權限時，遵循「最小權限原則」(principle of least privilege)，先從最小的層級開始授權，必要時再往上提升。

2. 使用者與角色的概念

MySQL 8.0 之後引入了 角色 (ROLE)，讓多個使用者可以共用同一套權限集合，降低管理成本。

-- 建立角色
CREATE ROLE 'report_reader';
-- 為角色授予 SELECT 權限於 sales 資料庫的所有表
GRANT SELECT ON sales.* TO 'report_reader';
-- 將角色指派給使用者
GRANT 'report_reader' TO 'alice'@'%' IDENTIFIED BY 'StrongPwd!';
-- 讓使用者在登入後自動啟用角色
SET DEFAULT ROLE 'report_reader' TO 'alice'@'%';

重點：角色本身 不會自動啟用，必須使用 SET ROLE 或 SET DEFAULT ROLE 讓使用者在連線後取得角色權限。

3. 常見的權限代碼說明

權限代碼	功能說明
`SELECT`	讀取資料
`INSERT`	新增資料
`UPDATE`	更新資料
`DELETE`	刪除資料
`CREATE`	建立資料庫或資料表
`DROP`	刪除資料庫或資料表
`GRANT OPTION`	允許使用者再授予自己擁有的權限
`EXECUTE`	執行 stored procedure / function
`FILE`	讀寫伺服器上的檔案（危險，僅限管理員）

程式碼範例

以下示範 5 個實務上常見的權限設定情境，每段程式碼皆加上說明註解，方便直接套用或改寫。

範例 1：建立僅能讀取 `sales` 資料庫的使用者

-- 建立使用者 alice，僅允許從任何 IP 連線
CREATE USER 'alice'@'%' IDENTIFIED BY 'StrongPwd!2025';

-- 授予 SELECT 權限於 sales 資料庫的所有表
GRANT SELECT ON sales.* TO 'alice'@'%';

-- 確認授權
SHOW GRANTS FOR 'alice'@'%';

說明：% 表示任意主機；若只允許特定 IP，請改為 'alice'@'192.168.1.50'。

範例 2：只允許 `bob` 在 `inventory` 資料庫的 `products` 表執行 `INSERT` 與 `UPDATE`

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'Another$tr0ngPwd';

-- 只在 inventory.products 表上授權
GRANT INSERT, UPDATE ON inventory.products TO 'bob'@'localhost';

-- 若未來需要授權 SELECT，可再補上
-- GRANT SELECT ON inventory.products TO 'bob'@'localhost';

範例 3：欄位層級權限 – 隱藏員工薪資欄位

CREATE USER 'hr_viewer'@'10.0.0.%' IDENTIFIED BY 'HRview!2025';

-- 只允許 SELECT 員工姓名與部門，排除 salary 欄位
GRANT SELECT (emp_name, department) ON company.employees TO 'hr_viewer'@'10.0.0.%';

提醒：欄位層級權限僅在 MySQL 8.0 以上支援，且在使用 SELECT * 時會自動過濾未授權欄位。

範例 4：使用角色管理多個使用者的權限

-- 建立角色
CREATE ROLE 'app_readonly';

-- 角色授權：只讀 analytics 資料庫
GRANT SELECT ON analytics.* TO 'app_readonly';

-- 建立兩個應用程式使用者
CREATE USER 'app_user1'@'%' IDENTIFIED BY 'AppPwd1!';
CREATE USER 'app_user2'@'%' IDENTIFIED BY 'AppPwd2!';

-- 將角色指派給使用者
GRANT 'app_readonly' TO 'app_user1'@'%';
GRANT 'app_readonly' TO 'app_user2'@'%';

-- 設定預設角色，使用者登入即自動取得
SET DEFAULT ROLE 'app_readonly' TO 'app_user1'@'%';
SET DEFAULT ROLE 'app_readonly' TO 'app_user2'@'%';

範例 5：撤銷過度授權的 `GRANT OPTION`

-- 假設 user 'dev' 目前擁有 GRANT OPTION，導致他能自行授權給其他人
SHOW GRANTS FOR 'dev'@'localhost';

-- 移除 GRANT OPTION，僅保留 SELECT、INSERT
REVOKE GRANT OPTION ON *.* FROM 'dev'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dev'@'localhost';
GRANT SELECT, INSERT ON dev_db.* TO 'dev'@'localhost';

安全建議：避免使用 GRANT OPTION 給非管理員帳號，防止權限蔓延。

常見陷阱與最佳實踐

陷阱	為何會發生	解決方案
使用 `GRANT ALL` 在測試環境時忘記收回	方便但會把 `GRANT OPTION` 也授給使用者，導致權限外洩	只授予必要權限，使用 `SHOW GRANTS` 檢查
忘記刷新權限 (`FLUSH PRIVILEGES`)	直接編輯 `mysql.user` 表後未刷新，導致設定不生效	大多數 `GRANT/REVOKE` 會自動刷新，若手動編輯請務必執行 `FLUSH PRIVILEGES`
*使用 `SELECT `** 逃過欄位層級限制	MySQL 仍會過濾未授權欄位，但會產生隱藏欄位的 `NULL`，易混淆	明確指定欄位，或在程式端避免 `*`
在 `root` 帳號上開放遠端登入	讓攻擊者直接以最高權限入侵	限制 root 只允許本機 (`'root'@'localhost'`)；使用 sudo 或 `mysqladmin` 管理
忘記定期檢查過期帳號	不再使用的帳號仍保有權限，成為安全漏洞	設定密碼過期政策 (`default_password_lifetime`) 並使用腳本定期審計

最佳實踐：

最小權限原則：從欄位 → 表 → 資料庫 → 全域，逐層檢視需求，只授予必要操作。
使用角色：將相同權限抽象為角色，減少重複授權與錯誤。
啟用密碼強度與過期：validate_password_policy=MEDIUM、default_password_lifetime=180。
審計與日誌：啟用 audit_log（Enterprise）或透過 general_log + slow_query_log 監控異常授權變更。
定期備份 mysql 系統資料庫：確保在權限誤操作時可快速回復。

實際應用場景

1. 多租戶 SaaS 平台

在 SaaS 系統中，每個客戶都有自己的資料庫或 schema。透過 資料庫層級 的 GRANT SELECT, INSERT, UPDATE, DELETE ON tenant_x.* TO 'tenant_user'@'%'，即能讓客戶只能操作自己租戶的資料，而不會碰到其他租戶的資料。

2. 只讀報表服務

公司內部的 BI 工具只需要讀取資料，絕不可寫入。建立 只讀角色 (report_reader) 並授予 SELECT，再將此角色指派給所有報表服務帳號，可確保即使程式碼被植入惡意 SQL，仍無法修改資料。

3. 敏感欄位保護（GDPR / 個資）

對於包含個人身分證、電話等敏感欄位的表格，使用 欄位層級權限 只授予特定人員（如資安部）SELECT (ssn, phone)，其他使用者只能看到非敏感欄位，降低資料外洩風險。

4. 自動化備份腳本

備份腳本只需要 SELECT、LOCK TABLES、SHOW VIEW、TRIGGER，因此可以建立一個 備份專用帳號，避免使用 root，降低腳本被竊取後的破壞面。

5. 入口 API 金鑰管理

若 API 服務直接以 MySQL 連線，建議為每個微服務建立 獨立使用者，僅授予該服務所需的 INSERT（寫入 log）或 SELECT（讀取設定），並在程式碼中使用 環境變數 保存密碼，避免硬編碼。

總結

MySQL 的權限機制雖然功能強大，但要發揮其安全效益，關鍵在於 正確的設計與持續的維護。本文從權限層級、使用者與角色的概念出發，提供了五個實務範例，說明如何在不同情境下授予最小必要權限；同時列出常見的陷阱與最佳實踐，協助你在日常管理中避免安全漏洞。

在實際專案中，建議先 規劃角色模型，再根據業務需求分配使用者與權限，並結合 密碼政策、審計日誌 與 定期權限檢查，形成完整的安全防護鏈。只要遵循「最小權限」與「定期檢視」的原則，即可讓 MySQL 成為既高效又可靠的資料平台。祝你在 MySQL 的使用者與權限管理上，玩得安心、玩得順手！