MySQL数据权限的实现详情

目录

数据权限模型

实现效果

数据模型

数据权限表结构

授权维度表

具体授权维度表(产品线)

组件路由表

角色表

角色组件绑定表

角色组件授权规则表(核心)

实现过程

代码实现

自定义数据权限注解

定义数据权限处理切面

数据权限工具类

查询组件规则

Controller调用

构建数据权限SQL

Dao层实现

小结

数据权限模型

上篇文章的数据模型是基于传统的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语句,作为查询条件直接拼接在业务语句之后。

小结

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

到此这篇关于MySQL数据权限的实现详情的文章就介绍到这了,更多相关SQL数据权限内容请搜索易知道(ezd.cc)以前的文章或继续浏览下面的相关文章希望大家以后多多支持易知道(ezd.cc)!

推荐阅读

    mac怎么安装mysql| macbook怎么下载mysql

    mac怎么安装mysql| macbook怎么下载mysql,标签,学习python 不得不学习的就是数据库,那么在开始学习数据库之前,那么今天我们先说下怎么安装m

    MySQL更新更新页面1/4。

    MySQL更新更新页面1/4。,,插入和替换 插入和替换语句的功能是将新数据插入表中,这两个语句的语法相似,它们之间的主要区别是如何处理重复的

    mysql数据库启动失败

    mysql数据库启动失败,报错,非正常,1、原因 公司服务器故障,非正常停机导致数据库启动失败。 报错信息 [ [email protected] dmp]# /etc/ini

    mysql长整型是什么

    mysql长整型是什么,整型,数据类型,语法,用户,填充,版本,MySQL长整型是“BIGINT”,是MySQL中最常用的数据类型之一,其可以用来存储较大的整数值,与

    深入理解MySQL分区

    深入理解MySQL分区,查询,数据,列表,索引,操作,按键,MySQL数据库是一个开源的关系型数据库管理系统。在一些大型数据环境中,为了更好地管理数据、

    mysql 如何查询

    mysql 如何查询,查询,数据,语句,条件,选取,排序,MySQL是一款常用的关系型数据库管理系统,被广泛应用于各种网站和应用开发。在MySQL中查询数据是

    MySQL中怎么实现分页操作

    MySQL中怎么实现分页操作,数据,显示,偏移量,分页,查询,初始,一、 背景什么是分页,就是查询时候数据量太大,一次性返回所有查询结果既耗费网络资源

    mysql怎么连接数据库

    mysql怎么连接数据库,服务,启动,数据库,命令,登录,步骤,mysql连接数据库的方法:1、通过计算机管理方式或通过命令行方式执行“net start mysql”

    mysql 查询拼接

    mysql 查询拼接,函数,字段,字符串,查询,分隔符,连接,MySQL 查询拼接在使用 MySQL 进行查询时,有时需要将多个字段或多张表中的字段进行拼接,这时

    怎么启动 mysql

    怎么启动 mysql,启动,服务器,输入,命令提示符,终端,命令,MySQL是一种广泛使用的关系型数据库管理系统。它是一种可扩展性强、性能卓越、跨平台

    mysql 如何卸载

    mysql 如何卸载,卸载,服务,命令,软件包,数据,安装,MySQL是一种广泛使用的数据库管理系统,用于管理数据的存储、检索和更新。在某些情况下,您需要

    mysql视图是什么

    mysql视图是什么,视图,查询,数据,替换,年龄,引用,mysql视图是一个虚拟表,其内容由查询定义;视图包含系列带有名称的列和行数据,而行和列数据来自定