多租户数据隔离解决方案

程序员文章站 2022-07-06 09:36:46

...

一、背景
saas环境下,会采用不用的数据存储方案，主要分为以下三种。
1.完全的数据隔离
即一个租户，一套数据库系统。优点：完全隔离，数据无任何交叉，缺点：可想而知，成本高。

2.同一个数据库系统，不同schema（oracle中的表空间概念）
即在同一个数据库系统下，建立不同的schema，oracle下为不同表空间，mysql即不同数据库。个人在曾经的项目多租户短信平台里面，见识过这种解决方案。优点：数据隔离程度较高，缺点：维护成本较高，涉及到跨租户的数据相关需求时，难以实现。

3.同一个数据库系统，同一个schema
即多租户数据在同一个数据库下，使用数据隔离字段进行标识，如corp_code。优点：数据集中，方便统计查询，缺点：无隔离，数据crud存在困难，例如查询时，都需要将corp_code值作为参数传递到数据库查询中。

二、如何解决在同一个schema多租户情况下，数据隔离问题
1.本人项目持久层采用mybatis框架，也是目前最流行的持久层框架之一，本方案只在mybatis框架基础上进行实现，其他框架，可以参考思想。废话不多说（开始上代码）。
多租户数据隔离解决方案
此工程作为单独的工程，作为jar包被其他工程所引用，创建META-INF/spring.factories
将工程纳入到spring开发环境中，与业务工程实现解耦。

# Auto Configure
org.springframework.boot.autoconfigure.EnableAutoConfiguration=com.example.core.data.isolation.config.DataIsolationConfiguration

二、自定义mybatis拦截器
自定义mybatis拦截器，将获取到的sl进行修改，添加对应的隔离字段和隔离值，如
where 1=1 and corp_code = ‘1222’。实现如下：

1.创建配置对象，从对应的工程中读取配置信息，主要配置信息如下：
数据库方言，数据库需要进行隔离的表集合，隔离字段。

@Data
@Component
@ConfigurationProperties(prefix = "data-isolation")
@RefreshScope
public class DataIsolationProperties {
    /**
	 * 数据隔离方言
	 */
	private String dialect = JdbcConstants.MYSQL;

    /**
	 * 数据隔离表集合
	 */
	private List<String> tableNames = Arrays.asList("uc_staff", "uc_dept", "uc_sys_role");

    /**
	 * 数据隔离字段
	 */
	private String tenantIdField = "org_id";

}

2.自定义拦截器

package com.example.core.data.isolation.interceptor;

import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.example.core.data.isolation.model.AuthUser;
import com.example.core.data.isolation.utils.ITableFieldConditionDecision;
import com.example.core.data.isolation.utils.SqlConditionHelper;
import com.example.framework.common.utils.LogUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.slf4j.Logger;

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

/**
 * 多租户数据隔离
 *
 * @author 
 */
@Intercepts({
	@Signature(type = StatementHandler.class,
		method = "prepare",
		args = {Connection.class, Integer.class})})
public class DataIsolationInterceptor implements Interceptor {

	private Logger log = LogUtil.getLogger(DataIsolationInterceptor.class);

	/**
	 * 数据隔离方言
	 */
	private String dialect;

	/**
	 * 数据隔离表集合
	 */
	private List<String> tableNames;

	/**
	 * 数据隔离字段
	 */
	private String tenantIdField;

	/**
	 * 数据隔离辅助类
	 */
	private SqlConditionHelper conditionHelper;


	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		AuthUser authUser = AuthUser.getUser();
		if (authUser == null) {
			return invocation.proceed();
		}
		StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
		BoundSql boundSql = statementHandler.getBoundSql();

		String originalSql = boundSql.getSql();


		// 数据隔离
		String originalSql2 = addTenantCondition(originalSql, authUser.getCurrentOrgId());

		log.info("数据隔离之前sql :{}", originalSql);
		log.info("数据隔离之后sql :{}", originalSql2);


