Mybatis-plus数据权限DataPermissionInterceptor实现

数据权限因分页问题，不可能通过代码对数据进行过滤处理，只能在数据库语句进行处理，而如果每个查询都进行特殊处理的话，是个巨大的工作量，在网上找到了mybatis的一种解决方案。

一、源码分析

• 继承抽象类JsqlParserSupport并重写processSelect方法。JSqlParser是一个SQL语句解析器，它将SQL转换为Java类的可遍历层次结构。plus中也引入了JSqlParser包，processSelect可以对Select语句进行处理。
• 实现InnerInterceptor接口并重写beforeQuery方法。InnerInterceptor是plus的插件接口，beforeQuery可以对查询语句执行前进行处理。
• DataPermissionHandler作为数据权限处理器，是一个接口，提供getSqlSegment方法添加数据权限 SQL 片段。
• 由上可知，我们只需要实现DataPermissionHandler接口，并按照业务规则处理SQL，就可以实现数据权限的功能。
• DataPermissionInterceptor为mybatis-plus 3.4.2版本以上才有的功能。

package com.baomidou.mybatisplus.extension.plugins.inner;

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.handler.DataPermissionHandler;
import lombok.*;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.SQLException;
import java.util.List;

/**
 * 数据权限处理器
 *
 * @author hubin
 * @since 3.4.1 +
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@SuppressWarnings({"rawtypes"})
public class DataPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor {
private DataPermissionHandler dataPermissionHandler;

@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
if (InterceptorIgnoreHelper.willIgnoreDataPermission(ms.getId())) return;
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
mpBs.sql(parserSingle(mpBs.sql(), ms.getId()));
}

@Override
protected void processSelect(Select select, int index, String sql, Object obj) {
SelectBody selectBody = select.getSelectBody();
if (selectBody instanceof PlainSelect) {
this.setWhere((PlainSelect) selectBody, (String) obj);
} else if (selectBody instanceof SetOperationList) {
SetOperationList setOperationList = (SetOperationList) selectBody;
List<SelectBody> selectBodyList = setOperationList.getSelects();
selectBodyList.forEach(s -> this.setWhere((PlainSelect) s, (String) obj));
}
}

/**
 * 设置 where 条件
 *
 * @param plainSelect  查询对象
 * @param whereSegment 查询条件片段
 */
protected void setWhere(PlainSelect plainSelect, String whereSegment) {
Expression sqlSegment = dataPermissionHandler.getSqlSegment(plainSelect.getWhere(), whereSegment);
if (null != sqlSegment) {
plainSelect.setWhere(sqlSegment);
}
}
}

二、使用案例

mybatis-plus在gitee的仓库中，有人询问了如何使用DataPermissionInterceptor，下面有人给出了例子，一共分为两步，一是实现dataPermissionHandler接口，二是将实现添加到mybstis-plus的处理器中。他的例子中是根据不同权限类型拼接sql。

通用的方案是在所有的表中增加权限相关的字段，如部门、门店、租户等。实现dataPermissionHandler接口时较方便，可直接添加这几个字段的条件，无需查询数据库。

