• RBAC（Role-Based Access Control）即：基于角色的权限控制。通过角色关联用户，角色关联权限的方式间接赋予用户权限。可以参数地址RBAC权限管理模型：基本模型及角色模型解析及举例一文介绍。

现在我们项目组在RBAC传统基础上进行了扩展，引入了用户组/部门概念，下面详细介绍。

1.关系图

图1 权限模型

数据库设计

2.数据库设计

备注:用户的权限总合是用户组+用户的并集。

数据库设计需根据实际的开发需求而定，表中字段仅仅包含初始参数，数据库脚本如下所示！

CREATE TABLE `sys_group`  (
  `key_id` bigint NOT NULL COMMENT '分组主键id',
  `group_name` varchar(30) NULL COMMENT '组名',
  PRIMARY KEY (`key_id`)
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '分组信息' ROW_FORMAT = DYNAMIC;

CREATE TABLE `sys_group_role`  (
  `key_id` bigint NOT NULL COMMENT '组别角色主键id',
  `group_id` bigint NULL COMMENT '组别id',
  `role_id` bigint NULL COMMENT '角色id',
  PRIMARY KEY (`key_id`)
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '组别角色信息' ROW_FORMAT = DYNAMIC;

CREATE TABLE `sys_menu`  (
  `key_id` bigint NOT NULL COMMENT '菜单主键id',
  `menu_name` varchar(30) NULL COMMENT '菜单名称',
  PRIMARY KEY (`key_id`)
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'sys菜单信息' ROW_FORMAT = DYNAMIC;

CREATE TABLE `sys_permission`  (
  `key_id` bigint NOT NULL COMMENT '权限主键id',
  `permission_name` varchar(50) NULL COMMENT '权限名称',
  PRIMARY KEY (`key_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '权限信息表' ROW_FORMAT = DYNAMIC;

CREATE TABLE `sys_permission_menu`  (
  `key_id` bigint NOT NULL COMMENT '权限菜单id',
  `permission_id` bigint NULL COMMENT '权限id',
  `menu_id` bigint NULL COMMENT '菜单id',
  PRIMARY KEY (`key_id`)
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '权限菜单信息' ROW_FORMAT = DYNAMIC;

CREATE TABLE `sys_role`  (
  `key_id` bigint NOT NULL COMMENT '角色id',
  `role_name` varchar(100) NULL COMMENT '角色名称',
  PRIMARY KEY (`key_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '系统角色' ROW_FORMAT = DYNAMIC;

CREATE TABLE `sys_role_permission`  (
  `key_id` bigint NOT NULL COMMENT '角色权限主键id',
  `permission_id` bigint NULL COMMENT '权限id ',
  `role_id` bigint NULL COMMENT '角色id',
  PRIMARY KEY (`key_id`)
);

CREATE TABLE `sys_user`  (
  `key_id` bigint NOT NULL COMMENT '用户主键id',
  `user_name` varchar(20) NULL COMMENT '用户姓名',
  `user_phone` varchar(20) NULL COMMENT '用户电话',
  PRIMARY KEY (`key_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '系统用户管理' ROW_FORMAT = DYNAMIC;

CREATE TABLE `sys_user_group`  (
  `key_id` bigint NOT NULL COMMENT '用户分组id',
  `group_id` bigint NULL COMMENT '组别id',
  `user_id ` bigint NULL COMMENT '用户id',
  PRIMARY KEY (`key_id`)
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户分组信息' ROW_FORMAT = DYNAMIC;

CREATE TABLE `sys_user_role`  (
  `key_id` bigint NOT NULL COMMENT '用户角色主键id',
  `user_id` bigint NOT NULL COMMENT '用户Id',
  `role_id` bigint NOT NULL COMMENT '角色Id',
  PRIMARY KEY (`key_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户角色关联表' ROW_FORMAT = DYNAMIC;

ALTER TABLE `sys_group_role` ADD CONSTRAINT `fk_sys_group_role_sys_group_role_1` FOREIGN KEY (`group_id`) REFERENCES `sys_group` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_group_role` ADD CONSTRAINT `fk_sys_group_role_sys_group_role_2` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_permission_menu` ADD CONSTRAINT `fk_sys_permission_menu_sys_permission_menu_1` FOREIGN KEY (`permission_id`) REFERENCES `sys_permission` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_permission_menu` ADD CONSTRAINT `fk_sys_permission_menu_sys_permission_menu_2` FOREIGN KEY (`menu_id`) REFERENCES `sys_menu` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_role_permission` ADD CONSTRAINT `fk_sys_role_permission_sys_role_permission_1` FOREIGN KEY (`permission_id`) REFERENCES `sys_permission` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_role_permission` ADD CONSTRAINT `fk_sys_role_permission_sys_role_permission_2` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_user_group` ADD CONSTRAINT `fk_sys_user_group_sys_user_group_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_user_group` ADD CONSTRAINT `fk_sys_user_group_sys_user_group_2` FOREIGN KEY (`group_id`) REFERENCES `sys_group` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_user_role` ADD CONSTRAINT `fk_sys_user_role_sys_user_role_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `sys_user_role` ADD CONSTRAINT `fk_sys_user_role_sys_user_role_2` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`key_id`) ON DELETE CASCADE ON UPDATE CASCADE;