		ReflectUtil.setFieldValue(boundSql, "sql", originalSql2);
		return invocation.proceed();
	}

	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	@Override
	public void setProperties(Properties properties) {

		dialect = properties.getProperty("dialect");
		tableNames = Arrays.asList(properties.getProperty("tableNames").split(StrUtil.COMMA));
		tenantIdField = properties.getProperty("tenantIdField");

		/**
		 * 多租户条件字段决策器
		 */
		ITableFieldConditionDecision conditionDecision = new ITableFieldConditionDecision() {
			@Override
			public boolean isAllowNullValue() {
				return false;
			}

			@Override
			public boolean adjudge(String tableName, String fieldName) {
				if (tableNames != null && tableNames.contains(tableName)) return true;
				return false;
			}
		};
		conditionHelper = new SqlConditionHelper(conditionDecision);
	}

	/**
	 * 给sql语句where添加租户id过滤条件
	 *
	 * @param sql      要添加过滤条件的sql语句
	 * @param tenantId 当前的租户id
	 * @return 添加条件后的sql语句
	 */
	private String addTenantCondition(String sql, String tenantId) {
		if (StringUtils.isBlank(sql) || StringUtils.isBlank(tenantId))
			return sql;
		List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dialect);

		if (statementList == null || statementList.size() == 0)
			return sql;

		SQLStatement sqlStatement = statementList.get(0);

		conditionHelper.addStatementCondition(sqlStatement, tenantIdField, tenantId);

		return SQLUtils.toSQLString(statementList, dialect);
	}

}

3.自定义条件判断接口

/**
 * 数据隔离决策接口
 * @author 
 * @create 2020-06-02
 **/
public interface ITableFieldConditionDecision {

	/**
	 * 条件字段是否运行null值
	 * @return
	 */
	boolean isAllowNullValue();
	/**
	 * 判决某个表是否需要添加某个字段过滤
	 *
	 * @param tableName   表名称
	 * @param fieldName   字段名称
	 * @return
	 */
	boolean adjudge(String tableName, String fieldName);
}

4.sql处理器

/**
 * @Description sql语句where条件处理辅助类
 * @Author 
 * @Date 2020/6/2 8:32
 * @Version v1.0-SNAPSHOT
 **/
public class SqlConditionHelper {

	private ITableFieldConditionDecision conditionDecision;

	public SqlConditionHelper(ITableFieldConditionDecision conditionDecision) {
		this.conditionDecision = conditionDecision;
	}

	/**
	 * 为sql'语句添加指定where条件
	 *
	 * @param sqlStatement
	 * @param fieldName
	 * @param fieldValue
	 */
	public void addStatementCondition(SQLStatement sqlStatement, String fieldName, String fieldValue) {
		if (sqlStatement instanceof SQLSelectStatement) {
			SQLSelectQueryBlock queryObject = (SQLSelectQueryBlock) ((SQLSelectStatement) sqlStatement).getSelect().getQuery();
			addSelectStatementCondition(queryObject, queryObject.getFrom(), fieldName, fieldValue);
		} else if (sqlStatement instanceof SQLUpdateStatement) {
			SQLUpdateStatement updateStatement = (SQLUpdateStatement) sqlStatement;
			addUpdateStatementCondition(updateStatement, fieldName, fieldValue);
		} else if (sqlStatement instanceof SQLDeleteStatement) {
			SQLDeleteStatement deleteStatement = (SQLDeleteStatement) sqlStatement;
			addDeleteStatementCondition(deleteStatement, fieldName, fieldValue);
		} else if (sqlStatement instanceof SQLInsertStatement) {
			SQLInsertStatement insertStatement = (SQLInsertStatement) sqlStatement;
			addInsertStatementCondition(insertStatement, fieldName, fieldValue);
		}
	}

	/**
	 * 为insert语句添加where条件
	 *
	 * @param insertStatement
	 * @param fieldName
	 * @param fieldValue
	 */
	private void addInsertStatementCondition(SQLInsertStatement insertStatement, String fieldName, String fieldValue) {
		if (insertStatement != null) {
			SQLInsertInto sqlInsertInto = insertStatement;
			SQLSelect sqlSelect = sqlInsertInto.getQuery();
			if (sqlSelect != null) {
				SQLSelectQueryBlock selectQueryBlock = (SQLSelectQueryBlock) sqlSelect.getQuery();
				addSelectStatementCondition(selectQueryBlock, selectQueryBlock.getFrom(), fieldName, fieldValue);
			}
		}
	}


