还在用root账户一把梭？本文将带你从零开始，系统掌握MySQL的用户管理与权限控制，涵盖5.7和8.0两个版本的核心差异与最佳实践。

一、为什么需要用户管理？

在实际生产环境中，如果所有开发人员都使用root账户操作数据库，会带来严重的安全隐患：

• 权限失控：root账户拥有所有数据库的全部权限，任何人都可以随意增删改查任何数据
• 责任不清：无法追溯具体是谁执行了危险操作
• 安全风险：一旦root密码泄露，整个数据库将面临灭顶之灾

最佳实践：为每个用户分配最小必要权限 —— 张三只能操作myest库，李四只能操作msg库，各司其职，互不干扰。

二、用户信息存储

MySQL中的所有用户信息都存储在系统数据库mysql的user表中。

-- 切换到mysql数据库
USE mysql;

-- 查看用户信息（三个核心字段）
SELECT host, user, authentication_string FROM user;

user表核心字段解释：

字段	说明
host	允许从哪个主机登录。localhost表示仅本机，%表示任意主机
user	用户名
authentication_string	经过加密算法处理后的密码密文

⚠️ 重要提示：切勿直接操作mysql.user表来增删改用户！应使用官方提供的CREATE USER、DROP USER等账户管理语句。直接修改系统表可能导致数据不一致甚至数据库损坏。

三、用户管理核心操作

3.1 创建用户（CREATE USER）

MySQL 5.7 语法：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

MySQL 8.0 语法（增强版）：

-- 基本创建
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 如果用户不存在则创建（避免重复创建报错）
CREATE USER IF NOT EXISTS '用户名'@'主机名' IDENTIFIED BY '密码';

-- 指定认证插件（解决客户端兼容性问题）
CREATE USER '用户名'@'主机名' 
IDENTIFIED WITH mysql_native_password BY '密码';

-- 随机生成密码（8.0新特性）
CREATE USER '用户名'@'主机名' IDENTIFIED BY RANDOM PASSWORD;

-- 账户锁定（创建后立即锁定，需手动解锁）
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码' ACCOUNT LOCK;

实际案例：

-- 创建一个只能从本机登录的用户（最安全）
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'MySecurePass123!';

-- 创建一个可以从192.168.1.x网段登录的用户
CREATE USER 'lisi'@'192.168.1.%' IDENTIFIED BY 'LiSiPass456!';

-- 创建一个可以从任意主机登录的用户（⚠️ 生产环境慎用）
CREATE USER 'wangwu'@'%' IDENTIFIED BY 'WangWuPass789!';

主机名格式说明：

主机格式	含义	安全性
localhost	仅本机连接	⭐⭐⭐⭐⭐
192.168.1.%	指定网段	⭐⭐⭐⭐
192.168.1.100	指定IP	⭐⭐⭐⭐⭐
%	任意主机	⭐

💡 最佳实践：生产环境应尽可能限制host范围，避免使用%通配符。

3.2 删除用户（DROP USER）

语法：

DROP USER '用户名'@'主机名';

MySQL 8.0增强：

-- 如果用户存在则删除（避免报错）
DROP USER IF EXISTS '用户名'@'主机名';

示例：

-- 正确写法：必须指定主机名
DROP USER 'zhangsan'@'localhost';

-- 错误写法：不指定主机名默认匹配'%'，会报错
DROP USER 'zhangsan';  -- ERROR 1396 (HY000)

⚠️ 易错点：删除用户时必须完整指定'用户名'@'主机名'，否则MySQL会默认匹配'用户名'@'%'，找不到则会报错。

四、密码管理

4.1 修改密码

MySQL 5.7 方式

-- 用户自己修改自己的密码
SET PASSWORD = PASSWORD('新密码');

-- root用户修改指定用户的密码
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

MySQL 8.0 方式（推荐使用ALTER USER）

-- 修改指定用户密码（8.0标准方式）
ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';

-- 修改当前用户自己的密码
ALTER USER USER() IDENTIFIED BY '新密码';