/**
* 自定义数据权限
*
* @Author PXL
* @Version 1.0
* @Date 2021-02-07 16:52
*/
public class DataPermissionHandlerImpl implements DataPermissionHandler {
@Override
public Expression getSqlSegment(Expression where, String mappedStatementId) {
try {
Class<?> clazz = Class.forName(mappedStatementId.substring(0, mappedStatementId.lastIndexOf(".")));
String methodName = mappedStatementId.substring(mappedStatementId.lastIndexOf(".") + 1);
Method[] methods = clazz.getDeclaredMethods();
for (Method method : methods) {
DataPermission annotation = method.getAnnotation(DataPermission.class);
if (ObjectUtils.isNotEmpty(annotation) && (method.getName().equals(methodName) || (method.getName() + "_COUNT").equals(methodName))) {
// 获取当前的用户
LoginUser loginUser = SpringUtils.getBean(TokenService.class).getLoginUser(ServletUtils.getRequest());
if (ObjectUtils.isNotEmpty(loginUser) && ObjectUtils.isNotEmpty(loginUser.getUser()) && !loginUser.getUser().isAdmin()) {
return dataScopeFilter(loginUser.getUser(), annotation.value(), where);
}
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return where;
}
/**
* 构建过滤条件
*
* @param user 当前登录用户
* @param where 当前查询条件
* @return 构建后查询条件
*/
public static Expression dataScopeFilter(SysUser user, String tableAlias, Expression where) {
Expression expression = null;
for (SysRole role : user.getRoles()) {
String dataScope = role.getDataScope();
if (DataScopeAspect.DATA_SCOPE_ALL.equals(dataScope)) {
return where;
}
if (DataScopeAspect.DATA_SCOPE_CUSTOM.equals(dataScope)) {
InExpression inExpression = new InExpression();
inExpression.setLeftExpression(buildColumn(tableAlias, "dept_id"));
SubSelect subSelect = new SubSelect();
PlainSelect select = new PlainSelect();
select.setSelectItems(Collections.singletonList(new SelectExpressionItem(new Column("dept_id"))));
select.setFromItem(new Table("sys_role_dept"));
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new Column("role_id"));
equalsTo.setRightExpression(new LongValue(role.getRoleId()));
select.setWhere(equalsTo);
subSelect.setSelectBody(select);
inExpression.setRightExpression(subSelect);
expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, inExpression) : inExpression;
}
if (DataScopeAspect.DATA_SCOPE_DEPT.equals(dataScope)) {
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(buildColumn(tableAlias, "dept_id"));
equalsTo.setRightExpression(new LongValue(user.getDeptId()));
expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, equalsTo) : equalsTo;
}
if (DataScopeAspect.DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope)) {
InExpression inExpression = new InExpression();
inExpression.setLeftExpression(buildColumn(tableAlias, "dept_id"));
SubSelect subSelect = new SubSelect();
PlainSelect select = new PlainSelect();
select.setSelectItems(Collections.singletonList(new SelectExpressionItem(new Column("dept_id"))));
select.setFromItem(new Table("sys_dept"));
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new Column("dept_id"));
equalsTo.setRightExpression(new LongValue(user.getDeptId()));
Function function = new Function();
function.setName("find_in_set");
function.setParameters(new ExpressionList(new LongValue(user.getDeptId()) , new Column("ancestors")));
select.setWhere(new OrExpression(equalsTo, function));
subSelect.setSelectBody(select);
inExpression.setRightExpression(subSelect);
expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, inExpression) : inExpression;
}
if (DataScopeAspect.DATA_SCOPE_SELF.equals(dataScope)) {
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(buildColumn(tableAlias, "create_by"));
equalsTo.setRightExpression(new StringValue(user.getUserName()));
expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, equalsTo) : equalsTo;
}
}
return ObjectUtils.isNotEmpty(where) ? new AndExpression(where, new Parenthesis(expression)) : expression;
}
/**
* 构建Column
*
* @param tableAlias 表别名
* @param columnName 字段名称
* @return 带表别名字段
*/
public static Column buildColumn(String tableAlias, String columnName) {
if (StringUtils.isNotEmpty(tableAlias)) {
columnName = tableAlias + "." + columnName;
}
return new Column(columnName);
}
}

// 自定义数据权限
interceptor.addInnerInterceptor(new DataPermissionInterceptor(new DataPermissionHandlerImpl()));

尝试验证

DataPermissionHandler 接口

可以看到DataPermissionHandler 接口使用中，传递来的参数是什么。

参数含义

where为当前sql已有的where条件mappedStatementId为mapper中定义的方法的路径

@InterceptorIgnore注解

拦截忽略注解 @InterceptorIgnore

