mybatisplus使用SQL拦截器做数据权限过滤

  |   0 评论   |   71 浏览

刚开始网上查了一下发现直接继承AbstractSqlParserHandler实现Interceptor就可以实现 拿来吧你

拿来之后发现

image.png

看了下import的路径 顺着路径去jar里找了一下

image.png

image.png

并没有找到这个类 翻了翻mybatisplus github发现3.4.2的时候该类还在 而我用的刚好是3.4.3版本 新版本中弃用并删除了该类

那我想着移除了就肯定有更好用的方法嘛 就去参考了一下官方的多租户及分页的代码 果然好用!

剽窃了多租户(TenantLineInnerInterceptor.java)的解析表的代码和分页(PaginationInnerInterceptor.java)的思路之后

  1import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
  2import com.baomidou.mybatisplus.core.toolkit.ExceptionUtils;
  3import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
  4import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
  5import lombok.extern.slf4j.Slf4j;
  6import net.sf.jsqlparser.JSQLParserException;
  7import net.sf.jsqlparser.expression.Expression;
  8import net.sf.jsqlparser.expression.Parenthesis;
  9import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
 10import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
 11import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
 12import net.sf.jsqlparser.parser.CCJSqlParserUtil;
 13import net.sf.jsqlparser.schema.Table;
 14import net.sf.jsqlparser.statement.Statement;
 15import net.sf.jsqlparser.statement.select.*;
 16import org.apache.ibatis.executor.Executor;
 17import org.apache.ibatis.mapping.BoundSql;
 18import org.apache.ibatis.mapping.MappedStatement;
 19import org.apache.ibatis.session.ResultHandler;
 20import org.apache.ibatis.session.RowBounds;
 21import org.apache.shiro.SecurityUtils;
 22import org.jeecg.common.system.vo.LoginUser;
 23import org.jeecg.modules.online.config.exception.BusinessException;
 24 25import java.sql.SQLException;
 26import java.util.ArrayList;
 27import java.util.List;
 28 29/**
 30 * @author wenbo
 31 * @since 2021/9/22 16:47
 32 */
 33@Slf4j
 34public class DataScopeInterceptor implements InnerInterceptor {
 35    private static final List<String> tenantTable = new ArrayList<>();
 36 37    static {
 38        //添加需要进行数据权限过滤的表
 39        tenantTable.add("demo");
 40    }
 41 42    //拿到当前拦截的表名
 43    private String tableName = null;
 44 45    @Override
 46    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
 47        String buildSql = boundSql.getSql();
 48        try {
 49            Statement statement = CCJSqlParserUtil.parse(buildSql);
 50            Select select = (Select) statement;
 51            processSelectBody(select.getSelectBody());
 52        } catch (JSQLParserException e) {
 53            throw ExceptionUtils.mpe("Failed to process, Error SQL: %s", e.getCause(), buildSql);
 54        } catch (BusinessException be) {
 55            log.warn(be.getMessage());
 56            return;
 57        }
 58        //获取登录用户信息
 59        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
 60        if (sysUser != null && sysUser.getOrgCode() != null && tableName != null) {
 61            //我这里是把原有的sql全部包起来加条件 但是有个缺陷就是原本的sql必须要查出来条件字段
 62            buildSql = "SELECT t1.* FROM (" + buildSql + ") t1 WHERE t1.sys_org_code LIKE '" + sysUser.getOrgCode() + "%'";
 63        }
 64        PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
 65        mpBoundSql.sql(buildSql);
 66    }
 67 68    private void processPlainSelect(PlainSelect plainSelect) throws BusinessException {
 69        FromItem fromItem = plainSelect.getFromItem();
 70        if (fromItem instanceof Table) {
 71            Table fromTable = (Table) fromItem;
 72            if (!tenantTable.contains(fromTable.getName())) {
 73                throw new BusinessException(fromTable.getName() + "表已被忽略 不进行数据权限过滤");
 74            }
 75            tableName = fromTable.getName();
 76        } else {
 77            processFromItem(fromItem);
 78        }
 79    }
 80 81    private void processSelectBody(SelectBody selectBody) throws BusinessException {
 82        if (selectBody == null) return;
 83        if (selectBody instanceof PlainSelect) {
 84            processPlainSelect((PlainSelect) selectBody);
 85        } else if (selectBody instanceof WithItem) {
 86            WithItem withItem = (WithItem) selectBody;
 87            processSelectBody(withItem.getSelectBody());
 88        } else {
 89            SetOperationList operationList = (SetOperationList) selectBody;
 90            List<SelectBody> selectBodys = operationList.getSelects();
 91            if (CollectionUtils.isNotEmpty(selectBodys)) {
 92                for (SelectBody body : selectBodys) {
 93                    processSelectBody(body);
 94                }
 95            }
 96        }
 97    }
 98 99    private void processFromItem(FromItem fromItem) throws BusinessException {
100        if (fromItem instanceof SubJoin) {
101            SubJoin subJoin = (SubJoin) fromItem;
102            if (subJoin.getJoinList() != null) {
103                for (Join join : subJoin.getJoinList()) processJoin(join);
104            }
105            if (subJoin.getLeft() != null) processFromItem(subJoin.getLeft());
106        } else if (fromItem instanceof SubSelect) {
107            SubSelect subSelect = (SubSelect) fromItem;
108            if (subSelect.getSelectBody() != null) processSelectBody(subSelect.getSelectBody());
109        } else if (fromItem instanceof ValuesList) {
110            log.debug("Perform a subquery, if you do not give us feedback");
111        } else if (fromItem instanceof LateralSubSelect) {
112            LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
113            if (lateralSubSelect.getSubSelect() != null) {
114                SubSelect subSelect = lateralSubSelect.getSubSelect();
115                if (subSelect.getSelectBody() != null) {
116                    processSelectBody(subSelect.getSelectBody());
117                }
118            }
119        }
120    }
121122    private void processJoin(Join join) throws BusinessException {
123        if (join.getRightItem() instanceof Table) {
124            Table fromTable = (Table) join.getRightItem();
125            if (!tenantTable.contains(fromTable.getName())) {
126                throw new BusinessException(fromTable.getName() + "表已被忽略 不进行数据权限过滤");
127            }
128            tableName = fromTable.getName();
129        }
130    }
131}

其实一大堆代码都是解析表名的 重点就三句

1//拿到sql
2String buildSql = boundSql.getSql();
3//这里进行sql加工
4buildSql = ...
5//设置新sql
6PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
7mpBoundSql.sql(buildSql);

以上就是最新版mybatisplus的sql拦截器实现方式


作者:wenbo

评论

发表评论


取消