mysql 树形结构_结合RBAC模型讲解权限管理系统需求及表结构创建

最新推荐文章于 2024-07-15 10:18:17 发布

weixin_39657662

最新推荐文章于 2024-07-15 10:18:17 发布

阅读量174

点赞数

文章标签： mysql 树形结构树形结构叶子节点的作用用户对自己创建的表拥有哪些权限用户权限表结构设计

结合RBAC模型讲解权限管理系统需求及表结构创建

在本号之前的文章中，已经为大家介绍了很多关于Spring Security的使用方法，也介绍了RBAC的基于角色权限控制模型。但是很多朋友虽然已经理解了RBAC控制模型，但是仍有很多的问题阻碍他们进一步开发。比如：

RBAC模型的表结构该如何创建？
具体到某个页面，某个按钮权限是如何控制的？
为了配合登录验证表，用户表中应该包含哪些核心字段？
这些字段与登录验证或权限分配的需求有什么关系？

那么本文就希望将这些问题，与大家进行一下分享。

一、回顾RBAC权限模型

回顾RBAC权限模型

用户与角色之间是多对多的关系，一个用户有多个角色，一个角色包含多个用户
角色与权限之间是多对多关系，一个角色有多种权限，一个权限可以属于多个角色

上图中：

User是用户表，存储用户基本信息
Role是角色表，存储角色相关信息
Menu(菜单)是权限表，存储系统包含哪些菜单及其属性
UserRole是用户和角色的关系表
RoleMenu是角色和权限的关系表

本文讲解只将权限控制到菜单的访问级别，即控制页面的访问权限。如果想控制到页面中按钮级别的访问，可以参考Menu与RoleMenu的模式同样的实现方式。或者干脆在menu表里面加上一个字段区别该条记录是菜单项还是按钮。

为了有理有据，我们参考一个比较优秀的开源项目界面：若依后台管理系统。

二、组织部门管理

2.1.需求分析

之所以先将部门管理提出来讲一下，是因为部门管理没有在我们上面的RBAC权限模型中进行提现。但是部门这样一个实体仍然是，后端管理系统的一个重要组成部分。通常有如下的需求：

部门要能体现出上下级的结构(如上图中的红框)。在关系型数据库中。这就需要使用到部门id及上级部门id，来组合成一个树形结构。这个知识是SQL学习中必备的知识，如果您还不知道，请自行学习。
如果组织与用户之间是一对多的关系，就在用户表中加上一个org_id标识用户所属的组织。原则是：实体关系在多的那一边维护。比如：是让老师记住自己的学生容易，还是让学生记住自己的老师更容易？
如果组织与用户是多对多关系，这种情况现实需求也有可能存在。比如：某人在某单位既是生产部长，又是技术部长。所以他及归属于技术部。也归属于生产部。对于这种情况有两种解决方案，把该人员放到公司级别，而不是放到部门级别。另外一种就是从数据库结构上创建User与Org组织之间的多对多关系。
组织信息包含一些基本信息，如组织名称、组织状态、展现排序、创建时间
另外，要有基本的组织的增删改查功能

2.2 组织部门表的CreateSQL

以下SQL以MySQL为例:

CREATE TABLE `sys_org` (`id` INT(11) NOT NULL AUTO_INCREMENT,`org_pid` INT(11) NOT NULL COMMENT '上级组织编码',`org_pids` VARCHAR(64) NOT NULL COMMENT '所有的父节点id',`is_leaf` TINYINT(4) NOT NULL COMMENT '0:不是叶子节点，1:是叶子节点',`org_name` VARCHAR(32) NOT NULL COMMENT '组织名',`address` VARCHAR(64) NULL DEFAULT NULL COMMENT '地址',`phone` VARCHAR(13) NULL DEFAULT NULL COMMENT '电话',`email` VARCHAR(32) NULL DEFAULT NULL COMMENT '邮件',`sort` TINYINT(4) NULL DEFAULT NULL COMMENT '排序',`level` TINYINT(4) NOT NULL COMMENT '组织层级',`status` TINYINT(4) NOT NULL COMMENT '0:启用,1:禁用',PRIMARY KEY (`id`))COMMENT='系统组织结构表'COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=1;

注意：mysql没有oracle中的start with connect by的树形数据汇总SQL。所以通常需要为了方便管理组织之间的上下级树形关系，需要加上一些特殊字段，如：org_pids：该组织所有上级组织id逗号分隔，即包括上级的上级；is_leaf是否是叶子结点；level组织所属的层级(1,2,3)。

三、菜单权限管理

3.1 需求分析

由上图可以看出，菜单仍然是树形结构，所以数据库表必须有id与menu_pid字段
必要字段：菜单跳转的url、是否启用、菜单排序、菜单的icon矢量图标等
最重要的是菜单要有一个权限标志，具有唯一性。通常可以使用菜单跳转的url路径作为权限标志。此标志作为权限管理框架识别用户是否具有某个页面查看权限的重要标志
需要具备菜单的增删改查基本功能
如果希望将菜单权限和按钮超链接相关权限放到同一个表里面，可以新增一个字段。用户标志该权限记录是菜单访问权限还是按钮访问权限。

