插件代码逻辑未处理,可直接使用
实现目标:
在编写sql时不需要编写数据隔离字段,通过mybatisplus插件自动添加,新增数据时自动填充数据权限隔离字段值,执行的sql如下
单表查询
编写sql: select * from A
执行的sql: select * from A where tenant_id=1 and dept_id in (123)
A表数据按租户分离 b表数据不权限隔离
编写sql: select A.* from A left join B on A.A1=B.B1
执行的sql: select A.* from A left join B on A.A1=B.B1 where A.tenant_id=1 and A.and dept_id in (123)
AB表数据按租户分离
编写sql: select A.* from A left join B on A.A1=B.B1
执行的sql: select A.* from A left join B on A.A1=B.B1 and B.tenant_id=1 and B.and dept_id in (123) where A.tenant_id=1 and A.and dept_id in (123)
此处是实现数据部门权限隔离,租户隔离类似,只需要将dept_id改成tenant_id,在查询sql拼接地方用and,代码实例中有,MyDataPermissionInterceptor 方法 builderExpression()注释部分代码就是拼接成and的逻辑
配置表:用于配置哪些表数据权限隔离
@Data
@RefreshScope
@Configuration
@ConfigurationProperties(prefix = "saas.tenant")
public class SaasTenantConfigProperties {
/**
* 维护租户列名称
*/
private String column = "tenant_id";
/**
* 多租户的数据表集合
*/
private List<String> tables = new ArrayList<>();
/**
* 部门数据表集合
*/
private List<String> deptTables = new ArrayList<>();
}
@Component
@Slf4j
@RequiredArgsConstructor
public class MyDataPermissionHandler {
private final SaasTenantConfigProperties properties;
private final FeignDeptService feignDeptService;
public String getDeptColumn(){
return "dept_id";
}
public Expression getDeptId() {
SaasUser user = SecurityUtils.getUser();
log.debug("当前租户为 >> {}", user);
if (user == null||null==user.getDeptId()) {
return new NullValue();
}
return new LongValue(user.getDeptId());
}
public Expression getTenantDeptId() {
SaasUser user = SecurityUtils.getUser();
log.debug("当前租户为 >> {}", user);
if (user != null&&null!=user.getDeptId()) {
return new LongValue(user.getDeptId());
}
if(null==TenantContextHolder.getTenantId()){
return new NullValue();
}
SysTenant sysTenant=new SysTenant();
sysTenant.setId(TenantContextHolder.getTenantId());
Long deptId = feignDeptService.getDefaultDeptByTenantId(sysTenant).getData();
return null==deptId?new NullValue():new LongValue(deptId);
}
public ItemsList getDeptIds() {
SaasUser user = SecurityUtils.getUser();
log.debug("当前租户为 >> {}", user);
ExpressionList expressionList=new ExpressionList();
expressionList.addExpressions();
if (user == null||null==user.getDeptId()) {
expressionList.addExpressions(null!=user.getDeptId()?new LongValue(user.getDeptId()):new NullValue());
return expressionList;
}
List<Long> deptIds= Arrays.asList(user.getDeptId(),user.getDeptId());
for (Long deptId : deptIds) {
expressionList.addExpressions(new LongValue(deptId));
}
return expressionList;
}
public boolean ignoreTable(String tableName) {
// 判断是否跳过当前查询的部门过滤
if (TenantContextHolder.getDeptSkip()) {
return Boolean.TRUE;
}
SaasUser user = SecurityUtils.getUser();
// 租户中ID 为空,查询全部,不进行过滤 todo 用户dsType==0查询全部
if (user == null||null==user.getDeptId()) {
return Boolean.TRUE;
}
return !properties.getDeptTables().contains(tableName);
}
public boolean ignoreInsert(List<Column> columns, String deptIdColumn) {
return columns.stream().map(Column::getColumnName).anyMatch(i -> i.equalsIgnoreCase(deptIdColumn));
}
}
@Data @NoArgsConstructor @AllArgsConstructor @ToString(callSuper = true) @EqualsAndHashCode(callSuper = true) @SuppressWarnings({"rawtypes"}) public class MyDataPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor { private MyDataPermissionHandler myDataPermissionHandler; @Override public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException { if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) return; PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql); mpBs.sql(parserSingle(mpBs.sql(), null)); } @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.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) { if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) { return; } PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql(); mpBs.sql(parserMulti(mpBs.sql(), null)); } } @Override protected void processSelect(Select select, int index, String sql, Object obj) { processSelectBody(select.getSelectBody()); List<WithItem> withItemsList = select.getWithItemsList(); if (!CollectionUtils.isEmpty(withItemsList)) { withItemsList.forEach(this::processSelectBody); } } protected void processSelectBody(SelectBody selectBody) { if (selectBody == null) { return; } if (selectBody instanceof PlainSelect) { processPlainSelect((PlainSelect) selectBody); } else if (selectBody instanceof WithItem) { WithItem withItem = (WithItem) selectBody; processSelectBody(withItem.getSubSelect().getSelectBody()); } else { SetOperationList operationList = (SetOperationList) selectBody; List<SelectBody> selectBodyList = operationList.getSelects(); if (CollectionUtils.isNotEmpty(selectBodyList)) { selectBodyList.forEach(this::processSelectBody); } } } @Override protected void processInsert(Insert insert, int index, String sql, Object obj) { if (myDataPermissionHandler.ignoreTable(insert.getTable().getName())) { // 过滤退出执行 return; } List<Column> columns = insert.getColumns(); if (CollectionUtils.isEmpty(columns)) { // 针对不给列名的insert 不处理 return; } String deptIdColumn = myDataPermissionHandler.getDeptColumn(); if (myDataPermissionHandler.ignoreInsert(columns, deptIdColumn)) { // 针对已给出部门列的insert 不处理 return; } columns.add(new Column(deptIdColumn)); // fixed gitee pulls/141 duplicate update List<Expression> duplicateUpdateColumns = insert.getDuplicateUpdateExpressionList(); if (CollectionUtils.isNotEmpty(duplicateUpdateColumns)) { EqualsTo equalsTo = new EqualsTo(); equalsTo.setLeftExpression(new StringValue(deptIdColumn)); equalsTo.setRightExpression(myDataPermissionHandler.getTenantDeptId()); duplicateUpdateColumns.add(equalsTo); } Select select = insert.getSelect(); if (select != null) { this.processInsertSelect(select.getSelectBody()); } else if (insert.getItemsList() != null) { // fixed github pull/295 ItemsList itemsList = insert.getItemsList(); if (itemsList instanceof MultiExpressionList) { ((MultiExpressionList) itemsList).getExpressionLists().forEach(el -> el.getExpressions().add(myDataPermissionHandler.getTenantDeptId())); } else { ((ExpressionList) itemsList).getExpressions().add(myDataPermissionHandler.getTenantDeptId()); } } else { throw ExceptionUtils.mpe("Failed to process multiple-table update, please exclude the tableName or statementId"); } } /** * update 语句处理 */ @Override protected void processUpdate(Update update, int index, String sql, Object obj) { final Table table = update.getTable(); if (myDataPermissionHandler.ignoreTable(table.getName())) { // 过滤退出执行 return; } update.setWhere(this.andExpression(table, update.getWhere())); } /** * delete 语句处理 */ @Override protected void processDelete(Delete delete, int index, String sql, Object obj) { if (myDataPermissionHandler.ignoreTable(delete.getTable().getName())) { // 过滤退出执行 return; } delete.setWhere(this.andExpression(delete.getTable(), delete.getWhere())); } /** * delete update 语句 where 处理 */ protected BinaryExpression andExpression(Table table, Expression where) { //获得where条件表达式 EqualsTo equalsTo = new EqualsTo(); equalsTo.setLeftExpression(this.getAliasColumn(table)); equalsTo.setRightExpression(myDataPermissionHandler.getDeptId()); if (null != where) { if (where instanceof OrExpression) { return new AndExpression(equalsTo, new Parenthesis(where)); } else { return new AndExpression(equalsTo, where); } } return equalsTo; } /** * 处理 insert into select * <p> * 进入这里表示需要 insert 的表启用了多部门,则 select 的表都启动了 * * @param selectBody SelectBody */ protected void processInsertSelect(SelectBody selectBody) { PlainSelect plainSelect = (PlainSelect) selectBody; FromItem fromItem = plainSelect.getFromItem(); if (fromItem instanceof Table) { // fixed gitee pulls/141 duplicate update processPlainSelect(plainSelect); appendSelectItem(plainSelect.getSelectItems()); } else if (fromItem instanceof SubSelect) { SubSelect subSelect = (SubSelect) fromItem; appendSelectItem(plainSelect.getSelectItems()); processInsertSelect(subSelect.getSelectBody()); } } /** * 追加 SelectItem * * @param selectItems SelectItem */ protected void appendSelectItem(List<SelectItem> selectItems) { if (CollectionUtils.isEmpty(selectItems)) { return; } if (selectItems.size() == 1) { SelectItem item = selectItems.get(0); if (item instanceof AllColumns || item instanceof AllTableColumns) { return; } } selectItems.add(new SelectExpressionItem(new Column(myDataPermissionHandler.getDeptColumn()))); } /** * 处理 PlainSelect */ protected void processPlainSelect(PlainSelect plainSelect) { //#3087 github List<SelectItem> selectItems = plainSelect.getSelectItems(); if (CollectionUtils.isNotEmpty(selectItems)) { selectItems.forEach(this::processSelectItem); } // 处理 where 中的子查询 Expression where = plainSelect.getWhere(); processWhereSubSelect(where); // 处理 fromItem FromItem fromItem = plainSelect.getFromItem(); List<Table> list = processFromItem(fromItem); List<Table> mainTables = new ArrayList<>(list); // 处理 join List<Join> joins = plainSelect.getJoins(); if (CollectionUtils.isNotEmpty(joins)) { mainTables = processJoins(mainTables, joins); } // 当有 mainTable 时,进行 where 条件追加 if (CollectionUtils.isNotEmpty(mainTables)) { plainSelect.setWhere(builderExpression(where, mainTables)); System.out.println("------------"); } } private List<Table> processFromItem(FromItem fromItem) { // 处理括号括起来的表达式 while (fromItem instanceof ParenthesisFromItem) { fromItem = ((ParenthesisFromItem) fromItem).getFromItem(); } List<Table> mainTables = new ArrayList<>(); // 无 join 时的处理逻辑 if (fromItem instanceof Table) { Table fromTable = (Table) fromItem; mainTables.add(fromTable); } else if (fromItem instanceof SubJoin) { // SubJoin 类型则还需要添加上 where 条件 List<Table> tables = processSubJoin((SubJoin) fromItem); mainTables.addAll(tables); } else { // 处理下 fromItem processOtherFromItem(fromItem); } return mainTables; } /** * 处理where条件内的子查询 * <p> * 支持如下: * 1. in * 2. = * 3. > * 4. < * 5. >= * 6. <= * 7. <> * 8. EXISTS * 9. NOT EXISTS * <p> * 前提条件: * 1. 子查询必须放在小括号中 * 2. 子查询一般放在比较操作符的右边 * * @param where where 条件 */ protected void processWhereSubSelect(Expression where) { if (where == null) { return; } if (where instanceof FromItem) { processOtherFromItem((FromItem) where); return; } if (where.toString().indexOf("SELECT") > 0) { // 有子查询 if (where instanceof BinaryExpression) { // 比较符号 , and , or , 等等 BinaryExpression expression = (BinaryExpression) where; processWhereSubSelect(expression.getLeftExpression()); processWhereSubSelect(expression.getRightExpression()); } else if (where instanceof InExpression) { // in InExpression expression = (InExpression) where; Expression inExpression = expression.getRightExpression(); if (inExpression instanceof SubSelect) { processSelectBody(((SubSelect) inExpression).getSelectBody()); } } else if (where instanceof ExistsExpression) { // exists ExistsExpression expression = (ExistsExpression) where; processWhereSubSelect(expression.getRightExpression()); } else if (where instanceof NotExpression) { // not exists NotExpression expression = (NotExpression) where; processWhereSubSelect(expression.getExpression()); } else if (where instanceof Parenthesis) { Parenthesis expression = (Parenthesis) where; processWhereSubSelect(expression.getExpression()); } } } protected void processSelectItem(SelectItem selectItem) { if (selectItem instanceof SelectExpressionItem) { SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem; if (selectExpressionItem.getExpression() instanceof SubSelect) { processSelectBody(((SubSelect) selectExpressionItem.getExpression()).getSelectBody()); } else if (selectExpressionItem.getExpression() instanceof Function) { processFunction((Function) selectExpressionItem.getExpression()); } } } /** * 处理函数 * <p>支持: 1. select fun(args..) 2. select fun1(fun2(args..),args..)<p> * <p> fixed gitee pulls/141</p> * * @param function */ protected void processFunction(Function function) { ExpressionList parameters = function.getParameters(); if (parameters != null) { parameters.getExpressions().forEach(expression -> { if (expression instanceof SubSelect) { processSelectBody(((SubSelect) expression).getSelectBody()); } else if (expression instanceof Function) { processFunction((Function) expression); } }); } } /** * 处理子查询等 */ protected void processOtherFromItem(FromItem fromItem) { // 去除括号 while (fromItem instanceof ParenthesisFromItem) { fromItem = ((ParenthesisFromItem) fromItem).getFromItem(); } if (fromItem instanceof SubSelect) { SubSelect subSelect = (SubSelect) fromItem; if (subSelect.getSelectBody() != null) { processSelectBody(subSelect.getSelectBody()); } } else if (fromItem instanceof ValuesList) { logger.debug("Perform a subQuery, if you do not give us feedback"); } else if (fromItem instanceof LateralSubSelect) { LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem; if (lateralSubSelect.getSubSelect() != null) { SubSelect subSelect = lateralSubSelect.getSubSelect(); if (subSelect.getSelectBody() != null) { processSelectBody(subSelect.getSelectBody()); } } } } /** * 处理 sub join * * @param subJoin subJoin * @return Table subJoin 中的主表 */ private List<Table> processSubJoin(SubJoin subJoin) { List<Table> mainTables = new ArrayList<>(); if (subJoin.getJoinList() != null) { List<Table> list = processFromItem(subJoin.getLeft()); mainTables.addAll(list); mainTables = processJoins(mainTables, subJoin.getJoinList()); } return mainTables; } /** * 处理 joins * * @param mainTables 可以为 null * @param joins join 集合 * @return List<Table> 右连接查询的 Table 列表 */ private List<Table> processJoins(List<Table> mainTables, List<Join> joins) { // join 表达式中最终的主表 Table mainTable = null; // 当前 join 的左表 Table leftTable = null; if (mainTables == null) { mainTables = new ArrayList<>(); } else if (mainTables.size() == 1) { mainTable = mainTables.get(0); leftTable = mainTable; } //对于 on 表达式写在最后的 join,需要记录下前面多个 on 的表名 Deque<List<Table>> onTableDeque = new LinkedList<>(); for (Join join : joins) { // 处理 on 表达式 FromItem joinItem = join.getRightItem(); // 获取当前 join 的表,subJoint 可以看作是一张表 List<Table> joinTables = null; if (joinItem instanceof Table) { joinTables = new ArrayList<>(); joinTables.add((Table) joinItem); } else if (joinItem instanceof SubJoin) { joinTables = processSubJoin((SubJoin) joinItem); } if (joinTables != null) { // 如果是隐式内连接 if (join.isSimple()) { mainTables.addAll(joinTables); continue; } // 当前表是否忽略 Table joinTable = joinTables.get(0); List<Table> onTables = null; // 如果不要忽略,且是右连接,则记录下当前表 if (join.isRight()) { mainTable = joinTable; if (leftTable != null) { onTables = Collections.singletonList(leftTable); } } else if (join.isLeft()) { onTables = Collections.singletonList(joinTable); } else if (join.isInner()) { if (mainTable == null) { onTables = Collections.singletonList(joinTable); } else { onTables = Arrays.asList(mainTable, joinTable); } mainTable = null; } mainTables = new ArrayList<>(); if (mainTable != null) { mainTables.add(mainTable); } // 获取 join 尾缀的 on 表达式列表 Collection<Expression> originOnExpressions = join.getOnExpressions(); // 正常 join on 表达式只有一个,立刻处理 if (originOnExpressions.size() == 1 && onTables != null) { List<Expression> onExpressions = new LinkedList<>(); onExpressions.add(builderExpression(originOnExpressions.iterator().next(), onTables)); join.setOnExpressions(onExpressions); leftTable = joinTable; continue; } // 表名压栈,忽略的表压入 null,以便后续不处理 onTableDeque.push(onTables); // 尾缀多个 on 表达式的时候统一处理 if (originOnExpressions.size() > 1) { Collection<Expression> onExpressions = new LinkedList<>(); for (Expression originOnExpression : originOnExpressions) { List<Table> currentTableList = onTableDeque.poll(); if (CollectionUtils.isEmpty(currentTableList)) { onExpressions.add(originOnExpression); } else { onExpressions.add(builderExpression(originOnExpression, currentTableList)); } } join.setOnExpressions(onExpressions); } leftTable = joinTable; } else { processOtherFromItem(joinItem); leftTable = null; } } return mainTables; } /** * 处理条件 */ protected Expression builderExpression(Expression currentExpression, List<Table> tables) { // 没有表需要处理直接返回 if (CollectionUtils.isEmpty(tables)) { return currentExpression; } // 部门 Expression deptId = myDataPermissionHandler.getDeptId(); ItemsList deptIds = myDataPermissionHandler.getDeptIds(); List<InExpression> inExpressions = tables.stream() .filter(x -> !myDataPermissionHandler.ignoreTable(x.getName())) .map(item -> new InExpression(getAliasColumn(item), deptIds)) .collect(Collectors.toList()); if(CollectionUtils.isEmpty(inExpressions)){ return currentExpression; } // 注入的表达式 Expression injectExpression = inExpressions.get(0); // 如果有多表,则用 and 连接 if (inExpressions.size() > 1) { for (int i = 1; i < inExpressions.size(); i++) { injectExpression = new AndExpression(injectExpression, inExpressions.get(i)); } } // // //todo 构造每张表的条件 // List<EqualsTo> equalsTos = tables.stream() // .filter(x -> !myDataPermissionHandler.ignoreTable(x.getName())) // .map(item -> new EqualsTo(getAliasColumn(item), deptId)) // .collect(Collectors.toList()); // // if(CollectionUtils.isEmpty(equalsTos)){ // return currentExpression; // } // // // 注入的表达式 // Expression injectExpression = equalsTos.get(0); // // 如果有多表,则用 and 连接 // if (equalsTos.size() > 1) { // for (int i = 1; i < equalsTos.size(); i++) { // injectExpression = new AndExpression(injectExpression, equalsTos.get(i)); // } // } if (currentExpression == null) { return injectExpression; } if (currentExpression instanceof OrExpression) { return new AndExpression(new Parenthesis(currentExpression), injectExpression); } else { return new AndExpression(currentExpression, injectExpression); } } /** * 部门字段别名设置 * <p>tenantId 或 tableAlias.tenantId</p> * * @param table 表对象 * @return 字段 */ protected Column getAliasColumn(Table table) { StringBuilder column = new StringBuilder(); // 为了兼容隐式内连接,没有别名时条件就需要加上表名 if (table.getAlias() != null) { column.append(table.getAlias().getName()); } else { column.append(table.getName()); } column.append(StringPool.DOT).append("dept_id"); return new Column(column.toString()); } @Override public void setProperties(Properties properties) { PropertyMapper.newInstance(properties).whenNotBlank("mybatisPlusMetaObjectHandler", ClassUtils::newInstance, this::setMyDataPermissionHandler); } }
@Configuration @ConditionalOnBean(DataSource.class) @AutoConfigureAfter(DataSourceAutoConfiguration.class) @EnableConfigurationProperties(SaasMybatisProperties.class) @AllArgsConstructor public class MybatisPlusConfiguration implements WebMvcConfigurer { private DataScopeProperties dataScopeProperties; private ShopScopeProperties shopScopeProperties; private FeignRoleService feignRoleService; private FeignOrganService feignOrganService; /** * 增加请求参数解析器,对请求中的参数注入SQL 检查 * @param resolverList */ @Override public void addArgumentResolvers(List<HandlerMethodArgumentResolver> resolverList) { resolverList.add(new SqlFilterArgumentResolver()); } @Bean public MybatisPlusInterceptor mybatisPlusInterceptor(TenantLineInnerInterceptor tenantLineInnerInterceptor, MyDataPermissionInterceptor dataPermissionInterceptor) { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 注入多租户支持 interceptor.addInnerInterceptor(tenantLineInnerInterceptor); interceptor.addInnerInterceptor(dataPermissionInterceptor); // 分页支持 PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(); paginationInnerInterceptor.setMaxLimit(1000L); interceptor.addInnerInterceptor(paginationInnerInterceptor); return interceptor; } //将需要用到的类直接定义在参数上即可 @Bean @ConditionalOnMissingBean public MyDataPermissionInterceptor myDataPermissionInterceptor(SaasTenantConfigProperties tenantConfigProperties,FeignDeptService feignDeptService) { MyDataPermissionInterceptor myDataPermissionInterceptor = new MyDataPermissionInterceptor(); myDataPermissionInterceptor.setMyDataPermissionHandler(new MyDataPermissionHandler(tenantConfigProperties,feignDeptService)); return myDataPermissionInterceptor; } /** * 创建租户维护处理器对象 * @return 处理后的租户维护处理器 */ @Bean @ConditionalOnMissingBean public TenantLineInnerInterceptor tenantLineInnerInterceptor(SaasTenantConfigProperties tenantConfigProperties) { TenantLineInnerInterceptor tenantLineInnerInterceptor = new TenantLineInnerInterceptor(); tenantLineInnerInterceptor.setTenantLineHandler(new SaasTenantHandler(tenantConfigProperties)); return tenantLineInnerInterceptor; } }