简介

在一些特定的业务需求下，要求创建只读用户，但是由于一些查询请求使用的是POST方法，因此在网关层面配置只允许请求GET方法又无法满足。所以就想到了是否可以在 JDBC 层面控制，判断角色并且只允许执行 SELECT 类型的SQL语句。

在Spring Boot项目中，我们可以通过结合网关和JDBC来实现基于角色的数据库访问权限控制。具体来说，我们可以通过拦截用户请求并判断其角色，然后根据角色限制用户执行的SQL语句。

方法一

添加数据库依赖

在 pom.xml 文件中添加数据库相关依赖，如 spring-boot-starter-jdbc 和相应数据库驱动。

配置数据库连接

首先，我们需要配置数据库连接，以便能够与数据库进行交互。在 application.properties 或 application.yml 文件中添加以下配置信息：

spring:datasource:url: jdbc:mysql://localhost:3306/mydatabase?useSSL=falseusername: rootpassword: passworddriver-class-name: com.mysql.jdbc.Driver

这里使用了MySQL数据库作为示例，你可以根据实际情况配置相应的数据库连接信息。

创建用户角色表

为了实现角色的判断和数据库访问权限的控制，我们需要创建一个用户角色表，其中包含用户ID和角色字段。示例中，我们创建一个名为 user_roles 的表：

CREATE TABLE user_roles (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,role VARCHAR(20) NOT NULL
);

你可以根据实际需求扩展该表的字段，例如添加其他用户属性。

创建Spring Data JPA实体和仓库

接下来，我们创建与 user_roles 表对应的实体类和Spring Data JPA仓库接口。在 src/main/java 目录下创建一个 com.example.demo.entity 包，并在其中创建一个 UserRole 类：

import javax.persistence.*;@Entity
@Table(name = "user_roles")
public class UserRole {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)private Long id;@Column(name = "user_id")private Long userId;private String role;// 省略构造函数、getter和setter方法
}

然后，在同一个包中创建一个 UserRoleRepository 接口，继承自 JpaRepository ：

import org.springframework.data.jpa.repository.JpaRepository;public interface UserRoleRepository extends JpaRepository<UserRole, Long> {UserRole findByUserId(Long userId);
}

这样，我们就创建了实体类和仓库接口，用于操作用户角色数据。

实现自定义的网关过滤器

接下来，我们需要实现一个自定义的网关过滤器，用于拦截用户请求并进行角色判断。在 src/main/java 目录下创建一个 com.example.demo.filter 包，并在其中创建一个 DatabaseFilter 类：

