您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 用戶與權限管理完全指南：創建、授權、密碼、刪除、最小權限模型與實戰模板

編輯：MySQL 資料庫相關知識

前言：權限是數據安全的「護城河」

MySQL 權限體系基於 mysql.user/db/tables_priv/columns_priv/procs_priv/proxies_priv 多層授權表，採用 **Host+User** 複合主鍵識別用戶。本文系統梳理**兩種創建方式**、**分層授權模板**、**密碼管理全流程**、**刪除用戶機制**、**最小權限模型**與**實戰部署腳本**，構建 DBA 權限治理的標準化操作手冊。

一、創建用戶：GRANT vs 直操授權表

1. GRANT 方式（推薦：原子性、加密自動、刷新自動）


-- 超級管理員（僅受信任主機）
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.%' IDENTIFIED BY 'DbaPass#2024' WITH GRANT OPTION;

-- 應用讀寫賬戶（庫級）
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `app_db`.* TO 'app_rw'@'192.168.1.%' IDENTIFIED BY 'AppRwPass#2024';

-- 應用只讀賬戶
GRANT SELECT ON `app_db`.* TO 'app_ro'@'192.168.1.%' IDENTIFIED BY 'AppRoPass#2024';

-- 開發/測試賬戶（開發庫全權）
GRANT ALL PRIVILEGES ON `dev_db`.* TO 'dev_user'@'192.168.1.%' IDENTIFIED BY 'DevPass#2024';

-- 管理操作賬戶（無數據權限，僅管理命令）
GRANT RELOAD, PROCESS, SHOW DATABASES, SUPER ON *.* TO 'admin'@'10.0.0.%' IDENTIFIED BY 'AdminPass#2024';

-- 佔位賬戶（無權限，後續動態授權）
GRANT USAGE ON *.* TO 'placeholder'@'192.168.1.%' IDENTIFIED BY 'PlaceholderPass#2024';

WITH GRANT OPTION：允許將擁有的權限授予他人（僅超管）。
IDENTIFIED BY：隱式創建用戶並加密密碼（5.7+ 推薦 CREATE USER ... IDENTIFIED BY ... 再 GRANT 分離）。
同一用戶名不同 Host 視為**不同用戶**（'root'@'localhost' ≠ 'root'@'%'）。

2. 直操授權表（不推薦：易遺漏刷新、密碼需手動 `PASSWORD()`）


INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired)
VALUES ('localhost', 'monty', PASSWORD('some_pass'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N');

FLUSH PRIVILEGES;  -- 必須！否則重啟前不生效

5.7+ 密碼列為 authentication_string，Password 列廢棄。
必須執行 FLUSH PRIVILEGES 重載授權表緩存。

二、分層授權模型：從全局到列級

層級	授權表	權限範例	GRANT 語法
全局	`mysql.user`	`ALL PRIVILEGES`, `SUPER`, `RELOAD`	`GRANT ... ON .`
庫級	`mysql.db`	`SELECT, INSERT, CREATE`	GRANT ... ON `db`.*
表級	`mysql.tables_priv`	`SELECT, UPDATE (col1)`	GRANT SELECT, UPDATE (col1) ON `db`.`tbl`
列級	`mysql.columns_priv`	`SELECT (col1, col2)`	GRANT SELECT (col1, col2) ON `db`.`tbl`
存儲程序	`mysql.procs_priv`	`EXECUTE`	GRANT EXECUTE ON PROCEDURE `db`.`proc`
代理用戶	`mysql.proxies_priv`	`PROXY`	`GRANT PROXY ON 'user'@'host' TO 'proxy'@'host'`

權限生效順序：全局 → 庫 → 表 → 列（**最細粒度優先**）。
GRANT 自動填充對應授權表，**無需手動 FLUSH PRIVILEGES**。

三、密碼管理全流程

1. 設置/修改密碼（多種等效方式）


-- 方式 1：SET PASSWORD（推薦，會話/指定用戶）
SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');  -- 5.7 語法
SET PASSWORD FOR 'jeffrey'@'%' = 'biscuit';            -- 8.0 語法 (內部哈希)
-- 修改自身密碼（無需 FOR 子句）
SET PASSWORD = PASSWORD('biscuit');

-- 方式 2：ALTER USER（8.0 推薦，支援密碼策略/過期/鎖定）
ALTER USER 'jeffrey'@'%' IDENTIFIED BY 'biscuit' PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;

-- 方式 3：GRANT USAGE 修改密碼（不影響現有權限）
GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

-- 方式 4：命令行 mysqladmin（需舊密碼）
mysqladmin -u jeffrey -p'oldpass' -h host password 'newpass'

-- 方式 5：直操表（需 FLUSH PRIVILEGES）
UPDATE mysql.user SET authentication_string=PASSWORD('newpass') WHERE User='jeffrey' AND Host='%';
FLUSH PRIVILEGES;

2. 密碼策略與過期（8.0+ `validate_password` 組件）


-- 安裝組件
INSTALL COMPONENT 'file://component_validate_password';

-- 策略配置
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
SET GLOBAL validate_password.policy = 'MEDIUM';  -- LOW/MEDIUM/STRONG

-- 用戶級過期控制
ALTER USER 'app_rw'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;  -- 90 天過期
ALTER USER 'app_rw'@'%' PASSWORD EXPIRE NEVER;            -- 永不過期

3. 忘記 Root 密碼重置（標準流程）


systemctl stop mysqld
mysqld_safe --skip-grant-tables &
mysql -u root
USE mysql;
-- 5.7
UPDATE user SET authentication_string=PASSWORD('RootPass#2024') WHERE User='root';
-- 8.0
ALTER USER 'root'@'localhost' IDENTIFIED BY 'RootPass#2024';
FLUSH PRIVILEGES;
systemctl restart mysqld

四、刪除用戶：`DROP USER` 機制與陷阱


DROP USER 'jeffrey'@'%';
DROP USER 'jeffrey'@'%', 'admin'@'localhost';  -- 批量

**原子性**：從所有授權表刪除記錄，**自動刷新權限緩存**（無需 FLUSH）。
關鍵陷阱：**不自動斷開現有連接**。被刪用戶的活躍會話持續有效，直到斷開重連才失效。
如需立即生效：KILL 強制斷開（查 SHOW PROCESSLIST 獲取 ID）。

五、匿名用戶與 Root 遠程訪問：經典陷阱與修復


-- 1. 刪除匿名用戶（安裝後必做）
DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;

-- 2. Root 僅本地 + 受信任 IP（禁用 %）
DELETE FROM mysql.user WHERE User='root' AND Host='%';
-- 或修改為具體 IP
UPDATE mysql.user SET Host='10.0.0.%' WHERE User='root' AND Host='%';
FLUSH PRIVILEGES;

匿名用戶 ''@'localhost' 優先級高於 'root'@'%'，導致本地 root 登錄被誤匹配報錯 1045（詳見文章 73739）。

六、實戰部署腳本模板（版本控制友好）


-- V1.2.0__create_app_users.sql
-- 1. 創建用戶（分離創建與授權，8.0 風格）
CREATE USER IF NOT EXISTS 'app_rw'@'192.168.1.%' IDENTIFIED BY 'AppRwPass#2024' PASSWORD EXPIRE INTERVAL 90 DAY;
CREATE USER IF NOT EXISTS 'app_ro'@'192.168.1.%' IDENTIFIED BY 'AppRoPass#2024' PASSWORD EXPIRE INTERVAL 90 DAY;
CREATE USER IF NOT EXISTS 'dev_user'@'192.168.1.%' IDENTIFIED BY 'DevPass#2024' PASSWORD EXPIRE NEVER;
CREATE USER IF NOT EXISTS 'dba'@'10.0.0.%' IDENTIFIED BY 'DbaPass#2024' PASSWORD EXPIRE NEVER;

-- 2. 授權
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `app_db`.* TO 'app_rw'@'192.168.1.%';
GRANT SELECT ON `app_db`.* TO 'app_ro'@'192.168.1.%';
GRANT ALL PRIVILEGES ON `dev_db`.* TO 'dev_user'@'192.168.1.%';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.%' WITH GRANT OPTION;

-- 3. 清理安裝殘留
DELETE FROM mysql.user WHERE User = '';
DELETE FROM mysql.user WHERE User='root' AND Host='%';
FLUSH PRIVILEGES;

-- 回滾腳本
-- DROP USER 'app_rw'@'192.168.1.%', 'app_ro'@'192.168.1.%', 'dev_user'@'192.168.1.%', 'dba'@'10.0.0.%';

七、權限審計與合規檢查


-- 1. 列出所有用戶及 Host
SELECT User, Host, plugin, authentication_string FROM mysql.user ORDER BY User, Host;

-- 2. 檢查超級權限用戶
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';

-- 3. 檢查通配符 Host 用戶（風險）
SELECT User, Host FROM mysql.user WHERE Host = '%';

-- 4. 檢查空密碼/弱密碼用戶（需配合 validate_password）
SELECT User, Host FROM mysql.user WHERE authentication_string = '' OR LENGTH(authentication_string) < 20;

-- 5. 權限矩陣導出（庫級）
SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv
FROM mysql.db ORDER BY User, Host, Db;

結語

權限管理非一次配置，乃持續治理。從「GRANT 原子化創建」到「分層最小權限模型」，從「密碼策略強制」到「匿名用戶清理」、從「DROP USER 不斷連接」到「定期審計矩陣導出」——每一個環節都在為**數據機密性、完整性、可用性**築牢防線。願本文手冊助您構建「權限全生命周期精益治理」體系，讓每一個賬戶都只擁有「剛好夠用」的權力。