记一次数据权限实现过程

记一次数据权限实现过程

前提条件, mybatis-plus版本大于等于3.4.1

新增数据权限枚举

import lombok.Getter;
import lombok.RequiredArgsConstructor;@Getter
@RequiredArgsConstructor
public enum DataScopeEnum {ALL(0, "全部"), DEPT(1, "本部门"), DEPT_AND_CHILD(2, "本部门及其子部门"), CUSTOM(3, "自定义"), SELF(4, "本人");private final Integer type;private final String description;public Integer getCustomType() {return CUSTOM.getType();}}

新增数据权限注解

import lombok.AllArgsConstructor;
import lombok.Getter;import java.lang.annotation.*;/*** 数据权限注解** @author tomcatzhuzhu*/
@Target(ElementType.METHOD)
@Inherited
@Retention(RetentionPolicy.RUNTIME)
public @interface DataPermission {/*** 数据权限字段名*/FieldName fieldName() default FieldName.CREATE_BY;/*** 主表别名** where ${alias}.${fieldName} = ...*/String alias() default "";@Getter@AllArgsConstructorenum FieldName {CREATE_BY("create_by"), UPDATE_BY("update_by"), USER_ID("user_id");String field;}}

新增数据权限处理器实现类

import your.DataPermission;
import your.DataScopeEnum;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.handler.DataPermissionHandler;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SubSelect;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Map;
import java.util.Optional;
import java.util.Set;/*** 自定义数据权限** 注意事项,使用数据权限插件时,jsqlparser(4.4版本)会把(ur | cs | rs | rr)等认为是关键字,要避开使用这些词 ** 关键词列表->* https://github.com/JSQLParser/JSqlParser/blob/master/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt** @author tomcatzhuzhu*/
public class DataPermissionHandlerImpl implements DataPermissionHandler {@Overridepublic 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);// 匹配方法(包括带count的计数方法)if (ObjectUtils.isNotEmpty(annotation)&& (method.getName().equals(methodName) || (method.getName() + "_COUNT").equals(methodName))) {// 获取当前的用户Authentication authentication = SecurityContextHolder.getContext().getAuthentication();if (Optional.ofNullable(authentication.getPrincipal()).isPresent()) {// 本人用的是微服务, 在这里拿不到登录用户的类, 所以转mapMap<String, Object> userMap = BeanUtil.beanToMap(authentication.getPrincipal());if (ObjectUtils.isNotEmpty(userMap)) {return dataScopeFilter(userMap, annotation, where);}}}}}catch (ClassNotFoundException | JSQLParserException e) {e.printStackTrace();}return where;}/*** 构建过滤条件* @param userMap 当前登录用户* @param where 当前查询条件* @return 构建后查询条件*/@SuppressWarnings({ "unchecked", "rawtypes" })private Expression dataScopeFilter(Map<String, Object> userMap, DataPermission annotation, Expression where)throws JSQLParserException {// 获取用户的数据权限Set<Integer> dataScopes = (Set) userMap.get("dataScopes");// 数据权限包含全部时, 不作任何限制if (dataScopes.contains(DataScopeEnum.ALL.getType()))return where;String fieldName = annotation.fieldName().getField(); // 字段名String alias = annotation.alias(); // 主表别名Expression expression = null;// 获取数据权限(本级及子部门,本级,自己)表达式expression = getDataScopeExpression(dataScopes, userMap, fieldName, alias);return ObjectUtils.isNotEmpty(where) ? new AndExpression(where, new Parenthesis(expression)) : expression;}@SuppressWarnings({ "unchecked", "rawtypes" })private Expression getDataScopeExpression(Set<Integer> dataScopes, Map<String, Object> userMap, String fieldName,String alias) throws JSQLParserException {Expression expression = null;// 从登录信息中获取自定义数据权限的部门idSet<Long> customScopeDeptIds = (Set) userMap.get("customScopeDeptIds");Long deptId = (Long) userMap.get("deptId"); // 从登录信息中获取部门idLong userId = (Long) userMap.get("id"); // 从登录信息中获取用户id// 库名: alibabaString alibabaDataBase = "alibaba";Table user = new Table(alibabaDataBase, "sys_user"); // alibaba的sys_user表Table sysRoleUser = new Table(alibabaDataBase, "sys_role_user");Table deptRelation = new Table(alibabaDataBase, "sys_dept_relation"); // 部门关系表, 字段descendant 棣属于ancestor// 自己部门及下属部门if (dataScopes.contains(DataScopeEnum.DEPT_AND_CHILD.getType())) {// create_by in (SELECT su.user_id from alibaba.sys_user su where su.dept_id in (select sdr.descendant from alibaba.sys_dept_relation sdr WHERE sdr.ancestor = 1))// 获取自己部门及下属部门id// (select sdr.descendant from alibaba.sys_dept_relation sdr WHERE sdr.ancestor = 1)EqualsTo deptEqual = new EqualsTo(buildColumn(null, "ancestor"), new LongValue(deptId));PlainSelect relation = new PlainSelect();relation.setFromItem(deptRelation);relation.setSelectItems(ListUtil.of(new SelectExpressionItem(buildColumn(null, "descendant"))));relation.setWhere(deptEqual);// 获取下属员工及自己的用户id// (SELECT su.user_id from alibaba.sys_user su where su.dept_id in (select sdr.descendant from alibaba.sys_dept_relation sdr WHERE sdr.ancestor = 1))PlainSelect sysUser = new PlainSelect();sysUser.setSelectItems(ListUtil.of(new SelectExpressionItem(buildColumn(null, "user_id"))));sysUser.setFromItem(user);InExpression userDeptIdIn = new InExpression();userDeptIdIn.setLeftExpression(buildColumn(null, "dept_id"));userDeptIdIn.setRightExpression(new SubSelect().withSelectBody(relation));sysUser.setWhere(userDeptIdIn);// fieldName inInExpression fieldIn = new InExpression();fieldIn.setLeftExpression(buildColumn(alias, fieldName));fieldIn.setRightItemsList(new SubSelect().withSelectBody(sysUser));expression = fieldIn;// 方式二 : 使用CCJSqlParserUtil解析sql// Statement parse = CCJSqlParserUtil.parse(String.format("SELECT su.user_id from alibaba.sys_user su where su.dept_id in (select sdr.descendant from alibaba.sys_dept_relation sdr WHERE sdr.ancestor = %s )", 1));// PlainSelect plainSelect = ((Select) parse).getSelectBody(PlainSelect.class);// InExpression fieldIn = new InExpression();// fieldIn.setLeftExpression(buildColumn(alias, fieldName));// fieldIn.setRightExpression(new SubSelect().withSelectBody(plainSelect));}// 只能查看自己部门else if (dataScopes.contains(DataScopeEnum.DEPT.getType())) {// create_by in (SELECT su.user_id from alibaba.sys_user su where su.dept_id = ? )// 获取自己部门本级所有人员的用户idSubSelect userIdSelect = new SubSelect();PlainSelect userIdSelectBody = new PlainSelect();userIdSelectBody.setFromItem(user);userIdSelectBody.setSelectItems(ListUtil.of(new SelectExpressionItem(new Column("user_id"))));EqualsTo deptIdEqual = new EqualsTo(buildColumn(null, "dept_id"), new LongValue(deptId));userIdSelectBody.setWhere(deptIdEqual);userIdSelect.setSelectBody(userIdSelectBody);// fieldName inInExpression fieldIn = new InExpression();fieldIn.setLeftExpression(buildColumn(alias, fieldName));fieldIn.setRightExpression(userIdSelect);expression = fieldIn;}// 只查看自己else if (dataScopes.contains(DataScopeEnum.SELF.getType())) {expression = new EqualsTo().withLeftExpression(buildColumn(alias, fieldName)).withRightExpression(new LongValue(userId));}// 自定义数据权限且部门id集合不为空if (dataScopes.contains(DataScopeEnum.CUSTOM.getType())) {// 目标sql: alias.fieldName in (SELECT su.user_id from alibaba.sys_user su where su.dept_id in (?,?,?));// 方式1: 字符串转expressionExpression deptIdIn = CCJSqlParserUtil.parseExpression(String.format("dept_id in (%s)", StrUtil.join(",", customScopeDeptIds)));// 方式2: 构造expression// InExpression deptIdIn = new InExpression();// deptIdIn.setLeftExpression(buildColumn(null,"dept_id"));// ExpressionList deptIdList = new  ExpressionList(customScopeDeptIds.parallelStream().map(LongValue::new).collect(Collectors.toList()));// deptIdIn.setRightItemsList(deptIdList);// 获取部门id集合内所有用户idSubSelect subSelect = new SubSelect();PlainSelect body = new PlainSelect();body.setSelectItems(ListUtil.of(new SelectExpressionItem(buildColumn(null, "user_id"))));body.setFromItem(sysRoleUser);body.setWhere(deptIdIn);subSelect.setSelectBody(body);// fieldName inInExpression fieldIn = new InExpression();fieldIn.setLeftExpression(buildColumn(alias, fieldName));fieldIn.setRightExpression(subSelect);expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, fieldIn) : fieldIn;}return 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);}}

添加数据权限拦截

@Configuration(proxyBeanMethods = false)
public class MybatisAutoConfiguration implements WebMvcConfigurer {/*** 分页插件, 对于单一数据库类型来说,都建议配置该值,避免每次分页都去抓取数据库类型*/@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// 必须配在分页插件之前interceptor.addInnerInterceptor(new DataPermissionInterceptor(new DataPermissionHandlerImpl()));interceptor.addInnerInterceptor(new ErpPaginationInnerInterceptor());return interceptor;}// ... 省略一堆配置}

用法

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;/*** 公司信息** @author tomcatzhuzhu* @date 2022-10-19 10:09:47*/
@Mapper
public interface SysUserMapper extends BaseMapper<SysUser> {@DataPermission(alias = "uu")  //会在where拼借条件 select * from user uu => select * from user uu where uu.create_by in (?,..) / uu.create_by = ? Page<VO> getVosPage(Page<VO> page, @Param("pm") Param query);}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！