在业务系统开发中,数据权限的需求是少不了的.大多数都是根据操作人的角色限制数据范围,例如只能查询某些部门,类型或者用户的数据.
例如,查询订单sql
select o.id,o.order_no,o.type,o.salesman_id ,s.salesman
from test_order o
join test_salesman s on o.salesman_id=s.id
如果要限制只能查询某个类型和某个业务员的数据,那得挨个加上where条件,如
select o.id,o.order_no,o.type,o.salesman_id ,s.salesman
from test_order o
join test_salesman s on o.salesman_id=s.id
WHERE o.type IN ('1') and s.salesman IN ('A', 'B')
这样的做法工作量大,容易出错,对系统的入侵性高,后续修改和维护非常困难,所以应该在框架层面上实现这样的需求,可以利用mybaits的Interceptor插件接口实现.
数据权限实体结构如下
@Data
public class DataPermission {
//数据库表名
private String tableName;
//需要限制数据范围的列名
private String columnName;
//限制的数据列表
private List<String> inValueList;
}
使用ThreadLocal线程变量存放数据权限列表,并且mock一些数据,如下
public class DataPermissionUtils {
private static ThreadLocal<List<DataPermission>> dataPermissionThreadLocal = new ThreadLocal<>();
public static List<DataPermission> getDataPermissionList() {
List<DataPermission> list = dataPermissionThreadLocal.get();
if (list == null) {
DataPermission dataPermission = new DataPermission();
dataPermission.setColumnName("salesman");
dataPermission.setTableName("test_salesman");
dataPermission.setInValueList(Arrays.asList("A", "B"));
DataPermission dataPermission1 = new DataPermission();
dataPermission1.setColumnName("type");
dataPermission1.setTableName("test_order");
dataPermission1.setInValueList(Arrays.asList("1"));
list = Arrays.asList(dataPermission, dataPermission1);
dataPermissionThreadLocal.set(list);
}
return list;
}
}
编写mybaits插件
在DataPermissionInterceptor插件中获取sql,通过CCJSqlParserUtil类解析,在处理子查询,join等语句中加入权限sql,如下
@Component
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class DataPermissionInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = getRealTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
//判断是否select语句和是否有数据权限控制
if (mappedStatement.getSqlCommandType().equals(SqlCommandType.SELECT)) {
Select selectStatement = (Select) CCJSqlParserUtil.parse(statementHandler.getBoundSql().getSql());
//开始处理sql
processSelectBody(selectStatement.getSelectBody());
metaObject.setValue("delegate.boundSql.sql", selectStatement.toString());
}
return invocation.proceed();
}
public static <T> T getRealTarget(Object target) {
if (Proxy.isProxyClass(target.getClass())) {
MetaObject metaObject = SystemMetaObject.forObject(target);
return getRealTarget(metaObject.getValue("h.target"));
}
return (T) target;
}
public void processSelectBody(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
processPlainSelect((PlainSelect) selectBody);
} else if (selectBody instanceof WithItem) {
WithItem withItem = (WithItem) selectBody;
if (withItem.getSelectBody() != null) {
processSelectBody(withItem.getSelectBody());
}
} else {
SetOperationList operationList = (SetOperationList) selectBody;
if (operationList.getSelects() != null && operationList.getSelects().size() > 0) {
operationList.getSelects().forEach(x -> processSelectBody(x));
}
}
}
protected void processPlainSelect(PlainSelect plainSelect) {
FromItem fromItem = plainSelect.getFromItem();
//子查询
processFromItem(fromItem);
//处理where条件
plainSelect.setWhere(builderExpression(plainSelect.getWhere(), fromItem));
//处理关联
List<Join> joins = plainSelect.getJoins();
if (joins != null && joins.size() > 0) {
joins.forEach(j -> {
processJoin(j);
});
}
}
/**
* 处理条件
*/
protected Expression builderExpression(Expression expression, FromItem fromItem) {
if (expression != null) {
if (expression instanceof FromItem) {
processFromItem((FromItem) expression);
}
if (expression instanceof BinaryExpression) {
BinaryExpression binaryExpression = (BinaryExpression) expression;
builderExpression(binaryExpression.getLeftExpression(), null);
builderExpression(binaryExpression.getRightExpression(), null);
}
if (expression instanceof InExpression) {
InExpression inExp = (InExpression) expression;
ItemsList rightItems = inExp.getRightItemsList();
if (rightItems instanceof SubSelect) {
processSelectBody(((SubSelect) rightItems).getSelectBody());
}
}
}
if (fromItem instanceof Table) {
Table table = (Table) fromItem;
DataPermission dataPermission = getDataPermission(table);
if (dataPermission != null) {
InExpression inExpression = new InExpression();
inExpression.setLeftExpression(this.getAliasColumn(table, dataPermission.getColumnName()));
MultiExpressionList multiExpressionList = new MultiExpressionList();
multiExpressionList.addExpressionList(dataPermission.getInValueList().stream().map(x -> new StringValue(x)).collect(Collectors.toList()));
inExpression.setRightItemsList(multiExpressionList);
if (expression == null) {
return inExpression;
} else {
if (expression instanceof OrExpression) {
return new AndExpression(inExpression, new Parenthesis(expression));
} else {
return new AndExpression(inExpression, expression);
}
}
}
}
return expression;
}
/**
* 处理FromItem,如果是子查询等,则递归处理
*/
protected void processFromItem(FromItem fromItem) {
if (fromItem instanceof SubJoin) {
SubJoin subJoin = (SubJoin) fromItem;
if (subJoin.getJoinList() != null) {
subJoin.getJoinList().forEach(this::processJoin);
}
if (subJoin.getLeft() != null) {
processFromItem(subJoin.getLeft());
}
} else if (fromItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) fromItem;
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody());
}
} else if (fromItem instanceof ValuesList) {
} else if (fromItem instanceof LateralSubSelect) {
LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
if (lateralSubSelect.getSubSelect() != null) {
SubSelect subSelect = lateralSubSelect.getSubSelect();
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody());
}
}
}
}
/**
* 处理联接语句
*/
protected void processJoin(Join join) {
processFromItem(join.getRightItem());
join.setOnExpression(builderExpression(join.getOnExpression(), join.getRightItem()));
}
// 拼接字段名
protected Column getAliasColumn(Table table, String columnName) {
StringBuilder column = new StringBuilder();
if (null == table.getAlias()) {
column.append(table.getName());
} else {
column.append(table.getAlias().getName());
}
column.append(".");
column.append(columnName);
return new Column(column.toString());
}
private DataPermission getDataPermission(Table fromTable) {
List<DataPermission> list = DataPermissionUtils.getDataPermissionList();
DataPermission dataPermission = list.stream().filter(x -> Objects.equals(x.getTableName(), fromTable.getName())).findFirst().orElse(null);
return dataPermission;
}
测试一下一条极端的sql语句,如
select o.id,o.order_no as orderNo,o.type,o.salesman_id as salesmanId,s.salesman
from (select * from test_order) o
join test_order oo on o.id=oo.id
join test_salesman s on o.salesman_id=s.id and s.id in (select id from test_salesman)
join (select * from test_salesman) ss on o.salesman_id=ss.id
where o.salesman_id in (select id from test_salesman)
使用插件后,自动在各个查询和子查询中加入对应的in语句,实际执行的sql如下
SELECT o.id, o.order_no AS orderNo, o.type, o.salesman_id AS salesmanId, s.salesman
FROM (SELECT * FROM test_order WHERE test_order.type IN ('1')) o
JOIN test_order oo ON oo.type IN ('1') AND o.id = oo.id
JOIN test_salesman s ON s.salesman IN ('A', 'B') AND o.salesman_id = s.id AND s.id IN (SELECT id FROM test_salesman WHERE test_salesman.salesman IN ('A', 'B'))
JOIN (SELECT * FROM test_salesman WHERE test_salesman.salesman IN ('A', 'B')) ss ON o.salesman_id = ss.id
WHERE o.salesman_id IN (SELECT id FROM test_salesman WHERE test_salesman.salesman IN ('A', 'B'))
上面插件还存在一个问题就是,插件启用后会对所有sql加入对应的权限sql,事实上有些场景的sql可能不需要数据权限,所以还需要定制一个注解,加上此注解的查询方法,忽略权限sql处理,注解如下
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
public @interface DataScope {
boolean enabled() default true;
}
DataPermissionInterceptor加入对注解的处理
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = getRealTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
//判断是否select语句和是否有数据权限控制
if (mappedStatement.getSqlCommandType().equals(SqlCommandType.SELECT) && isDataScope(mappedStatement)) {
Select selectStatement = (Select) CCJSqlParserUtil.parse(statementHandler.getBoundSql().getSql());
//开始处理sql
processSelectBody(selectStatement.getSelectBody());
metaObject.setValue("delegate.boundSql.sql", selectStatement.toString());
}
return invocation.proceed();
}
private Boolean isDataScope(MappedStatement mappedStatement) {
try {
String mappedStatementId = mappedStatement.getId();
int lastDotIndex = mappedStatementId.lastIndexOf(".");//id是由类名+方法名组成
String className = mappedStatementId.substring(0, lastDotIndex);//找出类名
String methodName = mappedStatementId.substring(lastDotIndex + 1);//找出方法名
//在注册的mapper中找出对应的类
Class aClass = mappedStatement.getConfiguration().getMapperRegistry().getMappers().stream().filter(x -> x.getName().equals(className)).findFirst().orElse(null);
if (aClass != null) {
Method method = null;
for (Method item : aClass.getMethods()) {
if (item.getName().equals(methodName)) {
method = item;
break;
}
}
//查找DataScope注解,默认是过滤数据
if (method != null) {
DataScope dataScopeAnnotation = AnnotationUtils.getAnnotation(method, DataScope.class);
if (dataScopeAnnotation != null) {
return dataScopeAnnotation.enabled();
}
return true;
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
return false;
}
注解使用,如下
@Mapper
public interface SalesmanMapper {
@DataScope(enabled = false)
List<SalesmanVO> list();
}
题外话
-
上面的插件可以实现数据权限的过滤,但是数据权限还有的场景是,不能查看某些列,所以我们还可以对查询的列进行处理
-
插件只处理了select类型的语句,按照这样的思路,也处理一下其他类型的sql,例如insert语句,默认插入某些列,例如租户之类的
-
上文利用ThreadLocal传递模拟的数据权限,实际开发中,这里的来源是根据登陆用户的角色来读取的,可以是读数据库,也可以读取redis之类的,而数据库或者redis的数据是怎么产生的,那就需要另外做功能界面来维护了
3079
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