	/**
	 * 为delete语句添加where条件
	 *
	 * @param deleteStatement
	 * @param fieldName
	 * @param fieldValue
	 */
	private void addDeleteStatementCondition(SQLDeleteStatement deleteStatement, String fieldName, String fieldValue) {
		SQLExpr where = deleteStatement.getWhere();
		//添加子查询中的where条件
		addSQLExprCondition(where, fieldName, fieldValue);

		SQLExpr newCondition = newEqualityCondition(deleteStatement.getTableName().getSimpleName(),
			deleteStatement.getTableSource().getAlias(), fieldName, fieldValue, where);
		deleteStatement.setWhere(newCondition);

	}

	/**
	 * where中添加指定筛选条件
	 *
	 * @param where      源where条件
	 * @param fieldName
	 * @param fieldValue
	 */
	private void addSQLExprCondition(SQLExpr where, String fieldName, String fieldValue) {
		if (where instanceof SQLInSubQueryExpr) {
			SQLInSubQueryExpr inWhere = (SQLInSubQueryExpr) where;
			SQLSelect subSelectObject = inWhere.getSubQuery();
			SQLSelectQueryBlock subQueryObject = (SQLSelectQueryBlock) subSelectObject.getQuery();
			addSelectStatementCondition(subQueryObject, subQueryObject.getFrom(), fieldName, fieldValue);
		} else if (where instanceof SQLBinaryOpExpr) {
			SQLBinaryOpExpr opExpr = (SQLBinaryOpExpr) where;
			SQLExpr left = opExpr.getLeft();
			SQLExpr right = opExpr.getRight();
			addSQLExprCondition(left, fieldName, fieldValue);
			addSQLExprCondition(right, fieldName, fieldValue);
		} else if (where instanceof SQLQueryExpr) {
			SQLSelectQueryBlock selectQueryBlock = (SQLSelectQueryBlock) (((SQLQueryExpr) where).getSubQuery()).getQuery();
			addSelectStatementCondition(selectQueryBlock, selectQueryBlock.getFrom(), fieldName, fieldValue);
		}
	}

	/**
	 * 为update语句添加where条件
	 *
	 * @param updateStatement
	 * @param fieldName
	 * @param fieldValue
	 */
	private void addUpdateStatementCondition(SQLUpdateStatement updateStatement, String fieldName, String fieldValue) {
		SQLExpr where = updateStatement.getWhere();
		//添加子查询中的where条件
		addSQLExprCondition(where, fieldName, fieldValue);
		SQLExpr newCondition = newEqualityCondition(updateStatement.getTableName().getSimpleName(),
			updateStatement.getTableSource().getAlias(), fieldName, fieldValue, where);
		updateStatement.setWhere(newCondition);
	}

