分享

用户、角色、权限、菜单--数据库设计

 和谐世界 2023-04-28 发布于福建

菜单表--menu
id--------------------主键
menu---------------菜单名称
permission_id--  菜单权限
url-------------------路径
sort-----------------排序
style----------------样式(可设置css图标)
parent_id----------父主键ID
create_time-------创建时间
is_deleted---------状态(0:未删除 1:删除)

用户表--user
id---------------------主键
user_id--------------用户ID(可设置唯一索引UNIQUE)
user_name---------用户名称
password-----------密码
create_time--------创建时间
is_deleted----------状态(0:未删除 1:删除)

权限表--permission
id------------------------主键
permission_id--------权限ID(自定义)可设置唯一索引UNIQUE
permission_name---权限名称
remark-----------------说明
create_time----------创建时间
is_deleted---- -------状态(0:未删除 1:删除)

角色表--role
id-------------------主键
role_id------------角色ID(自定义)可设置唯一索引UNIQUE
role_name-------角色名称
permission_id---权限类别(主要定义角色属于哪种层级)
create_time------创建时间
is_deleted--------状态(0:未删除 1:删除)

用户角色关联表--user_role
id-------------------主键
user_id------------用户ID
role_id-------------角色ID
create_time------创建时间
is_deleted--------状态(0:未删除 1:删除)

角色权限关联表--role_permission
id-------------------主键
role_id-------------角色ID
permission_id----权限ID
create_time-------创建时间
is_deleted---------状态(0:未删除 1:删除)

建表语句SQL,并且初始化了一些数据便于理解