-- 修改密码时验证旧密码（增强安全性）
ALTER USER '用户名'@'主机名' 
IDENTIFIED BY '新密码' REPLACE '旧密码';

-- 随机生成新密码
ALTER USER '用户名'@'主机名' IDENTIFIED BY RANDOM PASSWORD;

-- 账户解锁/锁定
ALTER USER '用户名'@'主机名' ACCOUNT UNLOCK;
ALTER USER '用户名'@'主机名' ACCOUNT LOCK;

版本对比：

操作	MySQL 5.7	MySQL 8.0
修改自己密码	SET PASSWORD = PASSWORD('...')	ALTER USER USER() IDENTIFIED BY '...'
修改他人密码	SET PASSWORD FOR 'user'@'host' = PASSWORD('...')	ALTER USER 'user'@'host' IDENTIFIED BY '...'
密码过期设置	不支持	ALTER USER ... PASSWORD EXPIRE

4.2 密码策略（validate_password）

MySQL 5.7和8.0都支持密码强度验证，但8.0将validate_password从插件（plugin）重构为组件（component）。

查看当前密码策略：

SHOW VARIABLES LIKE 'validate_password.%';

默认策略（8.0） ：

变量	默认值	说明
validate_password.length	8	密码最小长度
validate_password.mixed_case_count	1	至少包含1个大写和1个小写字母
validate_password.number_count	1	至少包含1个数字
validate_password.special_char_count	1	至少包含1个特殊字符
validate_password.policy	MEDIUM	策略等级：LOW/MEDIUM/STRONG

临时调整密码策略：

-- 降低密码强度要求（仅当前会话生效）
SET GLOBAL validate_password.length = 6;
SET GLOBAL validate_password.policy = 'LOW';

MySQL 8.0持久化配置（新特性）：

-- 永久生效（写入配置文件）
SET PERSIST validate_password.length = 6;

⚠️ 常见报错：设置简单密码时可能报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，需要调整密码策略或使用更复杂的密码。

五、权限管理

5.1 MySQL权限列表

MySQL提供了丰富的权限控制粒度：

权限	适用对象	说明
ALL [PRIVILEGES]	全局/库/表	授予所有权限（不含GRANT OPTION）
CREATE	库/表/索引	创建数据库、表或索引
DROP	库/表	删除数据库或表
SELECT	表	查询数据
INSERT	表	插入数据
UPDATE	表	更新数据
DELETE	表	删除数据
ALTER	表	修改表结构
CREATE USER	服务器管理	创建用户
SHOW DATABASES	服务器管理	查看所有数据库
RELOAD	服务器管理	执行FLUSH操作
SHUTDOWN	服务器管理	关闭数据库
PROCESS	服务器管理	查看进程列表
SUPER	服务器管理	高级管理权限

5.2 授予权限（GRANT）

MySQL 5.7 方式（一条语句完成创建+授权）

-- 如果用户不存在则自动创建，并授予权限
GRANT 权限列表 ON 库名.对象名 TO '用户名'@'主机名' IDENTIFIED BY '密码';

示例：

-- 5.7：创建用户并授权一步完成
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'pass123';

MySQL 8.0 方式（必须先创建用户再授权）

-- ❌ 8.0中以下写法会报错
GRANT SELECT ON mydb.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'pass123';
-- ERROR: You are not allowed to create a user with GRANT

-- ✅ 正确做法：分两步
-- 步骤1：创建用户
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'pass123';
-- 步骤2：授予权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'zhangsan'@'localhost';

权限授予示例：

-- 授予单张表的查询权限
GRANT SELECT ON mydb.users TO 'zhangsan'@'localhost';

-- 授予某个库的所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'zhangsan'@'localhost';

-- 授予所有库的所有权限（⚠️ 等同于root，极度危险）
GRANT ALL PRIVILEGES ON *.* TO 'zhangsan'@'localhost';

-- 授予多个权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'zhangsan'@'localhost';

-- 授予权限并允许该用户将权限授予他人
GRANT SELECT ON mydb.* TO 'zhangsan'@'localhost' WITH GRANT OPTION;

