First, how to use
Data permission control requires filtering the queried data, and the least intrusion into the business is to use Mybatis or the slices of the database connection pool to modify the SQL statements of existing services. After the slicing logic is completed, only a small amount of markup code needs to be added to the business to control data permissions. This modification method has no or only a small amount of intrusion into the logic of the old business, and basically does not affect the logic and readability of the old business. For new services, business developers don't need to pay much attention to permissions and can focus on business logic.
Due to the large number of database connection pools used in department code, which is not conducive to the rapid completion of the slice control logic, and the SQL splicing part is basically only directly spliced by Mybatis and Java strings, so the Mybatis slicing method is used to complete the data permission control logic. Add annotations to the APIs of the Mybatis mapper file, specifying the types of permissions to be controlled, the names of tables to be controlled, and the names of columns to control the data permissions of the APIs.
Since the same API in the MyBatis mapper file is called in multiple places, some need to control data permissions, and some do not, a permission control method is added: pass permission control rules through ThreadLocal to control data permissions during the current SQL execution.
The format of the permission control rule is as follows:
Privilege restriction rule code 1 (table name 1, field name 1, table name 2, field name 2); Privilege restriction rule code 2 (table name 3, field name 3, table name 4, field name 4)
例如：enterprise(channel.enterprise_code); account(table.column); channel(table3.id)
The context transfer tool class is as follows, and the ThreadLocal can be passed by using a callback to prevent the user from forgetting to clear the context.
public class DataAuthContextUtil {
/**
* 不方便使用注解的地方，可以直接使用上下文设置数据规则
*/
private static ThreadLocal<String> useDataAuth = new ThreadLocal<>();
/**
* 有的sql只在部分情况下需要使用数据权限限制
* 上下文和注解中均可设置数据权限规则，都设置时，上下文中的优先
*
* @param supplier
*/
public static <T> T executeSqlWithDataAuthRule(String rule, Supplier<T> supplier) {
try {
useDataAuth.set(rule);
return supplier.get();
} finally {
useDataAuth.remove();
}
}
/**
* 获取数据权限标志
*
* @return
*/
public static String getUseDataAuthRule() {
return useDataAuth.get();
}
} Second, the process of slicing implementation
3. Other technical details
(1) Obtain the original SQL statement in the slice
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import reactor.util.function.Tuple2;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
@Component
@Intercepts({
// @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@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 DataAuthInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
try {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
BoundSql boundSql = mappedStatement.getBoundSql(invocation.getArgs()[1]);
String sql = boundSql.getSql();
} catch (Exception e) {
log.error("数据权限添加出错,当前sql未加数据权限限制！", e);
throw e;
}
return invocation.proceed();
}
} (2) Add the permission items to the original SQL statement
Use the AST parsing function that comes with DRUID to modify the SQL statement, and the code is as follows:
/**
* 权限限制写入sql
*
* @param sql
* @param tableAuthMap key:table value1:column value2:values权限项
* @return
*/
public static StringBuilder addAuthLimitToSql(String sql, Map<String, Tuple2<String, Set<String>>> tableAuthMap) {
List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, "mysql");
StringBuilder authSql = new StringBuilder();
for (SQLStatement stmt : stmtList) {
stmt.accept(new MySqlASTVisitorAdapter() {
@Override
public boolean visit(MySqlSelectQueryBlock x) {
SQLTableSource from = x.getFrom();
Set<String> tableList = new HashSet<>();
getTableList(from, tableList);
for (String tableName : tableList) {
if (tableAuthMap.containsKey(tableName)) {
x.addCondition(tableName + "in (...略)");
}
}
return true;
}
});
authSql.append(stmt);
}
return authSql;
}
private static void getTableList(SQLTableSource from, Set<String> tableList) {
if (from instanceof SQLExprTableSource) {
SQLExprTableSource tableSource = (SQLExprTableSource) from;
String name = tableSource.getTableName().replace("`", "");
tableList.add(name);
String alias = tableSource.getAlias();
if (StringUtils.isNotBlank(alias)) {
tableList.add(alias.replace("`", ""));
}
} else if (from instanceof SQLJoinTableSource) {
SQLJoinTableSource joinTableSource = (SQLJoinTableSource) from;
getTableList(joinTableSource.getLeft(), tableList);
getTableList(joinTableSource.getRight(), tableList);
} else if (from instanceof SQLSubqueryTableSource) {
SQLSubqueryTableSource tableSource = (SQLSubqueryTableSource) from;
tableList.add(tableSource.getAlias().replace("`", ""));
} else if (from instanceof SQLLateralViewTableSource) {
log.warn("SQLLateralView不用处理");
} else if (from instanceof SQLUnionQueryTableSource) {
//union 不需要处理
log.warn("union不用处理");
} else if (from instanceof SQLUnnestTableSource) {
log.warn("Unnest不用处理");
} else if (from instanceof SQLValuesTableSource) {
log.warn("Values不用处理");
} else if (from instanceof SQLWithSubqueryClause) {
log.warn("子查询不用处理");
} else if (from instanceof SQLTableSourceImpl) {
log.warn("Impl不用处理");
}
}
} (3) Write the modified SQL back to mybatis
MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
BoundSql boundSql = ms.getBoundSql(invocation.getArgs()[1]);
// 组装 MappedStatement
MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), new MySqlSource(boundSql), ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
StringBuilder keyProperties = new StringBuilder();
for (String keyProperty : ms.getKeyProperties()) {
keyProperties.append(keyProperty).append(",");
}
keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
builder.keyProperty(keyProperties.toString());
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
MappedStatement newMappedStatement = builder.build();
MetaObject metaObject = MetaObject.forObject(newMappedStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
metaObject.setValue("sqlSource.boundSql.sql", newSql);
invocation.getArgs()[0] = newMappedStatement; References: https://blog.csdn.net/e_anjing/article/details/79102693