Data permission notes
It is best to place it under Mapper to avoid problems with queries in Service/Controller that do not require increased data permissions.
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataScope {
/**
* Main table alias associated with the user table
*/
String mainTableAlias() default "";
/**
* Alias of user table
*/
String userAlias() default "";
/**
* The user ID field name in the mainTableAlias table
*/
String userField() default "";
/**
* Associated user table fields, default user table ID
*/
String joinField() default "id";
}
Data Permission Blocker
@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()));
}
/**
* Construct new conditions through API and splice new conditions with previous conditions together
* @param select
* @param index
* @param sql
* @param obj
*/
@Override
protected void processSelect(Select select, int index, String sql, Object obj) {
// Parse SQL
SelectBody selectBody = select.getSelectBody();
try {
if (selectBody instanceof PlainSelect) {
// single sql
dataScopePermissionHandler.buildDataPermission((PlainSelect) selectBody);
} else if (selectBody instanceof SetOperationList) {
//Multiple sql, separated by ; sign, generally not used. For example: 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 failed to add data permission", e);
}
}
}
Annotate aspects and handle data permission SQL splicing
Originally, I wanted to get the annotations directly through the method name in the interceptor, but the PageHelper plug-in is used for paging, which causes the paging to encapsulate a _COUNT method to query the quantity. This method cannot obtain the annotations, which will result in the paging total number not having data permissions. . I don’t know if MybatisPlus’s own paging method has this problem. Therefore, we can only add aspects and use ThreadLocal to store annotation data. In the splicing data permission method, the calculation quantity SQL may form a subquery alias table_count for processing.
@Slf4j
@Aspect
@Component
public class DataScopePermissionHandler {
/**
* Full data access
*/
public static final int DATA_SCOPE_ALL = 1;
/**
* This department (and subordinate departments)
*/
public static final int DATA_SCOPE_DEPT_AND_CHILD = 2;
/**
*Only personal data permissions
*/
public static final int DATA_SCOPE_SELF = 3;
/**
*Designated department
*/
public static final int DATA_SCOPE_CUSTOM = 4;
/**
* User table, organization table, role organization relationship table
*/
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";
/**
* User table: Organization ID field
*/
public static final String FIELD_ORG_ID = "org_id";
/**
* pagehelper paging calculation quantity alias
*/
public static final String TABLE_COUNT = "table_count";
/**
* Record permission-related attribute values through 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);
}
}
/**
* Clear the last saved permission information of the current thread
* @param dataScope
*/
@After("@annotation(dataScope)")
public void clearThreadLocal(DataScope dataScope) {
threadLocal.remove();
}
/**
* Splicing data permissions SQL
* @param plainSelect
* @throws JSQLParserException
*/
public void buildDataPermission(PlainSelect plainSelect) throws JSQLParserException {
DataScopeParam dataScopeParam = threadLocal.get();
if (dataScopeParam == null || dataScopeParam.isAdmin()) {
return;
}
log.info("Original SQL:[{}]", plainSelect.toString());
SecurityUser currentUser = dataScopeParam.currentUser;
Set<Integer> dataRanges = currentUser.getRoles().stream().map(SysRole::getDataRange).collect(Collectors.toSet());
if (CollectionUtils.isEmpty(dataRanges)) {
//Data permission is empty, no data is queried
plainSelect.setWhere(new HexValue(" 1 = 0 "));
return;
}
if (dataRanges.stream().anyMatch(t -> t == DATA_SCOPE_ALL)) {
//Have all data permissions without restrictions
return;
}
//Not encapsulated by 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();
//Subquery to determine whether it is the number of acquisitions encapsulated by pagehelper
if (TABLE_COUNT.equals(fromSubSelect.getAlias().getName()) & amp; & amp; 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) {
//All must be associated with the user table
boolean hasUserTable = false;
if (!dataScopeParam.getUserAlias().equalsIgnoreCase(realSelect.getFromItem().getAlias().getName())) {
//The main table is not the user table
if (!CollectionUtils.isEmpty(joins)) {
//Determine whether there is a user table in the join table
for (Join join : joins) {
// Determine whether the user table exists in the join, if it does not exist, add it
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) {
//There is no user table, add associated user table
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) {
//Specify department
sql = StrUtil.format(" {}.{} IN ( SELECT `value` FROM {} WHERE role_id = {} ) ",
dataScopeParam.getUserAlias(), FIELD_ORG_ID, TABLE_ROLE_ORG, role.getId());
} else {
//This department (and subordinate departments)
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) {
//Only personal data permissions
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) {
//Data permission is greater than 1, use or between
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));
}
// Use separate brackets for data permissions to prevent conflicts with other conditions
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 {
/**
* Main table alias associated with the user table
*/
private String mainTableAlias;
/**
* Alias of user table
*/
private String userAlias;
/**
* User field name
*/
private String userField;
/**
* Associated user table fields, default user table ID
*/
private String joinField;
/**
* Whether administrator
*/
private boolean isAdmin;
/**
*Current logged in user
*/
private SecurityUser currentUser;
}
}
MybatisPlus plug-in configuration
@Configuration
public class MybatisPlusConfig {
@Autowired
private DataScopePermissionHandler dataScopePermissionHandler;
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// data permission
DataScopePermissionInterceptor dataScopePermissionInterceptor = new DataScopePermissionInterceptor();
dataScopePermissionInterceptor.setDataScopePermissionHandler(dataScopePermissionHandler);
interceptor.addInnerInterceptor(dataScopePermissionInterceptor);
return interceptor;
}
}
Reference: Spring Cloud microservices in action – building an enterprise-level development framework (Twenty-eight): Extending the MybatisPlus plug-in DataPermissionInterceptor to implement data permission control
Mybatis-Plus implements data permission filtering through annotations
MyBatis Plus interceptor implements data permission control (full version)
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java skill treeNotesBasic syntax 138353 people are learning the system