JAVA WEB项目中的功能权限和数据权限设计

一、功能权限

1.背景

项目目前的权限校验体系，采用的是用户-角色-权限模型，对应数据库的user-role-function表以及相关的映射表。
其中的权限表数据，需要由研发提前手动插入function表，然后在项目的页面上给角色分配权限。
目前这种方式存在如下问题

1.增加了人工维护数据的成本，不够灵活。
2.容易造成数据冗余。比如插入*select*正则形式，实际能匹配大部分查询请求了，但后面可能又插入selectByCondition这种具体的接口，无意中产生了重复数据。

2.目标

所以，我们希望后台能有一种机制，能自动识别和收集springmvc controller的每个接口信息并插入function表，不再需要人工维护。
通过了解，SpringMvc中的RequestMappingInfoHandlerMapping类可以获取controller的每个接口信息，所以我们可以从这个类入手。

3.实现方式

核心实现思路如下

1.声明一个注解，加在需要进行权限拦截的controller类和接口上。 每个controller里的接口信息，将会对应权限表一条记录；
2.权限记录的主键id，是类上注解id的加接口上注解id；
3.项目启动时，通过RequestMappingInfoHandlerMapping获取到有权限注解的类和方法，依次遍历并构造出权限对象，插入数据库。

以下是代码示例

3.1 定义注解

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.TYPE})
public @interface Auth {
    /**
     * 权限id，需要唯一
     */
    long id();
    /**
     * 权限名称
     */
    String name() default "";
    /**
     * 模块
     */
    String module() default "";

    String caption() default "";
}*

3.2 类和接口加注解

注意，类上id的值加接口id的值，要保证唯一性

3.2 权限初始化类

编写权限初始化类，项目启动时会执行收集注解、构建权限对象、插入数据库的逻辑，如下

@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());
        }
    }
}

二、数据权限

1.背景

除了功能权限，项目还用到了数据权限，后者采用的是用户-角色-数据模型，对应数据库的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参数，加大了代码维护成本。

2.目标

我们的目标，是借助Mybatis插件功能来无侵入地将这部分固定的sql进行拼接，而不是写在原mapper里。

3.实现方式

核心实现思路如下

1.声明一个注解，加在涉及数据权限的mapper接口上；
2.编写mybatis插件，扫描注解信息，获取原sql和入参，拼接成新sql；

3.1 定义注解

@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

3.2 mapper接口加注解

    @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的模式。

3.2 Aop拦截有注解的mapper接口

这一步目的是将注解信息放入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();
    }
}

3.3 编写Mybatis插件

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}} )