数据权限解决方案
一、表结构设计
二、实现思路
1.系统启动时将字典数据加载到redis作为可选常量池，以及mapper.xml、dao、数据规则信息加载到redis
2.用访问时通过springmvc拦截器对用户进行拦截获取token然后通过RSA解密获取用户信息，将用户信息，以及请求参数加入本地线程
3.mybatis-plus拦截器对mapper拦截然后解析对应dao层接口的方法，用于加载规则配置信息
4、通过策略模式实现读取不同常量池对规则表达式解析
5、使用jsqlparser实现sql解析注入
6、将处理后的sql交给mybatis-plus框架处理
三、核心代码：
@Slf4j
@Component
public class DpcInterceptor extends JsqlParserSupport implements InnerInterceptor {
ThreadLocal<String> mapperID = new ThreadLocal<String>();
ThreadLocal<String> methodID = new ThreadLocal<String>();
@Autowired
private RedisService redisService;
@Autowired
private Map<String, AbstractExpressionHandler> expressionHandlers;
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
if (!InterceptorIgnoreHelper.willIgnoreDataPermission(ms.getId())) {
String[] ids = ms.getId().replace(".", ",").split(",");
mapperID.set(ids[ids.length - 2]);
methodID.set(ids[ids.length - 1]);
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
mpBs.sql(this.parserSingle(mpBs.sql(), ms.getId()));
}
}
@Override
protected void processSelect(Select select, int index, String sql, Object obj) {
Authentication authentication = AuthenticationHolder.get();
try {
if (!Objects.isNull(authentication)) {
Long userId = authentication.getUserId();
String json = redisService.getByKey(String.format("DATA:SCOPE:%s:%s:%s", userId, mapperID.get(), methodID.get()));
if (String_.isNotEmpty(json)) {
Expression where = null;
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
if ((where = plainSelect.getWhere()) == null) {
plainSelect.setWhere(new HexValue("1 = 1"));
}
StringBuffer sbWhere = new StringBuffer();
for (MenuRuleDto menuRule : Json_.toList(json, MenuRuleDto.class)) {
if (1 == menuRule.getType() && 1 == menuRule.getStatus()) {
String sqlWhere = menuRule.getCriteria();
for (String variablePoolName : menuRule.getVariablePool().split(",")) {
String whereSql = expressionHandlers.get(String_.lowerFirst(variablePoolName) + "ExpressionHandler").expression(sqlWhere);
if (whereSql.indexOf("$") < 0 && whereSql.indexOf("::") < 0) {
sbWhere.append(" and (" + whereSql + ") ");
}
}
}
}
plainSelect.setWhere(new AndExpression(new Parenthesis(where),new Column(sbWhere.substring(4).toString())));
}
}
} catch (Exception e) {
log.debug(e.getMessage());
} finally {
methodID.remove();
mapperID.remove();
}
}
@Slf4j
@Component
public class AuthenticationExpressionHandler extends AbstractExpressionHandler{
@Override
public String expression(String whereSql) {
Authentication currenUser = AuthenticationHolder.get();
if (currenUser != null) {
try {
Map<String,String> params = new HashMap<>();
for (Field declaredField : currenUser.getClass().getDeclaredFields()) {
declaredField.setAccessible(true);
if (declaredField.get(currenUser) != null) {
params.put("current"+ String_.upperFirst(declaredField.getName()),declaredField.get(currenUser).toString());
}
}
StringSubstitutor stringSubstitutor = new StringSubstitutor(params);
return stringSubstitutor.replace(whereSql);
} catch (Exception e) {
log.debug("[AuthenticationExpressionHandler 参数替换失败]{}",e.getMessage(),e);
}
}
return whereSql;
}
}
/**
* 默认的表达式解析工具类,鉴权数据，请求数据作为参数
*/
@Component
public class DefaultExpressionHandler extends AbstractExpressionHandler{
@Override
public String expression(String whereSql) {
Map<String,String> params = ThreadLocal_.get("params");
StringSubstitutor stringSubstitutor = new StringSubstitutor(params);
return stringSubstitutor.replace(whereSql);
}
}
/**
* 表达式子解析处理接口
*/
public abstract class AbstractExpressionHandler {
public abstract String expression(String whereSql);
}
/**
* 默认的表达式解析工具类,字典数据作为参数
*/
@Component
public class DictionaryExpressionHandler extends AbstractExpressionHandler{
@Autowired
private RedisService redisService;
@Override
public String expression(String whereSql) {
Map<String,String> params = new HashMap<>();
for (String variable : getVariables(whereSql)) {
if (variable.indexOf("::")>0) {
String dictType = variable.substring(0,variable.indexOf("::"));
String dictCode = variable.substring(variable.indexOf("::")+2);
String dictJson = redisService.getByKey(RedisConst.DICT_DATA_KEY + dictType);
List<SysDictData> dictData = Json_.toList(dictJson,SysDictData.class);
for (SysDictData dictDatum : dictData) {
if (dictDatum.getDictLabel().equals(dictCode)) {
params.put(variable,dictDatum.getDictValue());
}
}
}
}
StringSubstitutor stringSubstitutor = new StringSubstitutor(params);
return stringSubstitutor.replace(whereSql);
}
public static Set<String> getVariables(String str) {
Set<String> variables = new HashSet<>();
Pattern pattern = Pattern.compile("\\$\\{([^}]+)}");
Matcher matcher = pattern.matcher(str);
while (matcher.find()) {
variables.add(matcher.group(1));
}
return variables;
}
}