💡 权限最小化原则：只授予用户完成工作所必需的最小权限集。例如，一个报表查询用户只需要SELECT权限，不需要INSERT、UPDATE或DELETE。

5.3 查看权限（SHOW GRANTS）

-- 查看当前用户的权限
SHOW GRANTS;

-- 查看指定用户的权限
SHOW GRANTS FOR '用户名'@'主机名';

输出示例：

mysql> SHOW GRANTS FOR 'zhangsan'@'localhost';
+--------------------------------------------------+
| Grants for zhangsan@localhost                     |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost'      |
| GRANT SELECT, INSERT, UPDATE ON `mydb`.* TO ...   |
+--------------------------------------------------+

说明：GRANT USAGE ON *.*表示该用户没有任何全局权限，只有登录权限。

5.4 回收权限（REVOKE）

语法：

REVOKE 权限列表 ON 库名.对象名 FROM '用户名'@'主机名';

示例：

-- 回收用户对mydb库的所有权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'zhangsan'@'localhost';

-- 回收特定权限
REVOKE DELETE ON mydb.* FROM 'zhangsan'@'localhost';

-- 回收GRANT OPTION权限
REVOKE GRANT OPTION ON mydb.* FROM 'zhangsan'@'localhost';

MySQL 8.0增强：

-- 如果用户不存在则忽略（避免报错）
REVOKE IF EXISTS SELECT ON mydb.* FROM 'zhangsan'@'localhost';

5.5 刷新权限（FLUSH PRIVILEGES）

什么情况需要执行FLUSH PRIVILEGES？

• 使用CREATE USER、GRANT、REVOKE、DROP USER等账户管理语句时：不需要！MySQL会自动将更改加载到内存中。
• 直接操作mysql.user表时：必须执行！因为直接修改系统表不会自动刷新权限缓存。

-- 重新加载权限表，使修改立即生效
FLUSH PRIVILEGES;

⚠️ 重要：一般情况下，使用官方账户管理语句后不需要执行FLUSH PRIVILEGES。只有当你直接操作了权限系统表时才需要。

六、MySQL 5.7 vs 8.0 核心差异总结

特性	MySQL 5.7	MySQL 8.0
用户创建与授权	GRANT可一步完成创建+授权	必须先CREATE USER，再GRANT
默认认证插件	mysql_native_password	caching_sha2_password（更安全）
密码管理	功能相对简单	支持密码过期、历史、重试锁定等
角色管理	❌ 不支持	✅ 支持（CREATE ROLE）
配置持久化	SET GLOBAL仅临时生效	SET PERSIST可永久生效
原子性DDL	多用户操作可能部分成功	要么全部成功，要么全部回滚
权限表引擎	MyISAM（非事务）	InnoDB（事务性）

6.1 认证插件差异详解

MySQL 8.0将默认认证插件从mysql_native_password改为caching_sha2_password，提供了更强的密码加密安全性。

兼容性问题：部分旧客户端或应用程序连接MySQL 8.0时可能失败。

解决方案：将用户认证插件改回mysql_native_password：

-- 创建时指定认证插件
CREATE USER 'zhangsan'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password';

-- 修改已存在用户的认证插件
ALTER USER 'zhangsan'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'new_password';

6.2 MySQL 8.0 角色管理（Role）

角色（Role）是MySQL 8.0引入的重要特性，可以理解为"权限模板"——将一组权限打包命名，然后批量分配给多个用户。

基本操作：

-- 1. 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 2. 为角色授予权限
GRANT SELECT ON app_db.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_admin';

-- 3. 将角色授予用户
GRANT 'app_read' TO 'zhangsan'@'localhost';
GRANT 'app_write' TO 'lisi'@'localhost';

-- 4. 设置默认激活的角色（登录时自动激活）
SET DEFAULT ROLE 'app_read' TO 'zhangsan'@'localhost';

-- 5. 查看当前激活的角色
SELECT CURRENT_ROLE();

-- 6. 手动切换角色（会话内临时切换）
SET ROLE 'app_write';

