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

MySQL 用戶與權限系統完整指南：從原理到實戰操作

編輯：MySQL 綜合教學

MySQL 用戶與權限系統完整指南：從原理到實戰操作

MySQL 的權限系統基於「用戶@主機」模型，採用分層授權機制。本文系統整理權限表結構、用戶命名規則、權限層級、核心管理指令與常見問題處理（含密碼重置、連線慢優化）。

一、權限系統核心常識

驗證流程：連線階段檢查「用戶@主機」+ 密碼 → 查詢階段按權限表逐層檢查（全域 → 庫 → 表 → 欄 → 程序/函數）。
權限資料存放於 mysql 系統庫的授權表中，啟動時載入記憶體，**權限變更需重新連線生效**（或執行 FLUSH PRIVILEGES）。
用戶唯一標識 = user_name@host_name，同名不同 host 視為不同用戶。
兩階段存取控制：連線驗證 + 語句授權檢查。

二、核心授權表結構（`mysql` 資料庫）

表名	用途	關鍵欄位
`user`	用戶帳號、全域權限、密碼雜湊、SSL/資源限制	Host, User, authentication_string, Select_priv, Super_priv...
`db`	庫級權限	Host, Db, User, Select_priv, Insert_priv...
`tables_priv`	表級權限	Host, Db, User, Table_name, Table_priv, Column_priv
`columns_priv`	欄級權限	Host, Db, User, Table_name, Column_name, Column_priv
`procs_priv`	預存程序/函數權限	Host, Db, User, Routine_name, Routine_type, Proc_priv
`proxies_priv`	代理用戶權限	Host, User, Proxied_host, Proxied_user, With_grant
`global_grants` (8.0+)	動態權限註冊	User, Host, Privilege, With_grant_option

註： host 表已廢棄 (5.6.7 移除)。

三、用戶命名規則

用戶名@主機
用戶名：最長 32 字元 (8.0 前 16 字元)
主機：
  - 主機名：localhost, db.example.com
  - IP：192.168.1.10
  - CIDR：192.168.0.0/255.255.0.0
  - 通配符：%, 192.168.%.%, %.example.com

四、權限層級分類

層級	典型權限	適用範圍
服務管理	SUPER, SHUTDOWN, RELOAD, PROCESS, FILE	全域 (.)
庫 (Database)	CREATE, DROP, EVENT	特定庫 (db.*)
表 (Table)	SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES	特定表 (db.tbl)
欄 (Column)	SELECT, INSERT, UPDATE	特定欄 (db.tbl.col)
程序/函數	EXECUTE, ALTER ROUTINE	特定程序 (db.proc)
動態權限 (8.0+)	BACKUP_ADMIN, CONNECTION_ADMIN, ROLE_ADMIN...	全域

五、核心管理指令速查

-- 建立用戶
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass#2024';

-- 授權 (支援通配符、指定欄位)
GRANT SELECT, INSERT, UPDATE ON `mydb`.* TO 'app_user'@'192.168.1.%';
GRANT SELECT (`id`,`name`) ON `mydb`.`users` TO 'report_user'@'%';

-- 回收權限
REVOKE INSERT ON `mydb`.* FROM 'app_user'@'192.168.1.%';

-- 查看權限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';
SHOW GRANTS;  -- 當前用戶

-- 修改密碼 (8.0+)
ALTER USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'NewPass#2024';

-- 刪除用戶
DROP USER 'app_user'@'192.168.1.%';

-- 重命名用戶
RENAME USER 'old'@'%' TO 'new'@'%';

六、實戰範例

-- 1. 查看所有用戶
SELECT User, Host, plugin FROM mysql.user;

-- 2. 建立超級管理員 (僅限本地)
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'DbaPass#2024';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

-- 3. 建立唯讀報表用戶
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly#2024';
GRANT SELECT ON `prod_db`.* TO 'readonly'@'%';

-- 4. 移除用戶所有權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'readonly'@'%';

七、常見問題處理

1. 忘記 root 密碼重置

# 方法 A：修改啟動腳本 /etc/init.d/mysqld (或 systemd override)
# 在 mysqld_safe 參數加入 --skip-grant-tables --skip-networking
systemctl restart mysqld

# 登入無需密碼
mysql -u root
UPDATE mysql.user SET authentication_string=PASSWORD('NewRootPass#2024') WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

# 移除啟動參數並重啟
systemctl restart mysqld

修改啟動腳本示意