核心 GRANT 語法結構

GRANT privilege_type [, ...] 
ON database.table 
TO 'user'@'host' 
[IDENTIFIED BY 'password'] 
[WITH GRANT OPTION | MAX_QUERIES_PER_HOUR n ...];

五大常見授權場景範本

1. 單一資料庫、完整權限、任意主機 (開發/測試用)

CREATE USER 'dev_user'@'%' IDENTIFIED BY 'StrongPass!2024';
GRANT ALL PRIVILEGES ON `myapp`.* TO 'dev_user'@'%';
FLUSH PRIVILEGES;

2. 全資料庫、超級管理員、僅本機 (生產根帳號)

CREATE USER 'root'@'localhost' IDENTIFIED BY 'VeryStrongRootPass!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3. 指定用戶、全庫、僅特定 IP 段 (應用伺服器)

CREATE USER 'app_user'@'10.2.1.%' IDENTIFIED BY 'AppPass!2024';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `myapp`.* TO 'app_user'@'10.2.1.%';
FLUSH PRIVILEGES;

4. 唯讀報表帳號、特定表、支援 SSL

CREATE USER 'readonly'@'10.2.1.10' IDENTIFIED BY 'ReadOnlyPass!' REQUIRE SSL;
GRANT SELECT ON `myapp`.`orders` TO 'readonly'@'10.2.1.10';
GRANT SELECT ON `myapp`.`customers` TO 'readonly'@'10.2.1.10';
FLUSH PRIVILEGES;

5. 程式遷移/同步帳號、REPLICATION 權限

CREATE USER 'repl'@'10.2.1.%' IDENTIFIED BY 'ReplPass!' REQUIRE SSL;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.2.1.%';
FLUSH PRIVILEGES;

權限粒度詳細說明

Host 匹配規則與優先序

• 'user'@'%'：任意主機 (最寬鬆)
• 'user'@'10.2.1.%'：IP 通配符
• 'user'@'10.2.1.10'：精確 IP
• 'user'@'localhost'：Unix Socket 連線 (非 127.0.0.1)
• 'user'@'127.0.0.1'：TCP 回環連線
• 優先序：具體 IP > 通配符 IP > % > localhost；同用戶不同 Host 視為不同帳號

安全強化清單

1. 移除匿名用戶：DROP USER ''@'%'; DROP USER ''@'localhost';
2. 禁用 root 遠程登入：DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1');
3. 強制密碼複雜度：INSTALL COMPONENT 'file://component_validate_password'; (MySQL 8.0+)
4. 最小權限原則：應用帳號僅授 SELECT/INSERT/UPDATE/DELETE，無 DDL、無 GRANT OPTION
5. 啟用 SSL/TLS：REQUIRE SSL、REQUIRE X509、REQUIRE SUBJECT '...'
6. 資源限制：WITH MAX_QUERIES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 50 MAX_USER_CONNECTIONS 10
7. 定期審計：SELECT User, Host, authentication_string FROM mysql.user;

驗證與疑難排解

-- 查看用戶權限
SHOW GRANTS FOR 'app_user'@'10.2.1.%';

-- 測試連線
mysql -u app_user -p -h 10.2.1.100 -P 3306 myapp

-- 常見錯誤
ERROR 1130 (HY000): Host '10.2.1.50' is not allowed to connect
→ 檢查 Host 欄位匹配、防火牆 3306、bind-address=0.0.0.0、skip_networking=OFF

ERROR 1045 (28000): Access denied for user 'app_user'@'10.2.1.50'
→ 密碼錯誤、或密碼插件不匹配 (caching_sha2_password vs mysql_native_password)
→ 修正：ALTER USER 'app_user'@'10.2.1.%' IDENTIFIED WITH mysql_native_password BY 'pass';

MySQL 8.0+ 新特性

• CREATE USER ... IDENTIFIED BY ... 分離建立與授權
• caching_sha2_password 預設認證插件，更安全，舊驅動需升級或改用 mysql_native_password
• GRANT ... AS 'role' 角色系統，簡化權限管理
• SHOW GRANTS FOR CURRENT_USER 顯示當前會話有效權限