属性名类型默认值描述

tenantLineString“”行级租户dynamicTableNameString“”动态表名blockAttackString“”攻击 SQL 阻断解析器,防止全表更新与删除illegalSqlString“”垃圾SQL拦截

实践应用

在维修小程序中，我使用了此方案。如下是我的代码：

/**
* @ClassName MyDataPermissionHandler
* @Description 自定义数据权限处理
* @Author FangCheng
* @Date 2022/4/2 14:54
**/
@Component
public class MyDataPermissionHandler implements DataPermissionHandler {
@Autowired
@Lazy
private UserRepository userRepository;
@Override
public Expression getSqlSegment(Expression where, String mappedStatementId) {
try {
Class<?> clazz = Class.forName(mappedStatementId.substring(0, mappedStatementId.lastIndexOf(".")));
String methodName = mappedStatementId.substring(mappedStatementId.lastIndexOf(".") + 1);
Method[] methods = clazz.getDeclaredMethods();
for (Method method : methods) {
if (!methodName.equals(method.getName())) {
continue;
}
// 获取自定义注解，无此注解则不控制数据权限
CustomDataPermission annotation = method.getAnnotation(CustomDataPermission.class);
if (annotation == null) {
continue;
}
// 自定义的用户上下文，获取到用户的id
ContextUserDetails contextUserDetails = UserDetailsContextHolder.getContextUserDetails();
String userId = contextUserDetails.getId();
User user = userRepository.selectUserById(userId);
// 如果是特权用户，不控制数据权限
if (Constants.ADMIN_RULE == user.getAdminuser()) {
return where;
}
// 员工用户
if (UserTypeEnum.USER_TYPE_EMPLOYEE.getCode().equals(user.getUsertype())) {
// 员工用户的权限字段
String field = annotation.field().getValue();
// 单据类型
String billType = annotation.billType().getFuncno();
// 操作类型
OperationTypeEnum operationType = annotation.operation();
// 权限字段为空则为不控制数据权限
if (StringUtils.isNotEmpty(field)) {
List<DataPermission> dataPermissions = userRepository.selectUserFuncnoDataPermission(userId, billType);
if (dataPermissions.size() == 0) {
// 没数据权限，但有功能权限则取所有数据
return where;
}
// 构建in表达式
InExpression inExpression = new InExpression();
inExpression.setLeftExpression(new Column(field));
List<Expression> conditions = null;
switch(operationType) {
case SELECT:
conditions = dataPermissions.stream().map(res -> new StringValue(res.getStkid())).collect(Collectors.toList());
break;
case INSERT:
conditions = dataPermissions.stream().filter(DataPermission::isAddright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList());
break;
case UPDATE:
conditions = dataPermissions.stream().filter(DataPermission::isModright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList());
break;
case APPROVE:
conditions = dataPermissions.stream().filter(DataPermission::isCheckright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList());
break;
default:
break;
}
if (conditions == null) {
return where;
}
conditions.add(new StringValue(Constants.ALL_STORE));
ItemsList itemsList = new ExpressionList(conditions);
inExpression.setRightItemsList(itemsList);
if (where == null) {
return inExpression;
}
return new AndExpression(where, inExpression);
} else {
return where;
}
} else {
// 供应商用户的权限字段
String field = annotation.vendorfield().getValue();
if (StringUtils.isNotEmpty(field)) {
// 供应商如果控制权限，则只能看到自己的单据。直接使用EqualsTo 
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new Column(field));
equalsTo.setRightExpression(new StringValue(userId));
if (where == null) {
return equalsTo;
}
// 创建 AND 表达式 拼接Where 和 = 表达式
return new AndExpression(where, equalsTo);
} else {
return where;
}
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return where;
}
}

/**
* @ClassName MybatisConfig
* @Description mybatis配置
* @Author FangCheng
* @Date 2022/4/2 15:32
**/
@Configuration
public class MybatisConfig {
@Autowired
private MyDataPermissionHandler myDataPermissionHandler;
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 添加数据权限插件
DataPermissionInterceptor dataPermissionInterceptor = new DataPermissionInterceptor();
// 添加自定义的数据权限处理器
dataPermissionInterceptor.setDataPermissionHandler(myDataPermissionHandler);
interceptor.addInnerInterceptor(dataPermissionInterceptor);
// 分页插件
//interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.SQL_SERVER));
return interceptor;
}
}

因为维修小程序相当于一个外挂程序，他的权限控制延用了七八年前程序的方案，设计的较为复杂，也有现成获取数据的存储过程供我们使用，此处做了一些特殊处理。增加了自定义注解、dataPermissionHandler接口实现类查询了数据库调用存储过程获取权限信息等。

自定义注解

/**
* @ClassName CustomDataPermission
* @Description 自定义数据权限注解
* @Author FangCheng
* @Date 2022/4/6 10:24
**/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface CustomDataPermission {
PermissionFieldEnum field();
PermissionFieldEnum vendorfield();
BillTypeEnum billType();
OperationTypeEnum operation();
}

使用注解

/**
* @ClassName ApplyHMapper
* @Description 维修申请单主表
* @Author FangCheng
* @Date 2022/4/6 13:06
**/
@Mapper
public interface ApplyHMapper extends BaseMapper<ApplyHPo> {
/**
* @Description:
* @author FangCheng
* @Date: 2022/4/1 15:26
* @methodName selectApplyHs
*/
@CustomDataPermission(field = PermissionFieldEnum.FIELD_STKID,
vendorfield = PermissionFieldEnum.FIELD_EMPTY,
billType = BillTypeEnum.APPLY_BILL,
operation = OperationTypeEnum.SELECT)
Page<ApplyHPo> selectApplyHs(IPage<ApplyHPo> page, @Param(Constants.WRAPPER) QueryWrapper<ApplyHPo> queryWrapper);
/**
* @Description:
* @author FangCheng
* @Date: 2022/4/1 15:26
* @methodName selectApplyHsForVendor
*/
Page<ApplyHPo> selectApplyHsForVendor(IPage<ApplyHPo> page, @Param("vendorid") String vendorid, @Param(Constants.WRAPPER) QueryWrapper<ApplyHPo> queryWrapper);
/**
* @Description:
* @author FangCheng
* @Date: 2022/4/1 15:26
* @methodName selectApplyH
*/
@CustomDataPermission(field = PermissionFieldEnum.FIELD_STKID,
vendorfield = PermissionFieldEnum.FIELD_EMPTY,
billType = BillTypeEnum.APPLY_BILL,
operation = OperationTypeEnum.SELECT)
ApplyHPo selectApplyH(@Param("billNo") String billNo);
/**
* @Description:
* @author FangCheng
* @Date: 2022/4/1 15:26
* @methodName selectApplyH
*/
@InterceptorIgnore
ApplyHPo selectApplyHNoPermission(@Param("billNo") String billNo);
/**
* @Description:
* @author FangCheng
* @Date: 2022/4/1 15:26
* @methodName saveApplyH
*/
void saveApplyH(ApplyHPo applyHPo);
}

最终的效果

2022-04-24 09:14:52.878 DEBUG 29254 --- [nio-8086-exec-2] c.y.w.i.p.m.ApplyHMapper.selectApplyHs : ==> select * from t_mt_apply_h WHERE stkid IN ('0025', 'all')

总结

以上就是今天要讲的内容，本文仅仅简单介绍了Mybatis-plus数据权限接口DataPermissionInterceptor的一种实现方式，没有过多的深入研究。

部分内容参考：
Mybatis-Plus入门系列(3)- MybatisPlus之数据权限插件DataPermissionInterceptor
@InterceptorIgnore

文档下载：Mybatis-plus数据权限DataPermissionInterceptor实现.doc文档