mysql如何查看权限表的底层存储结构_分析mysql.user与db表字段-mysql教程-PHP中文网

mysql.user表以Host和User为联合主键，authentication_string存哈希密码，所有_priv字段为enum('N','Y')；Host支持%通配但不支持正则，User区分大小写，全局权限优先级高于db表库级权限。

mysql如何查看权限表的底层存储结构_分析mysql.user与db表字段

mysql.user 表的字段结构和主键逻辑

MySQL 的权限校验第一步就是匹配 mysql.user 表中的一条记录，而匹配依据只有两个字段：Host 和 User —— 它们构成联合主键。这意味着哪怕只改一个字符（比如 'localhost' 和 '127.0.0.1'），就代表完全不同的用户条目。

常见错误现象：执行 CREATE USER 'app'@'%' 后仍无法远程连接，但本地能连。原因往往是 mysql.user 里同时存在 'app'@'localhost' 和 'app'@'%'，而客户端实际走的是前者（例如用 127.0.0.1 连却误匹配到 localhost 条目），导致权限不生效。

关键字段说明：

Host：char(60)，不允许 NULL，支持通配符（%、192.168.1.%），但不支持正则或子网掩码写法
User：char(32)，不允许 NULL，区分大小写（取决于 collation，通常是 utf8_bin）
authentication_string：text 类型，MySQL 5.7+ 替代了旧版的 Password 字段，存的是哈希值（如 *6BB4...）
所有 _priv 字段（如 Select_priv）：enum('N','Y')，默认为 'N'，全局权限仅在此生效

db 表的字段设计与权限叠加规则

mysql.db 表不是独立起作用的，它必须和 mysql.user 中对应用户的全局权限配合使用。它的核心逻辑是“补缺”：当 user 表中某用户的 Select_priv = 'N'，但 db 表里该用户对某个库有 Select_priv = 'Y'，那这个库就允许 SELECT。

容易踩的坑：

如果 user 表中用户已有 Select_priv = 'Y'，那么 db 表里对该库设成 'N' 是无效的——全局权限优先级高于库级
db 表的主键是 (Host, Db, User)，三者必须全部匹配才启用该行权限；Host 为空字符串（''）表示继承自 host 表（极少手动用）
Db 字段区分大小写且不支持通配符，'myapp%' 不会匹配 myapp_v2 库

典型场景：给运维账号 dba@'10.0.0.%' 开放所有库的查询权，但禁止对 sys 库操作。不能只在 user 表开全局 SELECT，而要在 db 表中显式插入一行：INSERT INTO mysql.db (Host,Db,User,Select_priv) VALUES ('10.0.0.%','sys','dba','N');，再执行 FLUSH PRIVILEGES;。

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

为什么直接 SELECT * FROM mysql.user 看不到明文密码

因为 authentication_string 字段存储的是不可逆哈希（SHA256 或 caching_sha2_password 插件生成），不是加密后可解密的密文。这也是为什么你执行 SELECT User,Host,authentication_string FROM mysql.user 只能看到一串类似 *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 的值。

注意点：

MySQL 8.0 默认认证插件是 caching_sha2_password，其哈希格式与老版本 mysql_native_password 不兼容；若应用连接报错 Client does not support authentication protocol，需在创建用户时指定插件：CREATE USER 'u'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd';
不要用 UPDATE 直接修改 authentication_string 字段，应使用 ALTER USER ... IDENTIFIED BY 或 SET PASSWORD，否则可能破坏插件状态
plugin 字段决定用哪个认证机制，若为 auth_socket（常见于 Ubuntu 自带 MySQL），则根本不用密码，而是靠系统用户身份验证

查看权限表结构的正确命令组合

想真正看清字段定义和约束，别只依赖 DESCRIBE mysql.user —— 它不显示 enum 默认值、collation、是否允许 NULL 等关键细节。

实操建议：

看完整建表语句：SHOW CREATE TABLE mysql.user\G（注意加 \G 让输出更清晰）
查字段元数据：SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user' ORDER BY ORDINAL_POSITION;
对比不同版本差异：MySQL 5.7 的 Password 字段在 8.0+ 已移除，被 authentication_string 和 plugin 共同替代；max_connections 等资源限制字段在 8.0 中仍保留，但单位含义未变

最易被忽略的是 collation：整个 mysql.user 表默认用 utf8_bin，意味着 'App'@'%' ≠ 'app'@'%'，而很多 DBA 在 GRANT 时习惯性小写用户名，结果权限没落到目标账户上。