-- 7. 回收角色
REVOKE 'app_read' FROM 'zhangsan'@'localhost';

-- 8. 删除角色
DROP ROLE 'app_read';

角色管理的优势：

• 大幅简化多用户场景下的权限管理
• 权限变更只需修改角色，所有拥有该角色的用户自动生效
• 更清晰地组织和管理权限

七、完整实战案例

场景：为三个不同角色创建用户

需求：

1. 开发人员dev_user：需要app_db库的全部权限
2. 只读用户read_user：只能查询app_db库的数据
3. 运维人员ops_user：需要所有库的只读权限

MySQL 5.7 实现

-- 开发人员：创建并授权一步完成
GRANT ALL PRIVILEGES ON app_db.* TO 'dev_user'@'localhost' IDENTIFIED BY 'DevPass@2024';

-- 只读用户
GRANT SELECT ON app_db.* TO 'read_user'@'localhost' IDENTIFIED BY 'ReadPass@2024';

-- 运维人员：所有库只读
GRANT SELECT ON *.* TO 'ops_user'@'localhost' IDENTIFIED BY 'OpsPass@2024';

MySQL 8.0 实现（推荐使用角色）

-- 步骤1：创建角色
CREATE ROLE 'dev_role', 'read_role', 'ops_role';

-- 步骤2：为角色授予权限
GRANT ALL PRIVILEGES ON app_db.* TO 'dev_role';
GRANT SELECT ON app_db.* TO 'read_role';
GRANT SELECT ON *.* TO 'ops_role';

-- 步骤3：创建用户
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'DevPass@2024';
CREATE USER 'read_user'@'localhost' IDENTIFIED BY 'ReadPass@2024';
CREATE USER 'ops_user'@'localhost' IDENTIFIED BY 'OpsPass@2024';

-- 步骤4：将角色授予用户
GRANT 'dev_role' TO 'dev_user'@'localhost';
GRANT 'read_role' TO 'read_user'@'localhost';
GRANT 'ops_role' TO 'ops_user'@'localhost';

-- 步骤5：设置默认激活角色
SET DEFAULT ROLE 'dev_role' TO 'dev_user'@'localhost';
SET DEFAULT ROLE 'read_role' TO 'read_user'@'localhost';
SET DEFAULT ROLE 'ops_role' TO 'ops_user'@'localhost';

八、安全最佳实践总结

1. 最小权限原则：只授予用户完成工作所必需的最小权限
2. 限制登录来源：尽量使用localhost或指定IP，避免使用%
3. 使用强密码：满足密码复杂度要求（长度≥8，包含大小写字母、数字、特殊字符）
4. 定期审计：定期检查用户列表和权限分配，清理无用账户
5. 生产环境禁用root远程登录：root账户应仅限localhost访问
6. 启用密码策略：配置validate_password强制密码复杂度
7. 使用角色管理（8.0+）：通过角色简化权限管理
8. 密码定期更换：利用密码过期策略强制用户定期更换密码

九、常见问题排查

Q1：为什么创建用户后无法登录？

• 检查host是否正确匹配（localhost vs % vs IP）
• 检查密码是否正确
• 检查用户是否被锁定（ACCOUNT LOCK）

Q2：为什么授权后权限不生效？

• 确认使用的是CREATE USER+GRANT（8.0）而非GRANT ... IDENTIFIED BY
• 新开的会话才会加载新权限
• 检查是否授予了正确的数据库和对象

Q3：8.0中执行GRANT报错"not allowed to create a user with GRANT"

• 这是8.0的安全改进，必须先CREATE USER再GRANT

Q4：旧客户端连接MySQL 8.0失败

• 8.0默认使用caching_sha2_password认证插件
• 将用户认证插件改为mysql_native_password即可兼容

---

📌 总结：MySQL用户管理是数据库安全的第一道防线。从5.7到8.0，MySQL在用户管理和权限控制方面不断增强，引入了角色管理、更安全的认证插件、精细化的密码策略等特性。掌握这些知识，是每一位DBA和开发者的必备技能。