spring boot+mybatis plus进行sql拦截实现权限过滤，使用mybatis plus的DataPermissionHandler

📃 关联文档

📄 前置文档

📄 前置文档

自定义一个AllbsDataPermissionHandler，使用mybatis plus自带的是可以的，主要考虑到后面还有其他自定义效果，所以单独写了一个

import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.schema.Table;

/**
 * 接口 AllbsDataPermissionHandler
 *
 * @author ChenQi
 * @date 2023/3/28
 */
public interface AllbsDataPermissionHandler {

    /**
     * 获取数据权限 SQL 片段
     *
     * @param table             表相关信息
     * @param where             待执行 SQL Where 条件表达式
     * @param mappedStatementId Mybatis MappedStatement Id 根据该参数可以判断具体执行方法
     * @return JSqlParser 条件表达式，返回的条件表达式会覆盖原有的条件表达式
     */
    Expression getSqlSegment(final Table table, Expression where, String mappedStatementId);
}

将mybatis plus中的PluginUtils工具类抽出使用，主要是为了解决其中方法jdk17不兼容的问题

当然以下代码还没改，后续更改了之后文档再更新

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;

import java.lang.reflect.Proxy;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * 说明: 从mybatis plus中抽出来主要是为了解决jdk17不兼容 realTarget 方法的问题、减少cpu占用
 */
public abstract class PluginUtils {
    public static final String DELEGATE_BOUNDSQL_SQL = "delegate.boundSql.sql";

    /**
     * 获得真正的处理对象,可能多层代理.
     */
    @SuppressWarnings("unchecked")
    public static <T> T realTarget(Object target) {
        if (Proxy.isProxyClass(target.getClass())) {
            MetaObject metaObject = SystemMetaObject.forObject(target);
            return realTarget(metaObject.getValue("h.target"));
        }
        return (T) target;
    }

    /**
     * 给 BoundSql 设置 additionalParameters
     *
     * @param boundSql             BoundSql
     * @param additionalParameters additionalParameters
     */
    public static void setAdditionalParameter(BoundSql boundSql, Map<String, Object> additionalParameters) {
        additionalParameters.forEach(boundSql::setAdditionalParameter);
    }

    public static PluginUtils.MPBoundSql mpBoundSql(BoundSql boundSql) {
        return new PluginUtils.MPBoundSql(boundSql);
    }

    public static PluginUtils.MPStatementHandler mpStatementHandler(StatementHandler statementHandler) {
        statementHandler = realTarget(statementHandler);
        MetaObject object = SystemMetaObject.forObject(statementHandler);
        return new PluginUtils.MPStatementHandler(SystemMetaObject.forObject(object.getValue("delegate")));
    }

    /**
     * {@link org.apache.ibatis.executor.statement.BaseStatementHandler}
     */
    public static class MPStatementHandler {
        private final MetaObject statementHandler;

        MPStatementHandler(MetaObject statementHandler) {
            this.statementHandler = statementHandler;
        }

        public ParameterHandler parameterHandler() {
            return get("parameterHandler");
        }

        public MappedStatement mappedStatement() {
            return get("mappedStatement");
        }

        public Executor executor() {
            return get("executor");
        }

        public PluginUtils.MPBoundSql mPBoundSql() {
            return new PluginUtils.MPBoundSql(boundSql());
        }

        public BoundSql boundSql() {
            return get("boundSql");
        }

        public Configuration configuration() {
            return get("configuration");
        }

        @SuppressWarnings("unchecked")
        private <T> T get(String property) {
            return (T) statementHandler.getValue(property);
        }
    }

    /**
     * {@link BoundSql}
     */
    public static class MPBoundSql {
        private final MetaObject boundSql;
        private final BoundSql delegate;

        MPBoundSql(BoundSql boundSql) {
            this.delegate = boundSql;
            this.boundSql = SystemMetaObject.forObject(boundSql);
        }

        public String sql() {
            return delegate.getSql();
        }

        public void sql(String sql) {
            boundSql.setValue("sql", sql);
        }

        public List<ParameterMapping> parameterMappings() {
            List<ParameterMapping> parameterMappings = delegate.getParameterMappings();
            return new ArrayList<>(parameterMappings);
        }

        public void parameterMappings(List<ParameterMapping> parameterMappings) {
            boundSql.setValue("parameterMappings", Collections.unmodifiableList(parameterMappings));
        }

        public Object parameterObject() {
            return get("parameterObject");
        }

        public Map<String, Object> additionalParameters() {
            return get("additionalParameters");
        }

        @SuppressWarnings("unchecked")
        private <T> T get(String property) {
            return (T) boundSql.getValue(property);
        }
    }
}

