登陆认证，权限控制——持续更新的一篇博客

前言

登陆认证，权限控制是一个系统必不可少的部分，一个开放访问的系统能否在上线后稳定持续运行其实很大程度上取决于登陆认证和权限控制措施是否到位，不然可能系统刚刚上线就会夭折。

在一开始使用session进行登陆认证

从http请求 到 cookie 到 session & 用 session控制 删改数据的权限

然后过渡到了jwt

使用token的权限验证方法 & 用户+角色+权限表设计 & SpringBoot项目应用

中间又引入了Redis等技术，有必要对登陆认证，权限控制进行单独的总结和思考。

引出

---

1.登陆认证，权限控制——持续更新的一篇博客

1.权限控制的模型

SELECT 
ut.username,
ut.password,
ut.realname,
rut.user_id,
art.role_name,
apt.privs_id,
apt.privs_name

FROM user_tab ut
LEFT JOIN auth_role_user_tab rut ON rut.user_id=ut.id
LEFT JOIN auth_role_tab art ON art.role_id=rut.role_id
LEFT JOIN auth_role_privs_tab arpt ON arpt.rp_role=art.role_id
LEFT JOIN auth_privs_tab apt ON apt.privs_id=arpt.rp_privs
1
2
3
4
5
6
7
8
9
10
11
12
13
14

