MySQL8.0与5.7用户管理及授权区别

MySQL 8.0与5.7在用户管理和权限授权方面，核心区别是：8.0移除了PASSWORD()函数和PASSWORD相关的旧式密码管理方式，默认认证插件、权限元数据存储及部分授权语法也有变化。

---

1.核心区别对比

对比项	MySQL 5.7	MySQL 8.0
默认认证插件	通常为mysql_native_password	默认是caching_sha2_password
创建用户	CREATE USER	CREATE USER
创建用户并设置密码	IDENTIFIED BY	IDENTIFIED BY
密码管理	支持旧式PASSWORD()函数	移除PASSWORD()函数
修改密码	SET PASSWORD等方式	ALTER USER或SET PASSWORD
创建用户时直接授权	部分版本允许GRANT隐式创建用户	不允许，必须先创建用户
权限存储	系统权限表	系统权限表，内部实现和数据字典有变化
授权后刷新权限	通常不需要	通常不需要
GRANT设置密码	不支持作为常规方式	同样不支持
mysql.user表	可查询，部分字段可用于管理	仍可查询，但不应直接修改系统表
mysql_native_password	默认可用	8.0可用，但从8.0.34开始弃用，后续版本有进一步变化

注意：MySQL8.0内部的小版本存在差异，特别是认证插件支持情况。

2.用户创建：5.7和8.0基本一致

2.1创建普通用户

MySQL5.7和8.0都可以使用：

CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass_123!';

查看用户：

SELECT user, host
FROM mysql.user
ORDER BY user, host;

注意：'app_user'@'localhost'和'app_user'@'%'是两个不同的账户。%通常表示允许从匹配的远程主机连接，但仍受网络、连接配置和其他安全限制影响。

2.2指定认证插件

MySQL8.0默认使用caching_sha2_password，也可以显式指定：

CREATE USER 'app_user'@'%'
IDENTIFIED WITH caching_sha2_password
BY 'StrongPass_123!';

如果旧版客户端不支持该认证插件，可能出现Authentication plugin 'caching_sha2_password' cannot be loaded等连接错误。

可以先检查账户实际使用的认证插件：

SELECT user, host, plugin
FROM mysql.user
WHERE user = 'app_user';

3.用户授权

3.1先创建用户/再授权

MySQL5.7和8.0都推荐采用这种方式。

-- 1.创建用户
CREATE USER 'app_user'@'%'
IDENTIFIED BY 'StrongPass_123!';

-- 2.授予指定数据库的常用读写权限
GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.*
TO 'app_user'@'%';

-- 3.查看授权
SHOW GRANTS FOR 'app_user'@'%';

3.2不再依靠GRANT隐式创建用户

旧式脚本可能直接执行：

GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.*
TO 'app_user'@'%'
IDENTIFIED BY 'StrongPass_123!';

这种在旧版本中使用的GRANT ... IDENTIFIED BY ...方式，在MySQL8.0中不可用。

MySQL8.0应拆成两条语句：

1.
CREATE USER 'app_user'@'%'
IDENTIFIED BY 'StrongPass_123!';

2.
GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.*
TO 'app_user'@'%';

3.3常见授权范围

授权范围	SQL示例	含义
全局	ON *.*	整个实例
数据库	ON app_db.*	指定数据库所有表
单表	ON app_db.orders	指定表
指定列	ON app_db.orders配合列名	指定列的特定权限

示例：

-- 只读指定数据库
GRANT SELECT
ON app_db.*
TO 'readonly'@'%';

-- 完整的数据读写权限，但不包含建库、授权等管理权限
GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.*
TO 'app_user'@'%';

-- 授予指定表的查询权限
GRANT SELECT
ON app_db.orders
TO 'report_user'@'%';

注意：GRANT ALL PRIVILEGES ON app_db.*并不等于授予整个实例的所有管理权限，也不意味着自动拥有GRANT OPTION。

4.修改密码和认证方式

4.1修改密码

推荐在5.7和8.0中统一使用ALTER USER：

ALTER USER 'app_user'@'%'
IDENTIFIED BY 'NewStrongPass_456!';

MySQL8.0还支持修改认证插件：

ALTER USER 'app_user'@'%'
IDENTIFIED WITH caching_sha2_password
BY 'NewStrongPass_456!';

旧式写法：

SET PASSWORD FOR 'app_user'@'%'
= PASSWORD('NewStrongPass_456!');

在MySQL5.7中已经弃用，并在8.0中移除。替换为ALTER USER即可。

4.2锁定和解锁用户

MySQL8.0可以直接使用：

-- 锁定用户，禁止新连接
ALTER USER 'app_user'@'%' ACCOUNT LOCK;

-- 解锁用户
ALTER USER 'app_user'@'%' ACCOUNT UNLOCK;

5.撤销权限、删除用户

5.1撤销指定权限

REVOKE INSERT, UPDATE, DELETE
ON app_db.*
FROM 'app_user'@'%';

这会撤销对应数据库范围内的指定权限，不会删除用户，也不会撤销其其他权限。

5.2撤销全部直接授权

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'app_user'@'%';

5.3删除用户

DROP USER 'app_user'@'%';

DROP USER会删除账户本身，而REVOKE只是撤销权限。两者用途不同。

6.MySQL8.0新增及增强的权限管理能力

MySQL8.0不仅调整了旧语法，还增加和完善了多项管理功能。

功能	说明
角色管理	可以将权限授予角色，再将角色分配给用户
动态权限	支持按需授予部分管理权限，减少对SUPER的依赖
密码策略	支持密码历史、密码复用限制、过期策略等
登录失败限制	支持配置连续登录失败次数和临时锁定时间
双密码	从8.0.14开始支持部分账户的主密码与辅助密码切换
原子性	权限表采用InnoDB，账户管理操作的事务性得到改善

6.1角色管理示例

例如，创建一个只读角色，然后分配给多个用户：

-- 创建角色
CREATE ROLE 'app_readonly';

-- 给角色授权
GRANT SELECT ON app_db.*
TO 'app_readonly';

-- 创建用户
CREATE USER 'user_a'@'%'
IDENTIFIED BY 'StrongPass_123!';

-- 将角色分配给用户
GRANT 'app_readonly'
TO 'user_a'@'%';

-- 设置为默认角色
SET DEFAULT ROLE 'app_readonly'
TO 'user_a'@'%';

后续只需要修改角色的权限，就可以统一调整拥有该角色的用户权限。