权限管理01-RBAC数据库表设计

高秉文

已于 2022-02-05 11:55:43 修改

阅读量1.9k

点赞数

分类专栏：权限管理实战文章标签： java

于 2022-02-05 11:54:30 首次发布

本文链接：https://blog.csdn.net/g759780748/article/details/122789146

版权

权限管理实战专栏收录该内容

4 篇文章 1 订阅

订阅专栏

本文介绍了如何通过创建用户表、角色表和关联表来实现用户角色管理，包括sys_user、sys_role和sys_user_role表的设计，以及角色与菜单的权限分配。重点展示了如何使用SQL语句进行表结构设置和数据插入，以支持权限控制功能。

摘要由CSDN通过智能技术生成

0 分析

1 建表语句

(1)用户表

CREATE TABLE `sys_user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(64) DEFAULT NULL COMMENT '用户名',
  `password` VARCHAR(64) DEFAULT NULL COMMENT '密码',
  `avatar` VARCHAR(255) DEFAULT NULL COMMENT '头像',
  `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `email` VARCHAR(64) DEFAULT NULL COMMENT '邮箱',
  `created` DATETIME DEFAULT NULL,
  `updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

insert into `sys_user` (`id`, `username`, `password`, `avatar`, `phone`, `email`, `created`, `updated`) values('1','admin','$2a$10$9Eu986L2ssW52quOquNMAeZYKPI9mJaoOjs35tR3E6juWU69YM7ly','https://img1.baidu.com/it/u=2716398045,2043787292&fm=253&fmt=auto&app=120&f=JPEG?w=800&h=800','18391818000','admin@163.com','2022-02-04 14:28:42','2022-02-04 14:28:48');
insert into `sys_user` (`id`, `username`, `password`, `avatar`, `phone`, `email`, `created`, `updated`) values('2','manager','$2a$10$9Eu986L2ssW52quOquNMAeZYKPI9mJaoOjs35tR3E6juWU69YM7ly','https://img2.baidu.com/it/u=4284492163,2611934395&fm=253&fmt=auto&app=138&f=JPEG?w=500&h=500','18391818001','manager@163.com','2022-02-05 11:36:16','2022-02-05 11:36:20');
insert into `sys_user` (`id`, `username`, `password`, `avatar`, `phone`, `email`, `created`, `updated`) values('3','user','$2a$10$9Eu986L2ssW52quOquNMAeZYKPI9mJaoOjs35tR3E6juWU69YM7ly','https://img2.baidu.com/it/u=1052567076,3275246168&fm=253&fmt=auto&app=120&f=JPEG?w=800&h=800','18391818002','user@163.com','2022-02-05 11:36:18','2022-02-05 11:36:22');

(2) 角色表

CREATE TABLE `sys_role` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL COMMENT '角色名',
  `code` varchar(64) NOT NULL COMMENT '角色代码',
  `remark` varchar(64) DEFAULT NULL COMMENT '备注',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`) USING BTREE,
  UNIQUE KEY `code` (`code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

insert into `sys_role` (`id`, `name`, `code`, `remark`, `created`, `updated`) values('1','系统管理员','admin','具有所有权限','2022-02-04 15:53:44','2022-02-04 15:53:48');
insert into `sys_role` (`id`, `name`, `code`, `remark`, `created`, `updated`) values('2','经理','manager','能够进行一些审批操作','2022-02-05 11:18:43','2022-02-05 11:18:46');
insert into `sys_role` (`id`, `name`, `code`, `remark`, `created`, `updated`) values('3','普通员工','user','只能看到普通权限','2022-02-05 11:18:21','2022-02-05 11:18:23');

(3)用户角色关联表

CREATE TABLE `sys_user_role` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `role_id` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户-角色关系表';

insert into `sys_user_role` (`id`, `user_id`, `role_id`) values('1','2','2');
insert into `sys_user_role` (`id`, `user_id`, `role_id`) values('2','3','3');

(4)菜单表

CREATE TABLE `sys_menu` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `parent_id` bigint DEFAULT NULL COMMENT '父菜单ID,一级菜单为0',
  `name` varchar(64) NOT NULL,
  `path` varchar(255) DEFAULT '' COMMENT '菜单URL',
  `perms` varchar(255) DEFAULT '' COMMENT '授权(例：sys:user:list)',
  `type` int NOT NULL COMMENT '类型,0：目录;1：菜单;2：按钮',
  `icon` varchar(32) DEFAULT '' COMMENT '菜单图标',
  `order_num` int DEFAULT NULL COMMENT '排序',
  `created` datetime NOT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='菜单表';

insert into `sys_menu` (`id`, `parent_id`, `name`, `path`, `perms`, `type`, `icon`, `order_num`, `created`, `updated`) values('1','0','系统管理','','sys:manage','0','el-icon-setting','0','2022-02-03 07:41:46','2022-02-03 07:41:48');
insert into `sys_menu` (`id`, `parent_id`, `name`, `path`, `perms`, `type`, `icon`, `order_num`, `created`, `updated`) values('2','1','用户管理','/sys/user','sys:user:list','1','el-icon-user','0','2022-02-03 07:43:34','2022-02-03 07:43:35');
insert into `sys_menu` (`id`, `parent_id`, `name`, `path`, `perms`, `type`, `icon`, `order_num`, `created`, `updated`) values('3','1','角色管理','/sys/role','sys:role:list','1','el-icon-user-solid','1','2022-02-03 07:44:34','2022-02-03 07:44:36');
insert into `sys_menu` (`id`, `parent_id`, `name`, `path`, `perms`, `type`, `icon`, `order_num`, `created`, `updated`) values('4','1','菜单管理','/sys/menu','sys:menu:list','1','el-icon-menu','2','2022-02-03 07:45:13','2022-02-03 07:45:15');
insert into `sys_menu` (`id`, `parent_id`, `name`, `path`, `perms`, `type`, `icon`, `order_num`, `created`, `updated`) values('5','0','OA管理','','oa:manage','0','el-icon-thumb','1','2022-02-05 11:21:08','2022-02-05 11:21:09');
insert into `sys_menu` (`id`, `parent_id`, `name`, `path`, `perms`, `type`, `icon`, `order_num`, `created`, `updated`) values('6','5','群组管理','/oa/group','oa:group:list','1','el-icon-chat-round','0','2022-02-05 11:22:42','2022-02-05 11:22:45');
insert into `sys_menu` (`id`, `parent_id`, `name`, `path`, `perms`, `type`, `icon`, `order_num`, `created`, `updated`) values('7','5','我的申请','/oa/apply','oa:apply:list','1','el-icon-stopwatch','1','2022-02-05 11:23:33','2022-02-05 11:23:34');
insert into `sys_menu` (`id`, `parent_id`, `name`, `path`, `perms`, `type`, `icon`, `order_num`, `created`, `updated`) values('8','5','我的审批','/oa/approve','oa:approve:list','1','el-icon-key','2','2022-02-05 11:24:42','2022-02-05 11:24:44');

(5)角色菜单表

CREATE TABLE `sys_role_menu` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `role_id` bigint NOT NULL,
  `menu_id` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色-菜单关系表';

insert into `sys_role_menu` (`id`, `role_id`, `menu_id`) values('1','2','5');
insert into `sys_role_menu` (`id`, `role_id`, `menu_id`) values('2','2','6');
insert into `sys_role_menu` (`id`, `role_id`, `menu_id`) values('3','2','7');
insert into `sys_role_menu` (`id`, `role_id`, `menu_id`) values('4','2','8');
insert into `sys_role_menu` (`id`, `role_id`, `menu_id`) values('5','3','5');
insert into `sys_role_menu` (`id`, `role_id`, `menu_id`) values('6','3','6');
insert into `sys_role_menu` (`id`, `role_id`, `menu_id`) values('7','3','7');