自定义一个AllbsDataPermissionHandler，使用mybatis plus自带的是可以的，主要考虑到后面还有其他自定义效果，所以单独写了一个
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.schema.Table;
/**
* 接口 AllbsDataPermissionHandler
*
* @author ChenQi
* @date 2023/3/28
*/
public interface AllbsDataPermissionHandler {
/**
* 获取数据权限 SQL 片段
*
* @param table 表相关信息
* @param where 待执行 SQL Where 条件表达式
* @param mappedStatementId Mybatis MappedStatement Id 根据该参数可以判断具体执行方法
* @return JSqlParser 条件表达式，返回的条件表达式会覆盖原有的条件表达式
*/
Expression getSqlSegment(final Table table, Expression where, String mappedStatementId);
}将mybatis plus中的PluginUtils工具类抽出使用，主要是为了解决其中方法jdk17不兼容的问题
当然以下代码还没改，后续更改了之后文档再更新
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import java.lang.reflect.Proxy;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
/**
* 说明: 从mybatis plus中抽出来主要是为了解决jdk17不兼容 realTarget 方法的问题、减少cpu占用
*/
public abstract class PluginUtils {
public static final String DELEGATE_BOUNDSQL_SQL = "delegate.boundSql.sql";
/**
* 获得真正的处理对象,可能多层代理.
*/
@SuppressWarnings("unchecked")
public static <T> T realTarget(Object target) {
if (Proxy.isProxyClass(target.getClass())) {
MetaObject metaObject = SystemMetaObject.forObject(target);
return realTarget(metaObject.getValue("h.target"));
}
return (T) target;
}
/**
* 给 BoundSql 设置 additionalParameters
*
* @param boundSql BoundSql
* @param additionalParameters additionalParameters
*/
public static void setAdditionalParameter(BoundSql boundSql, Map<String, Object> additionalParameters) {
additionalParameters.forEach(boundSql::setAdditionalParameter);
}
public static PluginUtils.MPBoundSql mpBoundSql(BoundSql boundSql) {
return new PluginUtils.MPBoundSql(boundSql);
}
public static PluginUtils.MPStatementHandler mpStatementHandler(StatementHandler statementHandler) {
statementHandler = realTarget(statementHandler);
MetaObject object = SystemMetaObject.forObject(statementHandler);
return new PluginUtils.MPStatementHandler(SystemMetaObject.forObject(object.getValue("delegate")));
}
/**
* {@link org.apache.ibatis.executor.statement.BaseStatementHandler}
*/
public static class MPStatementHandler {
private final MetaObject statementHandler;
MPStatementHandler(MetaObject statementHandler) {
this.statementHandler = statementHandler;
}
public ParameterHandler parameterHandler() {
return get("parameterHandler");
}
public MappedStatement mappedStatement() {
return get("mappedStatement");
}
public Executor executor() {
return get("executor");
}
public PluginUtils.MPBoundSql mPBoundSql() {
return new PluginUtils.MPBoundSql(boundSql());
}
public BoundSql boundSql() {
return get("boundSql");
}
public Configuration configuration() {
return get("configuration");
}
@SuppressWarnings("unchecked")
private <T> T get(String property) {
return (T) statementHandler.getValue(property);
}
}
/**
* {@link BoundSql}
*/
public static class MPBoundSql {
private final MetaObject boundSql;
private final BoundSql delegate;
MPBoundSql(BoundSql boundSql) {
this.delegate = boundSql;
this.boundSql = SystemMetaObject.forObject(boundSql);
}
public String sql() {
return delegate.getSql();
}
public void sql(String sql) {
boundSql.setValue("sql", sql);
}
public List<ParameterMapping> parameterMappings() {
List<ParameterMapping> parameterMappings = delegate.getParameterMappings();
return new ArrayList<>(parameterMappings);
}
public void parameterMappings(List<ParameterMapping> parameterMappings) {
boundSql.setValue("parameterMappings", Collections.unmodifiableList(parameterMappings));
}
public Object parameterObject() {
return get("parameterObject");
}
public Map<String, Object> additionalParameters() {
return get("additionalParameters");
}
@SuppressWarnings("unchecked")
private <T> T get(String property) {
return (T) boundSql.getValue(property);
}
}
}自定义一个注解，用于标注实体类中的需要过滤的字段
目前只取获取到的第一个字段，你要是标多个我也没办法
import java.lang.annotation.*;
/**
* 注解 ScopeField
*
* @author ChenQi
* @date 2023/3/28
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.ANNOTATION_TYPE})
public @interface ScopeField {
/**
* 字段名（该值可无）
*/
String value() default "";
}数据权限处理器
用于传递重新拼sql时所需的一些参数，新增的情况暂时还未处理，后续继续更新
import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.*;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import java.sql.Connection;
import java.util.List;
/**
* 数据权限处理器
* 类 DataPermissionInterceptor
*
* @author ChenQi
* @date 2023/3/28
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@SuppressWarnings({"rawtypes"})
public class DataPermissionInterceptor extends JsqlParserSupport implements InnerInterceptor {
private AllbsDataPermissionHandler dataPermissionHandler;
@Override
public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
MappedStatement ms = mpSh.mappedStatement();
SqlCommandType sct = ms.getSqlCommandType();
if (sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE || sct == SqlCommandType.SELECT) {
if (InterceptorIgnoreHelper.willIgnoreDataPermission(ms.getId())) {
return;
}
PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
mpBs.sql(parserMulti(mpBs.sql(), ms.getId()));
}
}
/**
* 查询
*/
@Override
protected void processSelect(Select select, int index, String sql, Object obj) {
SelectBody selectBody = select.getSelectBody();
if (selectBody instanceof PlainSelect) {
this.setWhere((PlainSelect) selectBody, (String) obj);
} else if (selectBody instanceof SetOperationList) {
SetOperationList setOperationList = (SetOperationList) selectBody;
List<SelectBody> selectBodyList = setOperationList.getSelects();
selectBodyList.forEach(s -> this.setWhere((PlainSelect) s, (String) obj));
}
}
/**
* 新增
*/
@Override
protected void processInsert(Insert insert, int index, String sql, Object obj) {
throw new UnsupportedOperationException();
}
/**
* 删除
*/
@Override
protected void processDelete(Delete delete, int index, String sql, Object obj) {
final Expression sqlSegment = getUpdateOrDeleteExpression(delete.getTable(), delete.getWhere(), (String) obj);
if (null != sqlSegment) {
delete.setWhere(sqlSegment);
}
}
/**
* 更新
*/
@Override
protected void processUpdate(Update update, int index, String sql, Object obj) {
final Expression sqlSegment = getUpdateOrDeleteExpression(update.getTable(), update.getWhere(), (String) obj);
if (null != sqlSegment) {
update.setWhere(sqlSegment);
}
}
/**
* 设置 where 条件
*
* @param plainSelect 查询对象
* @param whereSegment 查询条件片段
*/
protected void setWhere(PlainSelect plainSelect, String whereSegment) {
Table table = (Table) plainSelect.getFromItem();
Expression sqlSegment = dataPermissionHandler.getSqlSegment(table, plainSelect.getWhere(), whereSegment);
if (null != sqlSegment) {
plainSelect.setWhere(sqlSegment);
}
}
protected Expression getUpdateOrDeleteExpression(final Table table, final Expression where, final String whereSegment) {
return dataPermissionHandler.getSqlSegment(table, where, whereSegment);
}
}核心，进行sql重新封装的处理
import cn.allbs.allbsjwt.config.utils.SecurityUtils;
import cn.allbs.allbsjwt.config.vo.SysUser;
import cn.allbs.common.constant.StringPool;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import java.util.LinkedList;
import java.util.List;
import java.util.Optional;
import java.util.Set;
/**
* 类 CustomPermissionHandler
*
* @author ChenQi
* @date 2023/3/28
*/
@Slf4j
public class CustomPermissionHandler implements AllbsDataPermissionHandler {
private final static String DEFAULT_FILTER_FIELD = "ent_id";
/**
* 获取数据权限 SQL 片段
*
* @param where 待执行 SQL Where 条件表达式
* @param mappedStatementId Mybatis MappedStatement Id 根据该参数可以判断具体执行方法
* @return JSqlParser 条件表达式
*/
@Override
public Expression getSqlSegment(final Table table, Expression where, String mappedStatementId) {
// 在有权限的情况下查询用户所关联的企业列表
SysUser sysUser = SecurityUtils.getUser();
// 如果非权限用户则不往下执行，执行原sql
if (sysUser == null) {
return where;
}
Set<Long> permissionEntList = sysUser.getEntIdList();
// if (permissionEntList.size() == 0) {
// return where;
// }
TableInfo tableInfo = TableInfoHelper.getTableInfo(table.getName());
String fieldName = tableInfo.getFieldList().stream()
.filter(a -> a.getField().getAnnotation(ScopeField.class) != null)
.map(a -> a.getField().getAnnotation(ScopeField.class).value())
.findFirst()
.orElse(DEFAULT_FILTER_FIELD);
Alias fromItemAlias = table.getAlias();
String finalFieldName = Optional.ofNullable(fromItemAlias).map(a -> a.getName() + StringPool.DOT + fieldName).orElse(fieldName);
if (permissionEntList.size() > 1) {
// 把集合转变为 JSQLParser需要的元素列表
InExpression inExpression = new InExpression(new Column(finalFieldName), getItemList(permissionEntList));
// 组装sql
return where == null ? inExpression : new AndExpression(where, inExpression);
}
// 设置where
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new Column(finalFieldName));
equalsTo.setRightExpression(new LongValue(permissionEntList.stream().findFirst().orElse(0L)));
return where == null ? equalsTo : new AndExpression(where, equalsTo);
}
private ItemsList getItemList(Set<Long> permissionEntList) {
List<Expression> list = new LinkedList<>();
for (Long aLong : permissionEntList) {
try {
Expression expression = CCJSqlParserUtil.parseCondExpression(StrUtil.join(",", aLong));
list.add(expression);
} catch (JSQLParserException e) {
log.error("筛选数据转换为表达式失败!" + e.getLocalizedMessage());
}
}
return new ExpressionList(list);
}
}最后将改插件注册进mybatis plus即可
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* 类 MybatisPlusCustomConfig
*
* @author ChenQi
* @date 2023/3/28
*/
@Configuration
public class MybatisPlusCustomConfig {
@Bean
public MybatisPlusInterceptor customMybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 数据权限
DataPermissionInterceptor dataPermissionInterceptor = new DataPermissionInterceptor(new CustomPermissionHandler());
interceptor.addInnerInterceptor(dataPermissionInterceptor);
return interceptor;
}
}说明
-
所有sql将默认拦截并自动拼接筛选条件
-
如果想要对部分sql不进行筛选，则在dao层的类名上添加注解
@InterceptorIgnore或者dao层的某个方法上添加注解@InterceptorIgnore
-
如果想要使用mybatis plus自带的sdk并使其中某些方法不进行数据筛选，则重新写一个接口集成BaseMapper,单独对其中的某个方法添加注解。
import com.baomidou.mybatisplus.annotation.InterceptorIgnore;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;
import java.io.Serializable;
import java.util.List;
/**
* 类 DataScopeMapper
*
* @author ChenQi
* @date 2023/3/28
*/
@Mapper
@Component
public interface DataScopeMapper<T> extends BaseMapper<T> {
/**
* 根据 ID 查询
*
* @param id 主键ID
*/
@Override
@InterceptorIgnore
T selectById(Serializable id);
/**
* 根据 entity 条件，查询全部记录
*
* @param queryWrapper 实体对象封装操作类（可以为 null）
*/
@Override
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 entity 条件，查询全部记录（并翻页）
*
* @param page 分页查询条件（可以为 RowBounds.DEFAULT）
* @param queryWrapper 实体对象封装操作类（可以为 null）
*/
@Override
<P extends IPage<T>> P selectPage(P page, @Param(
Constants.WRAPPER) Wrapper<T> queryWrapper);
}- 可以进行功能反转，比如目前是全部sql拦截，添加注解不拦截。可以修改
beforePrepare使其默认全部不拦截，添加了该注解的方法拦截sql并重新组装。 - 如果不想违反mybatis plus注解本意，可以重新自定义一个注解来达成上述效果，本文不做展示，参考上一篇即可实现。
添加新增、更新、批量新增、批量更新的数据过滤
使用
AllbsDataPermissionHandler添加两个方法用于处理新增和更新。具体实现效果看
/**
* 新增数据时 判断是否存在越权行为，如果存在这种行为则进行拦截并重组sql
*
* @param insertStmt Insert
* @param boundSql BoundSql
*/
void insertParameter(Insert insertStmt, BoundSql boundSql);
/**
* 更新数据时 判断是否存在越权行为，如果存在这种行为则进行拦截并重组sql
*
* @param updateStmt Update
* @param mappedStatement MappedStatement
* @param boundSql BoundSql
*/
void updateParameter(Update updateStmt, MappedStatement mappedStatement, BoundSql boundSql);CustomPermissionHandler的实现
package cn.allbs.allbsjwt.config.datascope.mapper;
import cn.allbs.allbsjwt.config.utils.SecurityUtils;
import cn.allbs.allbsjwt.config.vo.SysUser;
import cn.allbs.common.constant.StringPool;
import cn.allbs.mybatis.datascope.DataPmsHandler;
import cn.allbs.mybatis.datascope.ScopeField;
import cn.allbs.mybatis.execption.UserOverreachException;
import cn.allbs.mybatis.utils.PluginUtils;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Optional;
import java.util.Set;
/**
* 类 CustomPermissionHandler
*
* @author ChenQi
* @date 2023/3/28
*/
@Slf4j
@Component
public class CustomPermissionHandler implements DataPmsHandler {
private final static String DEFAULT_FILTER_FIELD = "ent_id";
/**
* 获取数据权限 SQL 片段
*
* @param where 待执行 SQL Where 条件表达式
* @param mappedStatementId Mybatis MappedStatement Id 根据该参数可以判断具体执行方法
* @return JSqlParser 条件表达式
*/
@Override
public Expression getSqlSegment(final Table table, Expression where, String mappedStatementId) {
// 在有权限的情况下查询用户所关联的企业列表
SysUser sysUser = SecurityUtils.getUser();
// 如果非权限用户则不往下执行，执行原sql
if (sysUser == null) {
return where;
}
Set<Long> permissionEntList = sysUser.getEntIdList();
// if (permissionEntList.size() == 0) {
// return where;
// }
TableInfo tableInfo = TableInfoHelper.getTableInfo(table.getName());
String fieldName = tableInfo.getFieldList().stream()
.filter(a -> a.getField().getAnnotation(ScopeField.class) != null)
.map(a -> a.getField().getAnnotation(ScopeField.class).value())
.findFirst()
.orElse(DEFAULT_FILTER_FIELD);
Alias fromItemAlias = table.getAlias();
String finalFieldName = Optional.ofNullable(fromItemAlias).map(a -> a.getName() + StringPool.DOT + fieldName).orElse(fieldName);
if (permissionEntList.size() > 1) {
// 把集合转变为 JSQLParser需要的元素列表
InExpression inExpression = new InExpression(new Column(finalFieldName), PluginUtils.getItemList(permissionEntList));
// 组装sql
return where == null ? inExpression : new AndExpression(where, inExpression);
}
// 设置where
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new Column(finalFieldName));
equalsTo.setRightExpression(new LongValue(permissionEntList.stream().findFirst().orElse(0L)));
return where == null ? equalsTo : new AndExpression(where, equalsTo);
}
@Override
public void updateParameter(Update updateStmt, MappedStatement mappedStatement, BoundSql boundSql) {
TableInfo tableInfo = TableInfoHelper.getTableInfo(updateStmt.getTable().getName());
parameterHandler(tableInfo.getFieldList(), boundSql);
}
@Override
public void insertParameter(Insert insertStmt, BoundSql boundSql) {
TableInfo tableInfo = TableInfoHelper.getTableInfo(insertStmt.getTable().getName());
parameterHandler(tableInfo.getFieldList(), boundSql);
}
private void parameterHandler(List<TableFieldInfo> fieldList, BoundSql boundSql) {
// 过滤数据
SysUser sysUser = SecurityUtils.getUser();
// 如果当前用户是超级管理员，不处理
if (sysUser.getId() == 1L) {
return;
}
// 获取当前用户所具备的ent_id
Set<Long> permissionEntList = sysUser.getEntIdList();
// 获取当前表中需要权限过滤的字段名称
String fieldName = fieldList.stream()
.filter(a -> a.getField().getAnnotation(ScopeField.class) != null)
.map(a -> a.getField().getAnnotation(ScopeField.class).value())
.findFirst()
.orElse(DEFAULT_FILTER_FIELD);
MetaObject metaObject = SystemMetaObject.forObject(boundSql.getParameterObject());
for (ParameterMapping parameterMapping : boundSql.getParameterMappings()) {
String propertyName = parameterMapping.getProperty();
if (propertyName.startsWith("ew.paramNameValuePairs")) {
continue;
}
String[] arr = propertyName.split("\\.");
String propertyNameTrim = arr[arr.length - 1].replace("_", "").toUpperCase();
if (fieldName.replaceAll("[._\\-$]", "").toUpperCase().equals(propertyNameTrim)) {
if (!Optional.ofNullable(metaObject.getValue(propertyName)).isPresent()) {
return;
}
long currentEntId = Long.parseLong(metaObject.getValue(propertyName).toString());
// 判断是否在权限范围内
if (permissionEntList.contains(currentEntId)) {
metaObject.setValue(propertyName, currentEntId);
} else {
// 可以直接抛出异常 or 使用当前用户的ent_id 替换插入值 or 直接忽略当前插入sql但不抛出异常
throw new UserOverreachException();
}
}
}
}
}
关联查询
更新
删除
代码地址
引用站外地址
权限过滤使用demo
github
上文代码在目录cn.allbs.allbsjwt.config.datascope下，可以查看git提交记录，后面使用封装好的allbs-mybatis包就被我删了。
引用站外地址
权限过滤封装源码
github
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ALLBS！
- 支付宝
- 微信
- qq
相关推荐
评论