自定义一个注解，用于标注实体类中的需要过滤的字段

目前只取获取到的第一个字段，你要是标多个我也没办法

import java.lang.annotation.*;

/**
 * 注解 ScopeField
 *
 * @author ChenQi
 * @date 2023/3/28
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.ANNOTATION_TYPE})
public @interface ScopeField {

    /**
     * 字段名（该值可无）
     */
    String value() default "";
}

数据权限处理器

用于传递重新拼sql时所需的一些参数，新增的情况暂时还未处理，后续继续更新

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.*;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
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 net.sf.jsqlparser.statement.update.Update;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;

import java.sql.Connection;
import java.util.List;

/**
 * 数据权限处理器
 * 类 DataPermissionInterceptor
 *
 * @author ChenQi
 * @date 2023/3/28
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@SuppressWarnings({"rawtypes"})
public class DataPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor {
    private AllbsDataPermissionHandler dataPermissionHandler;

    @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.UPDATE || sct == SqlCommandType.DELETE || sct == SqlCommandType.SELECT) {
            if (InterceptorIgnoreHelper.willIgnoreDataPermission(ms.getId())) {
                return;
            }
            PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
            mpBs.sql(parserMulti(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));
        }
    }

    /**
     * 新增
     */
    @Override
    protected void processInsert(Insert insert, int index, String sql, Object obj) {
        throw new UnsupportedOperationException();
    }

    /**
     * 删除
     */
    @Override
    protected void processDelete(Delete delete, int index, String sql, Object obj) {
        final Expression sqlSegment = getUpdateOrDeleteExpression(delete.getTable(), delete.getWhere(), (String) obj);
        if (null != sqlSegment) {
            delete.setWhere(sqlSegment);
        }
    }

    /**
     * 更新
     */
    @Override
    protected void processUpdate(Update update, int index, String sql, Object obj) {
        final Expression sqlSegment = getUpdateOrDeleteExpression(update.getTable(), update.getWhere(), (String) obj);
        if (null != sqlSegment) {
            update.setWhere(sqlSegment);
        }
    }

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

    protected Expression getUpdateOrDeleteExpression(final Table table, final Expression where, final String whereSegment) {
        return dataPermissionHandler.getSqlSegment(table, where, whereSegment);
    }
}

核心，进行sql重新封装的处理

import cn.allbs.allbsjwt.config.utils.SecurityUtils;
import cn.allbs.allbsjwt.config.vo.SysUser;
import cn.allbs.common.constant.StringPool;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
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.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;

import java.util.LinkedList;
import java.util.List;
import java.util.Optional;
import java.util.Set;

/**
 * 类 CustomPermissionHandler
 *
 * @author ChenQi
 * @date 2023/3/28
 */
@Slf4j
public class CustomPermissionHandler implements AllbsDataPermissionHandler {

    private final static String DEFAULT_FILTER_FIELD = "ent_id";

    /**
     * 获取数据权限 SQL 片段
     *
     * @param where             待执行 SQL Where 条件表达式
     * @param mappedStatementId Mybatis MappedStatement Id 根据该参数可以判断具体执行方法
     * @return JSqlParser 条件表达式
     */
    @Override
    public Expression getSqlSegment(final Table table, Expression where, String mappedStatementId) {

        // 在有权限的情况下查询用户所关联的企业列表
        SysUser sysUser = SecurityUtils.getUser();
        // 如果非权限用户则不往下执行，执行原sql
        if (sysUser == null) {
            return where;
        }
        Set<Long> permissionEntList = sysUser.getEntIdList();
//        if (permissionEntList.size() == 0) {
//            return where;
//        }
        TableInfo tableInfo = TableInfoHelper.getTableInfo(table.getName());
        String fieldName = tableInfo.getFieldList().stream()
                .filter(a -> a.getField().getAnnotation(ScopeField.class) != null)
                .map(a -> a.getField().getAnnotation(ScopeField.class).value())
                .findFirst()
                .orElse(DEFAULT_FILTER_FIELD);
        Alias fromItemAlias = table.getAlias();
        String finalFieldName = Optional.ofNullable(fromItemAlias).map(a -> a.getName() + StringPool.DOT + fieldName).orElse(fieldName);

        if (permissionEntList.size() > 1) {
            // 把集合转变为 JSQLParser需要的元素列表
            InExpression inExpression = new InExpression(new Column(finalFieldName), getItemList(permissionEntList));

            // 组装sql
            return where == null ? inExpression : new AndExpression(where, inExpression);
        }
        // 设置where
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column(finalFieldName));
        equalsTo.setRightExpression(new LongValue(permissionEntList.stream().findFirst().orElse(0L)));
        return where == null ? equalsTo : new AndExpression(where, equalsTo);
    }

    private ItemsList getItemList(Set<Long> permissionEntList) {
        List<Expression> list = new LinkedList<>();
        for (Long aLong : permissionEntList) {
            try {
                Expression expression = CCJSqlParserUtil.parseCondExpression(StrUtil.join(",", aLong));
                list.add(expression);
            } catch (JSQLParserException e) {
                log.error("筛选数据转换为表达式失败!" + e.getLocalizedMessage());
            }
        }
        return new ExpressionList(list);
    }
}

