您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 用戶與權限管理實戰大全：從建帳號、授權、換密到刪帳號的完整流程

編輯：MySQL 綜合教學

MySQL 用戶與權限管理實戰大全：從建帳號、授權、換密到刪帳號的完整流程

MySQL 透過 mysql 系統庫中的五張權限表（user、db、table_priv、columns_priv、host）實現細粒度存取控制。本文以實戰案例串聯建立用戶、分層授權、密碼管理、權限回收、帳號清理全生命週期，並整理權限分層模型與常用指令速查表，助你建立安全合規的資料庫存取體系。

1. 五大權限表職責

表名	層級	說明
user	全域	記錄可連線的帳號與全域權限
db	資料庫	各帳號在各資料庫的操作權限
table_priv	資料表	表級操作權限
columns_priv	欄位	欄位級操作權限
host	主機+資料庫	輔助 db 表，針對特定主機細化資料庫權限（不受 GRANT/REVOKE 影響）

2. 實戰案例：分層授權體系建立

場景：Root 建立超級管理員 tom，再由 tom 授權給開發 tom1、tom2

步驟 1：Root 建立 tom 並給予 prod 庫全權 + 授權選項

mysql -u root -p
mysql> GRANT ALL ON prod.* TO 'tom'@'%' IDENTIFIED BY 'tom' WITH GRANT OPTION;

WITH GRANT OPTION 讓 tom 可將自己擁有的權限轉授他人。

步驟 2：Root 建立開發帳號 tom1、tom2（僅建帳號，無權限）

mysql> CREATE USER 'tom1' IDENTIFIED BY 'tom1', 'tom2' IDENTIFIED BY 'tom2';

步驟 3：tom 登入並授權（模擬權力下放）

# tom 本地登入失敗？見「匿名用戶遮蔽問題」章節
mysql -u tom -ptom -h localhost prod
mysql> GRANT SELECT ON prod.* TO 'tom1'@'%';
mysql> GRANT SELECT, INSERT, UPDATE ON prod.* TO 'tom2'@'%';

步驟 4：驗證 tom2 遠程登入並操作

C:\> mysql -h 192.168.8.240 -u tom2 -ptom2
mysql> USE prod;
mysql> INSERT INTO t1 VALUES (40, 'john');  -- 成功
mysql> UPDATE t1 SET name='ellen' WHERE id=40;  -- 成功
mysql> DELETE FROM t1;  -- ERROR 1142: DELETE command denied

步驟 5：回收 tom2 的 UPDATE 權限

mysql> REVOKE UPDATE ON prod.* FROM 'tom2'@'%';

重新登入後 UPDATE 即被拒絕。

3. 關鍵坑點：本地登入被匿名用戶遮蔽

現象：帳號 'tom'@'%' 遠程正常，本地 mysql -u tom -ptom 報 Access denied for user 'tom'@'localhost'。

原因：MySQL 認證排序規則——Host 匹配精確度優先，再比 User 非空優先。mysql_install_db 預設建立匿名用戶 ''@'localhost'，Host 精確匹配 localhost，優先級高於 'tom'@'%'，導致本地登入實際走匿名用戶驗證（無密碼）。

解決：

推薦：補授權 GRANT ALL ON prod.* TO 'tom'@'localhost' IDENTIFIED BY 'tom';
或刪除匿名用戶：DROP USER ''@'localhost'; FLUSH PRIVILEGES;（兼顧安全）

4. 密碼管理三板斧

方法	指令範例	適用對象
SET PASSWORD	`SET PASSWORD FOR 'tom1'@'%' = PASSWORD('oracle');`	Root 改他人密碼
mysqladmin	`mysqladmin -u tom1 -poldpass password newpass`	命令列自助改密
UPDATE user 表	`UPDATE mysql.user SET Password=PASSWORD('new') WHERE user='tom1'; FLUSH PRIVILEGES;`	批次/腳本修改

普通用戶自改密碼：SET PASSWORD = PASSWORD('newpass');（無需指定用戶名）。

5. 帳號清理標準流程

-- 1. 回收所有權限
REVOKE ALL ON prod.* FROM 'tom2'@'%';
-- 2. 刪除帳號
DROP USER 'tom2'@'%';
-- 3. 刷新生效
FLUSH PRIVILEGES;

6. 權限分層模型與常用授權範本

MySQL 權限可作用於 5 個層次：

全域：GRANT ALL ON *.* TO 'dba'@'localhost';
資料庫：GRANT SELECT ON testdb.* TO 'dev'@'%';
資料表：GRANT SELECT, INSERT ON testdb.orders TO 'app'@'%';
欄位：GRANT SELECT(id, name) ON testdb.users TO 'report'@'%';
存儲程序/函數：GRANT EXECUTE ON PROCEDURE testdb.proc TO 'dev'@'%';

常見角色授權範本

-- 唯讀報表帳號
GRANT SELECT ON prod.* TO 'report'@'%' IDENTIFIED BY 'pass';

-- 開發人員（DDL + DML）
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE TEMPORARY TABLES
   ON devdb.* TO 'dev'@'192.168.0.%' IDENTIFIED BY 'pass';

-- DBA（單庫全權）
GRANT ALL PRIVILEGES ON testdb.* TO 'dba'@'localhost' IDENTIFIED BY 'pass';

-- 超級 DBA（全例項全權）
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'pass' WITH GRANT OPTION;