基于Mybatis-Plus的多租户&数据权限隔离（全网最优）

作者：AllinToyou | 2024-04-11 21:15:23

踩

数据权限隔离

标题党了；哈哈！！！

1.多租户插件

我们来看mybatis-Plus 提供的多租户插件还是很方便的：多租户插件 | MyBatis-Plus

官方的例子其实已经很清晰，来看下我们实战中的例子；

首先是属性配置类：


import java.util.List;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
 
/**
 * 白名单配置
 */
@Data
@Configuration
@ConfigurationProperties(prefix = "tenant")
public class TenantProperties {
 
    /**
     * 是否开启租户模式
     */
    private Boolean enable;
 
    /**
     * 多租户字段名称
     */
    private String column;
 
    /**
     * 需要排除的多租户的表
     */
    private List<String> exclusionTable;
 
}

接下来是bean配置类


package com.caicongyang.hc.conf;
 
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import org.apache.ibatis.plugin.Interceptor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
import java.util.Objects;
 
@Configuration
public class MybatisPlusConfiguration {
 
 
    @Autowired
    TenantProperties tenantProperties;
 
 
   
 
    @Bean
    public Interceptor paginationInnerInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        if (tenantProperties.getEnable()) {
            interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
                @Override
                public Expression getTenantId() {
 
                    String merchantCode = SystemContext.getUserInfo().getMerchantCode();
                    if (Objects.isNull(merchantCode)) {
                        return new StringValue("-1");
                    } else {
                        return new StringValue(SystemContext.getUserInfo().getMerchantCode());
                    }
                }
 
                // 这是 default 方法,默认返回 false 表示所有表都需要拼多租户条件
                @Override
                public boolean ignoreTable(String tableName) {
                    return tenantProperties.getExclusionTable().stream().anyMatch(
                            (t) -> t.equalsIgnoreCase(tableName));
                }
 
 
                /**
                 * 获取多租户的字段名
                 * @return String
                 */
                @Override
                public String getTenantIdColumn() {
                    return tenantProperties.getColumn();
                }
            }));
        }
 
      
 
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
 
 
}

是不是很简单；基于管理平台这类的需求，如何忽略多租户呢；我的建议是新建不同的mapper对象


import com.baomidou.mybatisplus.annotation.InterceptorIgnore;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.caicongyang.hc.entity.User;
import org.apache.ibatis.annotations.Param;
 
/**
 * <p>
 * 用户表 Mapper 接口
 * </p>
 *
 * @author caicongyang
 * @since 2024-01-18
 */
@InterceptorIgnore(tenantLine = "true")
public interface UserTenantIngoreMapper extends BaseMapper<User> {
 
    User getUserByMobile(@Param("mobile") String mobile);
 
}

虽然 @InterceptorIgnore(tenantLine = "true") 注解也支持注在方法上，但是基于不同的mapper 来做是否租户管理，个人建议是比较清晰的；

以上基于官方提供的多租户管理插件还是很方便的；那接下来看看官方提供的数据权限插件；

2 数据权限插件

通看官方提供的数据权限插件数据权限插件 | MyBatis-Plus 感觉使用起来没有多租户插件来的清晰和方便；找了很多网上的例子，很多推荐基于注解等；在现实的项目中，往往几千个方法，一个个价注解实在不方便；个人基于多租户插件改造的数据权限可以参考，相对来还是蛮方便的，请君观看；

同样的熟悉配置类：


import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
 
import java.util.List;
 
/**
 * 白名单配置
 */
@Data
@Configuration
@ConfigurationProperties(prefix = "data.permission")
public class DataPermissionProperties {
 
    /**
     * 是否开启数据权限模式
     */
    private Boolean enable = false;
 
 
    /**
     * 需要排除的多租户的表
     */
    private List<String> exclusionTable;
 
}

拦截器类：


import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.handler.MultiDataPermissionHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.BaseMultiTableInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.ToString;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.delete.Delete;
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.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
 
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
public class TomDataPermissionInnerInterceptor extends BaseMultiTableInnerInterceptor implements InnerInterceptor {
 