/*
 Navicat Premium Data Transfer


 Source Server Type    : MySQL
 Source Server Version : 80027

 Source Schema         : pet_auth

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 07/10/2023 19:36:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for auth_privs_tab
-- ----------------------------
DROP TABLE IF EXISTS `auth_privs_tab`;
CREATE TABLE `auth_privs_tab`  (
  `privs_id` bigint NOT NULL AUTO_INCREMENT COMMENT '权限的id，权限表',
  `privs_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '权限的名称',
  `privs_createTime` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `privs_updateTime` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `privs_createBy` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`privs_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of auth_privs_tab
-- ----------------------------
INSERT INTO `auth_privs_tab` VALUES (1, 'findAllUsernames', '2023-08-06 17:23:23', '2023-08-06 17:23:25', 'admin');
INSERT INTO `auth_privs_tab` VALUES (2, 'findByUsername', '2023-08-06 17:23:41', '2023-08-06 17:23:44', 'admin');
INSERT INTO `auth_privs_tab` VALUES (3, 'updateUser', '2023-08-06 17:24:05', '2023-08-06 17:24:08', 'admin');

-- ----------------------------
-- Table structure for auth_role_privs_tab
-- ----------------------------
DROP TABLE IF EXISTS `auth_role_privs_tab`;
CREATE TABLE `auth_role_privs_tab`  (
  `rp_id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色权限的中间表relationship',
  `rp_role` bigint NULL DEFAULT NULL COMMENT '对应的是角色id',
  `rp_privs` bigint NULL DEFAULT NULL COMMENT '对应的权限id',
  `rp_createtime` datetime NULL DEFAULT NULL,
  `rp_updatetime` datetime NULL DEFAULT NULL,
  `rp_createby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`rp_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of auth_role_privs_tab
-- ----------------------------
INSERT INTO `auth_role_privs_tab` VALUES (1, 1, 1, '2023-08-06 17:26:48', '2023-08-06 17:26:53', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (2, 1, 2, '2023-08-06 17:27:09', '2023-08-06 17:27:12', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (3, 3, 1, '2023-08-06 17:28:14', '2023-08-06 17:28:21', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (4, 3, 2, '2023-08-06 17:28:17', '2023-08-06 17:28:24', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (5, 3, 3, '2023-08-06 17:28:19', '2023-08-06 17:28:26', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (6, 2, 1, '2023-10-07 19:32:23', '2023-10-07 19:32:26', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (7, 2, 3, '2023-10-07 19:32:29', '2023-10-07 19:32:31', 'admin');

-- ----------------------------
-- Table structure for auth_role_tab
-- ----------------------------
DROP TABLE IF EXISTS `auth_role_tab`;
CREATE TABLE `auth_role_tab`  (
  `role_id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色的id，角色表',
  `role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色的name',
  `role_createtime` datetime NULL DEFAULT NULL,
  `role_updatetime` datetime NULL DEFAULT NULL,
  `role_createby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`role_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of auth_role_tab
-- ----------------------------
INSERT INTO `auth_role_tab` VALUES (1, 'userBasicRole', '2023-08-06 17:24:43', '2023-08-06 17:24:46', 'admin');
INSERT INTO `auth_role_tab` VALUES (2, 'bookRole', '2023-08-06 17:25:03', '2023-08-06 17:25:05', 'admin');
INSERT INTO `auth_role_tab` VALUES (3, 'userAdvRole', '2023-08-06 17:25:38', '2023-08-06 17:25:41', 'admin');

-- ----------------------------
-- Table structure for auth_role_user_tab
-- ----------------------------
DROP TABLE IF EXISTS `auth_role_user_tab`;
CREATE TABLE `auth_role_user_tab`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '角色，用户关联表，用户可能有多个角色',
  `user_id` int NULL DEFAULT NULL COMMENT '用户表的主键id',
  `role_id` int NULL DEFAULT NULL COMMENT '角色表的主键id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of auth_role_user_tab
-- ----------------------------
INSERT INTO `auth_role_user_tab` VALUES (1, 1, 1);
INSERT INTO `auth_role_user_tab` VALUES (2, 2, 3);
INSERT INTO `auth_role_user_tab` VALUES (3, 1, 2);

-- ----------------------------
-- Table structure for user_tab
-- ----------------------------
DROP TABLE IF EXISTS `user_tab`;
CREATE TABLE `user_tab`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `realname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '真实姓名',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `user_role` bigint NULL DEFAULT NULL COMMENT '角色表主键---用户-权限-角色模型',
  `tel` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `gender` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `identity` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证号',
  `notes` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `operator` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_tab
-- ----------------------------
INSERT INTO `user_tab` VALUES (1, 'shirley', '雪莉', '123', 1, '19800305334', '女', '433123456787', '家里比较有钱', NULL, NULL, NULL);
INSERT INTO `user_tab` VALUES (2, 'peter', '彼得', '123', 3, '18802229403', '男', '433123456788', '在国企上班', NULL, NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

SELECT 
ut.username,
ut.realname,
ut.password,
art.role_name,
art.role_note,
at.auth_name,
at.auth_url

FROM user_tab ut
LEFT JOIN auth_role_user_tab arut ON arut.user_id=ut.id
LEFT JOIN auth_role_tab art ON art.role_id=arut.role_id
LEFT JOIN auth_role_privs_tab arpt ON arpt.rp_role=arut.role_id
LEFT JOIN auth_tab at ON at.auth_id=arpt.rp_privs
1
2
3
4
5
6
7
8
9
10
11
12
13
14

全部表格的sql语句

/*
 Navicat Premium Data Transfer


 Source Server Type    : MySQL
 Source Server Version : 80027

 Source Schema         : pet_auth2

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 07/10/2023 20:35:03
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for auth_role_privs_tab
-- ----------------------------
DROP TABLE IF EXISTS `auth_role_privs_tab`;
CREATE TABLE `auth_role_privs_tab`  (
  `rp_id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色权限的中间表relationship',
  `rp_role` bigint NULL DEFAULT NULL COMMENT '对应的是角色id',
  `rp_privs` bigint NULL DEFAULT NULL COMMENT '对应的权限id',
  `rp_createtime` datetime NULL DEFAULT NULL,
  `rp_updatetime` datetime NULL DEFAULT NULL,
  `rp_createby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`rp_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of auth_role_privs_tab
-- ----------------------------
INSERT INTO `auth_role_privs_tab` VALUES (1, 1, 1, '2023-08-06 17:26:48', '2023-08-06 17:26:53', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (2, 1, 2, '2023-08-06 17:27:09', '2023-08-06 17:27:12', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (3, 1, 3, '2023-08-06 17:28:14', '2023-08-06 17:28:21', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (4, 1, 4, '2023-08-06 17:28:17', '2023-08-06 17:28:24', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (5, 1, 5, '2023-08-06 17:28:19', '2023-08-06 17:28:26', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (6, 1, 6, '2023-10-07 19:32:23', '2023-10-07 19:32:26', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (7, 1, 7, '2023-10-07 19:32:29', '2023-10-07 19:32:31', 'admin');
INSERT INTO `auth_role_privs_tab` VALUES (8, 2, 1, '2023-10-07 19:53:21', NULL, NULL);
INSERT INTO `auth_role_privs_tab` VALUES (9, 2, 2, '2023-10-07 19:53:25', NULL, NULL);
INSERT INTO `auth_role_privs_tab` VALUES (10, 2, 3, '2023-10-07 19:53:28', NULL, NULL);
INSERT INTO `auth_role_privs_tab` VALUES (11, 2, 4, '2023-10-07 19:53:30', NULL, NULL);
INSERT INTO `auth_role_privs_tab` VALUES (12, 5, 5, NULL, NULL, NULL);
INSERT INTO `auth_role_privs_tab` VALUES (13, 5, 6, NULL, NULL, NULL);
INSERT INTO `auth_role_privs_tab` VALUES (14, 5, 7, NULL, NULL, NULL);
INSERT INTO `auth_role_privs_tab` VALUES (15, 3, NULL, NULL, NULL, NULL);
INSERT INTO `auth_role_privs_tab` VALUES (16, 4, 1, NULL, NULL, NULL);

-- ----------------------------
-- Table structure for auth_role_tab
-- ----------------------------
DROP TABLE IF EXISTS `auth_role_tab`;
CREATE TABLE `auth_role_tab`  (
  `role_id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色的id，角色表',
  `role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色的name',
  `role_note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注一下',
  `role_createtime` datetime NULL DEFAULT NULL,
  `role_updatetime` datetime NULL DEFAULT NULL,
  `role_createby` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`role_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of auth_role_tab
-- ----------------------------
INSERT INTO `auth_role_tab` VALUES (1, '老板Boss', '全部权限', '2023-08-06 17:24:43', '2023-08-06 17:24:46', 'admin');
INSERT INTO `auth_role_tab` VALUES (2, '商品管理员工', '商品相关', '2023-08-06 17:25:03', '2023-08-06 17:25:05', 'admin');
INSERT INTO `auth_role_tab` VALUES (3, '未实名用户', '无任何权限', '2023-08-06 17:25:38', '2023-08-06 17:25:41', 'admin');
INSERT INTO `auth_role_tab` VALUES (4, '普通用户', '商品查询', '2023-10-07 19:49:34', '2023-10-07 19:49:37', 'admin');
INSERT INTO `auth_role_tab` VALUES (5, '人事部门员工', '人事相关', '2023-10-07 19:49:40', '2023-10-07 19:49:41', 'admin');

-- ----------------------------
-- Table structure for auth_role_user_tab
-- ----------------------------
DROP TABLE IF EXISTS `auth_role_user_tab`;
CREATE TABLE `auth_role_user_tab`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '角色，用户关联表，用户可能有多个角色',
  `user_id` int NULL DEFAULT NULL COMMENT '用户表的主键id',
  `role_id` int NULL DEFAULT NULL COMMENT '角色表的主键id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of auth_role_user_tab
-- ----------------------------
INSERT INTO `auth_role_user_tab` VALUES (1, 1, 1);
INSERT INTO `auth_role_user_tab` VALUES (2, 2, 2);
INSERT INTO `auth_role_user_tab` VALUES (3, 3, 5);
INSERT INTO `auth_role_user_tab` VALUES (4, 4, 4);
INSERT INTO `auth_role_user_tab` VALUES (5, 5, 3);

-- ----------------------------
-- Table structure for auth_tab
-- ----------------------------
DROP TABLE IF EXISTS `auth_tab`;
CREATE TABLE `auth_tab`  (
  `auth_id` int NOT NULL AUTO_INCREMENT COMMENT '权限表的id',
  `auth_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '权限的名称',
  `auth_url` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '权限的url，安全框架用',
  PRIMARY KEY (`auth_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of auth_tab
-- ----------------------------
INSERT INTO `auth_tab` VALUES (1, '商品查询', '/good/query');
INSERT INTO `auth_tab` VALUES (2, '商品新增', '/good/add');
INSERT INTO `auth_tab` VALUES (3, '商品删除', '/good/del');
INSERT INTO `auth_tab` VALUES (4, '商品修改', '/good/update');
INSERT INTO `auth_tab` VALUES (5, '员工查询', '/emp/query');
INSERT INTO `auth_tab` VALUES (6, '员工新增', '/emp/add');
INSERT INTO `auth_tab` VALUES (7, '员工开除', '/emp/del');

-- ----------------------------
-- Table structure for user_tab
-- ----------------------------
DROP TABLE IF EXISTS `user_tab`;
CREATE TABLE `user_tab`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `realname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '真实姓名',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `tel` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `gender` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `identity` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证号',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `operator` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_tab
-- ----------------------------
INSERT INTO `user_tab` VALUES (1, 'admin', '超级管理', '123', '19800305334', '女', '433123456787', NULL, NULL, NULL);
INSERT INTO `user_tab` VALUES (2, 'peter', '商品管理', '123', '18802229403', '男', '433123456788', NULL, NULL, NULL);
INSERT INTO `user_tab` VALUES (3, 'shirley', '人事管理', '123', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `user_tab` VALUES (4, 'tom', '普通登陆用户', '123', NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `user_tab` VALUES (5, 'test', '未实名认证', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

2.给不同员工显示不同的侧边栏

SELECT
    t_menu.id,
    t_menu.name,
    t_menu.link,
    t_menu.parentid,
    t_menu.icon
FROM emp_tab
    LEFT JOIN t_employee_menu ON emp_tab.id = t_employee_menu.employeeId
    LEFT JOIN t_menu ON t_employee_menu.menuId = t_menu.id
WHERE emp_tab.id = 1
1
2
3
4
5
6
7
8
9
10

/*
 Navicat Premium Data Transfer


 Source Server Type    : MySQL
 Source Server Version : 80027

 Source Schema         : pet_auth3

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 07/10/2023 20:58:49
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp_tab
-- ----------------------------
DROP TABLE IF EXISTS `emp_tab`;
CREATE TABLE `emp_tab`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名 员工账号',
  `realName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '真实姓名',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `depId` int NULL DEFAULT NULL COMMENT '部门的id',
  `status` int NULL DEFAULT NULL COMMENT '状态，0正常，1冻结，2删除',
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `tel` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `updatetime` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `workage` int NULL DEFAULT NULL COMMENT '工龄',
  `inductiontime` datetime NULL DEFAULT NULL COMMENT '入职时间',
  `resigntime` datetime NULL DEFAULT NULL COMMENT '离职时间',
  `salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪水',
  `birthday` datetime NULL DEFAULT NULL COMMENT '生日',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `sex` int NULL DEFAULT NULL COMMENT '性别0:男1:女',
  `endTime` datetime NULL DEFAULT NULL COMMENT '结束时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp_tab
-- ----------------------------
INSERT INTO `emp_tab` VALUES (1, 'admin', '超级管理员', '123', NULL, 100, '', '', NULL, 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 28, 0, NULL);
INSERT INTO `emp_tab` VALUES (2, 'root', '仓库管理员1张三', '123', 1, 0, '', '', NULL, 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 29, 0, NULL);
INSERT INTO `emp_tab` VALUES (3, 'test', '领料操作员1李四', '123', 1, 0, NULL, '13333333333', NULL, 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 26, 0, NULL);
INSERT INTO `emp_tab` VALUES (4, 'warehouse1', '入库操作员1张三', NULL, NULL, 0, NULL, NULL, NULL, 4, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 30, 0, NULL);
INSERT INTO `emp_tab` VALUES (5, 'goodsmMa1', '商品资料管理员', '123', NULL, 0, NULL, NULL, NULL, 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 40, 0, NULL);
INSERT INTO `emp_tab` VALUES (6, 'starehouseMa2', '成品入库管理员1张三', '123', 4, 0, NULL, NULL, NULL, 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 40, 0, NULL);
INSERT INTO `emp_tab` VALUES (7, 'starehouseMa3', '成品入库管理员2李四', '123', 4, 0, NULL, NULL, NULL, 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 30, 0, NULL);
INSERT INTO `emp_tab` VALUES (8, 'starehouseMa2', '成品入库管理员3王五', '123', 4, 0, NULL, NULL, NULL, 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 20, 0, NULL);
INSERT INTO `emp_tab` VALUES (9, '木子', '财务管理员', '123', NULL, 0, NULL, '12574560010', '2023-09-15 14:33:06', 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', 30, 0, NULL);
INSERT INTO `emp_tab` VALUES (10, '先行者', '资料发送员', '123', NULL, 1, NULL, NULL, NULL, 12, '2023-09-16 00:00:00', '2023-09-17 00:00:00', 8000.00, '2023-09-17 00:00:00', NULL, 0, NULL);

-- ----------------------------
-- Table structure for t_employee_menu
-- ----------------------------
DROP TABLE IF EXISTS `t_employee_menu`;
CREATE TABLE `t_employee_menu`  (
  `id_menu` int NOT NULL AUTO_INCREMENT,
  `employeeId` int NULL DEFAULT NULL COMMENT '员工id',
  `menuId` int NULL DEFAULT NULL COMMENT '菜单表id',
  PRIMARY KEY (`id_menu`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 29 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_employee_menu
-- ----------------------------
INSERT INTO `t_employee_menu` VALUES (1, 1, 1);
INSERT INTO `t_employee_menu` VALUES (2, 1, 2);
INSERT INTO `t_employee_menu` VALUES (3, 1, 3);
INSERT INTO `t_employee_menu` VALUES (4, 1, 4);
INSERT INTO `t_employee_menu` VALUES (5, 1, 5);
INSERT INTO `t_employee_menu` VALUES (6, 1, 6);
INSERT INTO `t_employee_menu` VALUES (7, 1, 7);
INSERT INTO `t_employee_menu` VALUES (9, 1, 9);
INSERT INTO `t_employee_menu` VALUES (10, 1, 10);
INSERT INTO `t_employee_menu` VALUES (11, 1, 11);
INSERT INTO `t_employee_menu` VALUES (12, 1, 12);
INSERT INTO `t_employee_menu` VALUES (13, 1, 13);
INSERT INTO `t_employee_menu` VALUES (14, 1, 14);
INSERT INTO `t_employee_menu` VALUES (15, 1, 15);
INSERT INTO `t_employee_menu` VALUES (16, 1, 16);
INSERT INTO `t_employee_menu` VALUES (17, 1, 17);
INSERT INTO `t_employee_menu` VALUES (18, 1, 18);
INSERT INTO `t_employee_menu` VALUES (19, 1, 19);
INSERT INTO `t_employee_menu` VALUES (20, 1, 20);
INSERT INTO `t_employee_menu` VALUES (21, 1, 21);
INSERT INTO `t_employee_menu` VALUES (22, 1, 22);
INSERT INTO `t_employee_menu` VALUES (23, 1, 23);
INSERT INTO `t_employee_menu` VALUES (24, 1, 24);
INSERT INTO `t_employee_menu` VALUES (25, 1, 25);
INSERT INTO `t_employee_menu` VALUES (27, 1, 27);

-- ----------------------------
-- Table structure for t_menu
-- ----------------------------
DROP TABLE IF EXISTS `t_menu`;
CREATE TABLE `t_menu`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '权限名称',
  `link` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '菜单链接',
  `parentid` int NULL DEFAULT NULL COMMENT '父级',
  `status` int NULL DEFAULT NULL COMMENT '1:正常 0：禁用',
  `percode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '权限编码',
  `icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '图标',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK_ParentID`(`parentid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 29 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_menu
-- ----------------------------
INSERT INTO `t_menu` VALUES (1, '系统管理', '', NULL, NULL, NULL, 'el-icon-location');
INSERT INTO `t_menu` VALUES (2, '商品类别', 'goodsType', 1, NULL, NULL, '');
INSERT INTO `t_menu` VALUES (3, '计量单位', 'unit', 1, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (4, '仓库管理', 'warehouse', NULL, NULL, NULL, 'el-icon-s-data');
INSERT INTO `t_menu` VALUES (5, '原材料入库', 'rawMaterialList', 4, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (6, '原材料领库', 'pickRawList', 4, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (7, '订单管理', NULL, NULL, NULL, NULL, 'el-icon-s-tools');
INSERT INTO `t_menu` VALUES (9, '区域管理', 'district', 1, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (10, '商品资料', 'goodsList', 1, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (11, '供应商资料', 'supplier', 1, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (12, '客户资料', 'customer', 1, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (13, '仓库管理', 'warehouse', 1, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (14, '部门管理', 'department', 1, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (15, '员工管理', 'employee', 1, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (16, '成品入库', 'finishedPickRaw', 4, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (17, '次品入库', 'defectiveGoodsIn', 4, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (18, '成品发货', 'finishedGoodsOut', 4, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (19, '次品发货', 'defectiveGoodsSend', 4, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (20, '财务管理', NULL, NULL, NULL, NULL, 'el-icon-s-data');
INSERT INTO `t_menu` VALUES (21, '应收款管理', 'income', 20, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (22, '应付款管理', 'expenses', 20, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (23, '数据统计', NULL, NULL, NULL, NULL, 'el-icon-s-data');
INSERT INTO `t_menu` VALUES (24, '仓库报表', 'storeStatistics', 23, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (25, '财务报表', 'financialStatistics', 23, NULL, NULL, NULL);
INSERT INTO `t_menu` VALUES (27, '订单查询', 'orderSearch', 7, NULL, NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

---

总结

1.登陆认证，权限控制——持续更新的一篇博客