【Springboot进阶】springboot+mybatis+jsqlparser实现数据权限控制

SpringBoot + JSqlParser + MyBatis 数据权限实现方案

一、环境准备

1. 添加依赖

<!-- MyBatis 拦截器支持 -->
<dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.3.0</version>
</dependency><!-- SQL解析器 -->
<dependency><groupId>com.github.jsqlparser</groupId><artifactId>jsqlparser</artifactId><version>4.6</version>
</dependency>

二、用户上下文管理

1. 用户上下文持有类

public class UserContextHolder {private static final ThreadLocal<LoginUser> context = new ThreadLocal<>();public static void set(LoginUser user) {context.set(user);}public static LoginUser get() {return context.get();}public static void clear() {context.remove();}
}@Data
public class LoginUser {private Long userId;private String deptCode;  // 组织机构代码private List<String> dataScopes; // 数据权限范围
}

三、数据权限拦截器实现

1. MyBatis拦截器核心类

@Intercepts({@Signature(type = Executor.class, method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),@Signature(type = Executor.class,method = "update",args = {MappedStatement.class, Object.class})
})
public class DataPermissionInterceptor implements Interceptor {// 需要过滤的表（配置在application.yml）@Value("#{'${data-permission.ignore-tables:}'.split(',')}")private Set<String> ignoreTables;@Overridepublic Object intercept(Invocation invocation) throws Throwable {// 获取当前用户LoginUser user = UserContextHolder.get();if (user == null || CollectionUtils.isEmpty(user.getDataScopes())) {return invocation.proceed();}// 获取原始SQLObject[] args = invocation.getArgs();MappedStatement ms = (MappedStatement) args[0];BoundSql boundSql = ms.getBoundSql(args[1]);String originalSql = boundSql.getSql();// SQL解析与增强String modifiedSql = enhanceSql(originalSql, user);if (originalSql.equals(modifiedSql)) {return invocation.proceed();}// 反射修改SQLField field = boundSql.getClass().getDeclaredField("sql");field.setAccessible(true);field.set(boundSql, modifiedSql);return invocation.proceed();}private String enhanceSql(String originalSql, LoginUser user) {try {Select select = (Select) CCJSqlParserUtil.parse(originalSql);select.getSelectBody().accept(new SelectVisitorAdapter() {@Overridepublic void visit(PlainSelect plainSelect) {// 检查是否需要过滤if (isIgnoreTable(plainSelect)) return;// 构建权限表达式Expression where = buildDataScopeExpression(user, plainSelect.getTable());if (where == null) return;// 合并条件if (plainSelect.getWhere() == null) {plainSelect.setWhere(where);} else {plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), where));}}});return select.toString();} catch (JSQLParserException e) {throw new RuntimeException("SQL解析失败", e);}}private boolean isIgnoreTable(PlainSelect plainSelect) {Table table = plainSelect.getFromItem() instanceof Table ? (Table) plainSelect.getFromItem() : null;return table != null && ignoreTables.contains(table.getName().toLowerCase());}private Expression buildDataScopeExpression(LoginUser user, Table table) {// 构建组织机构过滤条件List<Expression> conditions = new ArrayList<>();for (String deptCode : user.getDataScopes()) {EqualsTo equals = new EqualsTo(new Column(table.getAlias() == null ? "dept_code" : table.getAlias().getName() + ".dept_code"),new StringValue(deptCode));conditions.add(equals);}return buildOrExpressionTree(conditions);}private Expression buildOrExpressionTree(List<Expression> conditions) {if (CollectionUtils.isEmpty(conditions)) return null;if (conditions.size() == 1) return conditions.get(0);Expression left = conditions.get(0);for (int i = 1; i < conditions.size(); i++) {left = new OrExpression(left, conditions.get(i));}return new Parenthesis(left);}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {}
}

四、Spring Security集成

1. 用户信息注入

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {@Overrideprotected void configure(HttpSecurity http) throws Exception {http.addFilterAfter(jwtFilter(), UsernamePasswordAuthenticationFilter.class);}@Beanpublic JwtAuthenticationFilter jwtFilter() {return new JwtAuthenticationFilter();}
}public class JwtAuthenticationFilter extends OncePerRequestFilter {@Overrideprotected void doFilterInternal(HttpServletRequest request, HttpServletResponse response,FilterChain chain) throws IOException, ServletException {// 从请求头解析用户信息String token = request.getHeader("Authorization");LoginUser user = parseToken(token);// 设置用户上下文try {UserContextHolder.set(user);chain.doFilter(request, response);} finally {UserContextHolder.clear();}}
}

五、配置项示例

application.yml

data-permission:enabled: trueignore-tables: sys_log, public_data # 不进行权限控制的表column-name: dept_code # 权限字段名

六、使用示例

1. 业务查询测试

@RestController
@RequestMapping("/users")
public class UserController {@Autowiredprivate UserMapper userMapper;@GetMappingpublic List<User> listUsers() {// 实际SQL将被增强为：// SELECT * FROM user WHERE dept_code IN ('DEPT001','DEPT002') return userMapper.selectList(null); }
}

七、高级功能扩展

1. 多维度权限控制

private Expression buildDataScopeExpression(LoginUser user, Table table) {List<Expression> conditions = new ArrayList<>();// 1. 部门过滤conditions.add(buildDeptCondition(table));// 2. 数据域过滤conditions.add(buildDataDomainCondition(table));// 3. 角色过滤conditions.add(buildRoleCondition(table));return buildAndExpressionTree(conditions);
}private Expression buildAndExpressionTree(List<Expression> conditions) {// 类似OR表达式的构建逻辑
}

2. 动态权限字段配置

@ConfigurationProperties(prefix = "data-permission")
public class DataPermissionProperties {private Map<String, String> columnMappings = new HashMap<>();// getters/setters
}// 在拦截器中根据表名获取字段名
String column = properties.getColumnMappings().getOrDefault(table.getName(), "dept_code");

八、注意事项

1. SQL兼容性：

   • 处理UNION语句时需遍历所有SELECT子句
   • 支持子查询中的权限控制
   • 注意表别名处理

2. 性能优化：

   // 使用弱引用缓存解析结果
   private static final Map<String, SoftReference<Select>> sqlCache = new ConcurrentHashMap<>();
   

3. 权限失效场景：

   • 直接SQL执行（绕过MyBatis）
   • 存储过程调用
   • 多租户架构下的跨库查询

4. 审计日志：

   // 记录修改前后的SQL
   log.info("Original SQL: {}\nModified SQL: {}", originalSql, modifiedSql);
   

完整实现需要根据具体业务需求调整权限条件生成逻辑，建议配合单元测试验证不同场景下的SQL修改效果。

关联知识

【Java知识】一款强大的SQL处理库JSqlPaser
【Spring相关技术】Spring进阶-SpEL深入解读