    private TomDataPermissionHandler 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
    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) {
            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));
        }
    }
 
    /**
     * 设置 where 条件
     *
     * @param plainSelect  查询对象
     * @param whereSegment 查询条件片段
     */
    protected void setWhere(PlainSelect plainSelect, String whereSegment) {
        if (dataPermissionHandler instanceof MultiDataPermissionHandler) {
            processPlainSelect(plainSelect, whereSegment);
            return;
        }
        // 兼容旧版的数据权限处理
        final Expression sqlSegment = dataPermissionHandler.getSqlSegment(plainSelect.getWhere(), whereSegment);
        if (null != sqlSegment) {
            plainSelect.setWhere(sqlSegment);
        }
    }
 
    /**
     * update 语句处理
     */
    @Override
    protected void processUpdate(Update update, int index, String sql, Object obj) {
        if (dataPermissionHandler.ignoreTable(update.getTable().getName())) {
            return;
        }
        final Expression sqlSegment = getUpdateOrDeleteExpression(update.getTable(), update.getWhere(), (String) obj);
        if (null != sqlSegment) {
            update.setWhere(sqlSegment);
        }
    }
 
    /**
     * delete 语句处理
     */
    @Override
    protected void processDelete(Delete delete, int index, String sql, Object obj) {
        if (dataPermissionHandler.ignoreTable(delete.getTable().getName())) {
            return;
        }
        final Expression sqlSegment = getUpdateOrDeleteExpression(delete.getTable(), delete.getWhere(), (String) obj);
        if (null != sqlSegment) {
            delete.setWhere(sqlSegment);
        }
    }
 
    protected Expression getUpdateOrDeleteExpression(final Table table, final Expression where, final String whereSegment) {
        if (dataPermissionHandler instanceof MultiDataPermissionHandler) {
            return andExpression(table, where, whereSegment);
        } else {
            // 兼容旧版的数据权限处理
            return dataPermissionHandler.getSqlSegment(where, whereSegment);
        }
    }
 
    @Override
    public Expression buildTableExpression(final Table table, final Expression where, final String whereSegment) {
        if (dataPermissionHandler.ignoreTable(table.getName())) {
            return null;
        }
        // 只有新版数据权限处理器才会执行到这里
        final MultiDataPermissionHandler handler = (MultiDataPermissionHandler) dataPermissionHandler;
        return handler.getSqlSegment(table, where, whereSegment);
    }
}

handler 接口


import net.sf.jsqlparser.expression.Expression;
 
public interface TomDataPermissionHandler {
 
    /**
     * 获取数据权限 SQL 片段
     *
     * @param where             待执行 SQL Where 条件表达式
     * @param mappedStatementId Mybatis MappedStatement Id 根据该参数可以判断具体执行方法
     * @return JSqlParser 条件表达式，返回的条件表达式会覆盖原有的条件表达式
     */
    Expression getSqlSegment(Expression where, String mappedStatementId);
 
    /**
     * 根据表名判断是否忽略拼接多租户条件
     * <p>
     * 默认都要进行解析并拼接多租户条件
     *
     * @param tableName 表名
     * @return 是否忽略, true:表示忽略，false:需要解析并拼接多租户条件
     */
    default boolean ignoreTable(String tableName) {
        return false;
    }
 
 
}

真正的handler 实现类


package com.caicongyang.hc.conf;
 
import com.caicongyang.cache.constant.CommonCacheConst;
import com.caicongyang.cache.util.RedisUtil;
import com.caicongyang.context.context.SystemContext;
import com.caicongyang.context.context.UserInfo;
import com.caicongyang.orm.mybatis.TomDataPermissionHandler;
import com.caicongyang.orm.mybatis.dto.UserDataAuthorityDTO;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
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 org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
 
import java.util.Objects;
import java.util.stream.Collectors;
 
@Slf4j
public class CommonDataPermissionHandler implements TomDataPermissionHandler {
 
 
    DataPermissionProperties dataPermissionProperties;
 
 
    public CommonDataPermissionHandler(DataPermissionProperties dataPermissionProperties) {
        this.dataPermissionProperties = dataPermissionProperties;
    }
 
