SQL数据权限

时间:2022-09-28 飘渺Jam 人气:0

数据权限模型

上篇文章的数据模型是基于传统的RBAC模型来设计的，由于我们这里的应用场景不一样，所以这里的数据权限模型并没有严格按照上篇文章的方案来设计，但是万变不离其宗，核心原理还是相同的。

首先我来介绍一下我们最终实现的效果

实现效果

一个组件（可以理解成菜单）可以绑定多个授权维度，当给角色授权组件时可以给这个授权组件赋予不同维度的权限。

关于数据权限的授权维度有以下几个关键点需要仔细体会：

给一个角色勾选授权维度实际上是在限制这个角色所能看到的数据范围
任何一个授权维度勾选了"全部"，相当于不限制此维度的权限。
- 如果一个角色勾选了客户群的全部 + A产品线，那么最终生成的sql 会是 where 产品线 in ('A产品线')
如果一个角色勾选了多个维度，维度之间用 AND 拼接
- 如果一个角色勾选了A客户群 + B产品线，那么最终生成的sql 会是 where 客户群 in（'A客户群'）AND 产品线 in ('B产品线')
一个用户可能有多个角色，角色之间用 OR 拼接
- 一个用户有两个角色：客户群总监，产品线经理。其中客户群总监角色拥有A客户群和B客户群的权限，产品线经理角色拥有A产品线权限，那么最终生成的sql会是 where 客户群 in ('A客户群','B客户群') OR 产品线 in ('A产品线')

当然我们业务场景中数据规则比较单一，全部使用 in作为sql条件连接符，你们可以根据实际业务场景进行补充。

数据模型

最终的数据模型如下所示：

这里的组件大家完全可以理解成RBAC模型中的资源、菜单，只不过叫法不同而已。

数据权限表结构

下面是具体的表结构设计

授权维度表