3.2 菜单权限表的CreateSQL

CREATE TABLE `sys_menu` (`id` INT(11) NOT NULL AUTO_INCREMENT,`menu_pid` INT(11) NOT NULL COMMENT '父菜单ID',`menu_pids` VARCHAR(64) NOT NULL COMMENT '当前菜单所有父菜单',`is_leaf` TINYINT(4) NOT NULL COMMENT '0:不是叶子节点，1:是叶子节点',`name` VARCHAR(16) NOT NULL COMMENT '菜单名称',`url` VARCHAR(64) NOT NULL COMMENT '跳转URL',`icon` VARCHAR(45) NULL DEFAULT NULL,`icon_color` VARCHAR(16) NULL DEFAULT NULL,`sort` TINYINT(4) NULL DEFAULT NULL COMMENT '排序',`level` TINYINT(4) NOT NULL COMMENT '菜单层级',`status` TINYINT(4) NOT NULL COMMENT '0:启用,1:禁用',PRIMARY KEY (`id`))COMMENT='系统菜单表'COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=83;

四、角色管理

上图为角色修改及分配权限的页面

4.1.需求分析

角色本身的管理需要注意的点非常少，就是简单的增删改查。重点在于角色分配该如何做。
角色表包含角色id，角色名称，备注、排序顺序这些基本信息就足够了
为角色分配权限：以角色为基础勾选菜单权限或者操作权限，然后先删除sys_role_menu表内该角色的所有记录，在将新勾选的权限数据逐条插入sys_role_menu表。
sys_role_menu的结构很简单，记录role_id与menu_id，一个角色拥有某一个权限就是一条记录。
角色要有一个全局唯一的标识，因为角色本身也是一种权限。可以通过判断角色来判断某用户的操作是否合法。
通常的需求：不会在角色管理界面为角色添加用户，而是在用户管理界面为用户分配角色。

4.2.角色表与角色菜单权限关联表的的CreateSQL

CREATE TABLE `sys_role` (`id` INT(11) NOT NULL AUTO_INCREMENT,`role_id` VARCHAR(16) NOT NULL COMMENT '角色ID',`role_name` VARCHAR(16) NOT NULL COMMENT '角色名',`role_flag` VARCHAR(64) NULL DEFAULT NULL COMMENT '角色标识',`sort` INT(11) NULL DEFAULT NULL COMMENT '排序',PRIMARY KEY (`id`))COMMENT='系统角色表'COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=2;

CREATE TABLE `sys_role_menu` (`id` INT(11) NOT NULL AUTO_INCREMENT,`role_id` VARCHAR(16) NOT NULL COMMENT '角色ID',`menu_id` INT(11) NOT NULL COMMENT '菜单ID',PRIMARY KEY (`id`))COMMENT='角色菜单多对多关联表'COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=534;

五、用户管理

5.1.需求分析

上图中点击左侧的组织菜单树结点，要能显示出该组织下的所有人员(系统用户)。在组织与用户是一对多的关系中，需要在用户表加上org_id字段，用于查询某个组织下的所有用户。
用户表中要保存用户的用户名、加密后的密码。页面提供密码修改或重置的功能。
角色分配:实际上为用户分配角色，与为角色分配权限的设计原则是一样的。所以可以参考。
实现用户基本信息的增删改查功能

5.2.sys_user 用户信息表及用户角色关系表的CreateSQL

CREATE TABLE `sys_user` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `org_id` INT(11) NOT NULL, `username` VARCHAR(64) NULL DEFAULT NULL COMMENT '用户名', `password` VARCHAR(64) NULL DEFAULT NULL COMMENT '密码', `enabled` INT(11) NULL DEFAULT '1' COMMENT '用户账户是否可用', `locked` INT(11) NULL DEFAULT '0' COMMENT '用户账户是否被锁定', `lockrelease_time` TIMESTAMP NULL '用户账户锁定到期时间', `expired_time` TIMESTAMP NULL '用户账户过期时间', `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户账户创建时间',PRIMARY KEY (`id`))COMMENT='用户信息表'ENGINE=InnoDB;

在用户的信息表中，体现了一些隐藏的需求。如：多次登录锁定与锁定到期时间的关系。账号有效期的设定规则等。

当然用户表中，根据业务的不同还可能加更多的信息，比如：用户头像等等。但是通常在比较大型的业务系统开发中，业务模块中使用的用户表和在权限管理模块使用的用户表通常不是一个，而是根据某些唯一字段弱关联，分开存放。这样做的好处在于：经常发生变化的业务需求，不会去影响不经常变化的权限模型。

CREATE TABLE `sys_user_role` (`id` INT(11) NOT NULL AUTO_INCREMENT,`role_id` VARCHAR(16) NULL DEFAULT NULL,`user_id` VARCHAR(18) NULL DEFAULT NULL,PRIMARY KEY (`id`))COLLATE='utf8_general_ci'ENGINE=InnoDBAUTO_INCREMENT=94;