mysql中限制数据库用户的查询权限与操作

MySQL限制用户仅能SELECT需明确授予权限并清理冗余权限：GRANT SELECT ON db.table TO 'user'@'host'; REVOKE其他DML权限；禁用跨库访问和INFORMATION_SCHEMA；应用层加LIMIT或代理层重写SQL；验证时须实际登录测试。

只允许 SELECT，禁止 INSERT/UPDATE/DELETE 的权限设置

MySQL 中限制

用户仅能查询，核心是只授予 SELECT 权限，且不授予任何其他 DML 权限。常见错误是误用 GRANT ALL PRIVILEGES 或遗漏 ON database.table 的范围限定，导致权限过大。

• 权限必须明确指定数据库和表，例如 GRANT SELECT ON mydb.users TO 'reporter'@'localhost';，不能只写 GRANT SELECT ON *.*
• 执行后务必运行 FLUSH PRIVILEGES;，否则权限不会立即生效
• 如果用户已存在并拥有其他权限，需先用 REVOKE INSERT, UPDATE, DELETE, DROP, CREATE ON mydb.* FROM 'reporter'@'localhost'; 清理冗余权限
• 注意通配符行为：mydb.% 匹配所有以 mydb. 开头的库名（如 mydb_backup），不是“所有表”的简写

限制查询结果行数：用 SQL_MODE + 应用层兜底

MySQL 本身不提供“对某个用户自动加 LIMIT”的机制。试图靠 sql_mode=STRICT_TRANS_TABLES 或变量控制行数都无效。真正可行的是组合策略：

• 在应用连接时统一设置会话级限制：SET SESSION max_rows = 1000;（但该变量仅影响 MySQL 客户端的显示，不阻止服务端返回全部结果）
• 更可靠的做法是在应用查询语句中显式加 LIMIT，例如 SELECT * FROM logs WHERE created_at > '2025-01-01' LIMIT 1000;
• 如需强约束，可用代理层（如 ProxySQL）或中间件重写 SQL，但属于架构级改造，非纯 MySQL 配置可解决

禁止跨库访问与 INFORMATION_SCHEMA 敏感列暴露

默认情况下，普通用户仍可访问 INFORMATION_SCHEMA 并查看表结构、索引甚至部分统计信息。若需进一步收敛，需主动限制：

• 撤销全局元数据访问：REVOKE SELECT ON INFORMATION_SCHEMA.* FROM 'reporter'@'localhost';（MySQL 8.0+ 支持，5.7 不支持此粒度）
• 禁止跨库查询：确保所有 GRANT 语句中的 database 名精确匹配，避免使用 *；同时检查 SHOW DATABASES 权限——只要没授 SHOW DATABASES，用户就看不到其他库名
• 敏感列（如密码哈希字段）应通过视图隔离：

  CREATE VIEW safe_users AS SELECT id, username, email FROM users;

  ，再对用户授权 SELECT ON mydb.safe_users

验证权限是否生效：用实际用户身份测试

别依赖 SHOW GRANTS FOR 'user'@'host' 就认为权限正确。真实行为受多个因素影响，必须切换身份验证：

• 用该用户登录：mysql -u reporter -p -h localhost mydb
• 尝试非法操作，观察错误：INSERT INTO users (username) VALUES ('test'); 应报错 ERROR 1142 (42000): INSERT command denied to user 'reporter'@'localhost' for table 'users'
• 检查当前生效权限：SELECT CURRENT_USER(), USER(); 确认 host 匹配（CURRENT_USER() 是认证用户，USER() 是连接声明的用户，二者可能不同）
• 注意权限缓存：修改后未 FLUSH PRIVILEGES，或用户连接复用旧会话，会导致测试结果失真

权限边界模糊常出现在 host 匹配、通配符范围、以及未清理历史授权这三个地方。动手前先查 SELECT User, Host FROM mysql.user WHERE User = 'xxx'; 和 SELECT * FROM mysql.db WHERE User = 'xxx';，比直接 GRANT 更安全。