CREATE TABLE `wb_dimension` (
  `ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `DIMENSION_CODE` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '维度编码',
  `DIMENSION_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '维度名称',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='授权维度'

具体授权维度表（产品线）

CREATE TABLE `wb_dimension_proc_line` (
  `ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `DIMENSION_CODE` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '维度编码',
  `PROC_LINE_CODE` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '产品线编码',
  `PROC_LINE_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '产品线名称',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='授权维度-产品线'

跟授权维度表实际是一个表继承的关系，由于每个授权维度的属性不一样，展现形式也不一样，所以分表存储。

组件路由表

CREATE TABLE `wb_route` (
  `ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `COMPONENT_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '组件ID',
  `ROUTE_URL` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '路由地址',
  `AUTHORIZATION_TYPE` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '授权方式:1 自定义，2 上下级授权， 3 范围授权',
  `AUTHORIZATION_DIMENSION` json DEFAULT NULL COMMENT '授权维度',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='组件路由'
复制代码

当组件属性授权方式为范围授权时在应用侧会强制要求选择具体的授权维度，如产品线、客户群。

角色表

CREATE TABLE `wb_role` (
  `ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `ROLE_CODE` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '角色CODE',
  `ROLE_NAME` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '角色名称',
  `IDENTITY_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '身份ID'
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='角色表'

角色上有一个身份属性，多个角色可以归属同一个身份，方便对角色进行分类管理。

角色组件绑定表

CREATE TABLE `role_component_relation` (
  `ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `ROLE_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '角色ID',
  `COMPONENT_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组件ID',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='角色授权组件'

角色组件授权规则表（核心）

CREATE TABLE `wb_role_component_rule` (
  `ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `ROLE_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '角色ID',
  `COMPONENT_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '组件ID',
  `RULE_CODE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规则编码',
  `RULE_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规则名称',
  `RULE_CONDITION` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规则条件',
  `RULE_VALUE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规则值',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='角色组件维度规则表'

数据权限的核心表，规则条件的取值为IN，规则值存储具体的维度编码，当在数据维度中选择全部时我们将规则值存储为ALL这个特殊值，方便后续生成SQL语句。

实现过程

自定义一个数据权限的注解，比如叫DataPermission
在对应的资源请求方法，比如商机列表上添加自定义注解@DataPermission
利用AOP抓取到用户对应角色的所有数据规则并进行SQL拼接，最终在SQL层面实现数据过滤。

代码实现

自定义数据权限注解

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.METHOD})
@Documented
public @interface DataPermission {
	/**
	 * 数据权限类型
	 * 1 上下级授权  2 数据范围授权
	 */
	String permissionType() default "2";
	
	/**
	 * 配置菜单的组件路径,用于数据权限
	 */
	String componentRoute() default "";
}

定义数据权限处理切面

@Aspect
@Slf4j
public class DataPermissionAspect {

	@Autowired
	private RoleComponentRuleService roleComponentRuleService;

	@Pointcut("@annotation(com.ifly.workbench.security.annotation.DataPermission)")
	public void pointCut() {
		
	}
	
	@Around("pointCut()")
	public Object around(ProceedingJoinPoint point) throws  Throwable{

		HttpServletRequest request = SpringContextUtils.getHttpServletRequest();

		//获取请求token
		String token = request.getHeader(CommonConstant.X_ACCESS_TOKEN);
		String userName = JwtUtil.getUsername(token);
    
		MethodSignature signature = (MethodSignature) point.getSignature();
		Method method = signature.getMethod();
		DataPermission permissionData = method.getAnnotation(DataPermission.class);

    //获取授权方式
		String permissionType = permissionData.permissionType();
		//获取组件路由
		String componentRoute = permissionData.componentRoute();
    
    if (StringUtils.isNotEmpty(componentRoute)){
			// 查找当前用户此组件下的所有规则
			List<RoleComponentRuleDTO> componentRules = roleComponentRuleService.getRoleComponentRule(userName, componentRoute);
      
			if(CollectionUtils.isNotEmpty(componentRules)){
					DataPermissionUtils.installDataSearchConditon(request, componentRules);
					SysUserCacheInfo userInfo = buildCacheUser(userName);
					DataPermissionUtils.installUserInfo(request, userInfo);
				}
		}
    
    return  point.proceed();
	}

	private SysUserCacheInfo buildCacheUser(String userName) {
		SysUserCacheInfo info = new SysUserCacheInfo();
		info.setSysUserName(userName);
		info.setOneDepart(true);
		return info;
	}
}

在AOP中获取当前用户、需要访问的组件中所有的数据规则，参考wb_role_component_rule表设计，并将其放到Request作用域中。

数据权限工具类

public class DataPermissionUtils {
	public static final String COMPONENT_DATA_RULES = "COMPONENT_DATA_RULES";

	public static final String SYS_USER_INFO = "SYS_USER_INFO";
   /**
	 * 往链接请求里面，传入数据查询条件
	 * @param request
	 * @param componentRules
	 */
	public static void installDataSearchConditon(HttpServletRequest request, List<RoleComponentRuleDTO> componentRules) {
		// 1.先从request获取MENU_DATA_AUTHOR_RULES，如果存则获取到LIST
		List<RoleComponentRuleDTO> list = loadDataSearchCondition();

		if (list==null) {
			// 2.如果不存在，则new一个list
			list = Lists.newArrayList();
		}
		list.addAll(componentRules);
		// 3.往list里面增量存指
		request.setAttribute(COMPONENT_DATA_RULES, list);
	}

	/**
	 * 获取请求对应的数据权限规则
	 *
	 */
	@SuppressWarnings("unchecked")
	public synchronized List<RoleComponentRuleDTO> loadDataSearchCondition() {
		return (List<RoleComponentRuleDTO>) SpringContextUtils.getHttpServletRequest().getAttribute(COMPONENT_DATA_RULES);
				
	}
	
	public synchronized void installUserInfo(HttpServletRequest request, SysUserCacheInfo userinfo) {
		request.setAttribute(SYS_USER_INFO, userinfo);
	}
}

在Request中存储数据规则。

查询组件规则

public interface RoleComponentRuleService extends IService<RoleComponentRule> {

    /**
     * 根据 用户域账户和组件编码 获取组件对应的关系
     *
     * @param userName      域账号
     * @param componentCode 组件编码
     * @return 用户的所有规则
     */
    List<RoleComponentRuleDTO> getRoleComponentRule(String userName, String componentCode);
}

@Service
public class RoleComponentRuleServiceImpl extends ServiceImpl<RoleComponentRuleMapper, RoleComponentRule> implements RoleComponentRuleService {

    @Resource
    private RoleComponentRuleMapper roleComponentRuleMapper;

    /**
     * 根据 用户域账户和组件编码 获取组件对应的关系
     * @param userName      域账号
     * @param componentCode 组件编码
     * @return 用户的所有规则
     */
    @Override
    public List<RoleComponentRuleDTO> getRoleComponentRule(String userName, String componentCode) {
        return roleComponentRuleMapper.getRoleComponentRule(userName,componentCode);
    }

}

<select id="getRoleComponentRule" resultType="com.ifly.vo.RoleComponentRuleDTO">
	SELECT
    tab1.id,
    tab1.role_id,
    tab4.role_code,
    tab1.component_id,
    tab1.rule_code,
    tab1.rule_name,
    tab1.rule_condition,
    tab1.rule_value,
    tab4.identity_id
  FROM
  	wb_role_component_rule tab1
  	LEFT JOIN user_role_relation tab2 ON tab2.role_id = tab1.role_id
  	LEFT JOIN wb_component tab3 ON tab3.id = tab1.component_id
  	LEFT JOIN wb_role tab4 ON tab4.id = tab1.role_id
  	JOIN role_component_relation tab5 ON tab5.role_id = tab1.role_id
  	AND tab5.component_id = tab1.component_id
  where
  	tab2.user_account = #{userName}
  	and tab3.component_code = #{componentCode}
</select>

Controller调用

@ApiOperation(value = "服务BU-领导-总览")
@GetMapping("opp/getLeaderOverviewSve")
@DataPermission(componentRoute = "020202")
public Result<SalesProjOverviewSve> getLeaderOverviewSve(@RequestParam(name = "identityId") String identityId) {
	String permissionSql = RuleQueryGenerator.getPermissionSql(identityId);
  log.info("查服务BU-领导-总览-permissionSQL==" + permissionSql);

	return Result.OK(overviewSveService.getLeaderOverviewSve(permissionSql));
}

在controller的请求方法上加上自定义注解@DataPermission并指定组件编码，然后通过工具类生成SQL条件，最后将SQL条件传入service层进行处理。

构建数据权限SQL

@Slf4j
@UtilityClass
public class RuleQueryGenerator {

    private static final String SQL_AND = " and ";

    private static final String SQL_OR = " or ";

    private static final String SQL_JOINT = " (%s) ";

    /**
     * 获取带有数据权限的SQL
     * @param identityId 身份ID
     */
    public String getPermissionSql(String identityId) {
        //------------------------获取当前身份的数据规则------------------------------------
        List<RoleComponentRuleDTO> conditionList = getCurrentIdentyPermission(identityId);
        if (CollectionUtils.isEmpty(conditionList)) {
            //没有权限
            return "1 = 0";
        }
        //存在权限
        //对当前身份根据规则编码分组-去除不同角色中相同编码且规则值为ALL的规则 并根据角色id分组
        Map<String, List<RoleComponentRuleDTO>> ruleMap = getRuleMapByRoleId(conditionList);

        StringBuilder sb = new StringBuilder();
        String roleSql;
        if (MapUtils.isNotEmpty(ruleMap)) {
            //按角色拼接SQL
            for (Map.Entry<String, List<RoleComponentRuleDTO>> entry : ruleMap.entrySet()) {

                List<RoleComponentRuleDTO> lists = entry.getValue();

                // 同角色之间使用 AND
                roleSql = buildRoleSql(lists);

                //角色之间使用 OR
                if (StringUtils.isNotEmpty(roleSql)) {
                    jointSqlByRoles(sb, roleSql);
                }
            }

        }
        return sb.toString();
    }

    private static List<RoleComponentRuleDTO> getCurrentIdentyPermission(String identityId) {
        //----------------------------获取所有数据规则-----------------------------
        List<RoleComponentRuleDTO> roleRuleList = DataPermissionUtils.loadDataSearchCondition();
        if(CollectionUtils.isEmpty(roleRuleList)){
            return null;
        }
        //-----------------------------过滤掉不属于当前身份的规则-----------------------------------
        return roleRuleList.stream()
                .filter(item -> item.getIdentityId().equals(identityId))
                .collect(Collectors.toList());
    }

    /**
     * 构建单角色SQL
     */
    private static String buildRoleSql(List<RoleComponentRuleDTO> lists) {
        StringBuilder roleSql = new StringBuilder();
        for (RoleComponentRuleDTO item : lists) {
            //如果出现全选 则 代表全部，不需要限定范围
            if ("ALL".equals(item.getRuleValue())) {
                continue;
            }
            //将规则转换成SQL
            String filedSql = convertRuleToSql(item);

            roleSql.append(SQL_AND).append(filedSql);
        }
        return roleSql.toString();
    }


    /**
     * 将单一规则转化成SQL，默认全部使用 In
     * ruleCode : area_test
     * ruleValue : 区域1，区域2，区域3
     * @param rule 规则值
     */
    private static String convertRuleToSql(RoleComponentRuleDTO rule) {
        String whereCondition = " in ";
        String ruleValueConvert = getInConditionValue(rule.getRuleValue());
        return rule.getRuleCode() + whereCondition + ruleValueConvert;
    }


    /**
     * IN字符串转换
     * 区域1, 区域2, 区域3  --> ("区域1","区域2","区域3")
     * 江西大区  --> ("江西大区")
     */
    private static String getInConditionValue(String ruleValue) {
        String[] temp = ruleValue.split(",");
        StringBuilder res = new StringBuilder();
        for (String string : temp) {
            res.append(",'").append(string).append("'");
        }
        return "(" + res.substring(1) + ")";
    }

    /**
     * 拼接单角色的SQL
     * @param sqlBuilder 总的SQL
     * @param roleSql    单角色SQL
     */
    private static void jointSqlByRoles(StringBuilder sqlBuilder, String roleSql) {
        roleSql = roleSql.replaceFirst(SQL_AND, "");
        if (StringUtils.isEmpty(sqlBuilder.toString())) {
            sqlBuilder.append(String.format(SQL_JOINT, roleSql));
        } else {
            sqlBuilder.append(SQL_OR).append(String.format(SQL_JOINT, roleSql));
        }
    }

    /**
     *
     * 1. 对当前身份根据规则编码分组-去除不同角色中相同编码且规则值为ALL的规则
     * 2. 对角色进行分组
     * @param conditionList 数据规则
     * @return 分组后的规则list
     */
    private static Map<String, List<RoleComponentRuleDTO>> getRuleMapByRoleId(List<RoleComponentRuleDTO> conditionList) {
    //--------过滤掉不属于当前身份的规则,并对条件编码进行分组-----------------------------------
    Map<String, List<RoleComponentRuleDTO>> conditionMap = conditionList.stream().collect(Collectors.groupingBy(RoleComponentRuleDTO::getRuleCode));

		//--------相同编码分组中存在ALL的排除掉-----------------------------------------------
    List<RoleComponentRuleDTO> newRoleRuleList = new ArrayList<>();
    if (MapUtils.isNotEmpty(conditionMap)) {
    	for (Map.Entry<String, List<RoleComponentRuleDTO>> entry : conditionMap.entrySet()) {
      	boolean flag = true;
        List<RoleComponentRuleDTO> lists = entry.getValue();
        for (RoleComponentRuleDTO item : lists) {
        	if ("ALL".equals(item.getRuleValue())) {
          	flag = false;
            break;
          }
        }
        
        if (flag) {
           newRoleRuleList.addAll(lists);
        }
     	}
     }
     if (CollectionUtils.isNotEmpty(newRoleRuleList)) {
        return newRoleRuleList.stream().collect(Collectors.groupingBy(RoleComponentRuleDTO::getRoleId));
      }
     return Maps.newHashMap();
    }
}

核心类，用于生成数据权限查询的SQL脚本。

Dao层实现

<select id="getLeaderOverviewSve" resultType="com.ifly.center.entity.SalesProjOverviewSve">
	SELECT <include refid="column_list"/>  FROM U_STD_ADS.LTC_SALES_PROJ_OVERVIEW_SVE
  <where>
  	<if test="permissionSql != null and permissionSql != ''">
  		${permissionSql}
    </if>
 	</where>
</select>

Dao层接受service层传入已经生成好的sql语句，作为查询条件直接拼接在业务语句之后。

小结

以上，就是数据权限的实现过程，其实代码实现并不复杂，主要还是得理解其中的实现原理。如果你也有数据权限的需求，不妨参考一下。

加载全部内容