MySQL 存取權限體系深度解析：認證、授權、層級與生效機制

用戶名與認證基礎

• MySQL 用戶名（≤16 字元）獨立於 OS 用戶名（通常 ≤8 字元）
• 客戶端預設嘗試 OS 用戶名，可用 -u 指定
• 所有用戶必須設密碼，空密碼極度危險
• 認證演算法：PASSWORD()（MySQL 專用，不可逆）≠ Unix crypt()/ENCRYPT()

連線參數與配置檔

shell> mysql -h host -u user -p'pass'   # -p 後無空格直接跟密碼（不安全，僅測試用）
shell> mysql --host=host --user=user --password=pass

預設值：host=localhost、user=OS登入名、無密碼

最佳實踐：~/.my.cnf [client] 區段存密碼（權限 600），或交互式輸入。

權限體系四層級架構

原則：mysql.user 僅授超級管理員，業務帳號務必在資料庫/表層級授權。

核心權限說明

• GRANT OPTION：允許將自己擁有的權限轉授他人
• FILE：LOAD DATA INFILE/SELECT ... INTO OUTFILE 讀寫伺服器檔案，**極度危險**，僅限 DBA
• PROCESS：SHOW PROCESSLIST 看見所有查詢文本（含敏感資料）
• SUPER：KILL 連線、修改全域變數、控制複寫

授權語法與生效機制

GRANT priv_type [(col_list)] ON db.tbl TO 'user'@'host' IDENTIFIED BY 'pass' [WITH GRANT OPTION];
REVOKE priv_type ON db.tbl FROM 'user'@'host';

• GRANT/REVOKE/SET PASSWORD → **即時生效**（記憶體同步）
• 手工 INSERT/UPDATE/DELETE mysql.* → **需 FLUSH PRIVILEGES 或 mysqladmin flush-privileges**
• 啟動時讀取授權表到記憶體，運行時以記憶體為準

初始權限與 Root 設密

# 安裝後初始狀態
root@localhost：超級用戶，空密碼（極度危險）
匿名用戶@localhost：可操作 test/test_% 資料庫

# 設定 root 密碼三種方式
# 1. UPDATE + FLUSH
UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;

# 2. SET PASSWORD (3.22+)
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

# 3. mysqladmin (最簡單)
mysqladmin -u root password 'newpass'

新增用戶實戰範本

-- 超級管理員（生產慎用）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.0.%' IDENTIFIED BY 'strongpass' WITH GRANT OPTION;

-- 應用讀寫帳號
GRANT SELECT,INSERT,UPDATE,DELETE ON appdb.* TO 'appuser'@'10.0.%' IDENTIFIED BY 'apppass';

-- 唯讀報表帳號
GRANT SELECT ON appdb.* TO 'readonly'@'10.0.%' IDENTIFIED BY 'ropass';

-- 僅建立帳號，後續再授權
GRANT USAGE ON *.* TO 'future'@'10.0.%' IDENTIFIED BY 'pass';

Host 匹配優先級陷阱

授權表按 Host 特異性降序、User 升序**排序。'%' 最寬鬆，'localhost' 較具體。匿名用戶 ''@'localhost' 優先級高於具名用戶 'user'@'%'，導致本地連線意外落入匿名帳號 → **刪除匿名用戶**或顯式授權 'user'@'localhost'。