多租户权限过滤查询-基于mybatisplus权限插件DataPermissionInterceptor实现

前言

因为业务需要对系统中的相关模块的权限通过不同的部门这种属性进行过滤，这边参考了开源项目ruoyi里面的权限过滤设计，然后结合自身的业务进行实现
优秀的开源项目地址:ruoyi-vue-pro
梳理了解了逻辑之后总结了一下实现原理，在需要进行权限过滤的表中新增类似dept_id的字段(可根据自身业务替换成其他字段)，然后通过自定义DataPermissionInterceptor，继承JsqlParserSupport中的方法进行覆写，里面自己根据业务进行过滤的逻辑，最终达到目的

下面通过自身的一个案例进行说明，需要达到的效果是根据不同的用户所在的部门查看不同部门下面的数据

实现案例

1.首先在相关的表上创建字段dept_id
2.自定义Interceptor，继承JsqlParserSupport并覆写逻辑

/**
 * 部门数据权限查询过滤
 */
public class DeptPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor {

    @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(this.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 = getSqlSegment(plainSelect, whereSegment);
        if (null != sqlSegment) {
            plainSelect.setWhere(sqlSegment);
        }
    }

    @SneakyThrows(Exception.class)
    private Expression getSqlSegment(PlainSelect plainSelect, String whereSegment) {
        Expression where = plainSelect.getWhere();
        if (where == null) {
            where = new HexValue(" 1 = 1 ");
        }
        //获取当前用户信息(根据自身业务信息来)
        User user = userReq.getUser();

        //获取mapper名称
        String className = whereSegment.substring(0, whereSegment.lastIndexOf("."));
        FromItem fromItem = plainSelect.getFromItem();
        String mainTableName = "";
        if (fromItem instanceof Table) {
            Table fromItem1 = (Table) plainSelect.getFromItem();
            Alias fromItemAlias = fromItem.getAlias();
            mainTableName = fromItemAlias == null ? fromItem1.getName() : fromItemAlias.getName();
        } else if (fromItem instanceof SubSelect) {
            SubSelect fromItem1 = (SubSelect) plainSelect.getFromItem();
            Alias fromItemAlias = fromItem1.getAlias();
            PlainSelect selectBody = (PlainSelect) fromItem1.getSelectBody();
            FromItem subFromItem = selectBody.getFromItem();
            if (subFromItem instanceof Table){
                Table fromItem2 = (Table) selectBody.getFromItem();
                mainTableName = fromItemAlias == null ? fromItem2.getName() : fromItemAlias.getName();
            }
        }
        //部门权限过滤
        if (ObjectUtil.isNotEmpty(user)){
            List<String> deptId = user.getVisibleDeptId();
            if (null != deptId && deptId.size() > 0){
                //把list转换成JSQLParser需要的元素列表
                ItemsList itemList = new ExpressionList(deptId.stream().map(StringValue::new).collect(Collectors.toList()));
                //构建in表达式
                InExpression inExpression = new InExpression(new Column("表名.dept_id"),itemList);
                Parenthesis parenthesis = new Parenthesis(inExpression);
                return new AndExpression(where, parenthesis);
            }
        }
        return where;
    }
}

3.再自定义新增的Permission实现新增时部门id自动插入，这个可以酌情考虑要不要添加

/**
 * 部门数据新增过滤
 */
public class DeptInsertInterceptor 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.INSERT) {
            //用来判断是否不需要插入该字段
            PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
                mpBs.sql(parserMulti(mpBs.sql(), null));
            } catch (Exception e) {
        }
    }

    @Override
    protected void processInsert(Insert insert, int index, String sql, Object obj) {
        //获取登录用户信息  可根据自身情况获取
        User user = UserReq.getUser();
        if (ObjectUtil.isEmpty(user) || StringUtils.isBlank(user.getDeptId())){
            return;
        }
        List<Column> columns = insert.getColumns();
        if (CollectionUtils.isEmpty(columns)) {
            return;
        }
        columns.add(new Column("dept_id"));

        StringValue deptIdVlaue = new StringValue(user.getDeptId());

        if (null != insert.getItemsList()) {
            ItemsList itemsList = insert.getItemsList();
            if (itemsList instanceof MultiExpressionList) {
                ((MultiExpressionList) itemsList).getExpressionLists().forEach(el -> el.getExpressions().add(deptIdVlaue));
            } else {
                ((ExpressionList) itemsList).getExpressions().add(deptIdVlaue);
            }
        } else {
            throw ExceptionUtils.mpe("Failed to process multiple-table update, please exclude the tableName or statementId");
        }
    }

    /*@Override
    public void setProperties(Properties properties) {
        PropertyMapper.newInstance(properties).whenNotBlank("rootDeptInsertHandler",
                ClassUtils::newInstance, this::setRootDeptInsertHandler);
    }*/

}

3.再将定义好的Interceptor加入到myBatisPlus中

@Configuration
@MapperScan(value = "com.test.**.mapper")
@Slf4j
public class MybatisPlusConfig {


  
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //部门id新增过滤 没有这个可以不加
        interceptor.addInnerInterceptor(new DeptInsertInterceptor());

        //部门权限过滤 
        interceptor.addInnerInterceptor(new DeptPermissionInterceptor());



        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
        
    }




}

4.然后操作相关业务的增加和查询，通过日志打印便能看到自动插入了dept_id的字段过滤
结果：

总结

这里其实就是用到了mybatisplus中的DataPermissionInterceptor插件原理来对自己新增或者查询的sql做了一次拦截，然后在中途根据自己的业务进行一些修改即可实现，有相同业务需求的可以参考一下