Nemo

关注TA

路漫漫其修远兮,吾将上下而求索。

  • 深圳市
  • 菜鸟工程师

最近留言

mybatis 数据层通用权限拦截过滤

2019年03月13 17:10 1,452 0 复制链接

下载.jpg

假设sql:

查询当前用户具有权限查看的产品信息,产品根据用户以及用户的机构信息作为权限规则:

select s.prod.id,s.prod.prod_name
from sys_prod sprod
join sys_user suser on sprod.create_by_id = suser.id
where 
suser.org_id = 1;

查询当前用户具有权限查看的菜单信息,菜单根据用户以及用户的机构信息作为权限规则:

select s.prod.id,s.prod.prod_name 
from sys_menu smenu
join sys_user suser on smenu.create_by_id = suser.id
where 
suser.org_id = 1;



可以看到,如果系统里面,权限控制规则如果几乎一致(这里是org_id),每处查询都需要添加权限相同的权限过滤代码,如果权限拦截的规则比较简单还好每处需要添加的代码不多,但是如果权限拦截规则比较复杂的话,那就不好处理了。聚个栗子:

select s.prod.id,s.prod.prod_name 
from sys_menu smenu
join sys_user suser on smenu.create_by_id = suser.id
where 
suser.org_id = 1 and suser.role_id=1 and suser.branch_id=1 and suser.status='NORMAL' and ....

如果每处sql都要把这一长串的where复制一遍粘贴上来,每个查询参数Bean里头都得注入作同样的注入查询参数操作...可以想象工程里头会有多少重复的代码。


那么应该怎么处理呢?

方案1,直接嵌套子查询:

select * from (  
  select sprod.id,sprod.prod_name,suser.org_id as dataFilterOrgId
  from sys_menu smenu
  join sys_user suser on smenu.create_by_id = suser.id
) dataTable
where 
dataTable.dataFilterOrgId = 1

只需要在mybatis执行sql之前,做下拦截。把执行的sql嵌套到这个子查询里面,然后在外面加上权限过滤的条件即可。

每个需要作权限拦截的sql中,必须返回

dataFilterOrgId

嵌套的上级查询则根据这个公用的字段作为过滤即可。


方案2,直接解析查询列中含有的需要作权限拦截的参数,把查询列提出来放到条件中做过滤。

举个栗子:

select sprod.id,sprod.prod_name,suser.org_id as dataFilterOrgId
from sys_menu smenu
join sys_user suser on smenu.create_by_id = suser.id
where suser.org_id = 1;

这里的过滤条件是取

dataFilterOrgId

前面的列作为过滤条件。这里的是suser.org_id。


大体思路就是上面这样了。下面具体实现下。


1、新建一个拦截器,用来拦截mybatis执行sql前的动作。

@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class DataFilterMybatisInterceptor implements Interceptor {

@Override
public Object intercept(Invocation invocation) throws Throwable {
//TODO 过滤操作
return invocation.proceed();
} }



只需要覆盖intercept方法,址对prepare相关的操作作过滤。这里考虑一些不需要作权限过滤的方法,所以就只过滤DataFilter以及DataFilterCount结尾的sql,其中*DataFilter是普通返回多行数据的过滤,*DataFilterCount是结果集数量数据的过滤。一下只对DataFilter作说明。

并且只对类型为Select的操作作过滤:

<select id="selectProdDataFilter" >
  select s.prod.id,s.prod.prod_name,suser.org_id as dataFilterOrgId
  from sys_prod sprod
  join sys_user suser on sprod.create_by_id = suser.id
</select>


1、嵌套查询的形式:

private final static String DATA_FILTER = "DataFilter";

@Override
public Object intercept(Invocation invocation) throws Throwable {
String interceptMethod = invocation.getMethod().getName();
if ("prepare".equals(interceptMethod)) {
StatementHandler handler = (StatementHandler) PluginUtil.processTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(handler);

MappedStatement ms = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
// ms.get
SqlCommandType sqlCmdType = ms.getSqlCommandType();
if (!(sqlCmdType == SqlCommandType.SELECT
&& (ms.getId().endsWith(DATA_FILTER)))) {             //除规则外,其余均不需要过滤
return invocation.proceed();
}

BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
Connection connection = (Connection) invocation.getArgs()[0];
handleDataFilterSubSelect(metaObject, ms, boundSql, connection);
}
return invocation.proceed();
}


private void handleDataFilterSubSelect(MetaObject metaObject, MappedStatement ms, BoundSql boundSql, Connection connection) {

String originalSql = boundSql.getSql();
if (StringUtils.isNotBlank(originalSql)) {

StringBuilder finalSql = new StringBuilder();
finalSql.append("select * from (");
finalSql.append(originalSql);
finalSql.append(") dataTable ");
if (handleSql) {
 Integer orgId = DataFilterThreadLocal.getOrgId();                   finalSql.append(" where orgId = ");                   finalSql.append(orgId);
}

metaObject.setValue(DELEGATE_SQL, finalSql.toString());
}

}

这里的orgId预先加载到了本地线程中,这里就不单独说明了。


2、条件拼接形式:

private final static String DATA_FILTER = "DataFilter";

@Override
public Object intercept(Invocation invocation) throws Throwable {
String interceptMethod = invocation.getMethod().getName();
if ("prepare".equals(interceptMethod)) {
StatementHandler handler = (StatementHandler) PluginUtil.processTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(handler);

MappedStatement ms = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
// ms.get
SqlCommandType sqlCmdType = ms.getSqlCommandType();
if (!(sqlCmdType == SqlCommandType.SELECT
&& (ms.getId().endsWith(DATA_FILTER)))) {             //除规则外,其余均不需要过滤
return invocation.proceed();
}

BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
Connection connection = (Connection) invocation.getArgs()[0];         String originalSql = boundSql.getSql();         Select select = (Select) CCJSqlParserUtil.parse(originalSql);         PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
handleDataFilterSelect(plainSelect);         metaObject.setValue("delegate.boundSql.sql", plainSelect.toString());
}
return invocation.proceed();
}
private String dealWithForceMaster(String originalSql, String sql) {
if (containsForceMaster(originalSql)) {
return FORCE_MASTER + sql;
}
return sql;
}


/**
* 数据过滤相关业务逻辑
* @param plainSelect
*/
private void dealDataFilter(PlainSelect plainSelect){

Expression where = plainSelect.getWhere();

//封装sql查询字段
List<SelectItem> selectItems = plainSelect.getSelectItems();
List<String> colums = Lists.newArrayList();
selectItems.forEach(item->{
String value = item.getASTNode().jjtGetValue().toString();
if(StringUtils.isNotBlank(value)){
colums.add(value.trim());;
}
});
Integer orgId = DataFilterThreadLocal.getOrgId();

//得到sql中含有需要过滤的关键字段
String orgKey = null;
String blankSpace = " ";
for (String item : colums) {
if(item.endsWith("dataFilterOrgId")){
orgKey = item.split(blankSpace)[0];
}
}

EqualsTo orgEq = null;

if (StringUtils.isNotBlank(orgKey)) {
orgEq = getEqExpresion(orgKey, orgId);
}

if(where != null) {
  if (orgEq != null) {
plainSelect.setWhere(new AndExpression(orgEq, where));
}
}else{
if (orgEq != null) {
plainSelect.setWhere(orgEq);
}
}
}



大概就是这样了。

先简单记录下,后续有事件再详细完善。

点赞(0)
本文标签java 数据库 mybatis 权限
点了个评