踩坑！真的还有DBA不会进行MySQL用户授权？

在应用项目中大概率都遇到过这样的场景：MySQL数据库中取消用户来源主机的绑定IP限制。而这样的需求，见识过如下操作：

为了让测试用户（test）能从任意IP远程连接MySQL，随手执行了一句更新语句：

update mysql.user set host='%' where user='test' and host='192.168.56.106';

语句执行显示“影响1行”，看似一切顺利，但当尝试用test用户远程连接时，却直接报出权限错误：

ERROR 1045 (28000): Access denied for user 'test'@'xxx.xxx.xxx.xxx' (using password: YES)

明明修改成功了，为什么还是无权限？甚至有些小伙伴反复执行修改语句，重启MySQL服务，问题依然没解决，急得抓耳挠腮。

今天就以这个高频踩坑场景为切入点，把背后的原因、正确操作方法，以及隐藏的安全风险一次性讲透，帮你彻底避开这个MySQL权限陷阱。

一、 案例复现

1. 初始情况

当前MySQL数据库在192.168.56.102（3306端口），上面有个test用户（绑定的IP为192.168.56.106，即test@192.168.56.106用户）

从192.168.56.106上也能正常访问，例如：

2. 错误授权

当前有个需求，因为应用加了很多台机器，要访问数据库，为了图省事，想取消IP限制（即所有IP都可以用test账号访问数据库）。此时，某DBA执行了如下操作：

mysql> update mysql.user  set host='%' where user='test' and host='192.168.56.106';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

看上去还是很细致的，也出现了表中数据修改成功的提示，查看用户表（mysql.user）也确实修改成功了。

3. 新节点访问异常

此时，其他新增机器进行访问，出现报错

[root@ob ~]# mysql -utest -p123456 -h 192.168.56.102
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'192.168.56.108' (using password: YES)

原先192.168.56.106上的访问是正常的

4. 刷新权限

此时，有同学提醒说，需要执行flush privileges刷新权限，这样才能生效。

此时，此DBA恍然大悟的样子执行了刷新权限操作。

执行完毕后。新节点终于可以登录了。

就在众人都以为问题解决了的时候，突然有人发现，新节点执行业务sql时报错，例如：

mysql> select  count(*) from db_monitor.db_instances;
ERROR 1142 (42000): SELECT command denied to user 'test'@'192.168.56.108' for table 'db_instances'
mysql> 

5. 新的疑惑

此时开发在2个应用节点分别查看权限，看到的结果都是：

看上去都没有权限了才对，为何一个可以执行，一个不可以？

二、权限探索

很多人误以为，只要修改了mysql.user表的host字段，权限就会自动生效，这其实是对MySQL权限机制的核心误解。

MySQL的权限体系有两个关键特点，直接决定了这次操作的失败：

1. 权限生效的核心：内存缓存与磁盘表不同步

MySQL启动时，会将mysql数据库中所有授权表（包括user、db、tables_priv等）的内容读入内存，后续的权限校验，都是基于内存中的缓存数据，而非直接读取磁盘上的表文件。

我们直接用update语句修改mysql.user表，只是修改了磁盘上的数据，但内存中的权限缓存并没有同步更新。这就导致，MySQL依然按照旧的host配置（192.168.56.106）进行权限校验，自然会拒绝其他IP的连接。

简单说：直改系统表，相当于“改了配置文件但没重启服务”，等于白改，所以上述的操作需要执行flush privileges后才会生效（但只是可以登录）。

2. 权限的本质：(user, host) 二元组决定一切

MySQL中，用户的权限并不是由“用户名”单独决定的，而是由“用户名+主机”（user, host）的二元组唯一确定的——这是很多人忽略的关键细节。

比如：'test'@'192.168.56.106' 和 'test'@'%' 是两个完全独立的用户，它们的权限、密码、认证方式都可以不同。

用update语句将'test'@'192.168.56.106' 的host改成'%'，看似是“修改”，实则是将原来的用户记录替换成了'test'@'%'，但如果原来的'test'@'%' 不存在，且没有给这个新的二元组授予权限，自然会出现无权限问题。

3. 隐藏坑：权限表关联异常

MySQL的权限存储在多个关联表中，除了user表（存储全局权限），还有db表（数据库级权限）、tables_priv表（表级权限）等。直接修改user表的host字段，会导致其他权限表中的关联记录（仍关联旧的host值）无法同步匹配。即便执行了flush privileges刷新权限，也可能出现“能登录但无法操作数据库”的情况，这也是很多人误以为“update+flush就万事大吉”，却依然踩坑的核心原因。

