MySQL 用戶授權與撤銷完整指南：GRANT、REVOKE、密碼管理、刪除用戶

MySQL 用戶授權與撤銷完整指南：GRANT、REVOKE、密碼管理、刪除用戶

MySQL 權限系統基於「用戶@主機」模型，掌握 GRANT/REVOKE/DROP USER 核心語法，能精準控制存取，避免過度授權風險。本文整理實戰常用場景與注意事項。

一、創建用戶並授權 (GRANT)

1. 基礎語法

GRANT privileges ON db.tbl TO 'user'@'host' [IDENTIFIED BY 'pwd'] [WITH GRANT OPTION];

• privileges：SELECT, INSERT, UPDATE, DELETE 等，或 ALL PRIVILEGES（除 GRANT OPTION 外所有權限）。
• db.tbl：*.* 全庫全表；mydb.* 庫級；mydb.mytbl 表級；mydb.routine 存儲過程級。
• host：% 任意主機；192.168.1.% 子網；localhost 僅本地 Socket 連線。
• WITH GRANT OPTION：允許該用戶將自己擁有的權限再授予他人（慎用）。

2. 實戰範例

-- 超級管理員（僅限本地）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'Str0ngPwd!' WITH GRANT OPTION;

-- 應用讀寫帳號（指定庫）
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_rw'@'10.0.1.%' IDENTIFIED BY 'AppRw#2024';

-- 僅讀報表帳號
GRANT SELECT ON report_db.* TO 'ro_user'@'%' IDENTIFIED BY 'ReadOnly@2024';

-- 僅執行存儲過程
GRANT EXECUTE ON PROCEDURE app_db.sp_export TO 'etl_user'@'10.0.2.5' IDENTIFIED BY 'EtlUser$2024';

二、修改密碼

-- 5.7.6+ / 8.0 標準語法
ALTER USER 'app_rw'@'10.0.1.%' IDENTIFIED BY 'NewStr0ngPwd!';

-- 兼容舊版 / 當前登入用戶
SET PASSWORD FOR 'app_rw'@'10.0.1.%' = PASSWORD('NewStr0ngPwd!');
-- 或
SET PASSWORD = PASSWORD('NewStr0ngPwd!');

8.0 建議使用 caching_sha2_password 插件（預設），客戶端驅動需 8.0+ 或啟用 mysql_native_password 兼容。

三、撤銷權限 (REVOKE)

REVOKE privileges ON db.tbl FROM 'user'@'host';

• 只移除指定權限，不刪除用戶帳號（仍可連線，但無操作權）。
• host 必須與原 GRANT 完全一致，否則報錯。
• 不支援 WITH GRANT OPTION 子句。

-- 回收應用寫權限，保留讀權限
REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_rw'@'10.0.1.%';

四、徹底刪除用戶

-- 8.0+ 推薦
DROP USER 'app_rw'@'10.0.1.%';

-- 5.7 兼容（需手動刷新）
DELETE FROM mysql.user WHERE User='app_rw' AND Host='10.0.1.%';
FLUSH PRIVILEGES;

DROP USER 會自動清理 mysql.user、mysql.db、mysql.tables_priv 等所有授權表記錄，並刷新權限緩存，比手動 DELETE 更安全完整。

五、查看權限

SHOW GRANTS FOR 'app_rw'@'10.0.1.%';
-- 輸出可直接作為 GRANT 腳本備份

六、最佳實踐清單

1. 最小權限原則：應用帳號僅授業務必需庫/表/列/操作；拒絕 *.* + ALL。
2. 主機限制：生產帳號綁定具體 IP/子網，禁用 %。
3. 分層帳號：Admin / App_RW / App_RO / ETL / Backup，各司其職。
4. 定期輪換密碼：配合 PASSWORD EXPIRE / PASSWORD HISTORY (8.0) 強制輪換。
5. 審計授權變更：啟用 audit_log 記錄所有 GRANT/REVOKE/DROP USER。
6. 避免共用 root：root 僅限 DBA 緊急介入，日常禁用遠程登入 (bind-address=127.0.0.1 / skip-networking)。

權限管理是資料庫安全基石，用腳本固化 GRANT 標準，納入變更流程審批，才能杜絕「臨時加個權限」演變成永久後門。