利用mybatis-plus和切面实现数据权限控制
前言：主要是通过在查询sql的方法增加注解标识该sql适用的权限控制字段，以及在切面中通过获取当前登录用户的数据权限范围，将数据权限范围和sql适用的权限控制字段通过上下文传递到mybatis-plus的sql拦截器中，动态生成数据权限的条件，将其拼接到查询sql中。
一、定义权限注解，上下文和切面
1、权限注解，这里可以根据自身业务需要进行调整
import java.lang.annotation.*;
/**
* 数据权限注解
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface DataScope {
/**
* 部门字段别名（表字段名）
*/
String orgAlias() default "";
/**
* 用户字段别名（表字段名，支持多个字段，用“:”隔开）
*/
String userAlias() default "";
/**
* 项目字段别名（表字段名）
* @return
*/
String projectAlias() default "";
}
2、上下文封装
/**
* 数据权限范围上下文封装
*/
@Data
public class DataScopeContext {
private final String orgAlias;
private final String userAlias;
private final String projectAlias;
public DataScopeContext(String orgAlias, String userAlias, String projectAlias) {
this.orgAlias = orgAlias;
this.userAlias = userAlias;
this.projectAlias = projectAlias;
}
}
/**
* 数据权限上下文
*/
public class DataScopeHolder {
//存储权限信息
private static final ThreadLocal<List<DataScopeDTO>> PERMISSION_HOLDER = new ThreadLocal<>();
//存储注解配置
private static final ThreadLocal<DataScopeContext> CONTEXT_HOLDER = new ThreadLocal<>();
public static void setPermissions(List<DataScopeDTO> permissions) {
PERMISSION_HOLDER.set(permissions);
}
public static List<DataScopeDTO> getPermissions() {
return PERMISSION_HOLDER.get();
}
public static void setContext(DataScopeContext context) {
CONTEXT_HOLDER.set(context);
}
public static DataScopeContext getContext() {
return CONTEXT_HOLDER.get();
}
public static void clear() {
PERMISSION_HOLDER.remove();
CONTEXT_HOLDER.remove();
}
}
3、数据权限DTO 根据自身业务需求进行调整
@Data
public class DataScopeDTO {
/**
* 数据权限类型 1 人员 2 组织 3 项目
*/
private String type;
/**
* 范围类型 1 仅本人 2 指定人员 3 指定采购组 4 仅本人所在组织 5 指定组织 6 全部组织 7 指定项目 8 指定项目类型 9 指定项目所属组织 10 全部项目
*/
private String scopeType;
/**
* 范围列表
*/
private List<String> scopeList;
}
[{
"type":1,
"scopeType":1,
"scopeList": []
},
{
"type":2,
"scopeType":5,
"scopeList": ["123","234","456"]
}]
/**
* 数据权限类型枚举
*/
@Getter
public enum DataScopeTypeEnum {
PERSONAL("person_data_scope", "人员数据权限"),
ORG("org_data_scope", "组织数据权限"),
PROJECT("project_data_scope", "项目数据权限"),
;
private String code;
private String name;
DataScopeTypeEnum(String code, String name){
this.code = code;
this.name = name;
}
}
/**
* 范围类型枚举
*/
@Getter
public enum ScopeTypeEnum {
ONLY_SELF("1", "仅本人"),
APPOINT_PERSON("2", "指定人员"),
APPOINT_GROUP("3", "指定采购组"),
ONLY_SELF_ORG("4", "仅本人所在组织"),
APPOINT_ORG("5", "指定组织"),
ALL_ORG("6", "全部组织"),
APPOINT_PROJECT("7", "指定项目"),
APPOINT_PROJECT_TYPE("8", "指定项目类型"),
APPOINT_PROJECT_ORG("9", "指定项目所属组织"),
ALL_PROJECT("10", "全部项目"),
ONLY_SELF_ORG_AND_CHILDREN("11", "仅本人所在组织及子组织"),
ONLY_SELF_ORG_AND_PARENT("12", "仅本人所在组织及以上"),
ALL_PERSON("13", "所有人员"),
;
private String code;
private String name;
ScopeTypeEnum(String code, String name){
this.code = code;
this.name = name;
}
}
4、切面
这里获取用户数据权限是通过工具类实现，可以结合自身业务需求进行调整。
/**
* 数据权限范围切面
*/
@Component
@Aspect
@RefreshScope
public class DataScopeAspect {
@Autowired
private SystemProperties systemProperties;
public DataScopeAspect() {
}
@Pointcut("@annotation(cn.iiot.myth.starter.common.annotation.DataScope)")
public void dataScopePointCut(){
}
@Around("dataScopePointCut()")
public Object handleDataScope(ProceedingJoinPoint joinPoint) throws Throwable {
Signature signature = joinPoint.getSignature();
MethodSignature methodSignature = (MethodSignature) signature;
Method method = methodSignature.getMethod();
DataScope dataScope = null;
if(Objects.nonNull(method)){
dataScope = method.getAnnotation(DataScope.class);
}
try {
if(Objects.nonNull(dataScope)){
// 获取当前用户的数据权限信息
UserBaseInfo userBaseInfo = AuthUtils.getUser();
//白名单不做数据权限控制
if(CollUtil.isNotEmpty(systemProperties.getAdminList()) && systemProperties.getAdminList().contains(userBaseInfo.getUid())){
return joinPoint.proceed();
}
//获取用户的数据权限集 自行实现
List<DataScopeDTO> resultList = UserDataScopeUtil.getUserDataScope();
DataScopeHolder.setPermissions(resultList);
//存储注解配置信息
DataScopeContext context = DataScopeHolder.getContext();
if(Objects.nonNull( context)){
context = new DataScopeContext(dataScope.orgAlias(), dataScope.userAlias(), dataScope.projectAlias());
}
DataScopeHolder.setContext(context);
}
return joinPoint.proceed();
} finally {
// 4. 清除上下文
DataScopeHolder.clear();
}
}
}
二、拦截器定义
1、sql拦截器，主要用于动态构建权限sql
public interface QueryInterceptor extends Ordered {
/**
* 拦截处理
*
* @param executor
* @param ms
* @param parameter
* @param rowBounds
* @param resultHandler
* @param boundSql
*/
void intercept(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql);
/**
* 排序
*
* @return int
*/
@Override
default int getOrder() {
return Ordered.LOWEST_PRECEDENCE;
}
}
@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 DataScopeInterceptor implements QueryInterceptor {
/**
* 动态构建权限 SQL
*
* @param originSql 原始 SQL
* @param deptAlias 部门字段别名（从注解获取）
* @param userAlias 用户字段别名（从注解获取）
* @param dataScopeDTOList 组织权限列表
* @param projectAlias 项目字段别名（从注解获取）
*/
private String buildPermissionSql(String originSql, String deptAlias, String userAlias,
String projectAlias,List<DataScopeDTO> dataScopeDTOList) {
StringBuilder finalSql = new StringBuilder(originSql);
// 动态构建条件
StringBuilder conditions = new StringBuilder(" 1=1 AND (");
Boolean shouldOr = false;
// 人员权限条件
for(DataScopeDTO dataScopeDTO : dataScopeDTOList){
log.info("权限类型：{}", JSONUtil.toJsonStr(dataScopeDTO));
if(DataScopeTypeEnum.PERSONAL.getCode().equals(dataScopeDTO.getType()) && StrUtil.isNotBlank(userAlias)){
String[] userAliasArr = userAlias.split(StrUtil.COLON);
for(String userAliasItem : userAliasArr){
if (Objects.nonNull(dataScopeDTO) && CollUtil.isNotEmpty(dataScopeDTO.getScopeList())
&& StrUtil.isNotBlank(userAliasItem) && dataScopeDTO.getScopeList().contains(OrmConstants.ALL)){
String personCondition = String.format(" %s 1 = 1 "
,shouldOr ? "OR" : "");
conditions.append(personCondition);
shouldOr = true;
} else if (Objects.nonNull(dataScopeDTO) && CollUtil.isNotEmpty(dataScopeDTO.getScopeList()) && StrUtil.isNotBlank(userAliasItem)) {
if (dataScopeDTO.getScopeList().size() > 200) {
// 使用临时表语法处理大量数据
StringBuilder tempTableSql = new StringBuilder();
tempTableSql.append(shouldOr ? " OR " : "")
.append(userAliasItem)
.append(" IN (SELECT id FROM (VALUES ");
List<String> scopeList = dataScopeDTO.getScopeList();
for (int i = 0; i < scopeList.size(); i++) {
if (i > 0) {
tempTableSql.append(", ");
}
tempTableSql.append("ROW('").append(scopeList.get(i)).append("')");
}
tempTableSql.append(") AS t(id))");
conditions.append(tempTableSql);
} else {
// 原有的 IN 语法
String personCondition = String.format(" %s %s IN ('%s') ",
shouldOr ? "OR" : "",
userAliasItem,
String.join("','", dataScopeDTO.getScopeList()));
conditions.append(personCondition);
}
shouldOr = true;
} else if (Objects.nonNull(dataScopeDTO) && CollUtil.isEmpty(dataScopeDTO.getScopeList()) && StrUtil.isNotBlank(userAliasItem)) {
String personCondition = String.format(" %s %s != %s "
,shouldOr ? "OR" : "",userAliasItem,userAliasItem);
conditions.append(personCondition);
shouldOr = true;
}
}
}else if(DataScopeTypeEnum.ORG.getCode().equals(dataScopeDTO.getType())){
// 部门权限条件
if (Objects.nonNull(dataScopeDTO) && CollUtil.isNotEmpty(dataScopeDTO.getScopeList())
&& StrUtil.isNotBlank(deptAlias) && dataScopeDTO.getScopeList().contains(OrmConstants.ALL)){
String personCondition = String.format(" %s 1 = 1 "
,shouldOr ? "OR" : "");
conditions.append(personCondition);
shouldOr = true;
} else if (Objects.nonNull(dataScopeDTO) && !StrUtil.equals(ScopeTypeEnum.ALL_ORG.getCode(),dataScopeDTO.getScopeType())
&& CollUtil.isNotEmpty(dataScopeDTO.getScopeList()) && StrUtil.isNotBlank(deptAlias)) {
if (dataScopeDTO.getScopeList().size() > 200) {
// 使用临时表语法处理大量数据
StringBuilder tempTableSql = new StringBuilder();
tempTableSql.append(shouldOr ? " OR " : "")
.append(deptAlias)
.append(" IN (SELECT id FROM (VALUES ");
List<String> scopeList = dataScopeDTO.getScopeList();
for (int i = 0; i < scopeList.size(); i++) {
if (i > 0) {
tempTableSql.append(", ");
}
tempTableSql.append("ROW('").append(scopeList.get(i)).append("')");
}
tempTableSql.append(") AS t(id))");
conditions.append(tempTableSql);
} else {
// 原有的 IN 语法
String personCondition = String.format(" %s %s IN ('%s') ",
shouldOr ? "OR" : "",
deptAlias,
String.join("','", dataScopeDTO.getScopeList()));
conditions.append(personCondition);
}
shouldOr = true;
}else if(Objects.nonNull(dataScopeDTO) && !StrUtil.equals(ScopeTypeEnum.ALL_ORG.getCode(),dataScopeDTO.getScopeType())
&& CollUtil.isEmpty(dataScopeDTO.getScopeList()) && StrUtil.isNotBlank(deptAlias)){
String personCondition = String.format(" %s %s != %s "
,shouldOr ? "OR" : "",deptAlias,deptAlias);
conditions.append(personCondition);
shouldOr = true;
}
}else if(DataScopeTypeEnum.PROJECT.getCode().equals(dataScopeDTO.getType())){
// 项目权限条件
if (Objects.nonNull(dataScopeDTO) && CollUtil.isNotEmpty(dataScopeDTO.getScopeList())
&& StrUtil.isNotBlank(projectAlias) && dataScopeDTO.getScopeList().contains(OrmConstants.ALL)){
String personCondition = String.format(" %s 1 = 1 "
,shouldOr ? "OR" : "");
conditions.append(personCondition);
shouldOr = true;
} else if(Objects.nonNull(dataScopeDTO) && !StrUtil.equals(ScopeTypeEnum.ALL_PROJECT.getCode(), dataScopeDTO.getScopeType())
&& CollUtil.isNotEmpty(dataScopeDTO.getScopeList()) && StrUtil.isNotBlank(projectAlias)){
if (dataScopeDTO.getScopeList().size() > 200) {
// 使用临时表语法处理大量数据
StringBuilder tempTableSql = new StringBuilder();
tempTableSql.append(shouldOr ? " OR " : "")
.append(projectAlias)
.append(" IN (SELECT id FROM (VALUES ");
List<String> scopeList = dataScopeDTO.getScopeList();
for (int i = 0; i < scopeList.size(); i++) {
if (i > 0) {
tempTableSql.append(", ");
}
tempTableSql.append("ROW('").append(scopeList.get(i)).append("')");
}
tempTableSql.append(") AS t(id))");
conditions.append(tempTableSql);
} else {
// 原有的 IN 语法
String personCondition = String.format(" %s %s IN ('%s') ",
shouldOr ? "OR" : "",
projectAlias,
String.join("','", dataScopeDTO.getScopeList()));
conditions.append(personCondition);
}
}else if (Objects.nonNull(dataScopeDTO) && !StrUtil.equals(ScopeTypeEnum.ALL_PROJECT.getCode(), dataScopeDTO.getScopeType())
&& CollUtil.isEmpty(dataScopeDTO.getScopeList()) && StrUtil.isNotBlank(projectAlias)){
String personCondition = String.format(" %s %s != %s "
,shouldOr ? "OR" : "",projectAlias,projectAlias);
conditions.append(personCondition);
shouldOr = true;
}
}
}
if(CollUtil.isEmpty(dataScopeDTOList)){
String alias = deptAlias;
if(StrUtil.isBlank(alias) && StrUtil.isNotBlank(userAlias)){
alias = userAlias.split(StrUtil.COLON)[0];
}
if(StrUtil.isBlank(alias) && StrUtil.isNotBlank(projectAlias)){
alias = projectAlias;
}
String sql = String.format(" %s != %s ",alias, alias);
conditions.append(sql);
}
conditions.append(" )");
// 智能插入 WHERE 位置
return insertWhereClause(finalSql.toString(), conditions.toString());
}
/**
* 智能插入 WHERE 条件
*/
private String insertWhereClause(String sql, String condition) {
String regex = "(?i)\bwhere\b";
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(sql);
// 找到 WHERE 关键词位置
if (matcher.find()) {
int position = matcher.end();
return sql.substring(0, position) + " " + condition + " AND " + sql.substring(position);
} else {
// 没有 WHERE 关键词则添加
int groupByPos = sql.toUpperCase().indexOf("GROUP BY");
int orderByPos = sql.toUpperCase().indexOf("ORDER BY");
int insertPos = Math.min(
groupByPos != -1 ? groupByPos : Integer.MAX_VALUE,
orderByPos != -1 ? orderByPos : Integer.MAX_VALUE
);
if (insertPos != Integer.MAX_VALUE) {
return sql.substring(0, insertPos) + " WHERE " + condition + " " + sql.substring(insertPos);
} else {
return sql + " WHERE " + condition;
}
}
}
@Override
public void intercept(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
//检查是否设置了权限上下文
if (DataScopeHolder.getPermissions() == null) {
return;
}
//从上下文获取当前注解配置
DataScopeContext context = DataScopeHolder.getContext();
if (context == null) {
return;
}
//动态构建 SQL
String finalSql = buildPermissionSql(
boundSql.getSql(),
context.getOrgAlias(),
context.getUserAlias(),
context.getProjectAlias(),
DataScopeHolder.getPermissions()
);
//替换原始 SQL
ReflectUtils.setFieldValue(boundSql, "sql", finalSql);
}
}
2、装配拦截器
/**
* 分页拦截器
*
* @return
*/
@Bean
@ConditionalOnMissingBean
public MybatisPlusInterceptor mybatisPlusInterceptor(ObjectProvider<QueryInterceptor[]> queryInterceptors, OrmConfigProperties ormConfigProperties) {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 配置分页拦截器
MythPaginationInterceptor paginationInterceptor = new MythPaginationInterceptor();
// 配置自定义查询拦截器
QueryInterceptor[] queryInterceptorArray = new QueryInterceptor[1];
//数据权限拦截器
queryInterceptorArray[0] = new DataScopeInterceptor();
AnnotationAwareOrderComparator.sort(queryInterceptorArray);
paginationInterceptor.setQueryInterceptors(queryInterceptorArray);
paginationInterceptor.setMaxLimit(ormConfigProperties.getPageLimit());
paginationInterceptor.setOptimizeJoin(ormConfigProperties.getOptimizeJoin());
interceptor.addInnerInterceptor(paginationInterceptor);
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return interceptor;
}
三、使用
在对应查询sql的方法上添加对应注解
如原始sql： select id,name,code,org_code,create_by from order_bill where code = 'xxx' ;
拼接后的sql会是
select id,name,code,org_code,create_by from order_bill where code = 'xxx' and 1=1 and ( org_code in ('123','321') or create_by in ('10011','10022')) ;
@DataScope(orgAlias = "org_code",userAlias = "t.create_by")
@Override
public <T> PageResult<T> pageList(QueryParam req, Class<T> tClass) {
MPJLambdaWrapper<Example> queryWrapper = wrapper.getWrapper(req, tClass);
IPage<T> page = new Page<>(req.getPageNo(), req.getLimit());
IPage<T> result = ExampleMapper.selectJoinPage(page,tClass, queryWrapper);
if (null == result) {
return null;
}
return PageResult.<T>builder().records(result.getRecords()).total(result.getTotal()).build();
}
© 版权声明
文章版权归作者所有，未经允许请勿转载。
