如何实现多租户数据隔离
在中台服务或者saas服务中,当多租户入驻时,如何保证不同租户的数据隔离性呢?通常的解决方法有三种,分别如下:
- 一个租户一个独立数据库,这种方案的用户数据隔离级别最高,安全性最好,但成本也高。
- 所有租户共享数据库,但一个租户一个数据库表。这种方案为安全性要求较高的租户提供了一定程度的逻辑数据隔离,并不是完全隔离,每个数据库可以支持更多的租户数量。
- 所有租户共享数据库,共享同一个数据库表,不同的租户数据通过租户的标识区分。这种方案共享程度最高、隔离级别最低。
通常为了降低成本,一般会选择第三种方案。这时,应该如何快速的实现多租户的数据隔离呢?在每一个查询语句中都添加上不同租户的标识语句么?基于mybatis提供的plugin插件,可以实现多租户过滤语句的横切逻辑,类似于AOP,让我们的业务代码从数据隔离的逻辑中抽离出来,专注于业务开发。
基于MyBatis插件plugin的实现
在MyBatis 中,通过其plugin插件机制,可以实现类似于AOP的横切逻辑编程,允许你在映射语句执行过程中的某一点进行拦截调用。定义了Interceptor 接口,实现指定方法的拦截,官网示例代码如下:
// ExamplePlugin.java
@Intercepts({@Signature(type= Executor.class,method = "update",args = {MappedStatement.class,Object.class})})
public class ExamplePlugin implements Interceptor {private Properties properties = new Properties();public Object intercept(Invocation invocation) throws Throwable {// implement pre processing if need// 拦截执行方法之前的逻辑处理Object returnObject = invocation.proceed();// implement post processing if need// 拦截执行方法之后的逻辑处理return returnObject;}public void setProperties(Properties properties) {this.properties = properties;}
}实现多租户的拦截过程中,通过对query操作进行拦截,实现了多租户过滤的如下功能:
- 可以动态设置多租户查询的开关,支持单个或者多个查询值的查询。
- 可以自定义多租户过滤的数据库字段,自定义查询数据库的别名设置,在多个JOIN关联查询中设置过滤的查询条件。
- 可以自定义多租户过滤查询的查询条件,例如,单个查询值的相等条件过滤,多个查询值的IN条件过滤。
其大致的流程如下:

MultiTenancyQueryInterceptor多租户过滤器
在实现中,定义MultiTenancyQueryInterceptor实现Interceptor实现如上流程的逻辑。其源码如下:
@Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class MultiTenancyQueryInterceptor implements Interceptor {private static final String WHERE_CONDITION = " where ";private static final String AND_CONDITION = " and ";/*** 条件生成Factory*/private final ConditionFactory conditionFactory;/*** 查询条件生成工厂缓存*/private volatile Map<Class<? extends MultiTenancyQueryValueFactory>, MultiTenancyQueryValueFactory> multiTenancyQueryValueFactoryMap;/*** 多租户属性*/@Getter@Setterprivate MultiTenancyProperties multiTenancyProperties;public MultiTenancyQueryInterceptor() {this.conditionFactory = new DefaultConditionFactory();this.multiTenancyProperties = new MultiTenancyProperties();this.multiTenancyQueryValueFactoryMap = new HashMap<>();}@Overridepublic Object intercept(Invocation invocation) throws Throwable {Object[] args = invocation.getArgs();MappedStatement mappedStatement = (MappedStatement) args[0];// 获取多租户查询注解MultiTenancy multiTenancy = this.getMultiTenancyFromMappedStatementId(mappedStatement.getId());if (!this.isMatchMultiTenancy(multiTenancy)) {log.info("{} is not match multi tenancy query!", mappedStatement.getId());return invocation.proceed();}// 验证多租户查询数据库字段if (StringUtils.isBlank(this.multiTenancyProperties.getMultiTenancyQueryColumn())) {log.error("property {} is invalid!", JSON.toJSONString(this.multiTenancyProperties));return invocation.proceed();}Object parameter = args[1];BoundSql boundSql = mappedStatement.getBoundSql(parameter);String originSql = boundSql.getSql();// 验证数据库表查询别名if (!this.matchPreTableName(originSql, multiTenancy.preTableName())) {log.info("pre table name {} is not matched sql {}!", multiTenancy.preTableName(), originSql);return invocation.proceed();}// 获取多租户查询条件Object queryObject;if (Objects.isNull(queryObject = this.getQueryObjectFromMultiTenancyQueryValueFactory(multiTenancy.multiTenancyQueryValueFactory()))) {log.error("parameter {} is invalid!", JSON.toJSONString(parameter));return invocation.proceed();}// 默认使用In 条件ConditionFactory.ConditionTypeEnum conditionTypeEnum = ConditionFactory.ConditionTypeEnum.IN;String conditionType;if (StringUtils.isNotBlank(conditionType = this.multiTenancyProperties.getConditionType())) {try {conditionTypeEnum = ConditionFactory.ConditionTypeEnum.valueOf(conditionType.toUpperCase());} catch (Exception e) {log.warn("invalid condition type {}!", conditionType);}}MultiTenancyQuery multiTenancyQuery = MultiTenancyQuery.builder().multiTenancyQueryColumn(this.multiTenancyProperties.getMultiTenancyQueryColumn()).multiTenancyQueryValue(queryObject).conditionType(conditionTypeEnum).preTableName(multiTenancy.preTableName()).build();String multiTenancyQueryCondition = this.conditionFactory.buildCondition(multiTenancyQuery);String newSql = this.appendWhereCondition(originSql, multiTenancyQueryCondition);// 使用反射替换BoundSql的sql语句Reflections.setFieldValue(boundSql, "sql", newSql);// 把新的查询放到statement里MappedStatement newMs = copyFromMappedStatement(mappedStatement, parameterObject -> boundSql);args[0] = newMs;return invocation.proceed();}private Object getQueryObjectFromMultiTenancyQueryValueFactory(Class<? extends MultiTenancyQueryValueFactory> multiTenancyQueryValueFactoryClass) {if (Objects.isNull(multiTenancyQueryValueFactoryClass)) {return null;}MultiTenancyQueryValueFactory multiTenancyQueryValueFactory = this.multiTenancyQueryValueFactoryMap.get(multiTenancyQueryValueFactoryClass);if (Objects.nonNull(multiTenancyQueryValueFactory)) {return multiTenancyQueryValueFactory.buildMultiTenancyQueryValue();}synchronized (this.multiTenancyQueryValueFactoryMap) {try {multiTenancyQueryValueFactory = multiTenancyQueryValueFactoryClass.newInstance();multiTenancyQueryValueFactoryMap.putIfAbsent(multiTenancyQueryValueFactoryClass, multiTenancyQueryValueFactory);return multiTenancyQueryValueFactory.buildMultiTenancyQueryValue();} catch (Exception e) {return null;}}}/*** 从MappedStatement 的id属性获取多租户查询注解 MultiTenancy** @param id MappedStatement 的id属性* @return MultiTenancy注解*/private MultiTenancy getMultiTenancyFromMappedStatementId(String id) {int lastSplitPointIndex = id.lastIndexOf(".");String mapperClassName = id.substring(0, lastSplitPointIndex);String methodName = id.substring(lastSplitPointIndex + 1, id.length());Class mapperClass;try {mapperClass = Class.forName(mapperClassName);Method[] methods = mapperClass.getMethods();for (Method method : methods) {if (method.getName().equals(methodName)) {return method.getAnnotation(MultiTenancy.class);}}} catch (ClassNotFoundException e) {e.printStackTrace();}return null;}private String appendWhereCondition(String originSql, String condition) {if (StringUtils.isBlank(originSql) || StringUtils.isBlank(condition)) {return originSql;}String[] sqlSplit = originSql.toLowerCase().split(WHERE_CONDITION.trim());// 没有查询条件if (this.noWhereCondition(sqlSplit)) {return originSql + WHERE_CONDITION + condition;}// 包含查询条件,添加到第一个查询条件的位置else {String sqlBeforeWhere = sqlSplit[0];String sqlAfterWhere = sqlSplit[1];return sqlBeforeWhere + WHERE_CONDITION + condition + AND_CONDITION + sqlAfterWhere;}}private boolean noWhereCondition(String[] sqlSplit) {return ArrayUtils.isNotEmpty(sqlSplit) && 1 == sqlSplit.length;}private boolean matchPreTableName(String sql, String preTableName) {if (StringUtils.isBlank(preTableName)) {return true;} else {return StringUtils.containsIgnoreCase(sql, preTableName);}}private boolean isMatchMultiTenancy(MultiTenancy multiTenancy) {return Objects.nonNull(multiTenancy)&& multiTenancy.isFiltered();}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {Object multiTenancyQueryColumn;if (Objects.nonNull(multiTenancyQueryColumn = properties.get(MULTI_TENANCY_QUERY_COLUMN_PROPERTY))) {multiTenancyProperties.setMultiTenancyQueryColumn(multiTenancyQueryColumn.toString());}Object conditionType;if (Objects.nonNull(conditionType = properties.get(CONDITION_TYPE_PROPERTY))) {multiTenancyProperties.setConditionType(conditionType.toString());}}private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());builder.resource(ms.getResource());builder.fetchSize(ms.getFetchSize());builder.statementType(ms.getStatementType());builder.keyGenerator(ms.getKeyGenerator());if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {builder.keyProperty(ms.getKeyProperties()[0]);}builder.timeout(ms.getTimeout());builder.parameterMap(ms.getParameterMap());builder.resultMaps(ms.getResultMaps());builder.resultSetType(ms.getResultSetType());builder.cache(ms.getCache());builder.flushCacheRequired(ms.isFlushCacheRequired());builder.useCache(ms.isUseCache());return builder.build();}
}
在使用时,使用MultiTenancy注解标识需要进行多租户过滤,可以配置数据库查询的别名,查询条件的生成逻辑,以及多租户过滤开关。配置MultiTenancyProperties属性可以设置需要被拦截的数据库字段,以及拦截字段的查询条件。
以单个商品查询为例,根据商品id查询商品,多租户查询过滤的字段为商品code,定义数据库查询的别名为g,代码示例如下:
/*** mybatis插件配置**/    
//MybatisPlusConfig.class    
@Bean
public MultiTenancyQueryInterceptor multiTenancyQueryInterceptor() {MultiTenancyQueryInterceptor multiTenancyQueryInterceptor = new MultiTenancyQueryInterceptor();MultiTenancyProperties multiTenancyProperties = new MultiTenancyProperties();// 数据库查询字段multiTenancyProperties.setMultiTenancyQueryColumn("code");multiTenancyQueryInterceptor.setMultiTenancyProperties(multiTenancyProperties);return multiTenancyQueryInterceptor;}public interface GoodsMapper extends BaseMapper<Goods> {/*** 使用MultiTenancy注解,标识需要进行多租户查询过滤* preTableName 设置数据库查询别名,multiTenancyQueryValueFactory设置多租户查询条件*/@MultiTenancy(preTableName = "g", multiTenancyQueryValueFactory = UserMultiTenancyQueryValueFactory.class)GoodsDetailBo getGoodsById(Long id);
}/*** MultiTenancyQuery 查询参数设置**/  
@Test
public void testMultiTenancyQuery() {Long goodsId = 1253217755332722792l;// 只传入商品id查询条件GoodsDetailBo goodsDetailBo = goodsMapper.getGoodsById(goodsId);Assert.assertNotNull(goodsDetailBo);}// GoodsMapper.class映射的mybatis sql 语句
<select id="getGoodsById" resultMap="goodsDetailResultMap">SELECTg.id AS 'id',g.name AS 'name',g.code AS 'code',g.size AS 'size',g.weight AS 'weight'FROM boutique_goods g// 只有根据id查询条件WHERE g.id=#{id}</select>测试运行结果,能够查询到id为1253217755332722792l的商品,并且查询的sql为包含id和code的组合查询条件,结果如图:

解析多租户过滤注解MultiTenancy与配置参数MultiTenancyProperties
定义多租户过滤注解MultiTenancy,可以设置执行过滤操作的开关,数据库查询别名,查询条件生成规则,以及多租户查询开关。在mybatis自定义的plugin拦截器,拦截query方法,通过MappedStatement 的id获取到定义在mapper层的MultiTenancy注解。其源码如下:
/*** 多租户查询注解**/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface MultiTenancy {/*** 是否可以过滤*/boolean isFiltered() default true;/*** 数据库表前缀名*/String preTableName();/*** 过滤条件查询值Factory*/Class<? extends MultiTenancyQueryValueFactory> multiTenancyQueryValueFactory();
}/**
* 从MappedStatement 的id属性获取多租户查询注解 MultiTenancy
*
* @param id MappedStatement 的id属性
* @return MultiTenancy注解
*/
// MultiTenancyQueryInterceptor.class
private MultiTenancy getMultiTenancyFromMappedStatementId(String id) {int lastSplitPointIndex = id.lastIndexOf(".");String mapperClassName = id.substring(0, lastSplitPointIndex);String methodName = id.substring(lastSplitPointIndex + 1, id.length());Class mapperClass;try {mapperClass = Class.forName(mapperClassName);Method[] methods = mapperClass.getMethods();for (Method method : methods) {if (method.getName().equals(methodName)) {return method.getAnnotation(MultiTenancy.class);}}} catch (ClassNotFoundException e) {e.printStackTrace();}return null;}/*** 根据用户登录信息设置查询条件**/
public class UserMultiTenancyQueryValueFactory implements MultiTenancyQueryValueFactory {@Overridepublic Object buildMultiTenancyQueryValue() {// TODO 根据业务系统设置多租户统一查询条件,一般业务逻辑从用户上下文获取过滤条件return "test";}
}定义MultiTenancyProperties,设置多租户中过滤的数据库字段,已经查询条件的设置,现在实现了相等条件和IN条件的查询条件实现方式,其源码如下:
public class MultiTenancyProperties implements Serializable {private static final long serialVersionUID = -1982635513027523884L;public static final String MULTI_TENANCY_QUERY_COLUMN_PROPERTY = "multiTenancyQueryColumn";public static final String CONDITION_TYPE_PROPERTY = "conditionType";/*** 租户的字段名称*/private String multiTenancyQueryColumn;/*** 租户字段查询条件* {@link ConditionFactory.ConditionTypeEnum}*/private String conditionType;public MultiTenancyProperties() {// 默认使用IN 条件,例如 id in(1,2,3)this.conditionType = ConditionFactory.ConditionTypeEnum.IN.name();}
}解析查询语句的生成规格ConditionFactory以及条件语句的追加逻辑
定义ConditionFactory接口实现查询sql查询语句的生成,其默认实现类DefaultConditionFactory实现了相等条件和IN条件的查询语句语法,其源码如下:
public class DefaultConditionFactory implements ConditionFactory {private static final String EQUAL_CONDITION = "=";private static final String IN_CONDITION = " in ";private final DBColumnValueFactory columnValueFactory;public DefaultConditionFactory() {this.columnValueFactory = new DefaultDBColumnValueFactory();}@Overridepublic String buildCondition(ConditionTypeEnum conditionType, String multiTenancyQueryColumn, MultiTenancyQuery multiTenancyQuery) {StringBuilder stringBuilder = new StringBuilder();String columnValue = this.columnValueFactory.buildColumnValue(multiTenancyQuery.getMultiTenancyQueryValue());// 根据条件类型设置查询条件switch (conditionType) {// IN条件case IN:stringBuilder.append(multiTenancyQueryColumn).append(IN_CONDITION).append("(").append(columnValue).append(")");break;// 相等条件case EQUAL:default:stringBuilder.append(multiTenancyQueryColumn).append(EQUAL_CONDITION).append(columnValue);break;}// 设置数据库表别名String preTableName;if (StringUtils.isNotBlank(preTableName = multiTenancyQuery.getPreTableName())) {stringBuilder.insert(0, ".").insert(0, preTableName);}return stringBuilder.toString();}
}在原生的sql查询语句新增自定义的查询条件方法,是根据是否存在where查询条件字段进行动态的拼接。如果没有查询条件则直接添加,反之,则添加到第一个查询条件的位置。其源码如下:
// MultiTenancyQueryInterceptor.class 
private String appendWhereCondition(String originSql, String condition) {if (StringUtils.isBlank(originSql) || StringUtils.isBlank(condition)) {return originSql;}String[] sqlSplit = originSql.toLowerCase().split(WHERE_CONDITION.trim());// 没有查询条件if (this.noWhereCondition(sqlSplit)) {return originSql + WHERE_CONDITION + condition;}// 包含查询条件,添加到第一个查询条件的位置else {String sqlBeforeWhere = sqlSplit[0];String sqlAfterWhere = sqlSplit[1];return sqlBeforeWhere + WHERE_CONDITION + condition + AND_CONDITION + sqlAfterWhere;}}多租户拦截器全部源码可以从多租户数据拦截器插件下载。
















