mysql中的权限表与管理机制

MySQL 权限表共五张核心表：user（连接与全局权限）、db（数据库级）、tables_priv（表级）、columns_priv（字段级）、procs_priv（存储过程级），按优先级逐级生效，显式拒绝即终止检查。

MySQL 权限表存在哪些，各自控制什么范围

MySQL 的权限控制完全依赖系统库 mysql 下的几张核心表，不是靠 配置文件 或外部服务。最关键的五张表是：user、db、tables_priv、columns_priv 和 procs_priv（MySQL 8.0+ 还增加了 role_edges 和 default_roles 支持角色）。

它们按优先级从高到低生效：连接级（user）→ 数据库级（db）→ 表级（tables_priv）→ 字段级（columns_priv）→ 存储过程 / 函数级（procs_priv）。只要某一级显式拒绝（Deny），就不会继续向下检查。

• user 表决定「能否连上服务器」以及全局操作权限（如 CREATE USER、SHUTDOWN），字段如 host、user、authentication_string、ssl_type、各类 _priv 布尔字段
• db 表绑定 host+user+db 三元组，控制对某个数据库的 SELECT/INSERT 等权限，但不包含对象创建类权限（如 CREATE ROUTINE）
• tables_priv 和 columns_priv 使用 Table_name 和 Column_name 字段做细粒度限制，注意它们的 Timestamp 和 Grantor 字段是只读的，由 MySQL 自动维护

GRANT / REVOKE 语句和直接改权限表的 区别

必须用 GRANT 或 REVOKE 操作权限，不能直接 UPDATE 或 INSERT 权限表 —— 否则权限不会实时生效，且可能破坏表结构一致性（比如 user 表的 plugin 和 account_locked 字段在不同版本中含义不同）。

执行 GRANT 后，MySQL 会自动刷新内存中的权限缓存（mysqld 内部的 acl_cache），但某些极端情况（如复制延迟、并发修改）可能导致短暂不一致。此时可手动执行 FLUSH PRIVILEGES 强制重载，不过这仅在绕过 GRANT 直接改表后才需要，正常流程下不需要。

• 用 GRANT SELECT ON mydb.* TO 'u1'@'192.168.1.%' 会同时写入 db 表和更新 user 表的 max_questions 等资源限制字段（如果指定了）
• REVOKE ALL PRIVILEGES ON *.* FROM 'u1'@'%' 不会删除该用户记录，只是清空其所有权限字段；要删用户得用 DROP USER
• MySQL 8.0+ 默认启用 sql_mode = 'STRICT_TRANS_TABLES'，对权限字段赋值非法值（如给 Select_priv 设为 'xyz'）会报错，而旧版本可能静默转成 'N'

常见权限错误现象与排查路径

用户报“Access denied”却确认账号密码正确，大概率是权限匹配失败，而非认证问题。重点查三件事：host 是否精确匹配、权限是否已生效、是否有显式拒绝规则。

执行 SELECT user(), current_user(); 可区分「你声称是谁」和「MySQL 认定你是谁」——后者由 user 表中 host 字段的最长匹配规则决定（比如 'u1'@'192.168.%' 优先于 'u1'@'%'）。再用 SHOW GRANTS FOR 'u1'@'192.168.1.100'; 查当前生效权限，它会合并所有层级的授权结果。

• 错误信息 ERROR 1045 (28000): Access denied for user 'u1'@'localhost' (using password: YES)：说明连接时 host 解析为 localhost，但 user 表里只有 'u1'@'127.0.0.1' 记录（二者不等价）
• 执行 SELECT 报 ERROR 1142 (42000): SELECT command denied to user ……：检查 db 表是否存在对应 host+user+db 记录，且 Select_priv='Y'
• 使用通配符库名（如 myapp_%）需确保 SQL 模式未开启 ANSI_QUOTES，否则反引号包裹的库名会被当字面量处理

MySQL 8.0 权限模型的关键变化

MySQL 8.0 彻底重构了权限系统，最明显的是将原来分散在 user 表里的几十个布尔权限字段（如 File_priv、Process_priv）统一归入 role 概念，并通过 role_edges 表管理角色继承关系。这意味着：老版本靠 UPDATE mysql.user SET File_priv='Y' WHERE user='u1'; 开启文件权限的方式，在 8.0+ 已失效。

现在必须用 CREATE ROLE 'backup_admin'; GRANT FILE ON *.* TO 'backup_admin'; GRANT 'backup_admin' TO 'u1'@'%';，再执行 SET DEFAULT ROLE 'backup_admin' TO 'u1'@'%'; 才能让权限生效。角色权限默认不自动激活，需显式 SET ROLE 或设为默认角色。

• password_expired 字段被移除，改由 password_history 和 password_reuse_interval 系统变量控制
• mysql.session 和 mysql.sys 这两个内置账户不再出现在 user 表查询结果中（即使加 SQL_NO_CACHE），它们受独立内核机制保护
• 权限检查路径变长：用户 → 角色 → 权限 → 对象匹配，任意一环失败即拒绝，调试时 SHOW GRANTS 输出也更复杂

权限表本身不加密，敏感字段如 authentication_string 是哈希值，但整个 mysql 库若被导出，仍可能被离线爆破。生产环境务必限制对 mysql 库的 SELECT 权限，尤其禁止普通用户查 user 表。