赞
踩
项目目前的权限校验体系,采用的是用户-角色-权限模型,对应数据库的user-role-function表以及相关的映射表。
其中的权限表数据,需要由研发提前手动插入function表,然后在项目的页面上给角色分配权限。
目前这种方式存在如下问题
1.增加了人工维护数据的成本,不够灵活。
2.容易造成数据冗余。比如插入*select*
正则形式,实际能匹配大部分查询请求了,但后面可能又插入selectByCondition这种具体的接口,无意中产生了重复数据。
所以,我们希望后台能有一种机制,能自动识别和收集springmvc controller的每个接口信息并插入function表,不再需要人工维护。
通过了解,SpringMvc中的RequestMappingInfoHandlerMapping
类可以获取controller的每个接口信息,所以我们可以从这个类入手。
核心实现思路如下
1.声明一个注解,加在需要进行权限拦截的controller类和接口上。 每个controller里的接口信息,将会对应权限表一条记录;
2.权限记录的主键id,是类上注解id的加接口上注解id;
3.项目启动时,通过RequestMappingInfoHandlerMapping
获取到有权限注解的类和方法,依次遍历并构造出权限对象,插入数据库。
以下是代码示例
@Retention(RetentionPolicy.RUNTIME) @Target({ElementType.METHOD, ElementType.TYPE}) public @interface Auth { /** * 权限id,需要唯一 */ long id(); /** * 权限名称 */ String name() default ""; /** * 模块 */ String module() default ""; String caption() default ""; }*
注意,类上id的值加接口id的值,要保证唯一性
编写权限初始化类,项目启动时会执行收集注解、构建权限对象、插入数据库的逻辑,如下
@Component @Slf4j public class FunInitConfig implements CommandLineRunner { @Autowired private BdpFuncMapper bdpFuncMapper; @Autowired private RequestMappingInfoHandlerMapping requestMappingInfoHandlerMapping; @Override public void run(String... args) throws Exception { Map<RequestMappingInfo, HandlerMethod> handlerMethods = requestMappingInfoHandlerMapping.getHandlerMethods(); List<BdpFuncTemp> bdpFuncTempList = new ArrayList<>(); for (Map.Entry<RequestMappingInfo, HandlerMethod> entry : handlerMethods.entrySet()) { RequestMappingInfo key = entry.getKey(); HandlerMethod value = entry.getValue(); Auth clzAuth = value.getBeanType().getAnnotation(Auth.class); if(clzAuth == null){ continue; } Auth methodAuth = value.getMethod().getAnnotation(Auth.class); if(methodAuth == null){ continue; } //url格式 GET:/alarm/selectAlarmByPage //有的方法可能没有请求方式 String url = ""; if(CollectionUtils.isEmpty(key.getMethodsCondition().getMethods())){ url = "GET"+ ":" + key.getPatternsCondition().getPatterns().toArray()[0]; }else { //有的方法可能有多种请求方式 url = key.getMethodsCondition().getMethods().stream().map(requestMethod -> requestMethod.name() + ":" + key.getPatternsCondition().getPatterns().toArray()[0]).collect(Collectors.joining(",")); } //权限表 BdpFuncTemp bdpFunc = new BdpFuncTemp(); bdpFunc.setFunctionId((int)(clzAuth.id() + methodAuth.id())); bdpFunc.setFunctionModule(clzAuth.module()); bdpFunc.setFunctionName(methodAuth.name()); bdpFunc.setFunctionUrl(url); bdpFunc.setFunctionCaption(methodAuth.caption()); bdpFuncTempList.add(bdpFunc); } try { //插入权限表 bdpFuncService.funcInit(bdpFuncTempList); } catch (Exception e) { log.error("", e); throw new GlobalException(e.getMessage()); } } }
除了功能权限,项目还用到了数据权限,后者采用的是用户-角色-数据模型,对应数据库的user-role-resource表以及相关的映射表。
当判断某用户对某数据是否有权限时,后台首先获取当前登陆的用户工号,然后根据一段基本固定的sql来从数据权限表中查询。
比如,现在判断用户是否有某个集群的主机权限,从下面的AND a.cluster_id IN
开始一直到结尾,除了其中的resourceType
(资源类型)和userId
(用户工号)参数,sql语法是固定的。
SELECT count(0) FROM cluster_host_mapping a, HOSTS b WHERE a.host_id = b.host_id AND a.cluster_id = #{clusterid,jdbcType=INTEGER} //下面是固定结构 AND a.cluster_id IN ( SELECT br.res_inst_id FROM bdp_resource br, ( SELECT rrm.mapping_id, rrm.bdp_role_id, rrm.bdp_res_id FROM bdp_role_res_mapping rrm WHERE rrm.bdp_role_id IN ( SELECT role_id FROM bdp_user_role_mapping urm, bdp_user u WHERE urm.user_id = u.id AND instr(urm.data_rights, 'r') <> 0 AND u.user_id = #{userId, jdbcType=VARCHAR} ) ) t WHERE br.id = t.bdp_res_id AND br.resource_type = #{resourceType,jdbcType=INTEGER} )
项目通过一个工具类封装了这段固定的sql文本,每个mapper接口方法要额外传一个resourceType
和userId
参数,并在xml中引用这个工具类来拼接sql语句。
但是,这种手动拼接的方式不仅不够优雅,而且导致使用mapper接口的上层方法也跟着多传resourceType
和userId
参数,加大了代码维护成本。
我们的目标,是借助Mybatis插件功能来无侵入地将这部分固定的sql进行拼接,而不是写在原mapper里。
核心实现思路如下
1.声明一个注解,加在涉及数据权限的mapper接口上;
2.编写mybatis插件,扫描注解信息,获取原sql和入参,拼接成新sql;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface DataSelectPermission {
//表别名
String tableAlias() default "";
//
String tableColumn() default "";
int resourceType() default 0;
}
tableAlias表示业务表的别名,比如上述cluster_host_mapping a
中的a
。
tableColumn表示业务表的字段,比如上述AND a.cluster_id
中的cluster_id
@Select({
"select",
"count(0)",
"from cluster_host_mapping a,hosts b",
"where a.host_id=b.host_id and a.cluster_id = #{clusterId,jdbcType=INTEGER}",
// "and a.cluster_id in (select br.res_inst_id from bdp_resource br, (select rrm.mapping_id, rrm.bdp_role_id, rrm.bdp_res_id from bdp_role_res_mapping rrm where rrm.bdp_role_id in ",
// "(select role_id from bdp_user_role_mapping urm, bdp_user u where urm.user_id = u.id ",
// "and instr(urm.data_rights,'r') <> 0 and u.user_id = #{userId, jdbcType=VARCHAR}))t ",
// "where br.id = t.bdp_res_id and br.resource_type = #{resourceType,jdbcType=INTEGER}) "
})
@DataSelectPermission(tableAlias = "a", tableColumn = "cluster_id")
Long selectHostCountByClusterId(@Param("clusterId") Integer clusterId, @Param("userId") String userId, @Param("resourceType") Integer resourceType);
其中被注释的内容,是之前使用固定sql的模式。
这一步目的是将注解信息放入ThreadLocal
,供后续Mybatis插件逻辑使用
@Aspect @Slf4j @Component @Order(-1) public class DataPermissonAop { @Autowired private BdpRightDao bdpRightDao; @Pointcut(value = "execution(* com.sf.mapper..*.*(..))") public void cut(){} @Around("cut()") public Object around(ProceedingJoinPoint joinPoint) throws Throwable { MethodSignature ms = (MethodSignature)joinPoint.getSignature(); //查询 DataSelectPermission dataSelectPermission = ms.getMethod().getAnnotation(DataSelectPermission.class); if(dataSelectPermission != null) { ThreadLocalInfoUtil.setValue("dataSelectPermission", dataSelectPermission); try { return joinPoint.proceed(); } catch (Exception e) { log.error("DataPermissonAop around method error", e); throw e; }finally { ThreadLocalInfoUtil.remove(); } } return joinPoint.proceed(); } }
import lombok.extern.slf4j.Slf4j; import net.sf.jsqlparser.JSQLParserException; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.select.*; import org.apache.commons.io.FileUtils; import org.apache.ibatis.executor.statement.PreparedStatementHandler; import org.apache.ibatis.executor.statement.RoutingStatementHandler; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.plugin.*; import org.springframework.beans.factory.annotation.Value; import org.springframework.core.io.Resource; import org.springframework.stereotype.Component; import org.springframework.util.ReflectionUtils; import org.springframework.util.StringUtils; import java.lang.reflect.Field; import java.sql.Connection; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; @Slf4j @Component @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}) }) public class DataSelectPermissonPlugin implements Interceptor { private final String pattern = "\\{\\{([\\w.]*)\\}\\}"; private final Pattern r = Pattern.compile(pattern); @Value("classpath:data_select_permission_sql") private Resource dataSelectPermissionSql; @Override public Object intercept(Invocation invocation) throws Throwable { if (invocation.getTarget() instanceof StatementHandler) { Object dataSelectPermission = ThreadLocalInfoUtil.getValue("dataSelectPermission"); if(dataSelectPermission != null){ DataSelectPermission annotation = (DataSelectPermission)dataSelectPermission; StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); Field delegate = getField(statementHandler, "delegate"); PreparedStatementHandler prepareStatement = (PreparedStatementHandler) delegate.get(statementHandler); Field boundSql = getField(prepareStatement, "boundSql"); BoundSql bsinstance = (BoundSql) boundSql.get(prepareStatement); //方法入参 if(bsinstance.getParameterObject() == null){ return invocation.proceed(); } if(bsinstance.getParameterObject() instanceof Object[]){ }else if(bsinstance.getParameterObject() instanceof Map){ Map<String, Object> param = (Map<String, Object>)bsinstance.getParameterObject(); List<ParameterMapping> parameterMappings = bsinstance.getParameterMappings(); boolean hasUserId = parameterMappings.stream().anyMatch(parameterMapping -> parameterMapping.getProperty().contains("userId")); //不包含userId字段,表示不需要数据权限 if(param.get("userId") == null && !hasUserId){ return invocation.proceed(); } } Field sql = getField(bsinstance, "sql"); String oldSql = (String) sql.get(bsinstance); //新的sql String replacedSql = getReplacedSql(oldSql, bsinstance, annotation); log.info("intercept sql:{}", replacedSql); sql.set(bsinstance, replacedSql); return invocation.proceed(); } } return invocation.proceed(); } private String getReplacedSql(String oldSql, BoundSql boundSql, DataSelectPermission annotation){ Statement stmt = null; try { //CCJSqlParserUtil来自开源工具 stmt = CCJSqlParserUtil.parse(oldSql); } catch (JSQLParserException e) { log.error("getReplacedSql error", e); } Select select = (Select) stmt; SelectBody selectBody = select.getSelectBody(); //获取模板参数InputParamDTO List<InputParamDTO> inputParamDTOList = getInputParamDTOS(boundSql, annotation); try { //拼接新sql getNewSql(selectBody, inputParamDTOList); return selectBody.toString(); } catch (Exception e) { log.error("", e); return null; } } private List<InputParamDTO> getInputParamDTOS(BoundSql boundSql, DataSelectPermission annotation) { List<InputParamDTO> inputParamDTOList = new ArrayList<>(); Map<String, Object> param = (Map<String, Object>)boundSql.getParameterObject(); for (Map.Entry<String, Object> entry : param.entrySet()) { InputParamDTO dto = new InputParamDTO(); dto.setParamName(entry.getKey()); dto.setParamValue(entry.getValue()+""); inputParamDTOList.add(dto); } if(annotation.resourceType() != 0){ InputParamDTO dto = new InputParamDTO(); dto.setParamName("resourceType"); dto.setParamValue(annotation.resourceType() + ""); inputParamDTOList.add(dto); } //表别名 String tableAlias = annotation.tableAlias(); //表字段 String tableColumn = annotation.tableColumn(); InputParamDTO dto = new InputParamDTO(); dto.setParamName("tableAliasAndTableColumn"); if(StringUtils.isEmpty(tableAlias)){ dto.setParamValue(tableColumn); }else { dto.setParamValue(tableAlias + "." + tableColumn); } inputParamDTOList.add(dto); inputParamDTOList.add(dto); return inputParamDTOList; } private void getNewSql(SelectBody selectBody, List<InputParamDTO> inputParamDTOList) throws Exception { PlainSelect plainSelect = (PlainSelect) selectBody; String templateSql = replaceParams(FileUtils.readFileToString(dataSelectPermissionSql.getFile(), Constant.DEFAULT_CHARSET), inputParamDTOList); //有where条件 if(((PlainSelect) selectBody).getWhere() != null){ String whereSql = ((PlainSelect) selectBody).getWhere().toString(); whereSql = whereSql + "and " + templateSql; //重新设置where条件 plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(whereSql)); }else { //pagehelper会生成类似"select count(0) from (子查询) table_count"的形式,而此时where关键字可能在子查询里 FromItem fromItem = plainSelect.getFromItem(); if(fromItem.getAlias() != null && "table_count".equals(fromItem.getAlias().getName())){ SubSelect subSelect = (SubSelect) fromItem; getNewSql(subSelect.getSelectBody(), inputParamDTOList); }else { plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(templateSql)); } } } private Field getField(Object o, String name) { Field field = ReflectionUtils.findField(o.getClass(), name); ReflectionUtils.makeAccessible(field); return field; } @Override public Object plugin(Object target) { if (target instanceof RoutingStatementHandler) { return Plugin.wrap(target, this); } return target; } public String replaceParams(String content, List<InputParamDTO> inputParamDTOList){ StringBuffer sb = new StringBuffer(); Matcher m = r.matcher(content); while (m.find()) { String paramName = m.group(1); InputParamDTO paramDTO = inputParamDTOList.stream() .filter(inputParamDTO -> inputParamDTO.getParamName().equals(paramName)) .findFirst() .orElse(new InputParamDTO().setParamValue("")); String paramValue = paramDTO.getParamValue(); m.appendReplacement(sb, paramValue); } m.appendTail(sb); return sb.toString(); } }
其中data_select_permission_sql的文本内容如下
{{tableAliasAndTableColumn}} IN (SELECT br.res_inst_id FROM
bdp_resource br, (SELECT rrm.mapping_id, rrm.bdp_role_id,
rrm.bdp_res_id FROM bdp_role_res_mapping rrm WHERE rrm.bdp_role_id IN
( SELECT role_id FROM bdp_user_role_mapping urm, bdp_user u WHERE
urm.user_id = u.id AND instr(urm.data_rights, ‘r’) <> 0 AND u.user_id
= {{userId}} ) ) t WHERE br.id = t.bdp_res_id AND br.resource_type = {{resourceType}} )
赞
踩
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。