MySQL 用戶賬號管理：GRANT 授權、密碼策略與最佳實踐

MySQL 用戶賬號管理完全指南

MySQL 採用「用戶@主機」二元組識別賬號，權限分級為全局、庫、表、列、程序。本文詳細講解 GRANT 授權語法、直接操作授權表、密碼管理、常見場景與安全最佳實踐。

一、用戶標識與認證模型

• 賬號格式：'user_name'@'host_spec'
• Host 匹配：% (任意主機)、localhost (本地 Socket)、192.168.1.% (網段)、host.domain (域名)。
• 排序優先級：具體 Host > 模糊 Host > %。'user'@'localhost' 優先於 'user'@'%'，防止匿名用戶搶佔。
• 認證插件：caching_sha2_password (8.0 默認)、mysql_native_password、auth_socket (Linux root 免密)。

二、GRANT 授權語法 (推薦方式)

GRANT priv_type [(columns)] ON db_obj TO 'user'@'host' [IDENTIFIED BY 'pass'] [WITH GRANT OPTION] [REQUIRE SSL];

權限層級對照表

三、典型授權場景

1. 超級管理員 (僅本地)

CREATE USER 'dba'@'localhost' IDENTIFIED BY 'DbaPass123!';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

2. 應用讀寫賬號 (特定 IP 段)

CREATE USER 'app_rw'@'192.168.1.%' IDENTIFIED BY 'AppRwPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'192.168.1.%';

3. 僅讀報表賬號 (任意主機)

CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnlyPass123!';
GRANT SELECT ON reporting_db.* TO 'readonly'@'%';

4. 開發人員 DDL 權限 (特定庫)

CREATE USER 'dev'@'10.0.0.%' IDENTIFIED BY 'DevPass123!';
GRANT CREATE, ALTER, DROP, INDEX, CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON dev_db.* TO 'dev'@'10.0.0.%';

5. 複製賬號 (從庫 IP)

CREATE USER 'repl'@'192.168.1.101' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101';

6. 列級權限 (敏感字段隔離)

GRANT SELECT (id, name, email) ON users TO 'api'@'%';
GRANT UPDATE (name, email) ON users TO 'api'@'%';
-- 不授權 password_hash, salt, ssn 等敏感列

四、密碼管理與安全策略

-- 修改密碼 (8.0)
ALTER USER 'user'@'host' IDENTIFIED BY 'NewPass123!';
-- 或
SET PASSWORD FOR 'user'@'host' = 'NewPass123!';

-- 密碼過期策略
ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'user'@'host' PASSWORD HISTORY 5;          -- 禁止最近 5 次密碼
ALTER USER 'user'@'host' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;  -- 鎖定策略

-- 強制 SSL 連接
GRANT SELECT ON db.* TO 'user'@'%' REQUIRE SSL;
ALTER USER 'user'@'host' REQUIRE SSL;

五、直接操作授權表 (不推薦，理解原理用)

授權表位於 mysql 系統庫：

• user：全局權限、認證信息
• db：庫級權限
• tables_priv：表級權限
• columns_priv：列級權限
• procs_priv：程序級權限
• proxies_priv：代理用戶權限

-- 等同 GRANT ALL ON *.* TO 'u'@'h' IDENTIFIED BY 'p';
INSERT INTO mysql.user (Host, User, authentication_string)
VALUES ('h', 'u', PASSWORD('p'));  -- 5.7 以下
-- 8.0 密碼存 authentication_string 需用 PASSWORD() 或採用掛載插件

UPDATE mysql.user SET Select_priv='Y', Insert_priv='Y' WHERE User='u' AND Host='h';
FLUSH PRIVILEGES;  -- 必須執行使生效 (GRANT 自動刷新)

六、查看與審計權限

-- 當前用戶
SHOW GRANTS;
-- 指定用戶
SHOW GRANTS FOR 'app'@'%';

-- 審計所有用戶權限
SELECT User, Host,
  Select_priv, Insert_priv, Update_priv, Delete_priv,
  Create_priv, Drop_priv, Grant_priv, Super_priv
FROM mysql.user
ORDER BY User, Host;

七、回收權限

REVOKE INSERT, UPDATE ON app_db.* FROM 'app'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_dba'@'localhost';
-- 徹底移除用戶
DROP USER 'old_dba'@'localhost';

八、最佳實踐清單

1. 最小權限原則：業務賬號僅授必要 CRUD，拒絕 GRANT ALL、DROP、SUPER。
2. 主機限制：避免 '%'，綁定應用服務器 IP 段或專用 VPC CIDR。
3. 賬號分離：讀寫分離賬號、只讀賬號、管理賬號、備份賬號、監控賬號互不混用。
4. 密碼複雜度：≥12 位、大小寫、數字、特殊字符，定期輪換，配置 validate_password 插件強制。
5. 審計日誌：開啟 audit_log 記錄所有 DDL/DML/登入/失敗嘗試。
6. 定期清理：SELECT User, Host FROM mysql.user WHERE ... 發現無用/殘留賬號及時 DROP USER。
7. 避免共用 root：root 僅供緊急運維，日常禁用或僅 localhost + auth_socket。

MySQL 權限系統雖顯老舊 (無角色 RBAC，8.0 引入 CREATE ROLE)，但掌握 GRANT 核心語法與最小權限原則，配合自動化腳本管理賬號全生命週期，即可構建合規安全的數據庫訪問控制體系。

1. 上一篇文章： MySQL 入門教程：基礎概念、SQL 語法與常用操作指南
2. 下一篇文章： Linux 下 MySQL 安裝、配置、備份還原與日常運維命令大全

MySQL 資料庫相關知識

解決 MySQL 客戶端輸出視窗中文亂碼：字符集診斷與動態切換實戰

在 Windows CMD 下使用 MySQL 客戶端查詢中

《MySQL必知必會》讀書筆記：第三章 顯示資料庫與結構

檢視表結構 SHOW COLUMNS FROM tablen

資料庫通用連接技術演進：從 ODBC 到 ADO、JDBC 深度解析

早期各資料庫擁有專屬通訊協議與介面，開發者需為每種資料庫撰寫

MySQL 修改用戶密碼四大方法圖文詳解：mysqladmin、UPDATE、SET PASSWORD、root 密碼丟失救援

方法一：mysqladmin 命令行工具（最簡單） # 格式