MySQL資料許可權的實現詳情

2022-08-12 14:05:38

資料許可權模型

上篇文章的資料模型是基於傳統的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語句，作為查詢條件直接拼接在業務語句之後。