数据权限注解
最好放在Mapper下,以免Service/Controller里有不需要增加数据权限的查询出现问题
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataScope {
/**
* 关联用户表的主表别名
*/
String mainTableAlias() default "";
/**
* 用户表的别名
*/
String userAlias() default "";
/**
* mainTableAlias表里的用户ID字段名
*/
String userField() default "";
/**
* 关联用户表字段,默认用户表ID
*/
String joinField() default "id";
}
数据权限拦截器
@Slf4j
public class DataScopePermissionInterceptor extends JsqlParserSupport implements InnerInterceptor {
private DataScopePermissionHandler dataScopePermissionHandler;
public void setDataScopePermissionHandler(DataScopePermissionHandler dataScopePermissionHandler) {
this.dataScopePermissionHandler = dataScopePermissionHandler;
}
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) {
return;
}
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
mpBs.sql(this.parserSingle(mpBs.sql(), ms.getId()));
}
/**
* 通过API构建出新的条件,并将新的条件和之前的条件拼接在一起
* @param select
* @param index
* @param sql
* @param obj
*/
@Override
protected void processSelect(Select select, int index, String sql, Object obj) {
// 解析SQL
SelectBody selectBody = select.getSelectBody();
try {
if (selectBody instanceof PlainSelect) {
// 单个sql
dataScopePermissionHandler.buildDataPermission((PlainSelect) selectBody);
} else if (selectBody instanceof SetOperationList) {
//多个sql,用;号隔开,一般不会用到。例如:select * from user;select * from role;
SetOperationList setOperationList = (SetOperationList) selectBody;
List<SelectBody> selectBodyList = setOperationList.getSelects();
for (SelectBody s : selectBodyList) {
dataScopePermissionHandler.buildDataPermission((PlainSelect) s);
}
}
} catch (Exception e) {
log.error("sql添加数据权限失败", e);
}
}
}
注解切面,并处理数据权限SQL拼接
原本是想在拦截器里通过方法名直接获取注解,但是分页用的是PageHelper插件,导致分页会封装一个_COUNT方法查询数量,这个方法就获取不到注解,会导致分页total数量没有做数据权限。不知道MybatisPlus自己的分页方法有没有这个问题。所以只能增加切面用ThreadLocal来存储注解数据。拼接数据权限方法中也有对计算数量sql可能会形成子查询别名为table_count做处理。
@Slf4j
@Aspect
@Component
public class DataScopePermissionHandler {
/**
* 全部数据权限
*/
public static final int DATA_SCOPE_ALL = 1;
/**
* 本部门(及下级部门)
*/
public static final int DATA_SCOPE_DEPT_AND_CHILD = 2;
/**
* 仅本人数据权限
*/
public static final int DATA_SCOPE_SELF = 3;
/**
* 指定部门
*/
public static final int DATA_SCOPE_CUSTOM = 4;
/**
* 用户表、组织表、角色组织关系表
*/
public static final String TABLE_USER = "sys_user";
public static final String TABLE_ORG = "sys_org";
public static final String TABLE_ROLE_ORG = "sys_role_org";
/**
* 用户表:组织ID字段
*/
public static final String FIELD_ORG_ID = "org_id";
/**
* pagehelper分页计算数量别名
*/
public static final String TABLE_COUNT = "table_count";
/**
* 通过ThreadLocal记录权限相关的属性值
*/
ThreadLocal<DataScopeParam> threadLocal = new ThreadLocal<>();
@Before("@annotation(dataScope)")
public void doBefore(JoinPoint point, DataScope dataScope) {
SecurityUser currentUser = SecurityUserUtil.getCurrentUser();
if (currentUser != null) {
DataScopeParam dataScopeParam = new DataScopeParam(dataScope.mainTableAlias(), dataScope.userAlias(), dataScope.userField(), dataScope.joinField(), SecurityUserUtil.isAdmin(), currentUser);
threadLocal.set(dataScopeParam);
}
}
/**
* 清空当前线程上次保存的权限信息
* @param dataScope
*/
@After("@annotation(dataScope)")
public void clearThreadLocal(DataScope dataScope) {
threadLocal.remove();
}
/**
* 拼接数据权限SQL
* @param plainSelect
* @throws JSQLParserException
*/
public void buildDataPermission(PlainSelect plainSelect) throws JSQLParserException {
DataScopeParam dataScopeParam = threadLocal.get();
if (dataScopeParam == null || dataScopeParam.isAdmin()) {
return;
}
log.info("原始SQL:[{}]", plainSelect.toString());
SecurityUser currentUser = dataScopeParam.currentUser;
Set<Integer> dataRanges = currentUser.getRoles().stream().map(SysRole::getDataRange).collect(Collectors.toSet());
if (CollectionUtils.isEmpty(dataRanges)) {
//数据权限为空,不查询数据
plainSelect.setWhere(new HexValue(" 1 = 0 "));
return;
}
if (dataRanges.stream().anyMatch(t -> t == DATA_SCOPE_ALL)) {
//有全部数据权限,不加限制
return;
}
//未被pagehelper count封装过的
PlainSelect realSelect = plainSelect;
List<Expression> expressions = new ArrayList<>();
for (SysRole role : currentUser.getRoles()) {
int dataRange = role.getDataRange();
if (plainSelect.getFromItem() instanceof SubSelect) {
SubSelect fromSubSelect = (SubSelect) plainSelect.getFromItem();
//子查询,判断是否为pagehelper封装的获取数量的
if (TABLE_COUNT.equals(fromSubSelect.getAlias().getName()) && CollectionUtils.isEmpty(plainSelect.getJoins())) {
realSelect = (PlainSelect) fromSubSelect.getSelectBody();
}
}
List<Join> joins = realSelect.getJoins();
String sql;
if (DATA_SCOPE_CUSTOM == dataRange || DATA_SCOPE_DEPT_AND_CHILD == dataRange) {
//都要关联user表
boolean hasUserTable = false;
if (!dataScopeParam.getUserAlias().equalsIgnoreCase(realSelect.getFromItem().getAlias().getName())) {
//主表不是user表
if (!CollectionUtils.isEmpty(joins)) {
//判断join的表里有没有user表
for (Join join : joins) {
// 判断join里面是否存在user表,不存在则新增
FromItem rightItem = join.getRightItem();
if (rightItem instanceof Table) {
Table table = (Table) rightItem;
if (table.getName().equalsIgnoreCase(TABLE_USER)) {
hasUserTable = true;
}
}
}
}
} else {
hasUserTable = true;
}
if (!hasUserTable) {
//没有user表,增加关联用户表
Table innerTable = new Table(TABLE_USER).withAlias(new Alias(dataScopeParam.getUserAlias(), false));
Join join = new Join();
join.withRightItem(innerTable);
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new StringValue(StrUtil.format("{}.{}", dataScopeParam.getMainTableAlias(), dataScopeParam.getUserField())));
equalsTo.setRightExpression(new Column(innerTable, dataScopeParam.getJoinField()));
join.withOnExpression(equalsTo);
realSelect.addJoins(join);
}
if (DATA_SCOPE_CUSTOM == dataRange) {
//指定部门
sql = StrUtil.format(" {}.{} IN ( SELECT `value` FROM {} WHERE role_id = {} ) ",
dataScopeParam.getUserAlias(), FIELD_ORG_ID, TABLE_ROLE_ORG, role.getId());
} else {
//本部门(及下级部门)
sql = StrUtil.format(" {}.{} IN ( SELECT id FROM {} WHERE id = {} or find_in_set( {} , ancestors ) ) ",
dataScopeParam.getUserAlias(), FIELD_ORG_ID, TABLE_ORG, currentUser.getOrgId(), currentUser.getOrgId());
}
} else if (DATA_SCOPE_SELF == dataRange) {
//仅本人数据权限
sql = StrUtil.format(" {}.{} = {} ", dataScopeParam.getMainTableAlias(), dataScopeParam.getUserField(), currentUser.getId());
} else {
sql = " 1 = 0";
}
Expression expression = CCJSqlParserUtil.parseCondExpression(sql);
expressions.add(expression);
}
Expression dataExpression;
if (expressions.size() > 1) {
//数据权限大于1个,之间用或
OrExpression orExpression = new OrExpression(expressions.get(0), expressions.get(1));
for (int i = 2; i < expressions.size(); i++) {
orExpression = new OrExpression(orExpression, expressions.get(i));
}
// 数据权限使用单独的括号 防止与其他条件冲突
dataExpression = new Parenthesis(orExpression);
} else {
dataExpression = expressions.get(0);
}
Expression where = realSelect.getWhere();
if (where != null) {
where = new AndExpression(where, dataExpression);
} else {
where = dataExpression;
}
realSelect.setWhere(where);
}
@Data
@AllArgsConstructor
static class DataScopeParam {
/**
* 关联用户表的主表别名
*/
private String mainTableAlias;
/**
* 用户表的别名
*/
private String userAlias;
/**
* 用户字段名
*/
private String userField;
/**
* 关联用户表字段,默认用户表ID
*/
private String joinField;
/**
* 是否管理员
*/
private boolean isAdmin;
/**
* 当前登录用户
*/
private SecurityUser currentUser;
}
}
MybatisPlus插件配置
@Configuration
public class MybatisPlusConfig {
@Autowired
private DataScopePermissionHandler dataScopePermissionHandler;
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 数据权限
DataScopePermissionInterceptor dataScopePermissionInterceptor = new DataScopePermissionInterceptor();
dataScopePermissionInterceptor.setDataScopePermissionHandler(dataScopePermissionHandler);
interceptor.addInnerInterceptor(dataScopePermissionInterceptor);
return interceptor;
}
}
参考:SpringCloud微服务实战——搭建企业级开发框架(二十八):扩展MybatisPlus插件DataPermissionInterceptor实现数据权限控制
Mybatis-Plus通过注解形式实现数据权限过滤