mysql如何设置用户的只读权限_mysql读写权限控制

<p> 用 GRANT 授予 SELECT 权限并限制库表范围，如 GRANT SELECT ON mydb. TO ‘readonly_user’@’192.168.1.%’; 执行 FLUSH PRIVILEGES; 生效，避免 ON .* 防止泄露系统库。</p>

如何用 GRANT 语句给用户只读权限

MySQL 中没有“只读用户”这个内置角色，必须显式授予 SELECT 权限，并确保不授予 INSERT、UPDATE、DELETE、DROP、ALTER 等写权限。最稳妥的方式是只授 SELECT，且限制到具体数据库或表。

实操建议：

• 对整个数据库设只读：

  GRANT SELECT ON `mydb`.* TO 'readonly_user'@'192.168.1.%';

• 对单张表设只读：

  GRANT SELECT ON `mydb`.`orders` TO 'readonly_user'@'%';

• 执行后务必运行：

  FLUSH PRIVILEGES;

  （否则权限不生效）

• 避免使用 GRANT SELECT ON *.* —— 这会授予所有库的读权限，包括系统库 mysql，存在信息泄露风险

为什么 SHOW DATABASES 还能看到其他库

只读权限不影响 SHOW DATABASES 的可见性。MySQL 5.7+ 默认开启 show_compatibility_56=OFF，且只要用户存在（哪怕只有 USAGE 权限），就会列出所有库名 —— 这是元数据可见性行为，和权限无关。

若需隐藏非授权库：

• MySQL 8.0+ 可启用 partial_revokes 并配合 RESTRICTED REPLICATION SLAVE 等机制，但不直接解决库列表问题
• 更实际的做法：应用层过滤，或在连接时指定默认库（USE mydb），减少误操作可能
• 注意：即使看不到库名，只要知道库名和表结构，仍可通过 SELECT 查询（前提是已有对应 SELECT 权限）

REVOKE 后权限没立刻失效？

常见现象：执行了 REVOKE INSERT, UPDATE ON mydb.* FROM 'user'@'%'，但用户还能写 —— 很可能是权限缓存未刷新，或用户有更高优先级权限源（如通配符主机匹配、全局权限残留）。

排查要点：

• 检查是否遗漏了 FLUSH PRIVILEGES;（尤其在直接改 mysql.tables_priv 表后）
• 运行 SHOW GRANTS FOR 'user'@'%'; 确认当前生效权限，注意输出中是否有 ON *.* 这类宽泛授权覆盖了细粒度 REVOKE
• MySQL 权限检查顺序是：用户名 + 主机组合 → 最精确匹配优先。例如 'user'@'192.168.1.100' 和 'user'@'%' 共存时，前者权限会优先生效
• 如果用户有 CREATE USER 或 GRANT OPTION，可能自行加权，需一并回收

只读用户连不上？检查 authentication plugin 和 host 匹配

权限设置正确但连接失败，大概率不是权限问题，而是认证层面被拦住。常见于 MySQL 8.0+ 默认使用 caching_sha2_password 插件，而旧客户端不兼容。

快速验证与修复：

• 确认用户 host 是否匹配：用 SELECT User, Host FROM mysql.user WHERE User = 'readonly_user'; 查看注册的 host，连接时必须完全一致（% 不匹配 localhost）
• 临时切回兼容插件：

  ALTER USER 'readonly_user'@'%' IDENTIFIED WITH mysql_native_password BY 'xxx';

• 若用 Docker 或云 RDS，注意网络策略（如安全组）是否放行 3306 端口，且绑定地址不是 127.0.0.1

权限控制真正的复杂点不在语句本身，而在权限叠加逻辑、host 匹配优先级、以及不同 MySQL 版本对 USAGE 和 PROXY 权限的解释差异。生产环境建议始终用 SHOW GRANTS 验证最终效果，而不是仅依赖 GRANT/REVOKE 命令的返回成功。