用戶建立

-- 5.7
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';

-- 8.0+ (推薦明確指定插件)
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED WITH mysql_native_password BY 'StrongPass123!';
-- 或 caching_sha2_password (8.0 預設，需驅動支援)
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';

授權模板 (按角色分層)

1. 唯讀帳號 (報表、BI、只讀 API)

GRANT SELECT ON mydb.* TO 'readonly'@'192.168.1.%';
-- 或特定表
GRANT SELECT ON mydb.orders, mydb.users TO 'readonly'@'192.168.1.%';

2. 應用讀寫帳號 (CRUD)

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_rw'@'192.168.1.%';

3. 開發帳號 (DDL + DML)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES,
       CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW,
       CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON mydb.* TO 'dev'@'192.168.1.%';

4. 遷移/同步帳號 (需 BINLOG 讀取)

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.%';

5. 監控/備份帳號

GRANT SELECT, PROCESS, RELOAD, LOCK TABLES, SHOW DATABASES, SUPER
ON *.* TO 'monitor'@'192.168.1.%';

6. 管理員帳號 (慎用)

GRANT ALL PRIVILEGES ON *.* TO 'dba'@'192.168.1.%' WITH GRANT OPTION;

權限回收

-- 回收特定權限
REVOKE DROP ON mydb.* FROM 'dev'@'192.168.1.%';

-- 回收所有權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dev'@'192.168.1.%';

完整生命週期範例

-- 1. 建立用戶
CREATE USER 'shop_api'@'10.0.1.%' IDENTIFIED BY 'Str0ngP@ssw0rd!';

-- 2. 授權核心業務庫讀寫
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_api'@'10.0.1.%';

-- 3. 授權只讀統計庫
GRANT SELECT ON stats_db.* TO 'shop_api'@'10.0.1.%';

-- 4. 禁止高危操作 (顯式拒絕無直接語法，靠不授權實現)
-- 不給 CREATE, DROP, ALTER, GRANT OPTION, FILE, SHUTDOWN, SUPER

-- 5. 驗證
SHOW GRANTS FOR 'shop_api'@'10.0.1.%';

-- 6. 密碼輪換 (每 90 天)
ALTER USER 'shop_api'@'10.0.1.%' IDENTIFIED BY 'NewStr0ngP@ssw0rd!';

安全加固清單

• ✅ 刪除匿名用戶：DROP USER ''@'%';
• ✅ 禁用 root 遠程：DELETE FROM mysql.user WHERE user='root' AND host!='localhost'; FLUSH PRIVILEGES;
• ✅ 移除 test 資料庫：DROP DATABASE test; DELETE FROM mysql.db WHERE db='test' OR db='test\\_%';
• ✅ 啟用密碼策略：INSTALL COMPONENT 'file://component_validate_password';
• ✅ 審計日誌：INSTALL PLUGIN audit_log SONAME 'audit_log.so';
• ✅ SSL 強制：REQUIRE SSL 在 GRANT 中指定

常見誤區

誤區	正確做法
GRANT ALL ON *.* TO 'app'@'%'	按庫/表/欄位最小授權
用 root 連線應用	建立專用應用帳號
密碼寫在代碼/配置明文	用 Vault/Secrets Manager/環境變數
從不審計權限	季度執行 SELECT * FROM mysql.user 審計
以為 GRANT 後需重啟	即時生效，無需重啟