    public CommonDataPermissionHandler() {
    }
 
 
    @Override
    public Expression getSqlSegment(Expression where, String mappedStatementId) {
 
 
        // 从上下文中取出数据权限
        UserInfo userInfo = SystemContext.getUserInfo();
        String token = SystemContext.getToken();
        UserDataAuthorityDTO dto = RedisUtil.get(String.format(CommonCacheConst.USER_DATA_ROLE_KEY, token), UserDataAuthorityDTO.class);
 
       
        log.debug("开始进行权限过滤:{} , where: {},mappedStatementId: {}", where, mappedStatementId);
        if (userInfo == null || StringUtils.isBlank(userInfo.getUserCode())) {
            return where;
        }
 
        //组数据权限
        if (Objects.nonNull(dto) && CollectionUtils.isNotEmpty(dto.getGroupCodes())) {
            ItemsList itemsList = new ExpressionList(dto.getGroupCodes().stream().map(StringValue::new).collect(Collectors.toList()));
            InExpression inExpression = new InExpression(new Column("group_code"), itemsList);
            return new AndExpression(where, inExpression);
        }
        // 供应商数据权限
        if (Objects.nonNull(dto) && CollectionUtils.isNotEmpty(dto.getSupplierCodes())) {
            ItemsList itemsList = new ExpressionList(dto.getSupplierCodes().stream().map(StringValue::new).collect(Collectors.toList()));
            InExpression inExpression = new InExpression(new Column("supplier_code"), itemsList);
            return new AndExpression(where, inExpression);
        }
 
        return where;
    }
 
 
    public boolean ignoreTable(String tableName) {
        return dataPermissionProperties.getExclusionTable().stream().anyMatch(
                (t) -> t.equalsIgnoreCase(tableName));
    }
 
 
}

完整的mybatis 配置类如下：


package com.caicongyang.hc.conf;
 
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.caicongyang.context.context.SystemContext;
import com.caicongyang.orm.mybatis.BasePOMetaObjectHandler;
import com.caicongyang.orm.mybatis.TenantProperties;
import com.caicongyang.orm.mybatis.TomDataPermissionInnerInterceptor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import org.apache.ibatis.plugin.Interceptor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
import java.util.Objects;
 
@Configuration
public class MybatisPlusConfiguration {
 
 
    @Autowired
    TenantProperties tenantProperties;
 
 
    @Autowired
    DataPermissionProperties dataPermissionProperties;
 
 
 
     /**
     *  先多租户配置，再数据权限配置，再分页插件；顺序不能乱
     * @return
     */
    @Bean
    public Interceptor paginationInnerInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        if (tenantProperties.getEnable()) {
            interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
                @Override
                public Expression getTenantId() {
 
                    String merchantCode = SystemContext.getUserInfo().getMerchantCode();
                    if (Objects.isNull(merchantCode)) {
                        return new StringValue("-1");
                    } else {
                        return new StringValue(SystemContext.getUserInfo().getMerchantCode());
                    }
                }
 
                // 这是 default 方法,默认返回 false 表示所有表都需要拼多租户条件
                @Override
                public boolean ignoreTable(String tableName) {
                    return tenantProperties.getExclusionTable().stream().anyMatch(
                            (t) -> t.equalsIgnoreCase(tableName));
                }
 
 
                /**
                 * 获取多租户的字段名
                 * @return String
                 */
                @Override
                public String getTenantIdColumn() {
                    return tenantProperties.getColumn();
                }
            }));
        }
 
        if (dataPermissionProperties.getEnable()) {
            interceptor.addInnerInterceptor(new TomDataPermissionInnerInterceptor(new CommonDataPermissionHandler(dataPermissionProperties)));
        }
 
 
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
 
 
}

希望大家从我的例子对大家有所帮助；我也是翻看了所有的网上的记录，自己改写的一个demo

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/AllinToyou/article/detail/407486