Mybatis-plus數據權限DataPermissionInterceptor實現
前言
數據權限因分頁問題,不可能通過代碼對數據進行過濾處理,隻能在數據庫語句進行處理,而如果每個查詢都進行特殊處理的話,是個巨大的工作量,在網上找到瞭mybatis的一種解決方案。
一、源碼分析
- 繼承抽象類JsqlParserSupport並重寫processSelect方法。JSqlParser是一個SQL語句解析器,它將SQL轉換為Java類的可遍歷層次結構。plus中也引入瞭JSqlParser包,processSelect可以對Select語句進行處理。
- 實現InnerInterceptor接口並重寫beforeQuery方法。InnerInterceptor是plus的插件接口,beforeQuery可以對查詢語句執行前進行處理。
- DataPermissionHandler作為數據權限處理器,是一個接口,提供getSqlSegment方法添加數據權限 SQL 片段。
- 由上可知,我們隻需要實現DataPermissionHandler接口,並按照業務規則處理SQL,就可以實現數據權限的功能。
- DataPermissionInterceptor為mybatis-plus 3.4.2版本以上才有的功能。
package com.baomidou.mybatisplus.extension.plugins.inner; import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper; import com.baomidou.mybatisplus.core.toolkit.PluginUtils; import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport; import com.baomidou.mybatisplus.extension.plugins.handler.DataPermissionHandler; import lombok.*; import net.sf.jsqlparser.expression.Expression; 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 org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; import java.sql.SQLException; import java.util.List; /** * 數據權限處理器 * * @author hubin * @since 3.4.1 + */ @Data @NoArgsConstructor @AllArgsConstructor @ToString(callSuper = true) @EqualsAndHashCode(callSuper = true) @SuppressWarnings({"rawtypes"}) public class DataPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor { private DataPermissionHandler 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 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) { Expression sqlSegment = dataPermissionHandler.getSqlSegment(plainSelect.getWhere(), whereSegment); if (null != sqlSegment) { plainSelect.setWhere(sqlSegment); } } }
二、使用案例
mybatis-plus在gitee的倉庫中,有人詢問瞭如何使用DataPermissionInterceptor,下面有人給出瞭例子,一共分為兩步,一是實現dataPermissionHandler接口,二是將實現添加到mybstis-plus的處理器中。他的例子中是根據不同權限類型拼接sql。
通用的方案是在所有的表中增加權限相關的字段,如部門、門店、租戶等。實現dataPermissionHandler接口時較方便,可直接添加這幾個字段的條件,無需查詢數據庫。
/** * 自定義數據權限 * * @Author PXL * @Version 1.0 * @Date 2021-02-07 16:52 */ public class DataPermissionHandlerImpl implements DataPermissionHandler { @Override public Expression getSqlSegment(Expression where, String mappedStatementId) { try { Class<?> clazz = Class.forName(mappedStatementId.substring(0, mappedStatementId.lastIndexOf("."))); String methodName = mappedStatementId.substring(mappedStatementId.lastIndexOf(".") + 1); Method[] methods = clazz.getDeclaredMethods(); for (Method method : methods) { DataPermission annotation = method.getAnnotation(DataPermission.class); if (ObjectUtils.isNotEmpty(annotation) && (method.getName().equals(methodName) || (method.getName() + "_COUNT").equals(methodName))) { // 獲取當前的用戶 LoginUser loginUser = SpringUtils.getBean(TokenService.class).getLoginUser(ServletUtils.getRequest()); if (ObjectUtils.isNotEmpty(loginUser) && ObjectUtils.isNotEmpty(loginUser.getUser()) && !loginUser.getUser().isAdmin()) { return dataScopeFilter(loginUser.getUser(), annotation.value(), where); } } } } catch (ClassNotFoundException e) { e.printStackTrace(); } return where; } /** * 構建過濾條件 * * @param user 當前登錄用戶 * @param where 當前查詢條件 * @return 構建後查詢條件 */ public static Expression dataScopeFilter(SysUser user, String tableAlias, Expression where) { Expression expression = null; for (SysRole role : user.getRoles()) { String dataScope = role.getDataScope(); if (DataScopeAspect.DATA_SCOPE_ALL.equals(dataScope)) { return where; } if (DataScopeAspect.DATA_SCOPE_CUSTOM.equals(dataScope)) { InExpression inExpression = new InExpression(); inExpression.setLeftExpression(buildColumn(tableAlias, "dept_id")); SubSelect subSelect = new SubSelect(); PlainSelect select = new PlainSelect(); select.setSelectItems(Collections.singletonList(new SelectExpressionItem(new Column("dept_id")))); select.setFromItem(new Table("sys_role_dept")); EqualsTo equalsTo = new EqualsTo(); equalsTo.setLeftExpression(new Column("role_id")); equalsTo.setRightExpression(new LongValue(role.getRoleId())); select.setWhere(equalsTo); subSelect.setSelectBody(select); inExpression.setRightExpression(subSelect); expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, inExpression) : inExpression; } if (DataScopeAspect.DATA_SCOPE_DEPT.equals(dataScope)) { EqualsTo equalsTo = new EqualsTo(); equalsTo.setLeftExpression(buildColumn(tableAlias, "dept_id")); equalsTo.setRightExpression(new LongValue(user.getDeptId())); expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, equalsTo) : equalsTo; } if (DataScopeAspect.DATA_SCOPE_DEPT_AND_CHILD.equals(dataScope)) { InExpression inExpression = new InExpression(); inExpression.setLeftExpression(buildColumn(tableAlias, "dept_id")); SubSelect subSelect = new SubSelect(); PlainSelect select = new PlainSelect(); select.setSelectItems(Collections.singletonList(new SelectExpressionItem(new Column("dept_id")))); select.setFromItem(new Table("sys_dept")); EqualsTo equalsTo = new EqualsTo(); equalsTo.setLeftExpression(new Column("dept_id")); equalsTo.setRightExpression(new LongValue(user.getDeptId())); Function function = new Function(); function.setName("find_in_set"); function.setParameters(new ExpressionList(new LongValue(user.getDeptId()) , new Column("ancestors"))); select.setWhere(new OrExpression(equalsTo, function)); subSelect.setSelectBody(select); inExpression.setRightExpression(subSelect); expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, inExpression) : inExpression; } if (DataScopeAspect.DATA_SCOPE_SELF.equals(dataScope)) { EqualsTo equalsTo = new EqualsTo(); equalsTo.setLeftExpression(buildColumn(tableAlias, "create_by")); equalsTo.setRightExpression(new StringValue(user.getUserName())); expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, equalsTo) : equalsTo; } } return ObjectUtils.isNotEmpty(where) ? new AndExpression(where, new Parenthesis(expression)) : expression; } /** * 構建Column * * @param tableAlias 表別名 * @param columnName 字段名稱 * @return 帶表別名字段 */ public static Column buildColumn(String tableAlias, String columnName) { if (StringUtils.isNotEmpty(tableAlias)) { columnName = tableAlias + "." + columnName; } return new Column(columnName); } }
// 自定義數據權限 interceptor.addInnerInterceptor(new DataPermissionInterceptor(new DataPermissionHandlerImpl()));
嘗試驗證
DataPermissionHandler 接口
可以看到DataPermissionHandler 接口使用中,傳遞來的參數是什麼。
參數 | 含義 |
---|---|
where | 為當前sql已有的where條件 |
mappedStatementId | 為mapper中定義的方法的路徑 |
@InterceptorIgnore註解
攔截忽略註解 @InterceptorIgnore
屬性名 | 類型 | 默認值 | 描述 |
---|---|---|---|
tenantLine | String | “” | 行級租戶 |
dynamicTableName | String | “” | 動態表名 |
blockAttack | String | “” | 攻擊 SQL 阻斷解析器,防止全表更新與刪除 |
illegalSql | String | “” | 垃圾SQL攔截 |
實踐應用
在維修小程序中,我使用瞭此方案。如下是我的代碼:
/** * @ClassName MyDataPermissionHandler * @Description 自定義數據權限處理 * @Author FangCheng * @Date 2022/4/2 14:54 **/ @Component public class MyDataPermissionHandler implements DataPermissionHandler { @Autowired @Lazy private UserRepository userRepository; @Override public Expression getSqlSegment(Expression where, String mappedStatementId) { try { Class<?> clazz = Class.forName(mappedStatementId.substring(0, mappedStatementId.lastIndexOf("."))); String methodName = mappedStatementId.substring(mappedStatementId.lastIndexOf(".") + 1); Method[] methods = clazz.getDeclaredMethods(); for (Method method : methods) { if (!methodName.equals(method.getName())) { continue; } // 獲取自定義註解,無此註解則不控制數據權限 CustomDataPermission annotation = method.getAnnotation(CustomDataPermission.class); if (annotation == null) { continue; } // 自定義的用戶上下文,獲取到用戶的id ContextUserDetails contextUserDetails = UserDetailsContextHolder.getContextUserDetails(); String userId = contextUserDetails.getId(); User user = userRepository.selectUserById(userId); // 如果是特權用戶,不控制數據權限 if (Constants.ADMIN_RULE == user.getAdminuser()) { return where; } // 員工用戶 if (UserTypeEnum.USER_TYPE_EMPLOYEE.getCode().equals(user.getUsertype())) { // 員工用戶的權限字段 String field = annotation.field().getValue(); // 單據類型 String billType = annotation.billType().getFuncno(); // 操作類型 OperationTypeEnum operationType = annotation.operation(); // 權限字段為空則為不控制數據權限 if (StringUtils.isNotEmpty(field)) { List<DataPermission> dataPermissions = userRepository.selectUserFuncnoDataPermission(userId, billType); if (dataPermissions.size() == 0) { // 沒數據權限,但有功能權限則取所有數據 return where; } // 構建in表達式 InExpression inExpression = new InExpression(); inExpression.setLeftExpression(new Column(field)); List<Expression> conditions = null; switch(operationType) { case SELECT: conditions = dataPermissions.stream().map(res -> new StringValue(res.getStkid())).collect(Collectors.toList()); break; case INSERT: conditions = dataPermissions.stream().filter(DataPermission::isAddright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList()); break; case UPDATE: conditions = dataPermissions.stream().filter(DataPermission::isModright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList()); break; case APPROVE: conditions = dataPermissions.stream().filter(DataPermission::isCheckright).map(res -> new StringValue(res.getStkid())).collect(Collectors.toList()); break; default: break; } if (conditions == null) { return where; } conditions.add(new StringValue(Constants.ALL_STORE)); ItemsList itemsList = new ExpressionList(conditions); inExpression.setRightItemsList(itemsList); if (where == null) { return inExpression; } return new AndExpression(where, inExpression); } else { return where; } } else { // 供應商用戶的權限字段 String field = annotation.vendorfield().getValue(); if (StringUtils.isNotEmpty(field)) { // 供應商如果控制權限,則隻能看到自己的單據。直接使用EqualsTo EqualsTo equalsTo = new EqualsTo(); equalsTo.setLeftExpression(new Column(field)); equalsTo.setRightExpression(new StringValue(userId)); if (where == null) { return equalsTo; } // 創建 AND 表達式 拼接Where 和 = 表達式 return new AndExpression(where, equalsTo); } else { return where; } } } } catch (ClassNotFoundException e) { e.printStackTrace(); } return where; } }
/** * @ClassName MybatisConfig * @Description mybatis配置 * @Author FangCheng * @Date 2022/4/2 15:32 **/ @Configuration public class MybatisConfig { @Autowired private MyDataPermissionHandler myDataPermissionHandler; @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); // 添加數據權限插件 DataPermissionInterceptor dataPermissionInterceptor = new DataPermissionInterceptor(); // 添加自定義的數據權限處理器 dataPermissionInterceptor.setDataPermissionHandler(myDataPermissionHandler); interceptor.addInnerInterceptor(dataPermissionInterceptor); // 分頁插件 //interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.SQL_SERVER)); return interceptor; } }
因為維修小程序相當於一個外掛程序,他的權限控制延用瞭七八年前程序的方案,設計的較為復雜,也有現成獲取數據的存儲過程供我們使用,此處做瞭一些特殊處理。增加瞭自定義註解、dataPermissionHandler接口實現類查詢瞭數據庫調用存儲過程獲取權限信息等。
自定義註解
/** * @ClassName CustomDataPermission * @Description 自定義數據權限註解 * @Author FangCheng * @Date 2022/4/6 10:24 **/ @Target({ElementType.METHOD, ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) public @interface CustomDataPermission { PermissionFieldEnum field(); PermissionFieldEnum vendorfield(); BillTypeEnum billType(); OperationTypeEnum operation(); }
使用註解
/** * @ClassName ApplyHMapper * @Description 維修申請單主表 * @Author FangCheng * @Date 2022/4/6 13:06 **/ @Mapper public interface ApplyHMapper extends BaseMapper<ApplyHPo> { /** * @Description: * @author FangCheng * @Date: 2022/4/1 15:26 * @methodName selectApplyHs */ @CustomDataPermission(field = PermissionFieldEnum.FIELD_STKID, vendorfield = PermissionFieldEnum.FIELD_EMPTY, billType = BillTypeEnum.APPLY_BILL, operation = OperationTypeEnum.SELECT) Page<ApplyHPo> selectApplyHs(IPage<ApplyHPo> page, @Param(Constants.WRAPPER) QueryWrapper<ApplyHPo> queryWrapper); /** * @Description: * @author FangCheng * @Date: 2022/4/1 15:26 * @methodName selectApplyHsForVendor */ Page<ApplyHPo> selectApplyHsForVendor(IPage<ApplyHPo> page, @Param("vendorid") String vendorid, @Param(Constants.WRAPPER) QueryWrapper<ApplyHPo> queryWrapper); /** * @Description: * @author FangCheng * @Date: 2022/4/1 15:26 * @methodName selectApplyH */ @CustomDataPermission(field = PermissionFieldEnum.FIELD_STKID, vendorfield = PermissionFieldEnum.FIELD_EMPTY, billType = BillTypeEnum.APPLY_BILL, operation = OperationTypeEnum.SELECT) ApplyHPo selectApplyH(@Param("billNo") String billNo); /** * @Description: * @author FangCheng * @Date: 2022/4/1 15:26 * @methodName selectApplyH */ @InterceptorIgnore ApplyHPo selectApplyHNoPermission(@Param("billNo") String billNo); /** * @Description: * @author FangCheng * @Date: 2022/4/1 15:26 * @methodName saveApplyH */ void saveApplyH(ApplyHPo applyHPo); }
最終的效果
2022-04-24 09:14:52.878 DEBUG 29254 — [nio-8086-exec-2] c.y.w.i.p.m.ApplyHMapper.selectApplyHs : ==> select * from t_mt_apply_h WHERE stkid IN ('0025', 'all')
總結
以上就是今天要講的內容,本文僅僅簡單介紹瞭Mybatis-plus數據權限接口DataPermissionInterceptor的一種實現方式,沒有過多的深入研究。
部分內容參考:
Mybatis-Plus入門系列(3)- MybatisPlus之數據權限插件DataPermissionInterceptor
@InterceptorIgnore
到此這篇關於Mybatis-plus數據權限DataPermissionInterceptor實現的文章就介紹到這瞭,更多相關Mybatis-plus DataPermissionInterceptor內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- mybatis-plus團隊新作mybatis-mate實現數據權限
- 關於mybatis plus 中的查詢優化問題
- mybatis3中@SelectProvider傳遞參數方式
- Mybatis中@Param註解的用法詳解
- 解析MyBatis源碼實現自定義持久層框架