变化：相比于之前写的数据权限拦截器，新增了白名单功能，通过注解的方式让哪些SQL不进行数据权限拦截，之前的文章地址
思路：通过MyBatisPlus的拦截器对每个要执行的SQL进行拦截，然后判断其是否为查询语句，如果是查询语句，则继续判断，其类或者方法上是否存在@DataScopeIgnore注解，如果存在则不做任何处理，反之对原始SQL进行解析，并获取该用户的角色，并获取其针对该接口设置的数据权限信息，改造原始的查询条件，以此来实现数据权限的控制
缺陷：目前@DataScopeIgnore注解只有作用于Mapper层才能生效，不过按理说应该是满足一般数据权限控制的要求
代码地址，其中包含一些暂时未使用的代码，是为了后续做单点登录准备的，所有涉及代码在下面已经全部提及
package com.xx.permission.config;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.xx.permission.entity.result.DataPermission;
import com.xx.permission.utils.ExpressionUtils;
import com.xx.permission.utils.UserUtils;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
/**
* @author aqi
* @describe 数据权限拦截器
*/
@Slf4j
@Component
public class DataScopeInterceptor extends JsqlParserSupport implements InnerInterceptor {
@Override
public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
MappedStatement ms = mpSh.mappedStatement();
SqlCommandType sct = ms.getSqlCommandType();
if (sct == SqlCommandType.SELECT) {
if (this.judgementDataScopeIgnore(ms)) {
return;
}
PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
mpBs.sql(parserMulti(mpBs.sql(), null));
}
}
/**
* 查询
*/
@Override
protected void processSelect(Select select, int index, String sql, Object obj) {
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
// 获取表名/别名(如果是关联查询是取第一个join左侧的表名/别名)
String tableName = ExpressionUtils.getTableName(plainSelect);
// 构建用户权限控制条件
this.buildUserPermissionSql(plainSelect, sql, tableName);
}
/**
* 构建用户权限控制条件
* 思路:
* 1、获取当前用户信息,再通过用户信息获取到角色对应的数据权限集合
* 2、通过获取到的该用户所有的数据权限集合过滤出本次请求的数据权限
* 3、将本次请求的数据权限拼接到已有的SQL中
* @param plainSelect 用于解析SQL的类
* @param tableName 表名/别名(join查询左侧表名)
*/
private void buildUserPermissionSql(PlainSelect plainSelect, String sql, String tableName) {
// 获取当前用户接口数据权限(这里的数据都是模拟的,实际上可能得从缓存或者session中获取)
Map<String, List<DataPermission>> userPermissionMap = UserUtils.getUserPermission();
// 获取本次请求的uri
String uri = UserUtils.getUri();
// 获取本次请求uri对应的数据权限集合
List<DataPermission> dataPermissions = userPermissionMap.get(uri);
if (!CollectionUtils.isEmpty(dataPermissions)) {
// 将多个条件合并在一起
dataPermissions.forEach(permission -> {
InExpression inExpression = ExpressionUtils.buildInSql(tableName, permission);
ExpressionUtils.appendExpression(plainSelect, inExpression);
});
}
log.info("[DataScopeInterceptor]请求uri:[{}],原始SQL:[{}]处理后SQL:[{}]", uri, sql, plainSelect);
}
/**
* 判断类/方法上是否存在DataScopeIgnore注解
* 存在的问题:这里只能获取到Mapper层中的一些东西,所以在别的地方加@DataScopeIgnore也不会生效
* @param ms ms
* @return 是否需要进行数据权限控制,true:不需要,false:需要
*/
private boolean judgementDataScopeIgnore(MappedStatement ms) {
try {
String id = ms.getId();
String classPath = id.substring(0, id.lastIndexOf("."));
Class<?> aClass = Class.forName(classPath);
// 判断该类上是否存在DataScopeIgnore注解
DataScopeIgnore declaredClassAnnotation = aClass.getDeclaredAnnotation(DataScopeIgnore.class);
if (declaredClassAnnotation != null) {
return true;
}
// 判断该方法上是否存在DataScopeIgnore注解
String methodName = id.substring(id.lastIndexOf(".") + 1);
Method method = aClass.getMethod(methodName);
DataScopeIgnore declaredMethodAnnotation = method.getDeclaredAnnotation(DataScopeIgnore.class);
return declaredMethodAnnotation != null;
} catch (ClassNotFoundException | NoSuchMethodException e) {
// 解析DataScopeIgnore出现异常,默认当作需要做数据权限控制
return false;
}
}
}
package com.xx.permission.config;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
/**
* @author aqi
* @since 2023/5/15 14:05
*/
@Slf4j
@Configuration
@Component
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
// 初始化Mybatis Plus拦截器
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 自定义数据拦截器
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
interceptor.addInnerInterceptor(new DataScopeInterceptor());
return interceptor;
}
}
package com.xx.permission.config;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author aqi
* @describe 不进行数据权限控制
* @since 2023/6/8 14:44
*/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataScopeIgnore {
}
package com.xx.permission.utils;
import com.xx.permission.entity.result.DataPermission;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.PlainSelect;
import java.util.Objects;
import java.util.stream.Collectors;
/**
* @author aqi
* @since 2023/5/17 10:16
* @describe JSqlParser工具类,用于通过API的方式操作SQL语句
*/
public class ExpressionUtils {
private final static String LONG_TYPE = "long";
private final static String STRING_TYPE = "string";
/**
* 构建in sql
* @param tableName 表名
* @param permission 字段权限
* @return InExpression
*/
public static InExpression buildInSql(String tableName, DataPermission permission) {
// 把集合转变为JSQLParser需要的元素列表
ItemsList itemsList = ExpressionUtils.handleFieldType(permission);
// 创建IN表达式对象，传入列名及IN范围列表
return new InExpression(new Column(tableName + "." + permission.getField()), itemsList);
}
/**
* 构建查询语句之前,判断字段类型
* @param permission 字段权限
*/
private static ItemsList handleFieldType(DataPermission permission) {
String fieldType = permission.getFieldType();
if (Objects.equals(fieldType, LONG_TYPE)) {
return new ExpressionList(permission.getValue().stream().map(LongValue::new).collect(Collectors.toList()));
} else {
return new ExpressionList(permission.getValue().stream().map(StringValue::new).collect(Collectors.toList()));
}
}
/**
* 构建eq sql
* @param columnName 字段名称
* @param value 字段值
* @return EqualsTo
*/
public static EqualsTo buildEq(String columnName, String value) {
return new EqualsTo(new Column(columnName), new StringValue(value));
}
/**
* 获取表名/别名
* @param plainSelect plainSelect
* @return 表名/别名
*/
public static String getTableName(PlainSelect plainSelect) {
// 获取别名
Table table= (Table) plainSelect.getFromItem();
Alias alias = table.getAlias();
return null == alias ? table.getName() : alias.getName();
}
/**
* 将2个where条件拼接到一起
* @param plainSelect plainSelect
* @param appendExpression 待拼接条件
*/
public static void appendExpression(PlainSelect plainSelect, Expression appendExpression) {
Expression where = plainSelect.getWhere() == null ? appendExpression : new AndExpression(plainSelect.getWhere(), appendExpression);
plainSelect.setWhere(where);
}
}
目前是简单的用户工具类，按照实际情况需要调整
package com.xx.permission.utils;
import com.xx.permission.config.CacheData;
import com.xx.permission.entity.result.DataPermission;
import com.xx.permission.entity.result.RoleDataPermission;
import com.xx.permission.entity.result.UserDTO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* @author aqi
* @since 2023/5/17 14:20
*/
@Slf4j
public class UserUtils {
public static UserDTO currentUser;
/**
* 获取当前用户信息
* @return 用户信息
*/
public static UserDTO getCurrentUserInfo() {
return currentUser;
}
/**
* 模拟登录之后往session或者redis中存储用户信息的过程
* @param user 用户信息
*/
public static void setCurrentUser(UserDTO user) {
UserUtils.currentUser = user;
}
/**
* 获取用户权限信息
* @return 用户权限信息
*/
public static Map<String, List<DataPermission>> getUserPermission() {
// 获取当前用户信息
UserDTO currentUserInfo = UserUtils.getCurrentUserInfo();
// 获取当前用户角色下的所有接口数据权限集合
List<RoleDataPermission> roleDataPermissionList = CacheData.ROLE_DATA_PERMISSION_MAP.get(currentUserInfo.getRoleId());
// key:uri(接口地址),value:DataPermission(数据权限)
return roleDataPermissionList.stream().collect(Collectors.toMap(RoleDataPermission::getUri, RoleDataPermission::getDataPermissionList));
}
/**
* 获取本次请求的uri
* @return uri
*/
public static String getUri() {
// 获取此次请求的uri
String uri = "";
RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();
if (null != requestAttributes) {
HttpServletRequest request = ((ServletRequestAttributes) requestAttributes).getRequest();
uri = request.getRequestURI();
}
return uri;
}
}
缓存
package com.xx.permission.config;
import com.xx.permission.entity.result.RoleDataPermission;
import java.util.List;
import java.util.Map;
/**
* @author xiaxing
* @describe
* @since 2023/5/18 15:06
*/
public class CacheData {
/**
* 角色权限信息
*/
public static List<RoleDataPermission> ROLE_DATA_PERMISSION_LIST;
/**
* 角色权限信息(key:角色ID,value:角色权限信息)
*/
public static Map<Long, List<RoleDataPermission>> ROLE_DATA_PERMISSION_MAP;
}
初始化初始化角色数据权限
package com.xx.permission.config;
import com.alibaba.fastjson.JSONArray;
import com.xx.permission.entity.result.DataPermission;
import com.xx.permission.entity.result.RoleDataPermission;
import com.xx.permission.service.SysRoleService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.util.List;
import java.util.stream.Collectors;
/**
* @author aqi
* @since 2023/5/18 14:48
* @describe 初始化配置数据
*/
@Slf4j
@Component
public class InitConfig {
@Resource
private SysRoleService sysRoleService;
@PostConstruct
public void init() {
this.initRoleDataPermission();
}
/**
* 初始化角色数据权限
*/
private void initRoleDataPermission() {
log.info("初始化角色数据权限...");
List<RoleDataPermission> roleDataPermission = sysRoleService.getRoleDataPermission();
for (RoleDataPermission dataPermission : roleDataPermission) {
List<DataPermission> dataPermissions = JSONArray.parseArray(dataPermission.getDataPermission(), DataPermission.class);
dataPermission.setDataPermissionList(dataPermissions);
}
CacheData.ROLE_DATA_PERMISSION_LIST = roleDataPermission;
CacheData.ROLE_DATA_PERMISSION_MAP = CacheData.ROLE_DATA_PERMISSION_LIST.stream()
.collect(Collectors.groupingBy(RoleDataPermission::getRoleId));
}
}
xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xx.permission.mapper.SysRoleMapper">
<select id="getRoleDataPermission" resultType="com.xx.permission.entity.result.RoleDataPermission">
SELECT
sys_role.id roleId,
sys_role.role_name roleName,
sys_role.DESCRIBE roleDescribe,
sys_role_api_permission.data_permission dataPermission,
sys_api.uri
FROM
sys_role
INNER JOIN sys_role_api_permission ON sys_role.id = sys_role_api_permission.role_id
INNER JOIN sys_api ON sys_api.id = sys_role_api_permission.api_id
</select>
</mapper>
这里在方法上加上了@DataScopeIgnore注解，避免获取角色权限时受到数据权限拦截的影响
package com.xx.permission.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xx.permission.config.DataScopeIgnore;
import com.xx.permission.entity.SysRole;
import com.xx.permission.entity.result.RoleDataPermission;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* <p>
* 角色表 Mapper 接口
* </p>
*
* @author aqi
* @since 2023-05-18
*/
@Mapper
public interface SysRoleMapper extends BaseMapper<SysRole> {
@DataScopeIgnore
List<RoleDataPermission> getRoleDataPermission();
}
实现类
package com.xx.permission.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xx.permission.entity.SysRole;
import com.xx.permission.entity.result.RoleDataPermission;
import com.xx.permission.mapper.SysRoleMapper;
import com.xx.permission.service.SysRoleService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
/**
* <p>
* 角色表 服务实现类
* </p>
*
* @author aqi
* @since 2023-05-18
*/
@Service
public class SysRoleServiceImpl extends ServiceImpl<SysRoleMapper, SysRole> implements SysRoleService {
@Resource
SysRoleMapper sysRoleMapper;
@Override
public List<RoleDataPermission> getRoleDataPermission() {
return sysRoleMapper.getRoleDataPermission();
}
}
实体类
package com.xx.permission.entity.result;
import lombok.Data;
import java.util.List;
/**
* @author xiaxing
* @describe 角色权限
* @since 2023/5/18 14:59
*/
@Data
public class RoleDataPermission {
/**
* 角色ID
*/
private Long roleId;
/**
* 角色名称
*/
private String roleName;
/**
* 角色描述
*/
private String roleDescribe;
private String dataPermission;
/**
* 角色权限集合
*/
private List<DataPermission> dataPermissionList;
/**
* 接口地址
*/
private String uri;
}
package com.xx.permission.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
/**
* <p>
* 角色表
* </p>
*
* @author aqi
* @since 2023-05-18
*/
@Data
@TableName("sys_role")
public class SysRole implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 角色ID
*/
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 角色名称
*/
private String roleName;
/**
* 角色说明
*/
private String describe;
}
这里的表结构只有最基础的字段
这里的数据权限存在了JSON中，只是为了少建表，其中field表示字段名称，value表示字段的值，fieldType表示字段的数据类型（目前只写了Long和String的支持）
package com.xx.permission.controller;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xx.permission.entity.result.UserDTO;
import com.xx.permission.service.TOrderService;
import com.xx.permission.utils.UserUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
/**
* <p>
* 订单表 前端控制器
* </p>
*
* @author aqi
* @since 2023-06-08
*/
@RestController
@RequestMapping("/order")
public class TOrderController {
@Resource
private TOrderService tOrderService;
@GetMapping("/list")
public void test() {
UserDTO userDTO = new UserDTO();
userDTO.setRoleId(2L);
UserUtils.setCurrentUser(userDTO);
tOrderService.page(new Page<>(1, 10));
}
}
效果
2023-06-09 09:47:12.349 INFO 33248 --- [nio-8080-exec-2] c.x.p.config.DataScopeInterceptor : [DataScopeInterceptor]请求uri:[/order/list],原始SQL:[SELECT COUNT(*) AS total FROM t_order]处理后SQL:[SELECT COUNT(*) AS total FROM t_order WHERE t_order.area IN (2) AND t_order.type IN ('1')]
2023-06-09 09:47:12.349 DEBUG 33248 --- [nio-8080-exec-2] c.x.p.m.TOrderMapper.selectPage_mpCount : ==> Preparing: SELECT COUNT(*) AS total FROM t_order WHERE t_order.area IN (2) AND t_order.type IN ('1')
2023-06-09 09:47:12.349 DEBUG 33248 --- [nio-8080-exec-2] c.x.p.m.TOrderMapper.selectPage_mpCount : ==> Parameters:
2023-06-09 09:47:12.356 DEBUG 33248 --- [nio-8080-exec-2] c.x.p.m.TOrderMapper.selectPage_mpCount : <== Total: 1
2023-06-09 09:47:12.364 INFO 33248 --- [nio-8080-exec-2] c.x.p.config.DataScopeInterceptor : [DataScopeInterceptor]请求uri:[/order/list],原始SQL:[SELECT id,number,type,area FROM t_order LIMIT ?]处理后SQL:[SELECT id, number, type, area FROM t_order WHERE t_order.area IN (2) AND t_order.type IN ('1') LIMIT ?]
2023-06-09 09:47:12.364 DEBUG 33248 --- [nio-8080-exec-2] c.x.p.mapper.TOrderMapper.selectPage : ==> Preparing: SELECT id, number, type, area FROM t_order WHERE t_order.area IN (2) AND t_order.type IN ('1') LIMIT ?
2023-06-09 09:47:12.365 DEBUG 33248 --- [nio-8080-exec-2] c.x.p.mapper.TOrderMapper.selectPage : ==> Parameters: 10(Long)
2023-06-09 09:47:12.377 DEBUG 33248 --- [nio-8080-exec-2] c.x.p.mapper.TOrderMapper.selectPage : <== Total: 1