import org.springframework.cloud.gateway.filter.GatewayFilter;
import org.springframework.cloud.gateway.filter.factory.AbstractGatewayFilterFactory;
import org.springframework.http.HttpStatus;
import org.springframework.http.server.reactive.ServerHttpRequest;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import reactor.core.publisher.Mono;@Component
public class DatabaseFilter extends AbstractGatewayFilterFactory<DatabaseFilter.Config> {private final UserRepository userRepository;private final UserRoleChecker userRoleChecker;public DatabaseFilter(UserRepository userRepository, UserRoleChecker userRoleChecker) {super(Config.class);this.userRepository = userRepository;this.userRoleChecker = userRoleChecker；}@Overridepublic GatewayFilter apply(Config config) {return (exchange, chain) -> {ServerHttpRequest request = exchange.getRequest();String userId = request.getHeaders().getFirst("UserId");if (!StringUtils.isEmpty(userId)) {Long userIdLong = Long.parseLong(userId);User user = userRepository.findById(userIdLong).orElse(null);if (user != null) {UserRole userRole = userRoleRepository.findByUserId(userIdLong);if (userRole != null) {if (config.getReadOnlyRoles().contains(userRole.getRole())) {// 只读角色，只允许执行SELECT查询语句String method = request.getMethodValue();if (!"GET".equals(method)) {exchange.getResponse().setStatusCode(HttpStatus.FORBIDDEN);return exchange.getResponse().setComplete();}}}}}return chain.filter(exchange);};}public static class Config {private List<String> readOnlyRoles;public List<String> getReadOnlyRoles() {return readOnlyRoles;}public void setReadOnlyRoles(List<String> readOnlyRoles) {this.readOnlyRoles = readOnlyRoles;}}
}

在这个过滤器中，我们首先从请求头中获取用户ID，然后通过该ID查询用户角色。如果用户角色是只读角色（即在配置中指定的只读角色列表中），则判断请求方法是否为GET，如果不是GET方法，则返回HTTP状态码403，拒绝请求。如果用户角色不是只读角色，或者用户ID或角色不存在，将请求传递给下一个过滤器。

配置网关过滤器

最后，我们需要在网关配置文件中配置过滤器。在src/main/resources目录下的application.yml文件中，添加以下配置信息：

spring:cloud:gateway:routes:- id: jdbc-routeuri: http://localhost:8080predicates:- Path=/api/**filters:- DatabaseFilter=readOnlyRoles: [ROLE_READ_ONLY]

其中， readOnlyRoles 参数指定只读角色的名称，这里使用了 ROLE_READ_ONLY 作为示例。 /api/** 表示拦截以 /api/ 开头的请求，将其传递给 http://localhost:8080 的目标服务。

几个简单的测试API

这里提供了一个简单的示例代码，用于演示如何从JDBC入手，结合网关，根据用户角色限制执行的SQL语句。请注意，这只是一个简单的示例，你可以根据具体需求进行扩展和优化。

@RestController
@RequestMapping("/api")
public class UserController {@Autowiredprivate UserRepository userRepository;@GetMapping("/users")public List<User> getAllUsers() {return userRepository.findAll();}@GetMapping("/users/{id}")public User getUserById(@PathVariable Long id) {return userRepository.findById(id).orElse(null);}@PostMapping("/users")public User createUser(@RequestBody User user) {return userRepository.save(user);}@PutMapping("/users/{id}")public User updateUser(@PathVariable Long id, @RequestBody User user) {User existingUser = userRepository.findById(id).orElse(null);if (existingUser != null) {existingUser.setName(user.getName());existingUser.setEmail(user.getEmail());// ... 更新其他属性return userRepository.save(existingUser);}return null;}@DeleteMapping("/users/{id}")public void deleteUser(@PathVariable Long id) {userRepository.deleteById(id);}
}

在这个示例中，我们定义了几个用户管理的API接口，包括获取所有用户、根据ID获取用户、创建用户、更新用户和删除用户。根据之前配置的网关过滤器，在只读角色的情况下，只有GET请求方法能够执行成功，而其他方法将返回HTTP状态码403。

方法二

创建数据库访问接口

创建一个数据库访问接口，用于执行SQL查询。可以使用Spring JDBC或者使用ORM框架如MyBatis。

public interface UserRepository {List<User> findAll();
}

实现数据库访问接口

在实现类中使用 JdbcTemplate 或者其他数据库操作工具执行SQL语句。

@Repository
public class JdbcUserRepository implements UserRepository {private final JdbcTemplate jdbcTemplate;public JdbcUserRepository(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}@Overridepublic List<User> findAll() {String sql = "SELECT * FROM users";return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));}
}

创建用户角色判断逻辑

创建一个用于判断用户角色的逻辑，可以使用Spring Security或者自定义注解来实现。

@Component
public class UserRoleChecker {public boolean isReadOnlyUser(User user) {// 根据用户角色判断是否只读用户return user.getRole().equals("READ_ONLY");}
}

创建网关过滤器

创建一个网关过滤器，用于在请求到达Controller之前进行权限判断，并阻止非只读用户执行非SELECT的SQL查询。

@Component
public class DatabaseFilter implements GlobalFilter, Ordered {private final UserRepository userRepository;private final UserRoleChecker userRoleChecker;public DatabaseFilter(UserRepository userRepository, UserRoleChecker userRoleChecker) {this.userRepository = userRepository;this.userRoleChecker = userRoleChecker;}@Overridepublic Mono<Void> filter(ServerWebExchange exchange, GatewayFilterChain chain) {// 获取请求中的用户信息User user = getUserFromRequest(exchange.getRequest());// 判断用户角色是否只读用户boolean isReadOnlyUser = userRoleChecker.isReadOnlyUser(user);// 获取请求的SQL语句String sql = getSqlFromRequest(exchange.getRequest());// 如果是非只读用户且SQL语句不是SELECT，则拒绝请求if (!isReadOnlyUser && !sql.startsWith("SELECT")) {exchange.getResponse().setStatusCode(HttpStatus.FORBIDDEN);return exchange.getResponse().setComplete();}return chain.filter(exchange);}@Overridepublic int getOrder() {return -1; // 设置过滤器优先级，确保在其他过滤器之前执行}private User getUserFromRequest(ServerHttpRequest request) {// 从请求中获取用户信息，可以从请求头、Cookie或者其他方式获取// 示例中直接返回一个固定的用户return new User("readonly", "READ_ONLY");}private String getSqlFromRequest(ServerHttpRequest request) {// 从请求中获取SQL语句，可以从请求参数、请求体或者其他方式获取。示例中直接返回一个固定的SQL语句。return "SELECT * FROM users";}}

配置网关过滤器

在Spring Boot的配置类中配置网关过滤器。

@Configuration
public class GatewayConfig {private final UserRepository userRepository;private final UserRoleChecker userRoleChecker;public GatewayConfig(UserRepository userRepository, UserRoleChecker userRoleChecker) {this.userRepository = userRepository;this.userRoleChecker = userRoleChecker;}@Beanpublic RouteLocator customRouteLocator(RouteLocatorBuilder builder) {return builder.routes().route("database", r -> r.path("/api/**").filters(f -> f.filter(new DatabaseFilter(userRepository, userRoleChecker))).uri("http://localhost:8080")).build();}
}

上述示例中，配置了一个名为"database"的路由，该路由会匹配所有以"/api/"开头的请求，并通过 DatabaseFilter 过滤器进行权限判断。如果用户角色是只读用户且SQL语句不是以"SELECT"开头，则拒绝请求。

总结

通过以上步骤，我们可以实现在Spring Boot项目中，根据用户的角色控制数据库访问权限。如果用户是只读人员角色，则只能执行SELECT的查询SQL，其他非SELECT的SQL语句会被拦截并拒绝执行。我们实现了从JDBC入手，结合网关，根据用户角色限制执行SQL语句的功能。你可以根据实际需求进行进一步的扩展和优化，例如在拦截器中添加更多的角色判断逻辑、使用自定义注解来标识只读方法等。

大家是否遇到类似问题，欢迎评论区讨论，如有错误之处，敬请留言!