视界 · 深度阅读

MySQL 5.7和8.0用户管理操作说明

2026-07-07 253501 代码约 24 分钟阅读

本文系统介绍了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';

角色管理的优势：

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

七、完整实战案例

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

需求：

开发人员dev_user：需要app_db库的全部权限
只读用户read_user：只能查询app_db库的数据
运维人员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';

八、安全最佳实践总结

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

九、常见问题排查

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和开发者的必备技能。

到此这篇关于MySQL 5.7和8.0用户管理操作说明的文章就介绍到这了,更多相关MySQL 5.7和8.0用户管理内容请搜索爱读视界以前的文章或继续浏览下面的相关文章希望大家以后多多支持爱读视界！

您可能感兴趣的文章:

MySQL用户管理使用及详解
MySQL视图、用户管理全面指南
MySQL 用户管理和数据库权限问题
Mysql中的用户管理实践
mysql用户管理和权限设置方式
MySQL超详细实现用户管理实例
mysql用户管理操作实例分析
Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析
mysql命令行下用户管理方法分享
MySQL用户管理

读到这里，发现更多一点。探索更多内容 ↗