一句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 ) 
FROMt_sys_account_role pJOIN t_sys_role r ON p.role_Id = r.id AND p.account_id = 1 AND r.STATUS = 1JOIN 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’ 开头的所有资源列表。

SELECTr.id 
FROMt_sys_resource rJOIN t_sys_role_resource rr ON r.id = rr.resource_idJOIN 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，从资源表中查询详细信息。

SELECTm.id,m.pid,m.weight,m.NAME,m.CODE,m.meta,m.type,m.STATUS 
FROMt_sys_resource m 
WHEREm.STATUS = 1 AND m.id IN (SELECT DISTINCT( rmb.resource_id ) FROMt_sys_account_role pJOIN t_sys_role r ON p.role_Id = r.id AND p.account_id = 1 AND r.STATUS = 1JOIN t_sys_role_resource rmb ON rmb.role_id = r.id UNIONSELECTr.id FROMt_sys_resource rJOIN t_sys_role_resource rr ON r.id = rr.resource_idJOIN t_sys_role ro ON rr.role_id = ro.id AND ro.STATUS = 1 AND ro.CODE LIKE 'COMMON%' ) 
ORDER BYm.pid ASC,m.weight ASC 

最终查询结果如下:

相关数据库查询知识

上一个经典联表查询图：

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

最后

最近还整理一份JavaScript与ES的笔记，一共25个重要的知识点，对每个知识点都进行了讲解和分析。能帮你快速掌握JavaScript与ES的相关知识，提升工作效率。

有需要的小伙伴，可以点击下方卡片领取，无偿分享