一句SQL看懂权限权限设计(mayfly-go开源项目)

前言

上次我们讲了如何实现安全登录，登录完成后会对用户权限进行查询(权限包含：系统权限(路由权限)、资源按钮权限)。 那在本项目中作者是如何设计权限的?

设计权限

设计权限之前依然先梳理下权限模块需求：（下文中模块可以理解为前端router）

• 应该有基本模块权限。（普通用户能进来）

• 应该有角色，角色和资源 关系是 N:N

• 用户归属于某个角色， 角色和用户 关系是 N:N

资源表

里面会记录出资源类型、状态、标识码（和前端资源ID对应）、meta 其他额外信息等。

目标是：通过此表能准确判断出某个资源详细信息。

// 资源表（路由 或者 按钮）
CREATE TABLE `t_sys_resource` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL COMMENT '父节点id',
  `type` tinyint(255) NOT NULL COMMENT '1：菜单路由；2：资源（按钮等）',
  `status` int(255) NOT NULL COMMENT '状态；1:可用，-1:禁用',
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
  `code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '菜单路由为path，其他为唯一标识',
  `weight` int(11) DEFAULT NULL COMMENT '权重顺序',
  `meta` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '元数据',
  `creator_id` bigint(20) NOT NULL,
  `creator` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `modifier_id` bigint(20) NOT NULL,
  `modifier` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='资源表'

表中存放数据如下:

角色表

角色表 记录 系统中存在的角色。核心：角色类型、状态等

CREATE TABLE `t_sys_role` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) COLLATE utf8mb4_bin NOT NULL,
  `code` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '角色code',
  `status` tinyint(255) DEFAULT NULL,
  `remark` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `type` tinyint(2) NOT NULL COMMENT '类型：1:公共角色；2:特殊角色',
  `create_time` datetime DEFAULT NULL,
  `creator_id` bigint(20) DEFAULT NULL,
  `creator` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `modifier_id` bigint(20) DEFAULT NULL,
  `modifier` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='角色表'

存放数据如下: (基础权限 都分配给一个角色)

角色-资源 关联表

记录用户和角色的对应关系

CREATE TABLE `t_sys_role_resource` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `role_id` bigint(20) NOT NULL,
  `resource_id` bigint(20) NOT NULL,
  `creator_id` bigint(20) unsigned DEFAULT NULL,
  `creator` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=526 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='角色资源关联表'

账号-角色 关联表

记录 账号 和 角色映射关系

CREATE TABLE `t_sys_account_role` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `account_id` bigint(20) NOT NULL COMMENT '账号id',
  `role_id` bigint(20) NOT NULL COMMENT '角色id',
  `creator` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL,
  `creator_id` bigint(20) unsigned DEFAULT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='账号角色关联表'

查询用户权限SQL

子查询1： 查找用户所有资源id

含义：通过userid_id， 查询出role_id列表，再查询出 resource_id 列表。

SELECT DISTINCT
   ( rmb.resource_id ) 
FROM
    t_sys_account_role p
    JOIN t_sys_role r ON p.role_Id = r.id 
    AND p.account_id = 1 
    AND r.STATUS = 1
    JOIN t_sys_role_resource rmb ON rmb.role_id = r.id
       
/* t_sys_role_resource 记录 账号 和 用户映射关系
   t_sys_role 角色表 记录 系统中存在的角色
   t_sys_role_resource 角色资源表
   这个查询的含义是 查找某个用户下的所有资源ID
 */

子查询2: 查询公共资源（所有账号都应该有的权限）

角色中表Code字段是 'COMMON' 开头的所有资源列表。

SELECT
    r.id 
FROM
    t_sys_resource r
    JOIN t_sys_role_resource rr ON r.id = rr.resource_id
    JOIN t_sys_role ro ON rr.role_id = ro.id 
    AND ro.STATUS = 1 
    AND ro.CODE LIKE 'COMMON%'

/* 
    t_sys_resource 资源表（路由 或者 按钮）
    t_sys_role_resource 记录 账号 和 用户映射关系
    t_sys_role 角色表 记录 系统中存在的角色
   这个查询的含义是: 查找公共角色下的资源
 */

最终： 最终用户资源列表

通过合并前面两步查询(UNION)，获得所有资源列表ID，从资源表中查询详细信息。

SELECT
        m.id,
        m.pid,
        m.weight,
        m.NAME,
        m.CODE,
        m.meta,
        m.type,
        m.STATUS 
FROM
        t_sys_resource m 
WHERE
        m.STATUS = 1 
        AND m.id IN (
        SELECT DISTINCT
                ( rmb.resource_id ) 
        FROM
                t_sys_account_role p
                JOIN t_sys_role r ON p.role_Id = r.id 
                AND p.account_id = 1 
                AND r.STATUS = 1
                JOIN t_sys_role_resource rmb ON rmb.role_id = r.id UNION
        SELECT
                r.id 
        FROM
                t_sys_resource r
                JOIN t_sys_role_resource rr ON r.id = rr.resource_id
                JOIN t_sys_role ro ON rr.role_id = ro.id 
                AND ro.STATUS = 1 
                AND ro.CODE LIKE 'COMMON%' 
        ) 
ORDER BY
        m.pid ASC,
        m.weight ASC

最终查询结果如下:

相关数据库查询知识

上一个经典联表查询图：

如何记住这些查询呢？ 这些查询结果都是笛卡尔积查询子集，从子集中按照一定条件捞出来。