	/**
	 * 给一个查询对象添加一个where条件
	 *
	 * @param queryObject
	 * @param fieldName
	 * @param fieldValue
	 */
	private void addSelectStatementCondition(SQLSelectQueryBlock queryObject, SQLTableSource from, String fieldName, String fieldValue) {
		if (StringUtils.isBlank(fieldName) || from == null || queryObject == null) return;

		SQLExpr originCondition = queryObject.getWhere();
		if (from instanceof SQLExprTableSource) {
			String tableName = ((SQLIdentifierExpr) ((SQLExprTableSource) from).getExpr()).getName();
			String alias = from.getAlias();
			SQLExpr newCondition = newEqualityCondition(tableName, alias, fieldName, fieldValue, originCondition);
			queryObject.setWhere(newCondition);
		} else if (from instanceof SQLJoinTableSource) {
			SQLJoinTableSource joinObject = (SQLJoinTableSource) from;
			SQLTableSource left = joinObject.getLeft();
			SQLTableSource right = joinObject.getRight();

			addSelectStatementCondition(queryObject, left, fieldName, fieldValue);
//			addSelectStatementCondition(queryObject, right, fieldName, fieldValue);
			addRightJoinSelectStatementCondition(joinObject, right, fieldName, fieldValue);
		} else if (from instanceof SQLSubqueryTableSource) {
			SQLSelect subSelectObject = ((SQLSubqueryTableSource) from).getSelect();
			SQLSelectQueryBlock subQueryObject = (SQLSelectQueryBlock) subSelectObject.getQuery();
			addSelectStatementCondition(subQueryObject, subQueryObject.getFrom(), fieldName, fieldValue);
		} else if (from instanceof SQLUnionQueryTableSource) {
			List<SQLSelectQuery> relations = ((SQLUnionQueryTableSource) from).getUnion().getRelations();
			relations.forEach(sqlSelectQuery -> {
				SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) sqlSelectQuery;
				addSelectStatementCondition(sqlSelectQueryBlock, sqlSelectQueryBlock.getFrom(), fieldName, fieldValue);

			});
		} else {
			throw new NotImplementedException("数据隔离未处理异常");
		}
	}

	/**
	 *
	 * @Description: left join 添加数据条件
	 * @Author: yaorun
	 * @Param:
	 * @Date: 2020/6/4 10:19
	 */
	private void addRightJoinSelectStatementCondition(SQLJoinTableSource joinObject, SQLTableSource right, String fieldName, String fieldValue) {
		SQLExpr condition = joinObject.getCondition();

		String tableName = ((SQLIdentifierExpr) ((SQLExprTableSource) right).getExpr()).getName();

		String alias = right.getAlias();

		SQLExpr newCondition = newEqualityCondition(tableName, alias, fieldName, fieldValue, condition);

		joinObject.setCondition(newCondition);
	}



	/**
	 * 根据原来的condition创建一个新的condition
	 *
	 * @param tableName       表名称
	 * @param tableAlias      表别名
	 * @param fieldName
	 * @param fieldValue
	 * @param originCondition
	 * @return
	 */
	private SQLExpr newEqualityCondition(String tableName, String tableAlias, String fieldName, String fieldValue, SQLExpr originCondition) {
		//如果不需要设置条件
		if (!conditionDecision.adjudge(tableName, fieldName)) return originCondition;
		//如果条件字段不允许为空
		if (fieldValue == null && !conditionDecision.isAllowNullValue()) return originCondition;

		String filedName = StringUtils.isBlank(tableAlias) ? fieldName : tableAlias + "." + fieldName;
		SQLExpr condition = new SQLBinaryOpExpr(new SQLIdentifierExpr(filedName), new SQLCharExpr(fieldValue), SQLBinaryOperator.Equality);
		return SQLUtils.buildCondition(SQLBinaryOperator.BooleanAnd, condition, false, originCondition);
	}




}

5.用户信息获取

@Data
public class AuthUser {
	private String userId;
	private String userName;
	private String currentOrgId;
	private String authId;
	private String oldToken;
	private String orgIds;
	private String staffIds;
	private String currentStaffId;
	private String deptId;
	private Boolean isSuperAdmin;
	private String menu;

	public static AuthUser getUser() {
		try {
			String userId = ContextHandler.getUserId();
			String userName = ContextHandler.getUsername();
			Map<String, Object> result = ContextHandler.getAll();
			AuthUser authUser = new AuthUser();
			authUser.setUserId(userId);
			authUser.setUserName(userName);
			authUser.setCurrentOrgId(Convert.toStr(result.get(ContextConstants.CONTEXT_CURRENT_ORG_ID)));
			authUser.setCurrentStaffId(Convert.toStr(result.get(ContextConstants.CONTEXT_CURRENT_STAFF_ID)));
			authUser.setAuthId(Convert.toStr(result.get(ContextConstants.CONTEXT_AUTH_ID)));
			authUser.setOldToken(Convert.toStr(result.get(ContextConstants.CONTEXT_KEY_OLD_TOKEN)));
			authUser.setOrgIds(Convert.toStr(result.get(ContextConstants.CONTEXT_ORG_IDS)));
			authUser.setStaffIds(Convert.toStr(result.get(ContextConstants.CONTEXT_STAFF_IDS)));
			authUser.setDeptId(Convert.toStr(result.get(ContextConstants.DEPT_ID)));
			authUser.setIsSuperAdmin(false);
			authUser.setMenu(Convert.toStr(result.get(ContextConstants.MENU)));
			return authUser;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

}

多租户数据隔离解决方案

荐 SaaS 系统架构，租户数据隔离模式与租户信息解析方案！

SaaS 系统架构，租户数据隔离模式与租户信息解析方案！

TPP稳定性之场景隔离和多租户

OpenStack 企业私有云的若干需求（3）：多租户和租户间隔离

多租户数据隔离解决方案

6表联合查询，有一个表数据有1万多，访问速度10多秒解决方案

6表联合查询，有一个表数据有1万多，访问速度10多秒解决方案

EEPlat PaaS中的多租户数据隔离模式

6表联合查询，有一个表数据有1万多，访问速度10多秒解决方案

6表联合查询，有一个表数据有1万多，访问速度10多秒解决方案