mysql中的权限管理与多用户环境的最佳实践

MySQL权限是层级叠加矩阵，自顶向下短路检查；推荐部署、应用（读写/只读分离）、审计三级账号分层，禁用通配符滥用，权限变更需版本化管理并防范主从不一致。

MySQL 用户权限模型到底怎么理解

MySQL 的权限不是“开/关”二值开关，而是按层级叠加的矩阵：全局、数据库、表、列、存储过程等粒度都可独立授权。用户登录后实际拥有的权限 = 所有匹配层级中权限的并集，但 REVOKE 只能收回显式授予的权限，不能撤回继承来的（比如全局 SELECT 会覆盖库级 DENY —— 实际上 MySQL 没有 DENY 语句，这点常被误解）。

关键点在于：权限检查是自顶向下短路的。例如用户对 app_db.users 表执行 SELECT，MySQL 先查全局 SELECT，有则放行；没有就查 app_db 级，再没有才查表级。所以低层级授权无法“限制”高层级已开放的权限。

• 不要依赖“不授就不给”——未授权 ≠ 显式拒绝，而是无权限
• USAGE 权限代表“仅能登录”，不隐含任何操作能力
• 权限变更后必须执行 FLUSH PRIVILEGES（仅当直接修改 mysql.user 等系统表时需要；用 GRANT/REVOKE 则自动生效）

生产环境该用几个账号？如何划分角色

别为每个应用或微服务建独立用户，也别让 DBA 和应用共用一个高权账号。推荐三级分层：

• 部署账号：deploy_user，只在上线时临时使用，拥有 CREATE DATABASE、ALTER TABLE、CREATE PROCEDURE 等 DDL 权限，用完即 DROP USER
• 应用账号：app_rw 和 app_ro 分离，前者仅限业务库的 SELECT, INSERT, UPDATE, DELETE，后者仅 SELECT；均禁用 FILE、PROCESS、SUPER 等危险权限
• 审计账号：audit_user，只有 SELECT 权限访问 performance_schema 和 information_schema 中的元数据表，不可连业务表

所有账号强制使用 localhost 或最小化 IP 段（如 'app1'@'10.20.30.%'），禁止 'user'@'%'。

GRANT 语句里 host 部分填错的后果很直接

MySQL 认证时先匹配 user + host 组合，顺序严格按 mysql.user 表中的排序（最长 host 匹配优先）。常见错误：

• 执行 GRANT SELECT ON app.* TO 'api'@'%' 后又执行 GRANT SELECT ON app.* TO 'api'@'10.20.30.5'，结果是两个独立账号，后者不会覆盖前者
• 误写成 'api'@'localhost' 却从容器内用宿主机 IP 连接，导致 “Access denied”，因为 localhost 触发 Unix socket 认证，而 IP 走 TCP，匹配不到同一行
• host 使用通配符时，'%' != '%%'，后者是字面量字符串，不参与模式匹配

查当前有效匹配项用：

SELECT User, Host FROM mysql.user WHERE User = 'api';

权限同步与配置漂移怎么防

MySQL 不提供原生权限导出/导入工具，手动 SHOW GRANTS FOR 'u'@'h' 容易漏掉新用户或 host 变更。建议把权限定义固化为 SQL 文件，用版本控制管理，并通过脚本批量部署：

• 用 mysqldump --no-data --skip-triggers --compact mysql user db tables_priv columns_priv 导出权限元数据（注意：5.7+ 中 tables_priv 等表结构有变更，需适配）
• 避免直接 INSERT INTO mysql.user —— 5.7+ 密码哈希方式变化（authentication_string 替代 password 字段），且字段校验更严
• CI 流程中加入权限比对步骤：用 SELECT CONCAT('SHOW GRANTS FOR ''',User,'''@''',Host,''';') FROM mysql.user; 生成所有 SHOW GRANTS 语句，执行后与基准文件 diff

真正麻烦的是跨主从、多集群场景下权限不一致——MySQL 复制默认不复制 mysql 库（除非显式开启 replicate_mysql_table=ON），这意味着从库权限可能长期 stale。