【实战】SpringBoot+JSP实现权限管理

数据库表结构

t_permission

DROP TABLE IF EXISTS `t_permission`;
CREATE TABLE `t_permission`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `icon` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `t_permission` VALUES (1, 0, '系统权限菜单', 'glyphicon glyphicon-th-list', NULL);
INSERT INTO `t_permission` VALUES (2, 1, '控制面板', 'glyphicon glyphicon-dashboard', 'main');
INSERT INTO `t_permission` VALUES (3, 1, '权限管理', 'glyphicon glyphicon glyphicon-tasks', NULL);
INSERT INTO `t_permission` VALUES (4, 3, '用户维护', 'glyphicon glyphicon-user', 'user/index');
INSERT INTO `t_permission` VALUES (5, 3, '角色维护', 'glyphicon glyphicon-king', 'role/index');
INSERT INTO `t_permission` VALUES (6, 3, '许可维护', 'glyphicon glyphicon-lock', 'permission/index');
INSERT INTO `t_permission` VALUES (7, 1, '业务审核', 'glyphicon glyphicon-ok', NULL);
INSERT INTO `t_permission` VALUES (8, 7, '实名认证审核', 'glyphicon glyphicon-check', 'authcert/index');
INSERT INTO `t_permission` VALUES (9, 7, '广告审核', 'glyphicon glyphicon-check', 'authadv/index');
INSERT INTO `t_permission` VALUES (10, 7, '项目审核', 'glyphicon glyphicon-check', 'authproject/index');
INSERT INTO `t_permission` VALUES (11, 1, '业务管理', 'glyphicon glyphicon-th-large', NULL);
INSERT INTO `t_permission` VALUES (12, 11, '资质维护', 'glyphicon glyphicon-picture', 'cert/index');
INSERT INTO `t_permission` VALUES (13, 11, '分类管理', 'glyphicon glyphicon-equalizer', 'certtype/index');
INSERT INTO `t_permission` VALUES (14, 11, '流程管理', 'glyphicon glyphicon-random', 'process/index');
INSERT INTO `t_permission` VALUES (15, 11, '广告管理', 'glyphicon glyphicon-hdd', 'advert/index');
INSERT INTO `t_permission` VALUES (16, 11, '消息模板', 'glyphicon glyphicon-comment', 'message/index');
INSERT INTO `t_permission` VALUES (17, 11, '项目分类', 'glyphicon glyphicon-list', 'projectType/index');
INSERT INTO `t_permission` VALUES (18, 11, '项目标签', 'glyphicon glyphicon-tags', 'tag/index');
INSERT INTO `t_permission` VALUES (19, 1, '参数管理', 'glyphicon glyphicon-list-alt', 'param/index');
SET FOREIGN_KEY_CHECKS = 1;

t_role_permission

