mysql中限制用户查询的权限与安全控制

应显式回收information_schema权限、用DEFINER视图实现行级过滤、借助Roles统一管理权限、设置MAX_QUERIES_PER_HOUR防扫描，并注意权限变更需重连生效。

只允许 SELECT 权限，禁止 SHOW CREATE TABLE 和元数据访问

MySQL 默认开启 information_schema 和 performance_schema，普通用户即使没显式授权也能查表结构、列名甚至部分执行计划。这会泄露敏感字段名或索引设计。要真正限制查询范围，不能只靠 GRANT SELECT ON db.table TO 'u'@'%' —— 还得关掉元数据可读性。

• 执行 SET GLOBAL show_compatibility_56 = OFF（仅 5.7+，影响 SHOW CREATE TABLE 输出格式，但不阻止访问）
• 更关键的是：用 REVOKE SELECT ON information_schema.* FROM 'u'@'%' 显式回收元库权限（MySQL 8.0 默认已禁用普通用户访问 information_schema 表，但低版本需手动处理）
• 检查是否残留 PRO

  

  CESS 或 REPLICATION CLIENT 权限，它们能让用户看到正在运行的 SQL 或主从状态，用 SHOW GRANTS FOR 'u'@'%' 确认

用视图 + DEFINER 实现行级过滤，避免应用层拼 WHERE

直接给用户 SELECT 底层表权限，等于把所有行都暴露出去。用视图配合 SQL SECURITY DEFINER 可以让查询自动带上固定条件，且用户无法绕过。

CREATE DEFINER = 'admin'@'localhost' VIEW user_orders AS
SELECT id, order_no, amount, status 
FROM orders 
WHERE user_id = USER();

• DEFINER 账户必须有底层表的 SELECT 权限；INVOKER 模式下权限检查基于调用者，起不到过滤作用
• 注意 USER() 返回 'u'@'host'，若业务用的是连接池（如 'app'@'10.0.1.%'），需改用 CURRENT_USER() 或在视图中硬编码角色字段（如 WHERE tenant_id = 123）
• 视图无法防止 SELECT * FROM user_orders WHERE 1=1 OR 1=1 类注入，仍需应用层参数化查询

用 MySQL 8.0 的 Roles 管理权限组，避免 GRANT 堆叠失控

老方式给每个用户单独 GRANT，权限变更时要逐个更新，容易漏。MySQL 8.0+ 支持 ROLE，把权限打包，再分配给用户。

CREATE ROLE 'readonly_analytics';
GRANT SELECT ON sales.* TO 'readonly_analytics';
GRANT SELECT ON information_schema.TABLES TO 'readonly_analytics';
CREATE USER 'bi_user'@'%' IDENTIFIED BY 'pwd';
GRANT 'readonly_analytics' TO 'bi_user'@'%';

• 启用角色需先执行 SET DEFAULT ROLE 'readonly_analytics' TO 'bi_user'@'%'，否则登录后角色不生效
• 角色不能嵌套（A 角色不能 GRANT 给 B 角色），也不能跨 host 分配（'role_name'@'localhost' 是非法语法）
• 撤销权限时，REVOKE SELECT ON sales.* FROM 'readonly_analytics' 会立刻影响所有拥有该角色的用户

限制查询资源：MAX_QUERIES_PER_HOUR 防暴力扫描

仅靠权限控制不够，恶意用户可能用合法账号发起高频小查询扫描表内容（比如遍历 id=1,2,3...）。MySQL 提供资源限制机制。

• 创建用户时加限制：CREATE USER 'scanner'@'%' WITH MAX_QUERIES_PER_HOUR 100;
• 已有用户用 ALTER USER 'u'@'%' WITH MAX_QUERIES_PER_HOUR 50; 修改
• 注意：该限制统计的是语句数，不是结果行数；SELECT COUNT(*) FROM huge_table 和 SELECT * FROM t LIMIT 1 都算 1 次
• 对 OLAP 类场景慎用，MAX_UPDATES_PER_HOUR 和 MAX_CONNECTIONS_PER_HOUR 同理，但 MAX_USER_CONNECTIONS 更常用（限制并发连接数）

实际部署中最容易被忽略的是：权限变更后，用户当前连接不会立即失效，必须重连才生效；而 FLUSH PRIVILEGES 在大多数情况下并不需要——只有直接修改 mysql.user 表时才需执行。