mysql权限校验的基础概念
mysql本身自带一套完整的权限管理体系，默认情况下会维护user、db、tables_priv等多张权限相关的系统表，这些表记录了不同用户对不同数据库、表、字段的操作权限。我们可以通过操作这些权限表，或者直接调用mysql提供的权限管理语句，来实现对用户操作权限的控制，这也是基于mysql实现权限校验的核心依据。
mysql权限的层级划分
mysql的权限按照作用范围可以分为以下几个层级：
- 全局权限：作用于mysql服务器的所有数据库，比如创建用户、关闭服务器等权限
- 数据库级权限：作用于指定数据库的所有表，比如对某个数据库的所有表执行查询、插入操作
- 表级权限：作用于指定数据库的指定表，比如对某张用户表执行修改、删除操作
- 字段级权限：作用于指定表的指定字段，比如只允许修改用户表的手机号字段，不能修改密码字段
实现简单权限校验的核心步骤
第一步：创建不同权限的mysql用户
首先我们需要根据业务需求创建不同权限的用户，比如创建只读用户、只写用户、管理员用户等。创建用户需要使用CREATE USER语句，同时可以指定用户的登录主机和密码。
下面是创建两个不同权限用户的示例代码：
-- 创建只读用户，只允许从本地登录，密码为readonly123 CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'readonly123'; -- 创建业务操作用户，只允许从192.168.0.1这个地址登录，密码为operator123 CREATE USER 'business_operator'@'192.168.0.1' IDENTIFIED BY 'operator123';
第二步：为用户分配对应权限
创建完用户之后，需要根据用户的角色分配对应的权限，使用GRANT语句可以完成权限授予操作。我们可以指定权限类型、作用范围、对应的用户。
以下是为上面创建的用户分配权限的示例：
-- 为只读用户授予test数据库下所有表的查询权限 GRANT SELECT ON test.* TO 'readonly_user'@'localhost'; -- 为业务操作用户授予test数据库下user表的查询、插入、更新权限，同时授予order表的查询权限 GRANT SELECT, INSERT, UPDATE ON test.user TO 'business_operator'@'192.168.0.1'; GRANT SELECT ON test.order TO 'business_operator'@'192.168.0.1'; -- 刷新权限，让权限修改立即生效 FLUSH PRIVILEGES;
第三步：在业务中实现权限校验逻辑
分配完权限之后，业务层只需要使用对应的用户连接mysql，就可以天然实现权限管控。如果使用了没有对应权限的用户执行操作，mysql会直接返回权限错误，业务层捕获这个错误就可以返回无权限的提示。
以下是一个简单的Java业务层校验示例，使用JDBC连接mysql，尝试执行无权限的操作：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class MysqlAuthCheck {
public static void main(String[] args) {
// 使用只读用户的连接信息
String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
String user = "readonly_user";
String password = "readonly123";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
// 只读用户没有插入权限，执行插入操作会报错
String insertSql = "INSERT INTO user (name, age) VALUES ('张三', 20)";
stmt.executeUpdate(insertSql);
System.out.println("插入成功");
} catch (SQLException e) {
// 捕获权限错误，返回无权限提示
if (e.getErrorCode() == 1142) {
System.out.println("当前用户没有执行该操作的权限");
} else {
e.printStackTrace();
}
}
}
}
第四步：权限回收与调整
如果用户的权限需要变更，比如业务操作用户不再需要修改用户表的权限，或者某个用户离职需要回收所有权限，可以使用REVOKE语句完成权限回收。
以下是权限回收的示例代码：
-- 回收业务操作用户对test.user表的更新权限 REVOKE UPDATE ON test.user FROM 'business_operator'@'192.168.0.1'; -- 回收只读用户的所有权限，并删除用户 REVOKE ALL PRIVILEGES ON test.* FROM 'readonly_user'@'localhost'; DROP USER 'readonly_user'@'localhost'; -- 刷新权限 FLUSH PRIVILEGES;
简单权限校验的适用场景与注意事项
这种基于mysql原生权限的校验方式适合用户量不多、权限规则简单的小型项目，不需要额外引入Spring Security、Shiro等权限框架，降低了项目的复杂度。但是需要注意以下几点：
- 不要给业务用户分配过高的权限，比如不要给普通业务用户分配ALL PRIVILEGES全局权限
- 用户的密码需要定期更换，避免密码泄露导致数据安全问题
- 如果项目用户量较大，权限规则复杂，还是建议使用专业的权限框架做更灵活的权限管控
- 每次修改权限之后都需要执行FLUSH PRIVILEGES语句，否则权限修改可能不会立即生效
常见问题解答
为什么执行了GRANT语句还是没有权限
通常是因为没有执行FLUSH PRIVILEGES语句，或者用户连接的mysql实例不是执行GRANT语句的实例，也有可能是权限作用范围没有匹配，比如授予了test库的权限，但是连接的是其他库。
如何查看某个用户的所有权限
可以使用SHOW GRANTS语句查看指定用户的权限，示例代码如下：
-- 查看业务操作用户的所有权限 SHOW GRANTS FOR 'business_operator'@'192.168.0.1';