MySQL用户Hash查询与系统表详解：从Access denied到认证插件

如果你的工作涉及维护 MySQL 实例那么“用户连不上库”这类问题一定不陌生。业务方丢来一张报错截图写着Access denied for user xxx10.0.0.x (using password: YES)你大概率会打开 MySQL 内置系统表里的mysql.user表去看这个账号的plugin和authentication_string字段。所谓用户 hash 查询查的其实就是这张表里存的那串“密码影子”。这篇文章我想把这件事彻底讲透MySQL 系统表到底有哪些、mysql.user里的字段怎么读、authentication_string存的 hash 是什么格式、不同认证插件之间有什么区别以及日常排查和迁移账号时怎么用、有哪些坑。适合看得懂 SQL、但还没认真研究过系统表的开发和运维同学也适合准备系统学习数据库权限管理的朋友。1. 绕不开的系统表从一次 Access denied 说起1.1 四个内置库先分清楚谁管什么MySQL 安装完成后默认就会带上几个系统库mysql、information_schema、performance_schema从 5.7 开始还多了一个sys。很多人平时只在意业务库对这几个“看起来很官方”的库从没打开过直到账号出问题才被迫打开mysql.user。我给这四个库一个非常直白的定位。库名里面有什么一句话理解mysql账号、权限、角色、时区、事件调度等系统数据核心“户籍档案室”账号信息和权限规则都落在这里information_schema所有库、表、列、索引的元数据视图只读的“信息大厅”不实际存数据performance_schema等待事件、线程、IO、锁等运行时数据数据库的“实时监控探头”sysperformance_schema的封装视图把监控数据整理成“体检报告”方便人看我们这篇文章的主角是mysql库里的表尤其是mysql.user。你执行CREATE USER、ALTER USER、GRANT、SET PASSWORD时MySQL 都会把这些操作翻译成对系统表的写入。用户发起登录时服务端也会先去mysql.user里找匹配的账号再根据记录里的认证插件和 hash 来完成身份校验。1.2 为什么业务再大也逃不过这张表登录流程可以简化成三步客户端发起连接MySQL 根据来源 IP 和用户名去mysql.user里找匹配记录找到之后看这条记录的plugin是什么认证插件按插件协议进行挑战-响应校验最终用authentication_string里保存的 hash 判断密码是否正确。注意一个很容易忽略的细节匹配记录时要同时看User和Host。比如库里同时存在devlocalhost和dev%两条记录你在本机用dev登录时MySQL 会优先匹配更具体的localhost条目而不是%。如果这两条记录的密码不一样就会出现“本机怎么都登不上但远程连一下却成功了”的诡异现象。排查这种问题不看mysql.user根本无从下手。再说一句权限相关的话mysql.user表默认只允许具备管理权限的账号读取普通业务账号连SELECT都会被拒绝。你能直接查这张表本身就说明你已经被授予了系统表访问权限。2. mysql.user每个账号的“户籍档案”2.1 先看全貌一条命令看所有字段想了解这张表的完整结构最直接的办法是执行SHOW COLUMNS FROM mysql.user;对于日常排查你只需要抓住几个核心字段。字段含义说明Host允许登录的来源主机可以是localhost、具体 IP、网段或%表示任意主机User用户名User为空时表示匿名账号生产环境建议直接禁用plugin认证插件mysql_native_password、caching_sha2_password等authentication_string密码哈希不是明文密码是经过不可逆算法计算后的字符串password_expired密码是否已过期Y表示该账号下次登录后会被要求改密password_lifetime密码有效期天数超过期限后密码失效account_locked账号是否锁定Y时无论密码是否正确都无法登录MySQL 8.0 里这张表已经从 MyISAM 换成了 InnoDB支持事务但核心字段的定位没有变。你在 5.7 里能看到的User、Host、plugin、authentication_string在 8.0 里同样存在。2.2 authentication_string不是密码而是密码的影子很多人第一次打开mysql.user看到authentication_string字段里的字符串会下意识以为这是某种加密后的密码。准确地说它是一段密码哈希。哈希函数是单向的你把明文密码喂进去能得到一长串看起来像乱码的结果但这串结果几乎不可能反推出原来的密码。就好比你只在保险柜登记处留下自己指纹的特征值而不是把整根手指存放在那里。服务端校验时只需要把用户输入密码再次计算哈希然后与authentication_string里的值做比对一致就通过。这也是为什么数据库即使被拖库攻击者拿到authentication_string也无法直接登录。但弱口令依然有被暴力碰撞的风险这就是为什么我一直强调生产环境必须强制复杂密码。2.3 plugin认证插件决定 hash 长什么样MySQL 的认证插件不是只有一种常见的主要有三种。认证插件hash 样式示例说明mysql_native_password*加 40 位十六进制MySQL 5.7 默认8.0 仍兼容caching_sha2_password$A$005开头MySQL 8.0 默认安全性更高sha256_password$5$开头使用较少部分场景用于 SSL 链路authentication_string里的内容长什么样完全取决于plugin字段。你看到$A$开头的 hash说明这个账号用的是caching_sha2_password看到*开头的 41 位字符串说明是mysql_native_password。这两者不能混用后面我会专门讲混用导致的登录失败。这里顺便解释一下为什么 8.0 把默认插件换成了caching_sha2_passwordmysql_native_password使用的加密算法相对较弱而且认证过程中对随机挑战数的处理方式在安全性上有短板。caching_sha2_password在保证兼容性的前提下提供了更强的加密与更合理的认证流程代价就是老版本客户端不认识它容易报“Authentication plugin cannot be loaded”。3. 用户 Hash 查询实操四条 SQL 覆盖日常大多数需求3.1 查全量所有账号和它的 hash想知道实例里到底有哪些账号以及各自的 hash 长什么样直接查SELECT User, Host, plugin, authentication_string FROM mysql.user\G这里推荐用\G结尾因为authentication_string内容较长横向表格会非常拥挤。如果只想看部分账号加上WHERE条件即可比如SELECT User, Host, plugin, authentication_string FROM mysql.user WHERE User app_user;如果你想快速判断所有账号使用的认证插件分布可以这样查SELECT User, Host, plugin, LEFT(authentication_string, 4) AS hash_head, LENGTH(authentication_string) AS hash_len FROM mysql.user;hash_head显示 hash 字段的前几个字符一眼就能看出是*开头还是$A$开头。hash_len显示长度mysql_native_password的 hash 长度恒为 41caching_sha2_password的 hash 通常会更长且包含换行符。3.2 按特征识别 hash 类型有时候你并不想逐条看而是想批量确认哪些账号用了什么插件。可以用一个CASE WHEN把 hash 类型映射成可读的文本SELECT User, Host, CASE WHEN authentication_string LIKE *% THEN mysql_native_password WHEN authentication_string LIKE $A$% THEN caching_sha2_password WHEN authentication_string LIKE $5$% THEN sha256_password WHEN authentication_string OR authentication_string IS NULL THEN 空密码或未设置 ELSE 其他 END AS hash_style FROM mysql.user;这里有个实操细节caching_sha2_password的 hash 在mysql.user里可能被存储为多行文本直接查询时你会看到authentication_string字段的值中间有换行看起来像数据损害一样。其实这是正常现象。识别方式就是看它是否以$A$开头不用因为换行而慌张。3.3 Hash 用于账号迁移什么情况下复制是安全的“用户 hash 查询”最常见的用途之一是把旧环境账号迁移到新环境时尝试直接拷贝authentication_string。这里我给一个明确结论相同明文密码加上相同认证算法计算出来的 hash 是确定的。也就是说如果源实例和目标实例的plugin一致复制 hash 是理论上可行的等于把密码也复制过去了。但实际生产环境我不推荐无脑复制。跨版本迁移时会遇到两个问题一是 5.7 默认mysql_native_password8.0 默认caching_sha2_password你把 5.7 的*开头 hash 塞进 8.0 账号里必须同时把plugin也改成mysql_native_password二是客户端驱动如果不支持新插件就算 hash 复制成功登录时依然报错。更干净的做法是迁移时在目标实例上重新执行完整的账号创建语句比如CREATE USER app_user% IDENTIFIED WITH caching_sha2_password BY 新密码;然后单独授权。这样能避免各种隐蔽的兼容性问题。3.4 查指定账号的更直观方式如果只想看一个账号的完整认证信息其实有比查mysql.user更人类友好的方式SHOW CREATE USER app_user%;输出大概是这样的形式CREATE USER app_user% IDENTIFIED WITH caching_sha2_password AS $A$005$... REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK这个输出把插件、hash、密码过期策略、账号锁定状态全部列了出来非常适合直接贴在排查报告里。对比直接查表SHOW CREATE USER会显示 MySQL 自己理解的标准格式也更容易发现字段之间的不一致。4. 不止 mysql.user系统表之间的权限拼图4.1 权限分层全局、库、表、列mysql.user只是权限体系里的第一层。MySQL 的权限校验是分层的mysql.user全局权限影响所有库mysql.db库级权限比如某个账号只能操作指定数据库mysql.tables_priv表级权限精确到某张表mysql.columns_priv列级权限精确到某个字段mysql.procs_priv存储过程、函数权限。判断一个用户是否有权限执行某操作时MySQL 会从全局权限开始逐级往下检查。如果mysql.user里对应权限为Y直接放行如果为N继续看mysql.db再不行就继续看表级和列级授权。这也是为什么有时候你GRANT SELECT ON db_name.* TO user%之后对方却发现连不了db_name之外的库——因为那份授权只落在mysql.db层而不是全局。理解了分层关系这类权限疑问就迎刃而解。4.2 用 information_schema 快速盘点权限直接改系统表来查权限当然可以但有更安全的只读入口SELECT * FROM information_schema.USER_PRIVILEGES; SELECT * FROM information_schema.SCHEMA_PRIVILEGES; SELECT * FROM information_schema.TABLE_PRIVILEGES;这些视图会把系统表里的权限信息整理成更容易阅读的表格。不过最直观的仍然是SHOW GRANTS FOR app_user%;它会用GRANT语句的形式告诉你这个账号实际被授予了什么权限。需要注意的是8.0 里如果账号被授予了角色SHOW GRANTS默认展示的是角色授权本身还是展开后的权限取决于当时激活的是哪个角色。排查最终生效权限时建议把角色因素也考虑进去。4.3 8.0 角色入局后的新表MySQL 8.0 引入了完整的 RBAC 角色体系mysql库里多了一些相关表mysql.role_edges记录角色与用户的关系相当于一张“谁拥有哪个角色”的关系表mysql.default_roles记录用户登录后默认激活的角色mysql.global_grants存放需要特殊标记的全局授权。管理角色时我强烈建议使用CREATE ROLE、GRANT这类官方 SQL 接口而不是直接INSERT或UPDATE这几张表。角色关系涉及多张表的一致性维护直接改表很容易破坏依赖关系引发难以排查的权限混乱。5. 系统表和 Hash 相关的坑我替你踩过了5.1 别直接用 UPDATE 改密码网上还能搜到不少老文章教人这样改密码UPDATE mysql.user SET authentication_stringPASSWORD(新密码) WHERE Userroot; FLUSH PRIVILEGES;这条命令在 MySQL 5.7 里跑得通但到了 8.0 会直接报错因为PASSWORD()函数已经被移除。就算你在 5.7 里成功执行了也会引入另一个隐患直接操作系统表绕过了ALTER USER这类管理接口的内部处理容易出现 plugin 和 hash 风格不一致的情况比如 plugin 指向caching_sha2_passwordhash 却是mysql_native_password格式。正确做法永远是ALTER USER app_user% IDENTIFIED BY 新密码;也可以显式指定插件ALTER USER app_user% IDENTIFIED WITH caching_sha2_password BY 新密码;FLUSH PRIVILEGES只在直接修改系统表之后才需要用ALTER USER时不需要手动刷。5.2 Hash“看起来对”却登录失败完整排查链路有一次同事把一套 5.7 环境的账号体系“搬”到新的 8.0 实例登录时一直报认证失败。他打开mysql.user检查发现 hash 复制过来了、账号也存在看起来一切正常。我让他按下面的链路一步步排查才定位到问题。第一步看plugin。查询结果里新实例账号的plugin还是 8.0 默认的caching_sha2_password但复制过来的 hash 却是*开头的mysql_native_password格式。这已经是一个明显的错位。第二步看 hash 前缀。*和$A$代表的算法完全不同服务端按caching_sha2_password去解析一串老算法的 hash必然对不上。第三步看客户端驱动。就算plugin也改成了mysql_native_password如果客户端本身版本过旧连接caching_sha2_password账号时同样会报Authentication plugin cannot be loaded。第四步决定修复方案。要么把账号显式切回老插件并重建密码要么升级客户端连接组件。生产环境中我建议优先升级驱动组件安全性更好而不是长期停留在老认证方式上。完整的排查顺序就是plugin对不对 → hash 格式对不对 → 客户端支不支持 → 最后再考虑重建账号或调整插件。5.3 审计口头禅查空密码和撞 hash用户 hash 查询还有一个高频用途账号安全审计。下面这条 SQL 能帮你快速发现空密码账号SELECT User, Host, plugin, authentication_string FROM mysql.user WHERE authentication_string OR authentication_string IS NULL;再比如想发现“多个账号共用同一密码”的隐患可以这样查SELECT User, Host, plugin, authentication_string FROM mysql.user WHERE authentication_string ORDER BY plugin, authentication_string;如果多条记录的authentication_string完全相同说明这几个账号很可能使用了相同口令在安全审计里属于需要重点整改的问题。注意authentication_string为空并不代表一定没有密码也可能是账号使用了外部认证、还没初始化完成需要结合plugin一起判断。5.4 备份 mysql 系统库关键时刻能救命mysql 库里的系统表一旦丢失实例里所有账号、权限、角色信息全部归零。恢复实例时如果你只有一个业务数据的逻辑备份没有系统库备份会发现即使业务数据回来了所有账号也都“消失”了应用全部连不上。所以我的建议非常简单直接mysql系统库必须纳入日常备份范围。主流备份工具默认都会备份它但你要确认自己的备份策略没有把它排除掉。另外做任何涉及mysql.user的批量修改之前先单独导出一下当前表内容mysqldump --single-transaction --skip-lock-tables mysql user mysql_user_backup.sql这样万一改坏了还有后路可退。6. 一点实操心得把系统表查询脚本化处理账号类问题时我习惯把几条常用排查 SQL 整理成一个脚本文件比如叫user_audit.sql每次遇到连接异常、权限缺失、迁移验证就直接执行一遍SELECT User, Host, plugin, LEFT(authentication_string, 4) AS hash_head, LENGTH(authentication_string) AS hash_len, account_locked, password_expired FROM mysql.user ORDER BY User, Host;配合SHOW CREATE USER和SHOW GRANTS基本能覆盖日常 95% 的账号排障需求。我个人的体会是把 MySQL 系统表和用户 hash 查询想明白之后这些东西就不再是“底层黑盒”而是一套非常顺手的排查工具。最后再分享一个我踩过坑才养成的习惯凡是涉及mysql.user、mysql.db这类系统表的修改第一步永远是备份第二步是优先使用CREATE USER、ALTER USER、GRANT、REVOKE这些官方接口而不是直接UPDATE。你要是坚持直接改表就要做好为它兜底的准备——我当年在生产环境里就吃过这样的亏希望你不需要再经历一次。