mysql中用户认证与权限管理的安全性强化

mysql 8.0+默认使用caching_sha2_password插件提升安全性，但旧客户端不兼容；应优先适配新插件而非降级，结合视图隔离、元数据权限控制、精确授权及正确验证权限方式保障安全。

MySQL 8.0+ 默认使用 caching_sha2_password 插件，但旧客户端可能连不上

MySQL 8.0 开始将默认认证插件从 mysql_native_password 改为 caching_sha2_password，安全性更高（支持 SHA-256 密码哈希、加盐、密钥派生），但部分老版本客户端（如 MySQL 5.7 客户端、某些 python 2 的 mysqlclient、旧版 navicat）不支持该插件，会报错：Authentication plugin 'caching_sha2_password' cannot be loaded。

解决方式不是降级回旧插件，而是明确适配：

• 新用户创建时显式指定插件：

  CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED WITH caching_sha2_password BY 'StrongPass!2024';

• 对已有用户升级认证方式（避免明文传输密码）：

  ALTER USER 'legacyuser'@'%' IDENTIFIED WITH caching_sha2_password BY 'NewPass!2024';

• 若必须兼容极旧客户端，仅对特定用户降级（不推荐全局改）：

  ALTER USER 'compat_user'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPass123';

  ，并确保该用户只用于隔离的内网低风险场景

权限最小化原则下，GRANT 不能只靠 select/INSERT 粗粒度控制

直接给用户 GRANT SELECT ON db.* TO 'reader'@'%' 会让其看到所有表结构、列名甚至注释，可能暴露敏感字段命名（如 user_ssn_hash、payment_token）。更危险的是，INforMATION_SCHEMA 默认对所有已认证用户可读，能查到表名、索引、外键约束等元数据。

真正限制可见性需组合手段：

• 用 REVOKE 显式关闭元数据访问（MySQL 8.0.22+）：

  REVOKE SELECT ON TABLE INFORMATION_SCHEMA.* FROM 'reader'@'%';

• 按业务实体建视图，只暴露必要字段：

  CREATE VIEW v_user_profile AS SELECT id, nickname, avatar_url FROM users WHERE status = 'active'; GRANT SELECT ON db.v_user_profile TO 'reader'@'%';

• 禁止跨库访问：不使用 db_name.*，而精确到 db_name.table_name；敏感库（如 sys、mysql）绝不授权

FLUSH PRIVILEGES 不是每次 GRANT 后都必需

执行 GRANT 或 CREATE USER 时，MySQL 会自动重载权限缓存，FLUSH PRIVILEGES 只在**直接修改 mysql.user、mysql.db 等系统表后**才需要。误用它不仅多余，还可能掩盖权限未生效的真实原因——比如用户 host 匹配失败（'user'@'192.168.1.100' ≠ 'user'@'192.168.1.%'）或 dns 解析延迟导致 host 判定异常。

排查权限是否生效，优先用：

• SHOW GRANTS FOR 'user'@'host'; —— 看当前生效的权限语句
• SELECT * FROM mysql.user WHERE User='user' AND Host='host'G —— 检查 account 表记录是否写入（注意：不要手动 UPDATE 这些表）
• 用目标用户连接后执行 SELECT USER(), CURRENT_USER(); —— 验证实际匹配的账号（CURRENT_USER() 是权限系统使用的身份）

密码策略与过期强制，靠 mysql.password_history 和 password_reuse_interval

MySQL 8.0+ 内置密码重用限制，但默认关闭。仅设 VALIDATE_PASSWORD 插件还不够，需主动启用历史记录防止“密码轮换”变成固定三组来回切。

启用方式：

• 安装插件：

  INSTALL PLUGIN validate_password SONAME 'validate_password.so';

• 设置策略强度：

  SET GLOBAL validate_password.policy = STRONG; SET GLOBAL validate_password.length = 12; SET GLOBAL validate_password.mixed_case_count = 2;

• 关键一步：开启密码历史（默认为 0，即不限制）：

  SET GLOBAL password_history = 5; SET GLOBAL password_reuse_interval = 365;

  —— 表示最近 5 次密码不可重复，且同密码至少隔 365 天才能复用

• 对现有用户强制立即过期：

  ALTER USER 'prod_app'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

注意：password_history 记录存在 mysql.password_history 表中，该表不加密存储哈希值，所以必须确保只有 dba 能查 —— 检查 SELECT 权限是否已被严格收回。