從 MySQL 遷移到 MariaDB 時,最容易踩坑的就是權限系統差異。本文深度剖析 ERROR 1045 (28000): Access denied for user 'root'@'localhost' 的根本原因,對比 MariaDB/MySQL 權限模型差異,給出正確的授權最佳實踐。
-- 這句在 MySQL 5.7 完美執行
GRANT ALL PRIVILEGES ON *.* TO 'tank'@'192.168.%' IDENTIFIED BY 'test';
-- MariaDB 10.x 直接報錯
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
錯誤訊息誤導性極強:看起來是 root 密碼錯,實際上是權限不足。
mysql> SHOW GRANTS;
+--------------------------------------------------------------+
| Grants for root@localhost |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
ALL PRIVILEGES 包含 GRANT OPTION,root 可任意授權。
MariaDB> SHOW GRANTS\G
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES,
SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,
CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' WITH GRANT OPTION
關鍵差異: MariaDB 明確列舉所有權限,**缺少 GRANT OPTION 的全域授權能力**。雖然列表尾部有 WITH GRANT OPTION,但僅限於列舉的權限範圍,不包含 GRANT OPTION 本身的遞歸授權。
參考官方文檔 MariaDB GRANT Syntax,核心限制:
ALL(或ALL PRIVILEGES) 不能與*.*通配符搭配使用。
GRANT ALL ON *.* TO 'tank'@'192.168.%' IDENTIFIED BY 'test'; -- 報錯 1045
GRANT ALL ON `business_db`.* TO 'tank'@'192.168.%' IDENTIFIED BY 'test';
針對具體資料庫授予所有權限,符合最小權限原則。
GRANT SELECT ON *.* TO 'tank'@'192.168.%' IDENTIFIED BY 'test';
全域僅授予 SELECT,其他權限按需在庫級授予。
-- 先建用戶
CREATE USER 'super_admin'@'192.168.%' IDENTIFIED BY 'VeryStrongPass#2024';
-- 分權限授予 (避開 ALL ON *.*)
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,
CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,
TRIGGER, EVENT ON *.* TO 'super_admin'@'192.168.%' WITH GRANT OPTION;
-- 如需管理用戶權限,額外授予
GRANT CREATE USER ON *.* TO 'super_admin'@'192.168.%';
| MySQL 習慣寫法 | MariaDB 兼容寫法 | 風險等級 |
|---|---|---|
GRANT ALL ON *.* TO 'u'@'%' IDENTIFIED BY 'p'; | 拆分為庫級 ALL + 全域單權限 | 🔴 必改 |
GRANT ALL PRIVILEGES ... WITH GRANT OPTION | 顯式列舉權限 + WITH GRANT OPTION | 🟠 建議改 |
匿名用戶 ''@'localhost' | MariaDB 預設保留,建議 DROP USER ''@'localhost' | 🟡 安全加固 |
PASSWORD() 加密函數 | MariaDB 10.4+ 棄用,改用 IDENTIFIED BY 明文或 SHA2() | 🟡 版本相容 |
-- 新連線測試
mysql -utank -ptest -h192.168.x.x -e "SHOW DATABASES;"
-- 檢查實際權限
SHOW GRANTS FOR 'tank'@'192.168.%';
MariaDB 為了安全性收緊了 GRANT 語義,拒絕 ALL ON *.* 這類「一鍵給全權」寫法。遷移時務必審視每個應用帳號的真實權限需求,遵循最小權限原則,按資料庫維度授權。這不僅解決報錯,更是資料庫安全基線的落地。