最后将改插件注册进mybatis plus即可

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 类 MybatisPlusCustomConfig
 *
 * @author ChenQi
 * @date 2023/3/28
 */
@Configuration
public class MybatisPlusCustomConfig {

    @Bean
    public MybatisPlusInterceptor customMybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 数据权限
        DataPermissionInterceptor dataPermissionInterceptor = new DataPermissionInterceptor(new CustomPermissionHandler());
        interceptor.addInnerInterceptor(dataPermissionInterceptor);
        return interceptor;
    }
}

说明

• 所有sql将默认拦截并自动拼接筛选条件

• 如果想要对部分sql不进行筛选，则在dao层的类名上添加注解@InterceptorIgnore或者dao层的某个方法上添加注解@InterceptorIgnore

  

  

• 如果想要使用mybatis plus自带的sdk并使其中某些方法不进行数据筛选，则重新写一个接口集成BaseMapper,单独对其中的某个方法添加注解。

  

import com.baomidou.mybatisplus.annotation.InterceptorIgnore;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

import java.io.Serializable;
import java.util.List;

/**
 * 类 DataScopeMapper
 *
 * @author ChenQi
 * @date 2023/3/28
 */
@Mapper
@Component
public interface DataScopeMapper<T> extends BaseMapper<T> {

    /**
     * 根据 ID 查询
     *
     * @param id 主键ID
     */
    @Override
    @InterceptorIgnore
    T selectById(Serializable id);

    /**
     * 根据 entity 条件，查询全部记录
     *
     * @param queryWrapper 实体对象封装操作类（可以为 null）
     */
    @Override
    List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

    /**
     * 根据 entity 条件，查询全部记录（并翻页）
     *
     * @param page         分页查询条件（可以为 RowBounds.DEFAULT）
     * @param queryWrapper 实体对象封装操作类（可以为 null）
     */
    @Override
    <P extends IPage<T>> P selectPage(P page, @Param(
        Constants.WRAPPER) Wrapper<T> queryWrapper);
}

• 可以进行功能反转，比如目前是全部sql拦截，添加注解不拦截。可以修改beforePrepare使其默认全部不拦截，添加了该注解的方法拦截sql并重新组装。
• 如果不想违反mybatis plus注解本意，可以重新自定义一个注解来达成上述效果，本文不做展示，参考上一篇即可实现。

添加新增、更新、批量新增、批量更新的数据过滤

使用

AllbsDataPermissionHandler添加两个方法用于处理新增和更新。具体实现效果看

/**
     * 新增数据时 判断是否存在越权行为，如果存在这种行为则进行拦截并重组sql
     *
     * @param insertStmt Insert
     * @param boundSql   BoundSql
     */
void insertParameter(Insert insertStmt, BoundSql boundSql);

    /**
     * 更新数据时 判断是否存在越权行为，如果存在这种行为则进行拦截并重组sql
     *
     * @param updateStmt      Update
     * @param mappedStatement MappedStatement
     * @param boundSql        BoundSql
     */
void updateParameter(Update updateStmt, MappedStatement mappedStatement, BoundSql boundSql);

CustomPermissionHandler的实现

package cn.allbs.allbsjwt.config.datascope.mapper;

import cn.allbs.allbsjwt.config.utils.SecurityUtils;
import cn.allbs.allbsjwt.config.vo.SysUser;
import cn.allbs.common.constant.StringPool;
import cn.allbs.mybatis.datascope.DataPmsHandler;
import cn.allbs.mybatis.datascope.ScopeField;
import cn.allbs.mybatis.execption.UserOverreachException;
import cn.allbs.mybatis.utils.PluginUtils;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Optional;
import java.util.Set;

/**
 * 类 CustomPermissionHandler
 *
 * @author ChenQi
 * @date 2023/3/28
 */
@Slf4j
@Component
public class CustomPermissionHandler implements DataPmsHandler {

    private final static String DEFAULT_FILTER_FIELD = "ent_id";

