MP自定义拦截器实现数据权限

LiJunYi 2/17/2023 mybatisInterceptor数据权限

# 角色权限划分

编号	名称	描述
1	管理员	全部数据权限
2	普通角色	自定义数据权限
3	部门权限	部门权限
4	部门及以下数据权限	部门及以下数据权限
5	本人数据	本人数据

# 大致思路

1、模仿 PageHelper.startPage 用来声明哪些操作需要做范围限制

2、定义 Mybatis plus 自定义拦截器 Interceptor，用于每次拦截查询 sql语句，附带数据范围权限sql条件

3、因为使用了 PageHelper.startPage 分页插件的使用，先计算总数，怎么在这之前拦截，需要拦截多次

4、考虑到 Mybatis拦截器能够拦截SQL执行的整个过程，因为我们可以考虑SQL执行之前，对SQL进行重写，从而达到数据行权限的目的。

# 部分代码实现步骤

# 权限工具类

public class SecurityUtils {

    /**
     * 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
     *  所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
     */
    protected static final ThreadLocal<String> threadLocal = new ThreadLocal();

    /**
     * 设置权限标识
     */
    public static void startDataScope(){
        threadLocal.set(SecurityConstants.DATA_SCOPE);
    }
    /**
     * 获取权限标识
     */
    public static String getDataScope(){
        return threadLocal.get();
    }
    /**
     * 清除权限标识
     */
    public static void cleanDataScope(){
        threadLocal.remove();
    }
    
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 权限常量

/**
 * 权限相关通用常量
 * 
 */
public class SecurityConstants {
    /**
     * 数据权限过滤关键字
     */
    public static final String DATA_SCOPE = "dataScope";
}

1
2
3
4
5
6
7
8
9
10

# 自定义MybatisPlus拦截器

/**
 * @description 数据权限插件
 */
@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        }
)
@Slf4j
public class MybatisDataPermissionIntercept implements Interceptor {
    CCJSqlParserManager parserManager = new CCJSqlParserManager();

    /**
     * 全部数据权限
     */
    public static final String DATA_SCOPE_ALL = "1";

    /**
     * 自定数据权限
     */
    public static final String DATA_SCOPE_CUSTOM = "2";

    /**
     * 部门数据权限
     */
    public static final String DATA_SCOPE_DEPT = "3";

    /**
     * 部门及以下数据权限
     */
    public static final String DATA_SCOPE_DEPT_AND_CHILD = "4";

