mysql数据库中的权限分离与最小权限策略

MySQL 创建只读账号须显式限定库表范围，禁用全局授权；按用途拆分账号、善用角色机制并坚持最小权限原则。

MySQL 中如何创建只读账号并限制库表范围

直接给用户 SELECT 权限不等于安全，必须显式限定数据库和表。MySQL 的权限是分层的（全局 → 数据库 → 表 → 列），默认 grant 语句若没指定 ON db_name.*，就会落到全局层级，可能意外获得其他库的访问权。

实操建议：

• 用 CREATE USER 'reporter'@'192.168.1.%' IDENTIFIED BY 'strong_pass_2024'; 先建用户，避免 GRANT …… WITH GRANT OPTION 自动创建弱密码账号
• 授权时严格写明范围：GRANT SELECT ON myapp_production.orders TO 'reporter'@'192.168.1.%';，不要用 GRANT SELECT ON *.*
• 如果需跨多张表，逐条授权比用通配符更可控，例如：GRANT SELECT ON myapp_production.users TO ……; 和 GRANT SELECT ON myapp_production.products TO ……;
• 执行完务必运行 FLUSH PRIVILEGES;，否则权限不会立即生效（尤其在跳过 grant tables 启动后改权限时容易漏）

为什么 不能直接用 root 或 application 用户跑定时脚本

常见错误：把应用连接池配置里的 username 直接拿去写 shell 脚本做备份或数据同步，结果脚本里执行了 DROP TABLE 或误删 WHERE 条件缺失的 DELETE —— 因为该账号有 DELETE、DROP 权限。

正确做法是按用途拆分账号：

• 应用连接池账号：仅 SELECT, INSERT, UPDATE, EXECUTE，禁止 DROP、CREATE、ALTER、FILE
• 备份脚本专用账号：只授 SELECT + LOCK TABLES（mysqldump 需要），且限定只读库
• 运维管理账号：单独分配，启用 require_secure_transport=ON 并绑定 IP 段，不用密码而用 SSL 证书认证

权限过大最直接的后果不是被黑，而是人误操作不可逆。MySQL 不记录「谁删了哪行」，只有 binlog 可追溯，但恢复成本极高。

SHOW GRANTS 输出里出现 USAGE 是什么意思

USAGE 是 MySQL 中一个“空权限”占位符，表示该账号存在但尚未授予任何实际权限。它常出现在以下场景：

• 执行 CREATE USER 后还没 GRANT 任何权限
• 用 REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%'; 清空权限后，账号仍保留 USAGE
• 某些自动化 工具（如 Ansible mysql_user 模块）默认行为就是先建 USAGE 再叠加权限

注意：USAGE 不代表能连上数据库——能否登录取决于认证插件和密码是否有效；但一旦有了 USAGE，后续 GRANT 就可直接追加，无需再 CREATE USER。排查权限问题时，先运行 SHOW GRANTS FOR 'user'@'host';，看到只有 USAGE 就说明权限根本没配。

MySQL 8.0+ 的角色（ROLE）机制怎么用才不翻车

MySQL 8.0 引入 CREATE ROLE，目标是解耦权限与用户，但实际落地要注意三点：

• 角色本身不自动激活，用户登录后需显式执行 SET ROLE role_name; 或在创建时指定默认角色：CREATE USER 'dev'@'%' DEFAULT ROLE app_reader;
• 角色权限不能跨数据库继承，比如 app_reader 在 db1 上有 SELECT，对 db2 无效，必须重复授权
• 谨慎使用 WITH ADMIN OPTION：它允许角色持有者把该角色再授给他人，相当于二级管理员权限，生产环境应禁用

CREATE ROLE app_reader; GRANT SELECT ON sales.* TO app_reader; CREATE USER 'bi_team'@'10.0.2.%' IDENTIFIED BY 'pass'; GRANT app_reader TO 'bi_team'@'10.0.2.%'; SET DEFAULT ROLE app_reader TO 'bi_team'@'10.0.2.%';

角色不是银弹。小团队用好 GRANT + 明确范围就足够；角色价值体现在中大型系统中频繁变更权限策略的场景，比如按季度切换 数据访问 范围，这时批量 GRANT/REVOKE role 比遍历几十个用户高效得多。

最小权限不是靠一次配置完成的，而是每次新增需求时都问一句：这个账号真的需要这个权限吗？连 INFORMATION_SCHEMA 的查询都要评估——有些敏感字段（比如 PROCESSLIST）能暴露正在执行的 SQL，包含未脱敏参数。