    /**
     * 获取数据权限 SQL 片段
     *
     * @param where             待执行 SQL Where 条件表达式
     * @param mappedStatementId Mybatis MappedStatement Id 根据该参数可以判断具体执行方法
     * @return JSqlParser 条件表达式
     */
    @Override
    public Expression getSqlSegment(final Table table, Expression where, String mappedStatementId) {

        // 在有权限的情况下查询用户所关联的企业列表
        SysUser sysUser = SecurityUtils.getUser();
        // 如果非权限用户则不往下执行，执行原sql
        if (sysUser == null) {
            return where;
        }
        Set<Long> permissionEntList = sysUser.getEntIdList();
//        if (permissionEntList.size() == 0) {
//            return where;
//        }
        TableInfo tableInfo = TableInfoHelper.getTableInfo(table.getName());
        String fieldName = tableInfo.getFieldList().stream()
                .filter(a -> a.getField().getAnnotation(ScopeField.class) != null)
                .map(a -> a.getField().getAnnotation(ScopeField.class).value())
                .findFirst()
                .orElse(DEFAULT_FILTER_FIELD);
        Alias fromItemAlias = table.getAlias();
        String finalFieldName = Optional.ofNullable(fromItemAlias).map(a -> a.getName() + StringPool.DOT + fieldName).orElse(fieldName);

        if (permissionEntList.size() > 1) {
            // 把集合转变为 JSQLParser需要的元素列表
            InExpression inExpression = new InExpression(new Column(finalFieldName), PluginUtils.getItemList(permissionEntList));

            // 组装sql
            return where == null ? inExpression : new AndExpression(where, inExpression);
        }
        // 设置where
        EqualsTo equalsTo = new EqualsTo();
        equalsTo.setLeftExpression(new Column(finalFieldName));
        equalsTo.setRightExpression(new LongValue(permissionEntList.stream().findFirst().orElse(0L)));
        return where == null ? equalsTo : new AndExpression(where, equalsTo);
    }

    @Override
    public void updateParameter(Update updateStmt, MappedStatement mappedStatement, BoundSql boundSql) {
        TableInfo tableInfo = TableInfoHelper.getTableInfo(updateStmt.getTable().getName());
        parameterHandler(tableInfo.getFieldList(), boundSql);
    }

    @Override
    public void insertParameter(Insert insertStmt, BoundSql boundSql) {
        TableInfo tableInfo = TableInfoHelper.getTableInfo(insertStmt.getTable().getName());
        parameterHandler(tableInfo.getFieldList(), boundSql);
    }

    private void parameterHandler(List<TableFieldInfo> fieldList, BoundSql boundSql) {
        // 过滤数据
        SysUser sysUser = SecurityUtils.getUser();
        // 如果当前用户是超级管理员，不处理
        if (sysUser.getId() == 1L) {
            return;
        }
        // 获取当前用户所具备的ent_id
        Set<Long> permissionEntList = sysUser.getEntIdList();

        // 获取当前表中需要权限过滤的字段名称
        String fieldName = fieldList.stream()
                .filter(a -> a.getField().getAnnotation(ScopeField.class) != null)
                .map(a -> a.getField().getAnnotation(ScopeField.class).value())
                .findFirst()
                .orElse(DEFAULT_FILTER_FIELD);

        MetaObject metaObject = SystemMetaObject.forObject(boundSql.getParameterObject());

        for (ParameterMapping parameterMapping : boundSql.getParameterMappings()) {
            String propertyName = parameterMapping.getProperty();
            if (propertyName.startsWith("ew.paramNameValuePairs")) {
                continue;
            }
            String[] arr = propertyName.split("\\.");
            String propertyNameTrim = arr[arr.length - 1].replace("_", "").toUpperCase();
            if (fieldName.replaceAll("[._\\-$]", "").toUpperCase().equals(propertyNameTrim)) {
                if (!Optional.ofNullable(metaObject.getValue(propertyName)).isPresent()) {
                    return;
                }
                long currentEntId = Long.parseLong(metaObject.getValue(propertyName).toString());
                // 判断是否在权限范围内
                if (permissionEntList.contains(currentEntId)) {
                    metaObject.setValue(propertyName, currentEntId);
                } else {
                    // 可以直接抛出异常 or 使用当前用户的ent_id 替换插入值 or 直接忽略当前插入sql但不抛出异常
                    throw new UserOverreachException();
                }
            }

        }
    }
}

关联查询

更新

删除

代码地址

引用站外地址

权限过滤使用demo

github

上文代码在目录cn.allbs.allbsjwt.config.datascope下，可以查看git提交记录，后面使用封装好的allbs-mybatis包就被我删了。

引用站外地址

权限过滤封装源码

github