MySQL 权限体系与 SQL 注入防护：grant 到预编译

后端开发每天跟 MySQL 打交道，但很少有人认真管过权限

一个常见的现象：项目里所有服务共用一个 root 账号，或者 API 层直连数据库用的是 SELECT * 权限外加 FILE 权限。这种配置在开发环境没问题，上了生产就是定时炸弹。

权限体系的面试题并不难，但考察的是你有没有「安全意识」：应用账号该给什么权限？DBA 怎么隔离？SQL 注入为什么到现在还在出？预编译为什么能防注入？这篇文章把权限的层级结构、GRANT/REVOKE 实操、8.0 角色机制和 SQL 注入防护串起来，回答两个问题：你应该给应用什么权限，以及为什么拼接 SQL 是非法的。

MySQL 权限模型：从全局到行列，四层漏斗

MySQL 的权限验证不是一张表了事，而是按范围从大到小逐层匹配。权限表有五张核心表，构成一个四层漏斗：

mysql.user         → 全局权限（连接级）
  ↓
mysql.db          → 库级权限
  ↓
mysql.tables_priv → 表级权限
  ↓
mysql.columns_priv → 列级权限（极少用）

验证规则：用户连接时，MySQL 先查 mysql.user 的 Host 和 User 字段判断是否允许连接。允许后，执行 SQL 时逐层检查权限——先看全局，再看库级，再看表级，最后列级，命中即放行。

Host 字段的匹配规则是面试高频点。mysql.user 里的 Host 支持通配符：

• % 匹配任意主机
• 192.168.1.% 匹配子网
• localhost 只匹配本地 socket 连接

执行 GRANT 时不指定 Host 默认 %，但 'root'@'localhost' 和 'root'@'%' 是两条记录，前者只能本地登录。很多 MySQL 刚装完 root 自动创建了 'root'@'localhost'，如果用 % 连不上，就是这个原因。查看当前用户：

SELECT user, host, account_locked FROM mysql.user;

最小权限原则：应用账号 vs 运维账号

生产环境应该至少分三类账号：

账号类型	权限范围	典型授权
应用只读账号	指定库的 SELECT	SELECT ON db_name.*
应用读写账号	指定库的 CRUD	SELECT, INSERT, UPDATE, DELETE ON db_name.*
运维账号	全局 DDL + 管理	ALL PRIVILEGES ON *.*，但需要 SSL 限制来源 IP

应用账号绝对不要给这些权限：

• FILE：允许执行 SELECT ... INTO OUTFILE 写文件到服务器，黑客能通过它写入 webshell
• SUPER：控制复制、kill 线程、修改全局变量，给应用就失控了
• CREATE TEMPORARY TABLES / ALTER：应用不需要改表结构，给了就有人写 SQL 里直接改

WITH GRANT OPTION 的坑：给应用账号授权时，如果写了 GRANT SELECT ON db.* TO 'app'@'%' WITH GRANT OPTION，应用账号就能把自己仅有的一点权限转授给别人。曾有案例是应用被注入后，攻击者通过这个 grant 选项把自己设成了管理员。

8.0 的角色机制：把权限包打包

MySQL 8.0 引入了角色（Role），本质是一组权限的集合，可以理解成 Linux 的 group。角色让权限管理从"一个个权限逐条赋"变成"把角色赋给用户"。

-- 创建角色
CREATE ROLE 'read_only', 'read_write', 'dba';

-- 给角色授权
GRANT SELECT ON mydb.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'read_write';
GRANT ALL PRIVILEGES ON *.* TO 'dba' WITH GRANT OPTION;

-- 把角色赋给用户
CREATE USER 'app_read'@'%' IDENTIFIED BY 'xxx';
CREATE USER 'app_write'@'%' IDENTIFIED BY 'xxx';
CREATE USER 'ops'@'192.168.1.%' IDENTIFIED BY 'xxx';

GRANT 'read_only' TO 'app_read'@'%';
GRANT 'read_write' TO 'app_write'@'%';
GRANT 'dba' TO 'ops'@'192.168.1.%';

-- 激活角色（默认不激活，需要显式 SET）
SET DEFAULT ROLE ALL TO 'app_read'@'%', 'app_write'@'%', 'ops'@'192.168.1.%';

角色的好处是批量管理。人员变动时，DBA 只需要改角色的权限，所有继承该角色的用户自动生效。不用再一个用户一个用户改。

还有一点：8.0 的 caching_sha2_password 是默认密码插件，比 5.7 的 mysql_native_password 安全得多（SHA-256 加盐，加密通道传输）。如果客户端驱动不支持，可以按需切换，但建议升级驱动而不是降级密码插件。

SQL 注入：为什么一个单引号就能崩掉你的数据库

SQL 注入属于 OWASP Top 10 的常客，2023 年 CWE 排名里 SQL 注入仍在前列。不是技术难，而是开发习惯没改过来。

拼接 SQL 的经典案例

一个登录接口的代码（伪代码）：

String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);

如果用户输入的用户名是 admin' OR '1'='1，密码随便填，那拼接出来的 SQL 变成：

SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = '随便填'

OR '1'='1' 恒真，整个 WHERE 条件变成 true AND password = '随便填'，实际上 password 条件被绕过了。更糟的，如果输入 admin'; DROP TABLE users; --，整个表就没了。

为什么预编译能防注入？

预编译（PreparedStatement）把 SQL 结构和参数分开发送。第一步，数据库收到 SQL 模板，做词法解析、编译成执行计划。第二步，参数单独传给执行计划，不走词法解析。

// 预编译版本
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();

当 username 传入 admin' OR '1'='1 时，数据库把它当作一个完整的字符串参数，而不是 SQL 语句的一部分。? 的位置已经被解析为"一个字符串值"，不会出现"单引号逃逸出字符串边界"的情况。

预编译不是银弹。以下场景预编译也救不了：

• 动态表名/列名：ORDER BY ? 参数不能替换列名，只能用白名单拼接
• LIKE 模糊查询：LIKE ? 本身没问题，但前端传 % 可能引起全表扫描——这是性能问题，不是安全问题
• MyBatis 的 $ 符号：${} 是直接拼接，#{} 才是预编译。写 MyBatis 时如果懒了用了 ${}，等于裸奔

其他安全配置

• 密码强度：validate_password 组件在 8.0 是内置的，可以配置密码长度和复杂度要求
• 备份文件权限：mysqldump 备份的文件经常带密码（-p 后面跟的密码会在进程列表里暴露），备份文件不要放在 web 可访问目录
• SSL 连接：生产环境应用和数据库之间建议用 SSL，GRANT ... REQUIRE SSL 强制加密连接
• 审计日志：audit_log 插件记录敏感操作，谁在什么时候执行了什么 DDL，追查时有用

总结

• 权限是四层漏斗：user → db → tables_priv → columns_priv，Host 匹配规则决定了谁能从哪里连
• 应用账号只给最小权限（CRUD），不要 FILE、SUPER、WITH GRANT OPTION
• 8.0 角色机制把权限打包，批量管理用户，减少人工失误
• SQL 注入的根源是拼接 SQL，预编译把结构和参数分开，参数不走词法解析，从根本上防注入
• 预编译不是万能的，动态表名/列名、MyBatis 的 ${} 仍然有风险

上线前查一次 mysql.user，在任意生产环境都能发现一两个 root 直连的接口。

参考

• MySQL 8.0 Privilege System
• MySQL 8.0 Role-Based Access Control
• OWASP SQL Injection Prevention Cheat Sheet