    /**
     * 仅本人数据权限
     */
    public static final String DATA_SCOPE_SELF = "5";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        try {
            Object[] args = invocation.getArgs();
            MappedStatement ms = (MappedStatement) args[0];
            Object parameter = args[1];
            RowBounds rowBounds = (RowBounds) args[2];
            ResultHandler resultHandler = (ResultHandler) args[3];
            Executor executor = (Executor) invocation.getTarget();
            CacheKey cacheKey;
            BoundSql boundSql;
            //由于逻辑关系，只会进入一次
            if (args.length == 4) {
                //4 个参数时
                boundSql = ms.getBoundSql(parameter);
                cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
            } else {
                //6 个参数时
                cacheKey = (CacheKey) args[4];
                boundSql = (BoundSql) args[5];
            }
            //TODO 自己要进行的各种处理
            String sql = boundSql.getSql();
            log.info("原始SQL： {}", sql);
            //判断线程内是否有权限信息
            String dataScope = SecurityUtils.getDataScope();
            if (SecurityConstants.DATA_SCOPE.equals(dataScope)){
                // 增强sql
                Select select = (Select) parserManager.parse(new StringReader(sql));
                SelectBody selectBody = select.getSelectBody();
                if (selectBody instanceof PlainSelect) {
                    this.setWhere((PlainSelect) selectBody);
                } else if (selectBody instanceof SetOperationList) {
                    SetOperationList setOperationList = (SetOperationList) selectBody;
                    List<SelectBody> selectBodyList = setOperationList.getSelects();
                    selectBodyList.forEach((s) -> {
                        this.setWhere((PlainSelect) s);
                    });
                }
                String dataPermissionSql = select.toString();
                log.info("增强SQL： {}", dataPermissionSql);
                BoundSql dataPermissionBoundSql = new BoundSql(ms.getConfiguration(), dataPermissionSql, boundSql.getParameterMappings(), parameter);
                return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, dataPermissionBoundSql);
            }
            //注：下面的方法可以根据自己的逻辑调用多次，在分页插件中，count 和 page 各调用了一次
            return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
        } finally {
            //清除线程中权限参数
            SecurityUtils.cleanDataScope();
        }
    }

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

    @Override
    public void setProperties(Properties properties) {
    }

    protected void setWhere(PlainSelect plainSelect) {
        Expression sqlSegment = this.getSqlSegment(plainSelect.getWhere());
        if (null != sqlSegment) {
            plainSelect.setWhere(sqlSegment);
        }
    }

    @SneakyThrows
    public Expression getSqlSegment(Expression where) {
        //用户信息是在gateway中去获取的,获取完之后直接存入到header中,然后再调用过程中传递header
        //getLoginUser 是获取用户信息,在header中去获取 
        JSONObject loginUser = getLoginUser();
        if (loginUser == null){
            return where;
        }
        Integer deptId = loginUser.getInteger("deptId");
        String userId = loginUser.getString("userId");
        JSONArray roles = loginUser.getJSONArray("roles");
        StringBuilder sqlString = new StringBuilder();
        for (Object role : roles) {
            JSONObject roleJson = JSONObject.parseObject(role.toString());
            String dataScopeNum = roleJson.getString(SecurityConstants.DATA_SCOPE);
            Integer roleId = roleJson.getInteger("roleId");
            if (DATA_SCOPE_ALL.equals(dataScopeNum)) {
                // 全部数据权限
                sqlString = new StringBuilder();
                break;
            } else if (DATA_SCOPE_CUSTOM.equals(dataScopeNum)) {
                sqlString.append(" OR `sys_dept`.dept_id IN ( SELECT dept_id FROM `sys_role_dept` WHERE role_id = '")
                        .append(roleId)
                        .append("' ) ");
            } else if (DATA_SCOPE_DEPT.equals(dataScopeNum)) {
                sqlString.append(" OR `sys_dept`.dept_id = '").append(deptId).append("' ");
            } else if (DATA_SCOPE_DEPT_AND_CHILD.equals(dataScopeNum)) {
                sqlString.append(" OR `sys_dept`.dept_id IN ( SELECT dept_id FROM `sys_dept` WHERE dept_id = '")
                        .append(deptId)
                        .append("' or find_in_set( '")
                        .append(deptId)
                        .append("' , ancestors ) ) ");
            }else if (DATA_SCOPE_SELF.equals(dataScopeNum)) {
                //TODO 暂时有问题
                sqlString.append(" OR `sys_user`.user_id = '").append(userId).append("' ");
            }
        }
        if (StringUtils.isNotBlank(sqlString.toString())) {
            if (where == null){
                where = new HexValue(" 1 = 1 ");
            }
            sqlString.insert(0," AND (");
            sqlString.append(")");
            sqlString.delete(7, 9);
            //判断是不是分页， 分页完成之后 清除权限标识
            return CCJSqlParserUtil.parseCondExpression(where + sqlString.toString());
        }else {
            return where;
        }
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159

# 修改MybatisDataPermissionIntercept

修改MybatisDataPermissionIntercept在PageHelper插件后面执行

/**
 * @description 数据权限插件 配置
 * @date 2022-04-01 17:01
 */
@AutoConfigureAfter(PageHelperAutoConfiguration.class)
@Configuration
public class MybatisInterceptorAutoConfiguration implements InitializingBean{

    @Autowired
    private List<SqlSessionFactory> sqlSessionFactoryList;

    @Override
    @PostConstruct
    public void afterPropertiesSet() throws Exception {
        //创建自定义mybatis拦截器，添加到chain的最后面
        MybatisDataPermissionIntercept mybatisInterceptor = new MybatisDataPermissionIntercept();

        for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
            org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();
            //自己添加
            configuration.addInterceptor(mybatisInterceptor);
        }
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 配置spring.factories

在resources下面创建文件夹META-INF,再创建一个文件spring.factories,用spring.factories 是为了让自定义插件在PageHelperAutoConfiguration之后注入到spring中,后注入的先执行

org.springframework.boot.autoconfigure.EnableAutoConfiguration=\
com.zhc.cloud.mybatis.intercept.MybatisInterceptorAutoConfiguration

1
2

# 代码示例

# 分页查询

@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUserPO> implements ISysUserService {
    
	@Autowired
    private SysUserMapper sysUserMapper;
    @Autowired
    private SysDeptMapper sysDeptMapper;

    /***
    * 查询列表
    * @return Result
    */
    @Override
    public Result<?> selectList(SysUserVO sysUserVO) {
        SecurityUtils.startDataScope();
        PageHelper.startPage(sysUserVO.getPageNum(), sysUserVO.getPageSize());
        SysUserDTO sysUserDTO = new SysUserDTO();
        BeanUtils.copyProperties(sysUserVO,sysUserDTO);
        List<SysUserPO> sysUserPOS = sysUserMapper.selectUserList(sysUserDTO);
        List<UserDTO> userDTOS = new ArrayList<>();
        for (SysUserPO sysUserPO : sysUserPOS) {
            UserDTO userDTO = new UserDTO();
            BeanUtils.copyProperties(sysUserPO,userDTO);
            SysDeptPO sysDeptPO = sysDeptMapper.selectById(sysUserPO.getDeptId());
            userDTO.setDeptName(sysDeptPO.getDeptName());
            userDTO.setPhone(SensitiveInfoUtils.mobilePhone(userDTO.getPhone()));
            userDTOS.add(userDTO);
        }
        PageInfo<UserDTO> pageInfo = new PageInfo<>(userDTOS);
        return Result.success(pageInfo);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 单个查询

@Service
public class SysDeptServiceImpl extends ServiceImpl<SysDeptMapper, SysDeptPO> implements ISysDeptService {
    @Autowired
    private SysDeptMapper sysDeptPOMapper;

    @Override
    public Result<?> treeselect(SysDeptVO dept) {
        SecurityUtils.startDataScope();
        LambdaQueryWrapper<SysDeptPO> entityWrapper = new LambdaQueryWrapper<SysDeptPO>();
        entityWrapper.eq((dept.getDeptId()!=null && dept.getDeptId() !=0),SysDeptPO::getDeptId,dept.getDeptId())
                .eq((dept.getParentId()!=null &&dept.getParentId()!=0),SysDeptPO::getParentId,dept.getDeptId())
                .like(StringUtils.isNotBlank(dept.getDeptName()),SysDeptPO::getDeptName,dept.getDeptName())
                .eq(StringUtils.isNotBlank(dept.getStatus()),SysDeptPO::getStatus,dept.getStatus())
                .orderByAsc(SysDeptPO::getParentId,SysDeptPO::getOrderNum);
        List<SysDeptPO> sysDeptPO = sysDeptPOMapper.selectList(entityWrapper);
        if(sysDeptPO == null){
            return Result.success();
        }
        sysDeptPO.sort(Comparator.comparing(SysDeptPO::getParentId));
        List<Long> tempList = new ArrayList<Long>();
        for (SysDeptPO deptPO : sysDeptPO) {
            tempList.add(deptPO.getParentId());
        }
        Long minParentId = Collections.min(tempList);
        return Result.success(buildTree(sysDeptPO,minParentId));
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 注意点

使用mybatis plus自定义拦截器的时候一定要遵守规范,否则可能会引起一些查询异常.
使用mybatis plus自定义拦截器可根据实际业务去进行扩展

我的开发笔记

Choose mode