此时我们回看一下之前数据库中的库级权限表（因为表级别的原先没有授予，所以没有记录，本次就不看了）。

可以看到，权限表里只有host是192.168.56.106的记录，这也就是为何只有192.168.56.106上可以执行业务SQL，其他节点报无权限的错误。

三、正规授权操作

知道了错误原因，解决起来就很简单了。先进行本次操作的补救方法，另外再给出正常的操作方式。下面看看正规军的操作。

1. 补救操作

如果已经执行了开头的update语句，然后也导致了当前的权限表异常的情况，那么我们就补救一下。

如果已经知道原先的账号权限（不知道的也可以通过查看权限表获取），那么可以通过执行

drop user test@'192.168.56.106';

将原账号test@'192.168.56.106'的权限删除，这样权限表就可以清理完毕了。

删除完毕后给test@'%'用户授权，此时便有了权限了

查看权限表也正常了。

2. 正常操作

正常的授权操作是新增账号的方式进行，如果不方便找原账号的密码也没有关系，可以复用现有账号信息。下面例子中我先还原回初始状态在演示。

2.1 查看原有账号信息

用如下语句可以获取原有账号的密码（加密后的），因此直接将结果拿出来修改主机就可以了。

show create user test@'192.168.56.106';

MySQL8.0及MySQL8.4中如果账号用的是caching_sha2_password认证插件的密码哈希值（hash），那么不能直接以字符串形式在CREATE USER语句中使用。否则会出现报错：

ERROR 1827 (HY000): The password hash doesn't have the expected format.

此时需要开启十六进制显示的方式进行，获取到十六进制哈希值，然后再进行上述操作即可

SET print_identified_with_as_hex = 1;

2.2 查看授权信息

查看原先账号的权限，新账号也要授予相同的权限

mysql> show  grants for  test@'192.168.56.106';

将结果拿出来，对应的主机进行修改即可。

2.3 授权并查看结果

将以上的结果拿出来，修改主机后再执行，进行创建账号及授权的操作。

再查看最终的授权结果

这样新账号就授权完毕了

3. 原账号的处理

新建的账号如果主机没有限制，即‘%’，那么可以考虑删除原账号test@'192.168.56.106'。如果新建的账号是具体IP的（推荐），例如 test@'192.168.56.108',那么原先的账号不能删除，后续授权时要对所有的涉及的账号都授权。

4. host设为%的风险及注意事项

很多人图方便，将host设为'%'，表示允许该用户从任意IP远程连接——但这其实是一个高危配置，尤其在生产环境中，必须谨慎使用。

4.1 安全风险：暴露权限入口

'%' 会匹配所有非本地IP，包括公网IP和内网其他网段，相当于把数据库用户的权限暴露在任意网络可达的位置，容易遭受暴力破解、中间人劫持等攻击，甚至导致内网横向移动风险。

建议：生产环境中，尽量不要将host设为'%'，而是限定具体的可信IP（如'192.168.56.%' 匹配整个子网，或具体IP'192.168.56.107'）。

4.2 常见误区：%不匹配localhost

很多人以为host设为'%' 就包括本地连接（localhost），其实不然，MySQL的'%' 不匹配localhost，localhost走的是Unix socket连接，而'%' 仅匹配TCP/IP连接的任意IP。

如果需要test用户既能本地登录，又能远程登录，需要分别创建两个用户：'test'@'localhost' 和 'test'@'%'，并分别授予权限。

四、总结

记住如下3点，再也不踩权限坑：

• 直接改mysql.user表后，执行flush privileges;仅能同步内存与磁盘的用户信息，无法解决关联权限表的匹配问题，仍可能出现权限异常（核心修正要点）
• MySQL权限由(user, host) 二元组决定，修改host本质是修改用户身份，需同步检查权限
• 生产环境慎用host='%'，遵循最小权限原则，限定可信IP，避免安全风险

其实MySQL的权限问题，大多是因为对其权限机制不了解，随手写代码导致的。看似简单的一句update，背后藏着不少细节陷阱。

最后想问一句：你有没有踩过MySQL权限的坑？比如修改密码后登录失败、授权后无法操作数据库？欢迎在评论区留言交流，一起避坑～