RBAC权限模型

竹下烟雨客

已于 2022-11-03 18:21:07 修改

阅读量237

点赞数

分类专栏：基础框架文章标签：学习 java

于 2022-11-03 18:01:49 首次发布

本文链接：https://blog.csdn.net/weixin_58973530/article/details/127675385

版权

基础框架专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、前言

RBAC ：

全称：基于角色的访问控制（Role-Based Access Control)

概念：通过用户与角色多对多，角色与权限多对多的关系来到权限控制的效果

二、数据库模型

三、数据库初始化

t_users

CREATE TABLE `t_users` (
  `user_id` bigint NOT NULL COMMENT '用户id',
  `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户姓名',
  `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户密码',
  `poster_url` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像地址',
  `phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号码',
  `status` int DEFAULT '1' COMMENT '用户状态。0：可用。1：不可用',
  `is_deleted` int DEFAULT '0' COMMENT '软删除，此数据是否删除。0：未删除，1：已删除',
  `version` int DEFAULT '0' COMMENT '版本号。每次数据更新时，版本号+1',
  `create_time` int DEFAULT NULL COMMENT '创建时间',
  `update_time` int DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

insert into `t_users` (`user_id`, `username`, `name`, `password`, `poster_url`, `phone`, `status`, `is_deleted`, `version`, `create_time`, `update_time`, `dept_id`, `timezone_id`) values('99999999','admin','admin','$2a$10$3JHuxSuIvUNRT2fUMvcDNOx0uVcUU5RM8OFm6aHpYgCAsi3U3HXLy',NULL,NULL,'1','0','0',NULL,NULL,NULL,'1');

t_roles

CREATE TABLE `t_roles` (
  `role_id` bigint NOT NULL COMMENT '角色id',
  `role` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '角色名称。使用ROLE_拼接角色名称的方式',
  `role_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '角色code',
  `status` int DEFAULT NULL COMMENT '是否可用。0：不可用，1可用',
  `version` int DEFAULT '0' COMMENT '版本号。每次修改数据，版本号+1',
  `create_time` int DEFAULT NULL COMMENT '创建时间',
  `update_time` int DEFAULT NULL COMMENT '更新时间',
  `description` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_deleted` int DEFAULT NULL COMMENT '是否删除。0：数据未删除，1：数据已删除',
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

insert into `t_roles` (`role_id`, `role`, `role_code`, `status`, `version`, `create_time`, `update_time`, `description`, `is_deleted`, `dept_id`, `user_id`) values('99999999','超级管理员','ROLE_ADMIN','1','0',NULL,NULL,NULL,'0',NULL,NULL);

t_user_role

CREATE TABLE `t_user_role` (
  `id` bigint NOT NULL COMMENT 'id',
  `user_id` bigint DEFAULT NULL COMMENT '用户id',
  `role_id` bigint DEFAULT NULL COMMENT '角色id',
  `create_time` int DEFAULT NULL COMMENT '创建时间',
  `update_time` int DEFAULT NULL COMMENT '更新时间',
  `is_deleted` int DEFAULT NULL COMMENT '数据是否删除。0：数据未删除，1：数据已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

insert into `t_user_role` (`id`, `user_id`, `role_id`, `create_time`, `update_time`, `is_deleted`) values('99999999','99999999','99999999',NULL,NULL,'0');

t_pemissions

CREATE TABLE `t_permissions` (
  `id` bigint NOT NULL COMMENT 'id',
  `parent_id` bigint DEFAULT NULL COMMENT '父菜单id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名称',
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '路径',
  `permission` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '权限',
  `sort` int DEFAULT NULL COMMENT '排序',
  `type` int DEFAULT NULL COMMENT '类型:1:菜单；2：按钮',
  `create_time` int DEFAULT NULL COMMENT '创建时间',
  `update_time` int DEFAULT NULL COMMENT '更新时间',
  `depth` int DEFAULT '1' COMMENT '几级菜单',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='菜单表'

insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('1','0','系统管理','/System','system','1','1',NULL,NULL,'1');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('2','1','用户管理','/User','user','2','1',NULL,NULL,'2');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('3','1','角色管理','/Role','role','3','1',NULL,NULL,'2');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('4','1','权限管理','/Permission','permission','4','1',NULL,NULL,'2');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('5','2','用户新增','/user/add','user:add','1','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('6','3','角色新增','/Role/add','role:add','1','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('7','2','用户查询','/user/list','user:list','2','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('8','2','用户修改','/user/update','user:update','2','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('9','2','用户删除','/user/delete','user:delete','2','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('10','3','角色查询','/role/list','role:list','3','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('11','3','角色修改','/role/update','role:update','3','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('12','3','角色删除','/role/delete','role:delete','3','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('13','4','权限查询','/permission/list','permission:list','4','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('14','4','权限新增','/permission/add','permission:add','4','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('15','4','权限修改','/permission/update','permission:update','4','2',NULL,NULL,'3');
insert into `t_permissions` (`id`, `parent_id`, `name`, `url`, `permission`, `sort`, `type`, `create_time`, `update_time`, `depth`) values('16','4','权限删除','/permission/delete','permission:delete','4','2',NULL,NULL,'3');

t_role_permission

CREATE TABLE `t_role_permission` (
  `id` bigint NOT NULL COMMENT 'id',
  `role_id` bigint DEFAULT NULL COMMENT '角色id',
  `permission_id` bigint DEFAULT NULL COMMENT '权限id',
  `create_time` int DEFAULT NULL COMMENT '创建时间',
  `update_time` int DEFAULT NULL COMMENT '更新时间',
  `is_deleted` int DEFAULT NULL COMMENT '是否删除。0：数据未删除，1：数据已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('1','99999999','1',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('2','99999999','2',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('3','99999999','3',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('4','99999999','4',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('5','99999999','5',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('6','99999999','6',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('7','99999999','7',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('8','99999999','8',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('9','99999999','9',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('10','99999999','10',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('11','99999999','11',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('12','99999999','12',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('13','99999999','13',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('14','99999999','14',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('15','99999999','15',NULL,NULL,'0');
insert into `t_role_permission` (`id`, `role_id`, `permission_id`, `create_time`, `update_time`, `is_deleted`) values('16','99999999','16',NULL,NULL,'0');

未完待续。。。