Mybatis-Plus通过注解形式实现数据权限过滤

背景本文借鉴若依快速开发框架的数据权限思路，通过注解形式实现数据权限过滤，优点是灵活方便。代码创建注解类@Target(ElementType.METHOD)@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface DataScope {/*** 部门表的别名*/public String deptAlias() default

大卫没什么

9164人浏览 · 2021-07-21 16:23:11

大卫没什么 · 2021-07-21 16:23:11 发布

背景

本文借鉴若依快速开发框架的数据权限思路，通过注解形式实现数据权限过滤，优点是灵活方便。

maven依赖

	<dependency>
	    <groupId>com.baomidou</groupId>
	    <artifactId>mybatis-plus-boot-starter</artifactId>
	    <version>3.4.3</version>
	</dependency>
	 
	<dependency>
	    <groupId>org.aspectj</groupId>
	    <artifactId>aspectjweaver</artifactId>
	    <version>1.9.6</version>
	</dependency>
	
	<!-- jsqlparser依赖，解决复杂SQL的ParseException问题 -->
	<dependency>
	 	<groupId>com.github.jsqlparser</groupId>
		<artifactId>jsqlparser</artifactId>
		<version>3.1</version>
	</dependency>

代码

创建注解类

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataScope {

    /**
     * 部门表的别名
     */
    public String deptAlias() default "";

    /**
     * 部门字段名
     */
    String deptField() default "dept_id";
}

创建DataScopePermissionHandler 处理器（重点）

@Aspect
@Slf4j
@Component
public class DataScopePermissionHandler implements DataPermissionHandler {

	/**
     * 通过ThreadLocal记录权限相关的属性值
     */
    ThreadLocal<DataScopeParam> threadLocal = new ThreadLocal<>();

	/**
     * 清空当前线程上次保存的权限信息
     */
     @After("dataScopePointCut()")
    public void clearThreadLocal(){
        threadLocal.remove();
        log.debug("threadLocal.remove()");
    }

	/**
     * 注解对象
     * 该成员变量在并发情况下导致多个线程公用了一套 @DataScope 配置的参数，导致sql拼接失败，必须改为局部变量
     * 修改于：2022.04.28
     */
//    private DataScope controllerDataScope;

    /**
     * 配置织入点
     */
    @Pointcut("@annotation(com.xxx.base.datascope.annotation.DataScope)")
    public void dataScopePointCut() {
    }

    @Before("dataScopePointCut()")
    public void doBefore(JoinPoint point) {
        // 获得注解
        DataScope controllerDataScope = getAnnotationLog(point);
        if (controllerDataScope != null) {
            // 获取当前的用户及相关属性，需提前获取和保存数据权限对应的部门ID集合
            User currentUser = SecurityUtil.getUser();
            DataScopeParam dataScopeParam = new DataScopeParam(controllerDataScope.deptAlias(),
                    controllerDataScope.deptField(),
                    currentUser.isAdmin(),
                    currentUser.getDataScope());
            threadLocal.set(dataScopeParam);
            log.debug("currentUser.getDataScope() = {}", currentUser.getDataScope());
        }
    }

    /**
     * 是否存在注解，如果存在就获取
     */
    private DataScope getAnnotationLog(JoinPoint joinPoint) {
        Signature signature = joinPoint.getSignature();
        MethodSignature methodSignature = (MethodSignature) signature;
        Method method = methodSignature.getMethod();
        if (method != null) {
            return method.getAnnotation(DataScope.class);
        }
        return null;
    }

    /**
     * @param where             原SQL Where 条件表达式
     * @param mappedStatementId Mapper接口方法ID
     * @return
     */
    @SneakyThrows
    @Override
    public Expression getSqlSegment(Expression where, String mappedStatementId) {
        log.debug("DataScopePermissionHandler .getSqlSegment");
        DataScopeParam dataScopeParam = threadLocal.get();
        if(dataScopeParam == null || dataScopeParam.isAdmin()){
            return where;
        }

        if (where == null) {
            where = new HexValue(" 1 = 1 ");
        }

        String deptSql = "".equals(dataScopeParam.deptAlias) ? dataScopeParam.deptField : dataScopeParam.deptAlias + "." + dataScopeParam.deptField;

        // 把集合转变为JSQLParser需要的元素列表
        ItemsList itemsList;
        if(CollectionUtils.isEmpty(dataScopeParam.secretary)){
        	//如果权限为空，则只能看自己部门的
            itemsList = new ExpressionList(Collections.singletonList(new LongValue(SecurityUtil.getUser().getOrganizeId())));
        }else {
        	//查看权限内的数据
            itemsList = new ExpressionList(dataScopeParam.secretary.stream().map(LongValue::new).collect(Collectors.toList()));
        }
        InExpression inExpression = new InExpression(new Column(deptSql), itemsList);
        log.debug("where = {}", where);
        log.debug("inExpression = {}", inExpression);
        return new AndExpression(where, inExpression);

    }

    /**
     * ThreadLocal存储对象
     */
    @Data
    @AllArgsConstructor
    static class DataScopeParam{
        /**
         * 部门表的别名
         */
        private String deptAlias;

        /**
         * 部门字段名
         */
        private String deptField;
        
        /**
         * 是否是管理员
         */
        private boolean isAdmin;

        /**
         * 数据权限范围
         */
        private Set<Integer> secretary;
    }
}

将DataScopePermissionHandler处理器添加进Mybatis-Plus配置文件

@Configuration
@MapperScan("com.xx.**.mapper*")
public class MybatisPlusConfig {

	@Resource
    private List<SqlSessionFactory> sqlSessionFactoryList;
    @Autowired
    private DataScopePermissionHandler dataScopePermissionHandler;

    /**
     * 最新版分页插件
      */
    //@Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        // 添加数据权限插件
        DataPermissionInterceptor dataPermissionInterceptor = new DataPermissionInterceptor();
        // 添加自定义的数据权限处理器
        dataPermissionInterceptor.setDataPermissionHandler(dataScopePermissionHandler);
        interceptor.addInnerInterceptor(dataPermissionInterceptor);

        // 分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

	/**
     * 解决拦截器加载顺序问题，ExecutorPlugin 只能添加在 PageInterceptor 后面
     * 在某些场景下，使用了MP自带的分页插件或者PageHelper分页插件，会导致先拼接分页SQL，
     * 再拼接数据权限SQL，导致SQL语法错误。此处调整拦截器加载顺序，可修复此问题。
     */
    @PostConstruct
    public void afterPropertiesSet() {
        new Thread(() -> {
            boolean hasPageInterceptor = false;
            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryList.get(0);
            org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();
            while (!hasPageInterceptor) {
                if (configuration.getInterceptors().stream().anyMatch(interceptor -> interceptor.getClass().equals(PageInterceptor.class))) {
                    hasPageInterceptor = true;
                }
            }
            configuration.addInterceptor(mybatisPlusInterceptor());
        }).start();
    }
}

使用方法

	@DataScope(deptAlias = "sys_user", deptField = "dept_id")
    @GetMapping("/page")
    public ResultEntity page(){
        Page page = PageUtil.getPageFromRequest();
        return ResultEntity.page(sysUserService.page(page));
    }

效果

2021-07-21 16:07:30.500 DEBUG 2584 --- [nio-8010-exec-2] c.v.b.d.DataScopePermissionHandler       : currentUser.getDataScope() = [96, 1, 97, 66, 98, 99, 106, 16, 112, 113, 20, 22, 89, 60, 92, 93, 63]
2021-07-21 16:07:30.596 DEBUG 2584 --- [nio-8010-exec-2] c.v.b.d.DataScopePermissionHandler       : DataScopePermissionHandler.getSqlSegment
2021-07-21 16:07:30.598 DEBUG 2584 --- [nio-8010-exec-2] c.v.b.d.DataScopePermissionHandler       : where = 1 = 1 
2021-07-21 16:07:30.598 DEBUG 2584 --- [nio-8010-exec-2] c.v.b.d.DataScopePermissionHandler       : inExpression = o.organize_id IN (96, 1, 97, 66, 98, 99, 106, 16, 112, 113, 20, 22, 89, 60, 92, 93, 63)
2021-07-21 16:07:30.910 DEBUG 2584 --- [nio-8010-exec-2] c.v.s.mapper.SysUserMapper.page  : ==>  Preparing: SELECT user_id, dept_id, password, username, head_icon, gender FROM sys_user WHERE 1 = 1 AND sys_user.dept IN (96, 1, 97, 66, 98, 99, 106, 16, 112, 113, 20, 22, 89, 60, 92, 93, 63) LIMIT 0, 10
2021-07-21 16:07:30.931 DEBUG 2584 --- [nio-8010-exec-2] c.v.s.mapper.SysUserMapper.page  : ==> Parameters: 
2021-07-21 16:07:30.962 DEBUG 2584 --- [nio-8010-exec-2] c.v.s.mapper.SysUserMapper.page  : <==      Total: 10
2021-07-21 16:07:30.969 DEBUG 2584 --- [nio-8010-exec-2] c.v.b.d.DataScopePermissionHandler       : DataScopePermissionHandler .getSqlSegment
2021-07-21 16:07:30.969 DEBUG 2584 --- [nio-8010-exec-2] c.v.b.d.DataScopePermissionHandler       : where = 1 = 1 
2021-07-21 16:07:30.969 DEBUG 2584 --- [nio-8010-exec-2] c.v.b.d.DataScopePermissionHandler       : inExpression = sys_user.dept_id IN (96, 1, 97, 66, 98, 99, 106, 16, 112, 113, 20, 22, 89, 60, 92, 93, 63)
2021-07-21 16:07:30.969 DEBUG 2584 --- [nio-8010-exec-2] c.v.s.mapper.SysUserMapper.count   : ==>  Preparing: SELECT count(1) FROM sys_user WHERE 1 = 1 AND sys_user.dept_id IN (96, 1, 97, 66, 98, 99, 106, 16, 112, 113, 20, 22, 89, 60, 92, 93, 63)
2021-07-21 16:07:30.969 DEBUG 2584 --- [nio-8010-exec-2] c.v.s.mapper.SysUserMapper.count   : ==> Parameters: 
2021-07-21 16:07:30.972 DEBUG 2584 --- [nio-8010-exec-2] c.v.s.mapper.SysUserMapper.count   : <==      Total: 1

拓展

可根据需要多增加一些过滤条件，实现多种过滤方式

2023.12.21 解决问题

关于拦截器加载顺序问题

PageHelper的PageInterceptor会比Mybatis-Plus的自定义拦截器先执行，在有CASE WHEN、IFNULL 等函数时，PageHelper分页查询会在原SQL的基础上包一层select count(0) from (原sql) … 此时如果自定义权限拦截器拼接的SQL有可能会报错。
解决方案参考下篇文章：
https://blog.csdn.net/PPLIU010119/article/details/125912497

关于ParseException问题

复杂SQL会报错：net.sf.jsqlparser.parser.ParseException，例如子查询、IF、CASE等语句会解析失败，需要引入jsqlparser依赖

	<dependency>
	 	<groupId>com.github.jsqlparser</groupId>
		<artifactId>jsqlparser</artifactId>
		<version>3.1</version>
	</dependency>

华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

实践探讨Python如何进行异常处理与日志记录

华为云开发者联盟

HCDG天津站精彩回顾 | AI高效开发， ModelArts技术动手工作坊

华为云开发者联盟

一次故障演练，十分钟自动搞定?

华为云开发者联盟

所有评论(0)

查看更多评论

大卫没什么

@u011584350

已为社区贡献1条内容