sys_menu菜单表

  1. -- ----------------------------
  2. -- Table structure for sys_menu
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `sys_menu`;
  5. CREATE TABLE `sys_menu` (
  6. `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  7. `menu_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '菜单名称',
  8. `permission_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '权限ID',
  9. `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '请求路径',
  10. `sort` tinyint NULL DEFAULT NULL COMMENT '排序',
  11. `style` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '样式(可设置css图标)',
  12. `parent_id` int NULL DEFAULT NULL COMMENT '父主键ID(有值的,属于该值菜单的下级菜单)',
  13. `create_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  14. `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  15. `update_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  16. `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  17. `is_deleted` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '是否删除(0:正常/1:删除)',
  18. PRIMARY KEY (`id`) USING BTREE
  19. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '菜单表' ROW_FORMAT = Dynamic;
  20. -- ----------------------------
  21. -- Records of sys_menu
  22. -- ----------------------------
  23. INSERT INTO `sys_menu` VALUES (1, '系统管理', '10001', NULL, 1, NULL, NULL, '88888888', '2021-03-23 15:09:11', NULL, NULL, 0);
  24. INSERT INTO `sys_menu` VALUES (2, '权限管理', '10002', '/sys/permission', 2, NULL, 1, '88888888', '2021-03-23 15:09:11', NULL, NULL, 0);
  25. INSERT INTO `sys_menu` VALUES (3, '角色管理', '10003', '/sys/role', 3, NULL, 1, '88888888', '2021-03-23 15:09:11', NULL, NULL, 0);
  26. INSERT INTO `sys_menu` VALUES (4, '用户管理', '10004', '/sys/user', 4, NULL, 1, '88888888', '2021-03-23 15:09:11', NULL, NULL, 0);

sys_permission权限表 

权限分类型:比如说页面菜单展示的权限、访问接口的权限,根据当前登录用户拥有的所有角色的菜单权限   展示所拥有的菜单列表

配置接口类型的权限:用于查询数据以及新增、修改、删除等等按钮请求后台接口路径权限

  1. -- ----------------------------
  2. -- Table structure for sys_permission
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `sys_permission`;
  5. CREATE TABLE `sys_permission` (
  6. `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  7. `permission_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '权限ID(自定义)可设置唯一索引UNIQUE',
  8. `permission_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '权限名称',
  9. `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述说明',
  10. `create_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  11. `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  12. `update_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  13. `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  14. `is_deleted` tinyint UNSIGNED NULL DEFAULT 0 COMMENT '是否删除(0:正常/1:删除)',
  15. PRIMARY KEY (`id`) USING BTREE
  16. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '权限表' ROW_FORMAT = Dynamic;
  17. -- ----------------------------
  18. -- Records of sys_permission
  19. -- ----------------------------
  20. INSERT INTO `sys_permission` VALUES (1, '10001', '系统管理', '菜单权限(一级菜单)', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  21. INSERT INTO `sys_permission` VALUES (2, '10002', '权限管理', '菜单权限(二级菜单)', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  22. INSERT INTO `sys_permission` VALUES (3, '10003', '角色管理', '菜单权限(二级菜单)', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  23. INSERT INTO `sys_permission` VALUES (4, '10004', '用户管理', '菜单权限(二级菜单)', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  24. INSERT INTO `sys_permission` VALUES (5, '00001', '超级管理员', '当用户角色拥有该权限时,可分配sys_role表中权限ID为该值的角色给用户', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  25. INSERT INTO `sys_permission` VALUES (6, '50001', '组长管理员', '组长角色拥有该权限时,可分配测试员的角色给用户', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  26. INSERT INTO `sys_permission` VALUES (7, '60001', '查询权限列表', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  27. INSERT INTO `sys_permission` VALUES (8, '60002', '新增权限', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  28. INSERT INTO `sys_permission` VALUES (9, '60003', '修改权限', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  29. INSERT INTO `sys_permission` VALUES (10, '60004', '删除权限', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  30. INSERT INTO `sys_permission` VALUES (11, '60005', '查询角色列表', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  31. INSERT INTO `sys_permission` VALUES (12, '60006', '新增角色', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  32. INSERT INTO `sys_permission` VALUES (13, '60007', '修改角色', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  33. INSERT INTO `sys_permission` VALUES (14, '60008', '删除角色', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  34. INSERT INTO `sys_permission` VALUES (15, '60009', '查询用户列表', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  35. INSERT INTO `sys_permission` VALUES (16, '60010', '新增用户', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  36. INSERT INTO `sys_permission` VALUES (17, '60011', '修改用户', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);
  37. INSERT INTO `sys_permission` VALUES (18, '60012', '删除用户', '接口权限', '88888888', '2021-03-23 15:11:42', NULL, NULL, 0);

sys_role角色表

根据初始化的一些数据可以看出,组长这个角色拥有50001的权限,而测试员的角色的权限ID属于50001,所有当拥有组长角色的用户登录时,前端页面展示该用户可以给其他新用户赋予测试员的角色,而超级管理员角色的用户拥有赋予其他用户     组长和测试员角色的权限

  1. -- ----------------------------
  2. -- Table structure for sys_role
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `sys_role`;
  5. CREATE TABLE `sys_role` (
  6. `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  7. `role_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色ID(自定义)可设置唯一索引UNIQUE',
  8. `role_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色名称',
  9. `permission_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '权限类别(主要定义角色属于哪种层级)',
  10. `create_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  11. `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  12. `update_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  13. `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  14. `is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除(0:正常/1:删除)',
  15. PRIMARY KEY (`id`) USING BTREE
  16. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;
  17. -- ----------------------------
  18. -- Records of sys_role
  19. -- ----------------------------
  20. INSERT INTO `sys_role` VALUES (1, '888888', '超级管理员', '', '88888888', '2021-03-23 15:18:10', NULL, NULL, 0);
  21. INSERT INTO `sys_role` VALUES (2, '100001', '组长', '00001', '88888888', '2021-03-23 15:18:10', NULL, NULL, 0);
  22. INSERT INTO `sys_role` VALUES (3, '100002', '测试员', '50001', '88888888', '2021-03-23 15:18:10', NULL, NULL, 0);

sys_role_permission角色权限关联表

超级管理员拥有所有的权限,所有角色首先要先拥有菜单权限,然后才有某个后台接口的请求权限,比如:组长需要拥有系统管理菜单下的用户管理菜单,并且有该页面上的新增、修改、删除等按钮权限

  1. -- ----------------------------
  2. -- Table structure for sys_role_permission
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `sys_role_permission`;
  5. CREATE TABLE `sys_role_permission` (
  6. `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  7. `role_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色ID',
  8. `permission_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '权限ID',
  9. `create_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  10. `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  11. `update_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  12. `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  13. `is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除(0:正常/1:删除)',
  14. PRIMARY KEY (`id`) USING BTREE
  15. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '角色权限关联表' ROW_FORMAT = Dynamic;
  16. -- ----------------------------
  17. -- Records of sys_role_permission
  18. -- ----------------------------
  19. INSERT INTO `sys_role_permission` VALUES (1, '888888', '10001', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  20. INSERT INTO `sys_role_permission` VALUES (2, '888888', '10002', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  21. INSERT INTO `sys_role_permission` VALUES (3, '888888', '10003', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  22. INSERT INTO `sys_role_permission` VALUES (4, '888888', '10004', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  23. INSERT INTO `sys_role_permission` VALUES (5, '888888', '00001', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  24. INSERT INTO `sys_role_permission` VALUES (6, '888888', '50001', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  25. INSERT INTO `sys_role_permission` VALUES (7, '888888', '60001', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  26. INSERT INTO `sys_role_permission` VALUES (8, '888888', '60002', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  27. INSERT INTO `sys_role_permission` VALUES (9, '888888', '60003', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  28. INSERT INTO `sys_role_permission` VALUES (10, '888888', '60004', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  29. INSERT INTO `sys_role_permission` VALUES (11, '888888', '60005', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  30. INSERT INTO `sys_role_permission` VALUES (12, '888888', '60006', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  31. INSERT INTO `sys_role_permission` VALUES (13, '888888', '60007', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  32. INSERT INTO `sys_role_permission` VALUES (14, '888888', '60008', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  33. INSERT INTO `sys_role_permission` VALUES (15, '888888', '60009', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  34. INSERT INTO `sys_role_permission` VALUES (16, '888888', '60010', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  35. INSERT INTO `sys_role_permission` VALUES (17, '888888', '60011', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  36. INSERT INTO `sys_role_permission` VALUES (18, '888888', '60012', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  37. INSERT INTO `sys_role_permission` VALUES (19, '100001', '10001', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  38. INSERT INTO `sys_role_permission` VALUES (20, '100001', '10004', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  39. INSERT INTO `sys_role_permission` VALUES (21, '100001', '60009', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  40. INSERT INTO `sys_role_permission` VALUES (22, '100001', '60010', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  41. INSERT INTO `sys_role_permission` VALUES (23, '100001', '60011', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);
  42. INSERT INTO `sys_role_permission` VALUES (24, '100001', '60012', '88888888', '2021-03-23 15:29:09', NULL, NULL, 0);

sys_user用户表

  1. -- ----------------------------
  2. -- Table structure for sys_user
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `sys_user`;
  5. CREATE TABLE `sys_user` (
  6. `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  7. `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户ID(可设置唯一索引UNIQUE)',
  8. `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名称',
  9. `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
  10. `create_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  11. `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  12. `update_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  13. `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  14. `is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除(0:正常/1:删除)',
  15. PRIMARY KEY (`id`) USING BTREE
  16. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
  17. -- ----------------------------
  18. -- Records of sys_user
  19. -- ----------------------------
  20. INSERT INTO `sys_user` VALUES (1, '88888888', '超级管理员', '88888888', '88888888', '2021-03-23 15:51:27', NULL, NULL, 0);
  21. INSERT INTO `sys_user` VALUES (2, '80000001', '张三', '123456', '88888888', '2021-03-23 15:51:27', NULL, NULL, 0);
  22. INSERT INTO `sys_user` VALUES (3, '80000002', '李四', '123456', '88888888', '2021-03-23 15:51:27', NULL, NULL, 0);
  23. INSERT INTO `sys_user` VALUES (4, '80000003', '王五', '123456', '88888888', '2021-03-23 15:51:27', NULL, NULL, 0);

sys_user_role用户角色关联表

一个用户可以拥有多个角色,在权限判断时需要对重复的权限做去重处理

一个角色可以赋予多个用户使用

  1. -- ----------------------------
  2. -- Table structure for sys_user_role
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `sys_user_role`;
  5. CREATE TABLE `sys_user_role` (
  6. `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  7. `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户ID',
  8. `role_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色ID',
  9. `create_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  10. `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  11. `update_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  12. `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  13. `is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除(0:正常/1:删除)',
  14. PRIMARY KEY (`id`) USING BTREE
  15. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户角色关联表' ROW_FORMAT = Dynamic;
  16. -- ----------------------------
  17. -- Records of sys_user_role
  18. -- ----------------------------
  19. INSERT INTO `sys_user_role` VALUES (1, '88888888', '888888', '88888888', '2021-03-23 15:54:17', NULL, NULL, 0);
  20. INSERT INTO `sys_user_role` VALUES (2, '88888888', '100001', '88888888', '2021-03-23 15:54:17', NULL, NULL, 0);
  21. INSERT INTO `sys_user_role` VALUES (3, '88888888', '100002', '88888888', '2021-03-23 15:54:17', NULL, NULL, 0);
  22. INSERT INTO `sys_user_role` VALUES (4, '80000001', '100001', '88888888', '2021-03-23 15:54:17', NULL, NULL, 0);
  23. INSERT INTO `sys_user_role` VALUES (5, '80000001', '100002', '88888888', '2021-03-23 15:54:17', NULL, NULL, 0);
  24. INSERT INTO `sys_user_role` VALUES (6, '80000002', '100002', '88888888', '2021-03-23 15:54:17', NULL, NULL, 0);
  25. INSERT INTO `sys_user_role` VALUES (7, '80000003', '100002', '88888888', '2021-03-23 15:54:17', NULL, NULL, 0);

 

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约