MySQL 學習筆記（十三）：使用者管理與權限控制完整指南

MySQL 權限系統架構

MySQL 透過 mysql 系統資料庫內的授權表實施存取控制，分兩階段：

1. 連線驗證階段：檢查 user、host、password，決定是否允許連線。
2. 請求驗證階段：連線成功後，每個查詢/操作皆依權限表（db、tables_priv、columns_priv、procs_priv）判斷是否具備對應權限（SELECT、INSERT、DROP 等）。

核心授權表結構

注意：mysql.user 表缺少「建立時間」欄位，無法直接查知帳號建立時間，排查連線問題時需結合日誌分析。

帳號管理實戰

1. 建立使用者

-- 一般帳號
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

-- 指定認證插件 (MySQL 5.5.7+)
CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH my_auth_plugin;

-- 使用密碼雜湊值避免明文寫入日誌
CREATE USER 'tom'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

2. GRANT 建立並授權（一步到位）

GRANT SELECT, UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd';

3. 直接操作 user 表（不建議，易遺漏欄位導致錯誤）

INSERT INTO mysql.user (host, user, password) VALUES ('localhost', 'customer1', PASSWORD('customer1'));

若報錯 Field 'ssl_cipher' doesn't have a default value，需補齊所有 NOT NULL 無預設值欄位。

4. 刪除使用者

DROP USER 'testUser'@'localhost';
-- 或
DELETE FROM mysql.user WHERE Host='localhost' AND User='testUser';
FLUSH PRIVILEGES;

密碼管理

Root 修改自身密碼

-- 方式 1: mysqladmin
mysqladmin -u root -p password 'newpwd'

-- 方式 2: UPDATE user 表
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

-- 方式 3: SET PASSWORD
SET PASSWORD = PASSWORD('newpwd');

Root 修改一般使用者密碼

SET PASSWORD FOR 'user'@'host' = PASSWORD('newpwd');
-- 或
GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY 'newpwd';

一般使用者修改自身密碼

SET PASSWORD = PASSWORD('newpwd');

Root 密碼遺失救援

以 --skip-grant-tables 啟動 MySQL，跳過權限檢查：

# Linux
mysqld_safe --skip-grant-tables --user=mysql &
# 或
/etc/init.d/mysql start --skip-grant-tables

# Windows: 在 my.ini [mysqld] 區段加入 skip-grant-tables，重啟服務

登入後執行 UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root'; FLUSH PRIVILEGES;，移除參數並重啟。

權限分層與授權/回收

• 全域層級：GRANT ALL ON *.* TO ... 寫入 user 表。
• 資料庫層級：GRANT ALL ON db_name.* TO ... 寫入 db, host 表。
• 表層級：GRANT ALL ON db_name.tbl_name TO ... 寫入 tables_priv 表。
• 欄位層級：GRANT SELECT (col1) ON db.tbl TO ... 寫入 columns_priv 表。

-- 授予 GRANT OPTION 允許轉授權
GRANT SELECT, INSERT ON *.* TO 'grantUser'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;

-- 回收特定權限
REVOKE INSERT ON *.* FROM 'grantUser'@'localhost';

-- 查看權限
SHOW GRANTS FOR 'grantUser'@'localhost';

安全檢查：匿名帳號

SELECT * FROM mysql.user WHERE User='';
-- 若有資料，立即刪除
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

MySQL 5.7+ 新增功能

• 支援 CREATE USER ... IDENTIFIED WITH auth_plugin 多因子認證。
• 密碼過期、鎖定帳號、密碼歷史策略。
• 角色 簡化權限管理。

總結

MySQL 使用者管理核心在於理解兩階段驗證與多層級授權表。生產環境建議：

1. 移除匿名帳號、測試資料庫。
2. Root 僅允許本地登入，強制強密碼。
3. 應用程式帳號最小權限原則，僅授予特定資料庫 CRUD。
4. 定期 SHOW GRANTS 審計權限。
5. 備份前執行 FLUSH PRIVILEGES 確保權限表持久化。

更多細節可參考《MySQL 權限架構體系》兩張流程圖：客戶端連線認證階段、客戶端操作請求認證階段。