-- ----------------------------
-- Table structure for t_role_permission
-- ----------------------------
DROP TABLE IF EXISTS `t_role_permission`;
CREATE TABLE `t_role_permission`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roleid` int(11) NULL DEFAULT NULL,
  `permissionid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK_Reference_3`(`roleid`) USING BTREE,
  INDEX `FK_Reference_4`(`permissionid`) USING BTREE,
  CONSTRAINT `FK_Reference_3` FOREIGN KEY (`roleid`) REFERENCES `t_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Reference_4` FOREIGN KEY (`permissionid`) REFERENCES `t_permission` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 171 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `t_role_permission` VALUES (38, 2, 1);
INSERT INTO `t_role_permission` VALUES (39, 2, 2);
INSERT INTO `t_role_permission` VALUES (40, 2, 4);
INSERT INTO `t_role_permission` VALUES (41, 2, 5);
INSERT INTO `t_role_permission` VALUES (42, 2, 6);
INSERT INTO `t_role_permission` VALUES (71, 3, 1);
INSERT INTO `t_role_permission` VALUES (72, 3, 2);
INSERT INTO `t_role_permission` VALUES (85, 4, 1);
INSERT INTO `t_role_permission` VALUES (86, 4, 2);
INSERT INTO `t_role_permission` VALUES (87, 4, 11);
INSERT INTO `t_role_permission` VALUES (88, 4, 12);
INSERT INTO `t_role_permission` VALUES (89, 4, 13);
INSERT INTO `t_role_permission` VALUES (90, 4, 14);
INSERT INTO `t_role_permission` VALUES (91, 4, 15);
INSERT INTO `t_role_permission` VALUES (92, 4, 16);
INSERT INTO `t_role_permission` VALUES (93, 4, 17);
INSERT INTO `t_role_permission` VALUES (94, 4, 18);
INSERT INTO `t_role_permission` VALUES (95, 5, 1);
INSERT INTO `t_role_permission` VALUES (96, 5, 2);
INSERT INTO `t_role_permission` VALUES (97, 5, 11);
INSERT INTO `t_role_permission` VALUES (98, 5, 12);
INSERT INTO `t_role_permission` VALUES (99, 5, 13);
INSERT INTO `t_role_permission` VALUES (100, 5, 14);
INSERT INTO `t_role_permission` VALUES (101, 5, 15);
INSERT INTO `t_role_permission` VALUES (102, 5, 16);
INSERT INTO `t_role_permission` VALUES (103, 5, 17);
INSERT INTO `t_role_permission` VALUES (104, 5, 18);
INSERT INTO `t_role_permission` VALUES (105, 5, 19);
INSERT INTO `t_role_permission` VALUES (106, 6, 1);
INSERT INTO `t_role_permission` VALUES (107, 6, 2);
INSERT INTO `t_role_permission` VALUES (108, 8, 1);
INSERT INTO `t_role_permission` VALUES (109, 8, 2);
INSERT INTO `t_role_permission` VALUES (110, 9, 1);
INSERT INTO `t_role_permission` VALUES (111, 9, 2);
INSERT INTO `t_role_permission` VALUES (112, 7, 1);
INSERT INTO `t_role_permission` VALUES (113, 7, 2);
INSERT INTO `t_role_permission` VALUES (142, 1, 1);
INSERT INTO `t_role_permission` VALUES (143, 1, 2);
INSERT INTO `t_role_permission` VALUES (144, 1, 3);
INSERT INTO `t_role_permission` VALUES (145, 1, 4);
INSERT INTO `t_role_permission` VALUES (146, 1, 5);
INSERT INTO `t_role_permission` VALUES (147, 1, 6);
INSERT INTO `t_role_permission` VALUES (148, 1, 7);
INSERT INTO `t_role_permission` VALUES (149, 1, 8);
INSERT INTO `t_role_permission` VALUES (150, 1, 9);
INSERT INTO `t_role_permission` VALUES (151, 1, 10);
INSERT INTO `t_role_permission` VALUES (152, 10, 1);
INSERT INTO `t_role_permission` VALUES (153, 10, 2);
INSERT INTO `t_role_permission` VALUES (154, 10, 3);
INSERT INTO `t_role_permission` VALUES (155, 10, 4);
INSERT INTO `t_role_permission` VALUES (156, 10, 5);
INSERT INTO `t_role_permission` VALUES (157, 10, 6);
INSERT INTO `t_role_permission` VALUES (158, 10, 7);
INSERT INTO `t_role_permission` VALUES (159, 10, 8);
INSERT INTO `t_role_permission` VALUES (160, 10, 9);
INSERT INTO `t_role_permission` VALUES (161, 10, 10);
INSERT INTO `t_role_permission` VALUES (162, 10, 11);
INSERT INTO `t_role_permission` VALUES (163, 10, 12);
INSERT INTO `t_role_permission` VALUES (164, 10, 13);
INSERT INTO `t_role_permission` VALUES (165, 10, 14);
INSERT INTO `t_role_permission` VALUES (166, 10, 15);
INSERT INTO `t_role_permission` VALUES (167, 10, 16);
INSERT INTO `t_role_permission` VALUES (168, 10, 17);
INSERT INTO `t_role_permission` VALUES (169, 10, 18);
INSERT INTO `t_role_permission` VALUES (170, 10, 19);
SET FOREIGN_KEY_CHECKS = 1;

t_user

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loginacct` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `userpswd` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `createtime` char(19) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `t_user` VALUES (18, 'root', 'e10adc3949ba59abbe56e057f20f883e', 'root', 'root@qq.com', '2020-06-18 11:25:00');
SET FOREIGN_KEY_CHECKS = 1;

t_user_role

DROP TABLE IF EXISTS `t_user_role`;
CREATE TABLE `t_user_role`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NULL DEFAULT NULL,
  `roleid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK_Reference_1`(`userid`) USING BTREE,
  INDEX `FK_Reference_2`(`roleid`) USING BTREE,
  CONSTRAINT `FK_Reference_1` FOREIGN KEY (`userid`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Reference_2` FOREIGN KEY (`roleid`) REFERENCES `t_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 27 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact