SpringSecurity 微服务权限方案

1 什么是微服务

1.1 微服务的由来

微服务最早由Martin Fowler和James Lewis于2014年共同剔除，微服务架构风格是一种使用一套小服务来开发单个应用的方式途径，每个服务运行在自己的进程中，并使用轻量级机制通信，通常是HTTP API，这些服务基于业务能力构建，并能够通过自动化部署机制来独立部署，这些服务使用不同的编程语言实现，以及不同数据存储技术，并能够最低限度的集中式管理。

1.2 微服务的优势

1️⃣微服务每个模块就相当于一个单独的项目，代码量明显减少，遇到问题相对来说比较好解决。
2️⃣微服务每个模块可以使用不同的存储方式（比如有的使用Redis，有的使用MySQL等），每个模块甚至可以有自己的数据库。
3️⃣微服务每个模块可以使用不同的开发技术，开发模块更灵活。

1.3 微服务本质

1️⃣微服务，关键其实不仅仅是微服务本身，而是系统要提供一套基础的架构，这种架构使得微服务可以独立的部署、运行、升级，不仅如此，这个系统架构还让微服务和微服务之间在结构上“松耦合”，而在功能上则表现为一个统一的整体。这种所谓的“统一的整体”表现出来的是统一风格的界面，统一的权限管理，统一的安全策略，统一的上线过程，统一的日志和审计方法，统一的调度，统一的访问入口等。
2️⃣微服务的目的是有效的拆分应用，实现敏捷开发和部署。

2 微服务认证和授权实现思路

2.1 认证授权过程分析

1️⃣如果是基于Session，那么Spring Security会对Cookie里面的session id进行解析，找到服务器存储的session信息，然后判断当前用户是否符合请求的要求。
2️⃣如果是token，则是解析出token，然后将当前请求加入到Spring Security管理的权限信息中去。

基于token实现认证

如果系统模块众多，那么每个模块都需要进行授权和认证，所以我们选择基于token的形式进行认证和授权，用户根据用户名和密码进行认证成功，然后获取当前用户角色的一系列的权限值，并以用户名为key，权限列表为value的形式存入Redis缓存，根据用户名相关信息生成token返回，浏览器将token记录到cookie中，每次调用API接口都默认将token携带到header请求头中，Spring Security解析header请求头获取token信息，解析token信息获取当前用户名，根据用户名就可以从Redis中获取权限列表了，这样Spring Security就能够判断当前请求是否有权限访问。

2.2 权限管理模型

权限管理模型

3 微服务认证和授权

3.1 实现功能

1️⃣登录（认证）。
2️⃣添加角色。
3️⃣为角色分配菜单。
4️⃣添加用户。
5️⃣为用户分配角色。

3.2 技术选型

Maven。
Spring Boot。
Spring Security。
Spring Cloud Alibaba。
Spring Cloud。
Spring Data JPA。
Redis。
JWT。
Swagger。
……

3.3 准备工作

3.3.1 sql脚本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for acl_permission
-- ----------------------------
DROP TABLE IF EXISTS `acl_permission`;
CREATE TABLE `acl_permission`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '编号',
  `pid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '所属上级',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
  `type` int(3) NOT NULL DEFAULT 0 COMMENT '类型(1:菜单,2:按钮)',
  `permission_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '权限值',
  `path` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '访问路径',
  `component` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '组件路径',
  `icon` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '图标',
  `status` int(4) NULL DEFAULT NULL COMMENT '状态(0:禁止,1:正常)',
  `been_deleted` int(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除 1（true）已删除， 0（false）未删除',
  `gmt_create` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_pid`(`pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '权限' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of acl_permission
-- ----------------------------
INSERT INTO `acl_permission` VALUES ('1', '0', '全部数据', 0, NULL, NULL, NULL, NULL, 1, 0, '2019-11-15 17:13:06', '2019-11-15 17:13:06', NULL);
INSERT INTO `acl_permission` VALUES ('1195268474480156673', '1', '权限管理', 1, NULL, '/acl', 'Layout', NULL, 1, 0, '2019-11-15 17:13:06', '2019-11-18 13:54:25', NULL);
INSERT INTO `acl_permission` VALUES ('1195268616021139457', '1195268474480156673', '用户管理', 1, NULL, 'user/list', '/acl/user/list', NULL, 1, 0, '2019-11-15 17:13:40', '2019-11-18 13:53:12', NULL);
INSERT INTO `acl_permission` VALUES ('1195268788138598401', '1195268474480156673', '角色管理', 1, NULL, 'role/list', '/acl/role/list', NULL, 1, 0, '2019-11-15 17:14:21', '2019-11-15 17:14:21', NULL);
INSERT INTO `acl_permission` VALUES ('1195268893830864898', '1195268474480156673', '菜单管理', 1, NULL, 'menu/list', '/acl/menu/list', NULL, 1, 0, '2019-11-15 17:14:46', '2019-11-15 17:14:46', NULL);
INSERT INTO `acl_permission` VALUES ('1195269143060602882', '1195268616021139457', '查看', 2, 'user.list', '', '', NULL, 1, 0, '2019-11-15 17:15:45', '2019-11-17 21:57:16', NULL);
INSERT INTO `acl_permission` VALUES ('1195269295926206466', '1195268616021139457', '添加', 2, 'user.add', 'user/add', '/acl/user/form', NULL, 1, 0, '2019-11-15 17:16:22', '2019-11-15 17:16:22', NULL);
INSERT INTO `acl_permission` VALUES ('1195269473479483394', '1195268616021139457', '修改', 2, 'user.update', 'user/update/:id', '/acl/user/form', NULL, 1, 0, '2019-11-15 17:17:04', '2019-11-15 17:17:04', NULL);
INSERT INTO `acl_permission` VALUES ('1195269547269873666', '1195268616021139457', '删除', 2, 'user.remove', '', '', NULL, 1, 0, '2019-11-15 17:17:22', '2019-11-15 17:17:22', NULL);
INSERT INTO `acl_permission` VALUES ('1195269821262782465', '1195268788138598401', '修改', 2, 'role.update', 'role/update/:id', '/acl/role/form', NULL, 1, 0, '2019-11-15 17:18:27', '2019-11-15 17:19:53', NULL);
INSERT INTO `acl_permission` VALUES ('1195269903542444034', '1195268788138598401', '查看', 2, 'role.list', '', '', NULL, 1, 0, '2019-11-15 17:18:47', '2019-11-15 17:18:47', NULL);
INSERT INTO `acl_permission` VALUES ('1195270037005197313', '1195268788138598401', '添加', 2, 'role.add', 'role/add', '/acl/role/form', NULL, 1, 0, '2019-11-15 17:19:19', '2019-11-18 11:05:42', NULL);
INSERT INTO `acl_permission` VALUES ('1195270442602782721', '1195268788138598401', '删除', 2, 'role.remove', '', '', NULL, 1, 0, '2019-11-15 17:20:55', '2019-11-15 17:20:55', NULL);
INSERT INTO `acl_permission` VALUES ('1195270621548568578', '1195268788138598401', '角色权限', 2, 'role.acl', 'role/distribution/:id', '/acl/role/roleForm', NULL, 1, 0, '2019-11-15 17:21:38', '2019-11-15 17:21:38', NULL);
INSERT INTO `acl_permission` VALUES ('1195270744097742849', '1195268893830864898', '查看', 2, 'permission.list', '', '', NULL, 1, 0, '2019-11-15 17:22:07', '2019-11-15 17:22:07', NULL);
INSERT INTO `acl_permission` VALUES ('1195270810560684034', '1195268893830864898', '添加', 2, 'permission.add', '', '', NULL, 1, 0, '2019-11-15 17:22:23', '2019-11-15 17:22:23', NULL);
INSERT INTO `acl_permission` VALUES ('1195270862100291586', '1195268893830864898', '修改', 2, 'permission.update', '', '', NULL, 1, 0, '2019-11-15 17:22:35', '2019-11-15 17:22:35', NULL);
INSERT INTO `acl_permission` VALUES ('1195270887933009922', '1195268893830864898', '删除', 2, 'permission.remove', '', '', NULL, 1, 0, '2019-11-15 17:22:41', '2019-11-15 17:22:41', NULL);
INSERT INTO `acl_permission` VALUES ('1195349439240048642', '1', '讲师管理', 1, NULL, '/edu/teacher', 'Layout', NULL, 1, 0, '2019-11-15 22:34:49', '2019-11-15 22:34:49', NULL);
INSERT INTO `acl_permission` VALUES ('1195349699995734017', '1195349439240048642', '讲师列表', 1, NULL, 'list', '/edu/teacher/list', NULL, 1, 0, '2019-11-15 22:35:52', '2019-11-15 22:35:52', NULL);
INSERT INTO `acl_permission` VALUES ('1195349810561781761', '1195349439240048642', '添加讲师', 1, NULL, 'create', '/edu/teacher/form', NULL, 1, 0, '2019-11-15 22:36:18', '2019-11-15 22:36:18', NULL);
INSERT INTO `acl_permission` VALUES ('1195349876252971010', '1195349810561781761', '添加', 2, 'teacher.add', '', '', NULL, 1, 0, '2019-11-15 22:36:34', '2019-11-15 22:36:34', NULL);
INSERT INTO `acl_permission` VALUES ('1195349979797753857', '1195349699995734017', '查看', 2, 'teacher.list', '', '', NULL, 1, 0, '2019-11-15 22:36:58', '2019-11-15 22:36:58', NULL);
INSERT INTO `acl_permission` VALUES ('1195350117270261762', '1195349699995734017', '修改', 2, 'teacher.update', 'edit/:id', '/edu/teacher/form', NULL, 1, 0, '2019-11-15 22:37:31', '2019-11-15 22:37:31', NULL);
INSERT INTO `acl_permission` VALUES ('1195350188359520258', '1195349699995734017', '删除', 2, 'teacher.remove', '', '', NULL, 1, 0, '2019-11-15 22:37:48', '2019-11-15 22:37:48', NULL);
INSERT INTO `acl_permission` VALUES ('1195350299365969922', '1', '课程分类', 1, NULL, '/edu/subject', 'Layout', NULL, 1, 0, '2019-11-15 22:38:15', '2019-11-15 22:38:15', NULL);
INSERT INTO `acl_permission` VALUES ('1195350397751758850', '1195350299365969922', '课程分类列表', 1, NULL, 'list', '/edu/subject/list', NULL, 1, 0, '2019-11-15 22:38:38', '2019-11-15 22:38:38', NULL);
INSERT INTO `acl_permission` VALUES ('1195350500512206850', '1195350299365969922', '导入课程分类', 1, NULL, 'import', '/edu/subject/import', NULL, 1, 0, '2019-11-15 22:39:03', '2019-11-15 22:39:03', NULL);
INSERT INTO `acl_permission` VALUES ('1195350612172967938', '1195350397751758850', '查看', 2, 'subject.list', '', '', NULL, 1, 0, '2019-11-15 22:39:29', '2019-11-15 22:39:29', NULL);
INSERT INTO `acl_permission` VALUES ('1195350687590748161', '1195350500512206850', '导入', 2, 'subject.import', '', '', NULL, 1, 0, '2019-11-15 22:39:47', '2019-11-15 22:39:47', NULL);
INSERT INTO `acl_permission` VALUES ('1195350831744782337', '1', '课程管理', 1, NULL, '/edu/course', 'Layout', NULL, 1, 0, '2019-11-15 22:40:21', '2019-11-15 22:40:21', NULL);
INSERT INTO `acl_permission` VALUES ('1195350919074385921', '1195350831744782337', '课程列表', 1, NULL, 'list', '/edu/course/list', NULL, 1, 0, '2019-11-15 22:40:42', '2019-11-15 22:40:42', NULL);
INSERT INTO `acl_permission` VALUES ('1195351020463296513', '1195350831744782337', '发布课程', 1, NULL, 'info', '/edu/course/info', NULL, 1, 0, '2019-11-15 22:41:06', '2019-11-15 22:41:06', NULL);
INSERT INTO `acl_permission` VALUES ('1195351159672246274', '1195350919074385921', '完成发布', 2, 'course.publish', 'publish/:id', '/edu/course/publish', NULL, 1, 0, '2019-11-15 22:41:40', '2019-11-15 22:44:01', NULL);
INSERT INTO `acl_permission` VALUES ('1195351326706208770', '1195350919074385921', '编辑课程', 2, 'course.update', 'info/:id', '/edu/course/info', NULL, 1, 0, '2019-11-15 22:42:19', '2019-11-15 22:42:19', NULL);
INSERT INTO `acl_permission` VALUES ('1195351566221938690', '1195350919074385921', '编辑课程大纲', 2, 'chapter.update', 'chapter/:id', '/edu/course/chapter', NULL, 1, 0, '2019-11-15 22:43:17', '2019-11-15 22:43:17', NULL);
INSERT INTO `acl_permission` VALUES ('1195351862889254913', '1', '统计分析', 1, NULL, '/statistics/daily', 'Layout', NULL, 1, 0, '2019-11-15 22:44:27', '2019-11-15 22:44:27', NULL);
INSERT INTO `acl_permission` VALUES ('1195351968841568257', '1195351862889254913', '生成统计', 1, NULL, 'create', '/statistics/daily/create', NULL, 1, 0, '2019-11-15 22:44:53', '2019-11-15 22:44:53', NULL);
INSERT INTO `acl_permission` VALUES ('1195352054917074946', '1195351862889254913', '统计图表', 1, NULL, 'chart', '/statistics/daily/chart', NULL, 1, 0, '2019-11-15 22:45:13', '2019-11-15 22:45:13', NULL);
INSERT INTO `acl_permission` VALUES ('1195352127734386690', '1195352054917074946', '查看', 2, 'daily.list', '', '', NULL, 1, 0, '2019-11-15 22:45:30', '2019-11-15 22:45:30', NULL);
INSERT INTO `acl_permission` VALUES ('1195352215768633346', '1195351968841568257', '生成', 2, 'daily.add', '', '', NULL, 1, 0, '2019-11-15 22:45:51', '2019-11-15 22:45:51', NULL);
INSERT INTO `acl_permission` VALUES ('1195352547621965825', '1', 'CMS管理', 1, NULL, '/cms', 'Layout', NULL, 1, 0, '2019-11-15 22:47:11', '2019-11-18 10:51:46', NULL);
INSERT INTO `acl_permission` VALUES ('1195352856645701633', '1195353513549205505', '查看', 2, 'banner.list', '', NULL, NULL, 1, 0, '2019-11-15 22:48:24', '2019-11-15 22:48:24', NULL);
INSERT INTO `acl_permission` VALUES ('1195352909401657346', '1195353513549205505', '添加', 2, 'banner.add', 'banner/add', '/cms/banner/form', NULL, 1, 0, '2019-11-15 22:48:37', '2019-11-18 10:52:10', NULL);
INSERT INTO `acl_permission` VALUES ('1195353051395624961', '1195353513549205505', '修改', 2, 'banner.update', 'banner/update/:id', '/cms/banner/form', NULL, 1, 0, '2019-11-15 22:49:11', '2019-11-18 10:52:05', NULL);
INSERT INTO `acl_permission` VALUES ('1195353513549205505', '1195352547621965825', 'Bander列表', 1, NULL, 'banner/list', '/cms/banner/list', NULL, 1, 0, '2019-11-15 22:51:01', '2019-11-18 10:51:29', NULL);
INSERT INTO `acl_permission` VALUES ('1195353672110673921', '1195353513549205505', '删除', 2, 'banner.remove', '', '', NULL, 1, 0, '2019-11-15 22:51:39', '2019-11-15 22:51:39', NULL);
INSERT INTO `acl_permission` VALUES ('1195354076890370050', '1', '订单管理', 1, NULL, '/order', 'Layout', NULL, 1, 0, '2019-11-15 22:53:15', '2019-11-15 22:53:15', NULL);
INSERT INTO `acl_permission` VALUES ('1195354153482555393', '1195354076890370050', '订单列表', 1, NULL, 'list', '/order/list', NULL, 1, 0, '2019-11-15 22:53:33', '2019-11-15 22:53:58', NULL);
INSERT INTO `acl_permission` VALUES ('1195354315093282817', '1195354153482555393', '查看', 2, 'order.list', '', '', NULL, 1, 0, '2019-11-15 22:54:12', '2019-11-15 22:54:12', NULL);
INSERT INTO `acl_permission` VALUES ('1196301740985311234', '1195268616021139457', '分配角色', 2, 'user.assgin', 'user/role/:id', '/acl/user/roleForm', NULL, 1, 0, '2019-11-18 13:38:56', '2019-11-18 13:38:56', NULL);

-- ----------------------------
-- Table structure for acl_role
-- ----------------------------
DROP TABLE IF EXISTS `acl_role`;
CREATE TABLE `acl_role`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '角色id',
  `role_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '角色名称',
  `role_code` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色编码',
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `been_deleted` int(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除 1（true）已删除， 0（false）未删除',
  `gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime(0) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of acl_role
-- ----------------------------
INSERT INTO `acl_role` VALUES ('1', '普通管理员', NULL, NULL, 0, '2019-11-11 13:09:32', '2019-11-18 10:27:18');
INSERT INTO `acl_role` VALUES ('1193757683205607426', '课程管理员', NULL, NULL, 0, '2019-11-11 13:09:45', '2019-11-18 10:25:44');
INSERT INTO `acl_role` VALUES ('1196300996034977794', 'test', NULL, NULL, 0, '2019-11-18 13:35:58', '2019-11-18 13:35:58');

-- ----------------------------
-- Table structure for acl_role_permission
-- ----------------------------
DROP TABLE IF EXISTS `acl_role_permission`;
CREATE TABLE `acl_role_permission`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `role_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `permission_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `been_deleted` int(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除 1（true）已删除， 0（false）未删除',
  `gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime(0) NOT NULL COMMENT '更新时间',
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_role_id`(`role_id`) USING BTREE,
  INDEX `idx_permission_id`(`permission_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色权限' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of acl_role_permission
-- ----------------------------
INSERT INTO `acl_role_permission` VALUES ('1196301979754455041', '1', '1', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301979792203778', '1', '1195268474480156673', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301979821563906', '1', '1195268616021139457', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301979842535426', '1', '1195269143060602882', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301979855118338', '1', '1195269295926206466', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301979880284161', '1', '1195269473479483394', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301979913838593', '1', '1195269547269873666', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301979926421506', '1', '1196301740985311234', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301979951587330', '1', '1195268788138598401', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980014501889', '1', '1195269821262782465', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980035473410', '1', '1195269903542444034', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980052250626', '1', '1195270037005197313', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980077416450', '1', '1195270442602782721', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980094193665', '1', '1195270621548568578', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980119359489', '1', '1195268893830864898', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980136136706', '1', '1195270744097742849', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980249382913', '1', '1195270810560684034', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980270354434', '1', '1195270862100291586', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980287131649', '1', '1195270887933009922', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980303908866', '1', '1195349439240048642', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980320686082', '1', '1195349699995734017', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980345851905', '1', '1195349979797753857', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980362629121', '1', '1195350117270261762', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980383600641', '1', '1195350188359520258', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980408766465', '1', '1195349810561781761', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980421349378', '1', '1195349876252971010', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980438126593', '1', '1195350299365969922', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980450709506', '1', '1195350397751758850', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980501041153', '1', '1195350612172967938', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980517818370', '1', '1195350500512206850', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980538789889', '1', '1195350687590748161', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980622675970', '1', '1195350831744782337', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980639453186', '1', '1195350919074385921', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980660424705', '1', '1195351159672246274', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980677201922', '1', '1195351326706208770', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980698173441', '1', '1195351566221938690', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980714950658', '1', '1195351020463296513', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980723339266', '1', '1195351862889254913', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980744310786', '1', '1195351968841568257', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980761088001', '1', '1195352215768633346', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980777865217', '1', '1195352054917074946', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980794642434', '1', '1195352127734386690', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980811419650', '1', '1195352547621965825', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980828196865', '1', '1195353513549205505', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980844974082', '1', '1195352856645701633', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980861751298', '1', '1195352909401657346', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980886917122', '1', '1195353051395624961', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980928860162', '1', '1195353672110673921', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980954025986', '1', '1195354076890370050', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980970803201', '1', '1195354153482555393', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196301980987580418', '1', '1195354315093282817', 1, '2019-11-18 13:39:53', '2019-11-18 13:39:53', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293070077953', '1', '1', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293099438081', '1', '1195268474480156673', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293120409602', '1', '1195268616021139457', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293153964034', '1', '1195269143060602882', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293183324162', '1', '1195269295926206466', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293212684290', '1', '1195269473479483394', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293237850114', '1', '1195269547269873666', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293271404545', '1', '1196301740985311234', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293485314049', '1', '1195268788138598401', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293506285569', '1', '1195269821262782465', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293527257089', '1', '1195269903542444034', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293552422914', '1', '1195270037005197313', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293565005825', '1', '1195270442602782721', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293594365954', '1', '1195270621548568578', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293611143169', '1', '1195268893830864898', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293627920385', '1', '1195270744097742849', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293657280513', '1', '1195349439240048642', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293674057729', '1', '1195349699995734017', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293690834946', '1', '1195349979797753857', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293716000770', '1', '1195350117270261762', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293736972290', '1', '1195350188359520258', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293749555202', '1', '1195349810561781761', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293766332417', '1', '1195349876252971010', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293795692546', '1', '1195350299365969922', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293812469762', '1', '1195350397751758850', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293837635586', '1', '1195350612172967938', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293858607106', '1', '1195350500512206850', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293875384322', '1', '1195350687590748161', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293892161538', '1', '1195350831744782337', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293950881794', '1', '1195350919074385921', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305293976047617', '1', '1195351159672246274', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294127042561', '1', '1195351326706208770', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294156402690', '1', '1195351566221938690', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294177374209', '1', '1195351862889254913', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294194151425', '1', '1195351968841568257', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294223511554', '1', '1195352215768633346', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294240288770', '1', '1195352054917074946', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294248677377', '1', '1195352127734386690', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294248677378', '1', '1195352547621965825', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294319980546', '1', '1195353513549205505', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294319980547', '1', '1195352856645701633', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294319980548', '1', '1195352909401657346', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294378700802', '1', '1195353051395624961', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294378700803', '1', '1195353672110673921', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294458392577', '1', '1195354076890370050', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294483558402', '1', '1195354153482555393', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305294500335618', '1', '1195354315093282817', 1, '2019-11-18 13:53:03', '2019-11-18 13:53:03', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566656139266', '1', '1', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566689693698', '1', '1195268474480156673', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566706470913', '1', '1195268616021139457', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566740025346', '1', '1195269143060602882', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566756802561', '1', '1195269295926206466', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566781968385', '1', '1195269473479483394', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566811328514', '1', '1195269547269873666', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566828105730', '1', '1196301740985311234', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566853271554', '1', '1195268788138598401', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566878437378', '1', '1195269821262782465', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566895214593', '1', '1195269903542444034', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566916186113', '1', '1195270037005197313', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566949740546', '1', '1195270442602782721', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566966517761', '1', '1195270621548568578', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305566991683585', '1', '1195268893830864898', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567012655106', '1', '1195270744097742849', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567029432322', '1', '1195270810560684034', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567042015233', '1', '1195270862100291586', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567100735490', '1', '1195270887933009922', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567117512705', '1', '1195349439240048642', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567142678530', '1', '1195349699995734017', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567155261442', '1', '1195349979797753857', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567172038658', '1', '1195350117270261762', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567188815873', '1', '1195350188359520258', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567218176001', '1', '1195349810561781761', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567234953217', '1', '1195349876252971010', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567251730434', '1', '1195350299365969922', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567272701954', '1', '1195350397751758850', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567289479170', '1', '1195350612172967938', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567310450690', '1', '1195350500512206850', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567327227905', '1', '1195350687590748161', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567448862722', '1', '1195350831744782337', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567478222850', '1', '1195350919074385921', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567495000065', '1', '1195351159672246274', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567520165889', '1', '1195351326706208770', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567541137409', '1', '1195351566221938690', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567570497538', '1', '1195351862889254913', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567587274754', '1', '1195351968841568257', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567604051970', '1', '1195352215768633346', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567633412098', '1', '1195352054917074946', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567683743745', '1', '1195352127734386690', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567721492481', '1', '1195352547621965825', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567742464002', '1', '1195353513549205505', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567771824129', '1', '1195352856645701633', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567792795650', '1', '1195352909401657346', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567809572866', '1', '1195353051395624961', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567843127298', '1', '1195353672110673921', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567868293122', '1', '1195354076890370050', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567885070338', '1', '1195354153482555393', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196305567910236162', '1', '1195354315093282817', 1, '2019-11-18 13:54:08', '2019-11-18 13:54:08', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702601695234', '1', '1', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702652026881', '1', '1195268474480156673', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702668804098', '1', '1195268616021139457', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702698164226', '1', '1195269143060602882', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702723330049', '1', '1195269295926206466', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702744301569', '1', '1195269473479483394', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702765273089', '1', '1195269547269873666', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702790438913', '1', '1196301740985311234', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702945628161', '1', '1195268788138598401', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312702970793985', '1', '1195269821262782465', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703000154114', '1', '1195269903542444034', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703025319938', '1', '1195270037005197313', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703046291458', '1', '1195270442602782721', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703063068673', '1', '1195270621548568578', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703084040193', '1', '1195268893830864898', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703113400321', '1', '1195270744097742849', 0, '2019-11-18 14:22:29', '2019-11-18 14:22:29', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703134371842', '1', '1195270810560684034', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703159537665', '1', '1195270862100291586', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703184703490', '1', '1195270887933009922', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703209869313', '1', '1195349439240048642', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703230840834', '1', '1195349699995734017', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703251812354', '1', '1195349979797753857', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703272783873', '1', '1195350117270261762', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703293755394', '1', '1195350188359520258', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703327309826', '1', '1195349810561781761', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703348281345', '1', '1195349876252971010', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703365058561', '1', '1195350299365969922', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703386030082', '1', '1195350397751758850', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703440556034', '1', '1195350612172967938', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703486693378', '1', '1195350500512206850', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703511859202', '1', '1195350687590748161', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703654465537', '1', '1195350831744782337', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703683825665', '1', '1195350919074385921', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703700602882', '1', '1195351159672246274', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703717380098', '1', '1195351326706208770', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703738351618', '1', '1195351566221938690', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703759323137', '1', '1195351020463296513', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703776100353', '1', '1195351862889254913', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703792877570', '1', '1195351968841568257', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703830626305', '1', '1195352215768633346', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703843209217', '1', '1195352054917074946', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703868375041', '1', '1195352127734386690', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703889346561', '1', '1195352547621965825', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703901929473', '1', '1195353513549205505', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703918706689', '1', '1195352856645701633', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703952261121', '1', '1195352909401657346', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703973232642', '1', '1195353051395624961', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312703990009857', '1', '1195353672110673921', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312704048730114', '1', '1195354076890370050', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312704069701633', '1', '1195354153482555393', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);
INSERT INTO `acl_role_permission` VALUES ('1196312704094867457', '1', '1195354315093282817', 0, '2019-11-18 14:22:30', '2019-11-18 14:22:30', NULL);

-- ----------------------------
-- Table structure for acl_user
-- ----------------------------
DROP TABLE IF EXISTS `acl_user`;
CREATE TABLE `acl_user`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '会员id',
  `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '微信openid',
  `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '密码',
  `nick_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '昵称',
  `been_deleted` int(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除 1（true）已删除， 0（false）未删除',
  `gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime(0) NOT NULL COMMENT '更新时间',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_username`(`username`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of acl_user
-- ----------------------------
INSERT INTO `acl_user` VALUES ('1', 'admin', '$2a$10$VgjWFFU4wQviC8kOnuAOyuuS7S1zkotmqQl4OcFSkN3lsJvuTm9vG', 'admin', 0, '2019-11-01 10:39:47', '2019-11-01 10:39:47', NULL);
INSERT INTO `acl_user` VALUES ('2', 'test', '$2a$10$VgjWFFU4wQviC8kOnuAOyuuS7S1zkotmqQl4OcFSkN3lsJvuTm9vG', 'test', 0, '2019-11-01 16:36:07', '2019-11-01 16:40:08', NULL);

-- ----------------------------
-- Table structure for acl_user_role
-- ----------------------------
DROP TABLE IF EXISTS `acl_user_role`;
CREATE TABLE `acl_user_role`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '主键id',
  `role_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '角色id',
  `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '用户id',
  `been_deleted` int(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除 1（true）已删除， 0（false）未删除',
  `gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime(0) NOT NULL COMMENT '更新时间',
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_role_id`(`role_id`) USING BTREE,
  INDEX `idx_user_id`(`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of acl_user_role
-- ----------------------------
INSERT INTO `acl_user_role` VALUES ('1', '1', '1', 0, '2019-11-11 13:09:53', '2019-11-11 13:09:53', NULL);

SET FOREIGN_KEY_CHECKS = 1;

3.3.2 搭建项目工程

1️⃣创建父工程acl-parent：管理依赖版本。
2️⃣在父工程创建子模块：
- acl-common模块：常用的工具类等。
- api-gateway模块：API网关。
- service-acl模块：权限业务模块。

搭建微服务认证和授权项目工程

3.4 Spring Security 微服务权限方案图示

Spring Security 微服务权限方案

3.5 项目工程

3.5.1 总工程acl-parent的pom

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.sunxiaping</groupId>
    <artifactId>acl-parent</artifactId>
    <packaging>pom</packaging>
    <version>1.0</version>
    <modules>
        <module>acl-common</module>
        <module>api-gateway</module>
        <module>service-acl</module>
    </modules>

    <properties>
        <jwt.version>0.9.1</jwt.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <java.version>1.8</java.version>
        <hutool.version>5.4.7</hutool.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <scope>runtime</scope>
            <groupId>io.jsonwebtoken</groupId>
            <artifactId>jjwt-impl</artifactId>
            <version>0.11.2</version>
        </dependency>
        <dependency>
            <groupId>io.jsonwebtoken</groupId>
            <artifactId>jjwt-api</artifactId>
            <version>0.11.2</version>
        </dependency>
        <dependency>
            <scope>runtime</scope>
            <groupId>io.jsonwebtoken</groupId>
            <artifactId>jjwt-jackson</artifactId>
            <version>0.11.2</version>
        </dependency>
    </dependencies>


    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>2.3.3.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>Hoxton.SR8</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>com.alibaba.cloud</groupId>
                <artifactId>spring-cloud-alibaba-dependencies</artifactId>
                <version>2.2.1.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
</project>

3.5.2 acl-common模块

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>acl-parent</artifactId>
        <groupId>com.sunxiaping</groupId>
        <version>1.0</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
    <artifactId>acl-common</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
    </dependencies>

</project>

GuliException.java

package com.sunxiaping.acl.exceptionhandler;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

/**
 * 自定义异常
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 14:38
 */
@Setter
@Getter
@AllArgsConstructor  //生成有参数构造方法
@NoArgsConstructor   //生成无参数构造
public class GuliException extends RuntimeException {
    private Integer code;//状态码
    private String msg;//异常信息
}

3.5.3 api-gateway模块

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>acl-parent</artifactId>
        <groupId>com.sunxiaping</groupId>
        <version>1.0</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>api-gateway</artifactId>

    <dependencies>
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-openfeign</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-gateway</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

application.yml

server:
  port: 9015

spring:
  application:
    name: api-gateway
  cloud:
    # 服务发现和配置中心Nacos
    nacos:
      discovery:
        server-addr: 127.0.0.1:8848 # 配置Nacos的地址
      config:
        server-addr: 127.0.0.1:8848 # 配置中心的地址
        file-extension: yml # 执行yaml格式的配置
    # 微服务网关
    gateway:
      discovery:
        locator:
          enabled: true # 开启从注册中心动态创建路由的功能，利用微服务名进行路由
          lower-case-service-id: true # 微服务名称以小写形式呈现
#      routes:
#        # 配置路由： 路由id，路由到微服务的uri,断言（判断条件）
#        - id: service-acl    # 路由id
#          #
#          uri: lb://service-acl # 路由到微服务的uri。 lb://xxx，lb代表从注册中心获取服务列表，xxx代表需要转发的微服务的名称
#          predicates:                # 断言（判断条件）
#            #  - Path=/product/**
#            - Path=/service-acl/**
#          filters: # 配置路由过滤器  http://localhost:7007/product-service/product/findById/1 --> http://localhost:7007/product/findById/1
#            - RewritePath=/service-acl/(?<segment>.*), /$\{segment} # 路径重写的过滤器

management:
  endpoints:
    web:
      exposure:
        include: '*'

APIGatewayApplication.java

package com.sunxiaping.acl;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 13:24
 */
@EnableDiscoveryClient
@SpringBootApplication
public class APIGatewayApplication {
    public static void main(String[] args) {
        SpringApplication.run(APIGatewayApplication.class, args);
    }
}

3.5.4 service-acl模块

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>acl-parent</artifactId>
        <groupId>com.sunxiaping</groupId>
        <version>1.0</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>service-acl</artifactId>

    <dependencies>
        <dependency>
            <groupId>com.sunxiaping</groupId>
            <artifactId>acl-common</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
</project>

application.yml

server:
  port: 9016

spring:
  application:
    name: service-acl
  cloud:
    # 服务发现和配置中心Nacos
    nacos:
      discovery:
        server-addr: 127.0.0.1:8848 # 配置Nacos的地址
      config:
        server-addr: 127.0.0.1:8848 # 配置中心的地址
        file-extension: yml # 执行yaml格式的配置
  # 配置数据源
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.1.57:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
    username: root
    password: 123456
    # Hikari 连接池配置
    hikari:
      # 最小空闲连接数量
      minimum-idle: 5
      # 空闲连接存活最大时间，默认600000（10分钟）
      idle-timeout: 180000
      # 连接池最大连接数，默认是10
      maximum-pool-size: 1000
      # 此属性控制从池返回的连接的默认自动提交行为,默认值：true
      auto-commit: true
      # 连接池名称
      pool-name: HikariCP
      # 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
      max-lifetime: 1800000
      # 数据库连接超时时间,默认30秒，即30000
      connection-timeout: 30000
      connection-test-query: SELECT 1
      data-source-properties:
        useInformationSchema: true
  # JPA
  jpa:
    hibernate:
      ddl-auto: update  # 第一次建表create  后面用update
      naming:
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
      use-new-id-generator-mappings: false
    show-sql: true
    database: mysql
    database-platform: org.hibernate.dialect.MySQL8Dialect
    properties:
      hibernate: com.jason.config.MySQL5TableType
    open-in-view: true
  # redis
  redis:
    database: 0
    port: 6379
    host: 127.0.0.1

management:
  endpoints:
    web:
      exposure:
        include: '*'

springfox:
  documentation:
    open-api:
      enabled: false

ServiceApplication.java

package com.sunxiaping.acl;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import springfox.documentation.oas.annotations.EnableOpenApi;

/**
 * 启动类
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 16:31
 */
@EnableOpenApi
@SpringBootApplication
@EnableTransactionManagement
@EnableJpaAuditing
//@EntityScan(basePackages = "com.sunxiaping.acl.domain")
public class ServiceApplication {
    public static void main(String[] args) {
        SpringApplication.run(ServiceApplication.class, args);
    }
}

实体类：

BaseModel.java

package com.sunxiaping.acl.domain.base;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.GenericGenerator;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

/**
 * 所有实体类的抽象父类
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 11:11
 */
@MappedSuperclass
@Setter
@Getter
@EntityListeners(AuditingEntityListener.class)
public abstract class BaseModel implements Serializable {

    @Id
    @Column(name = "`id`", columnDefinition = "varchar(255) comment '主键'")
    @GenericGenerator(name = "idGenerator", strategy = "uuid")
    @GeneratedValue(generator = "idGenerator")
    private String id;


    @JsonIgnore
    @Column(name = "`been_deleted`", columnDefinition = "int comment '逻辑删除 0表示逻辑未删除 1表示逻辑删除' default 0 ")
    private Integer beenDeleted;


    @Temporal(TemporalType.TIMESTAMP)
    @CreatedDate
    @Column(name = "`gmt_create`", columnDefinition = "datetime comment '创建时间'")
    @JsonIgnore
    @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    private Date created;


    @Temporal(TemporalType.TIMESTAMP)
    @LastModifiedDate
    @Column(name = "`gmt_modified`", columnDefinition = "datetime comment '修改时间'")
    @JsonIgnore
    @JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    private Date updated;



    @Column(name = "`remark`", columnDefinition = "varchar(255) comment '备注'")
    private String remark;

}

AclUser.java

package com.sunxiaping.acl.domain;

import com.sunxiaping.acl.domain.base.BaseModel;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.*;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 11:11
 */
@Entity
@Setter
@Getter
@Table(name = "`acl_user`")
@org.hibernate.annotations.Table(appliesTo = "`acl_user`", comment = "用户表")
@SQLDelete(sql = "UPDATE `acl_user` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@SQLDeleteAll(sql = "UPDATE `acl_user` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@Where(clause = "`been_deleted` = 0")
@DynamicInsert
@DynamicUpdate
public class AclUser extends BaseModel {

    @Column(name = "`username`", columnDefinition = "varchar(255) comment '用户名'")
    private String username;

    @Column(name = "`password`", columnDefinition = "varchar(255) comment '密码'")
    private String password;

    @Column(name = "`nick_name`", columnDefinition = "varchar(255) comment '昵称'")
    private String nickname;
}

AclUserRole.java

package com.sunxiaping.acl.domain;

import com.sunxiaping.acl.domain.base.BaseModel;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.*;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 14:47
 */
@Entity
@Setter
@Getter
@Table(name = "`acl_user_role`")
@org.hibernate.annotations.Table(appliesTo = "`acl_user_role`", comment = "用户角色表")
@SQLDelete(sql = "UPDATE `acl_user_role` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@SQLDeleteAll(sql = "UPDATE `acl_user_role` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@Where(clause = "`been_deleted` = 0")
@DynamicInsert
@DynamicUpdate
public class AclUserRole extends BaseModel {

    @Column(name = "`role_id`", columnDefinition = "varchar(255) comment '角色的id'")
    private String roleId;

    @Column(name = "`user_id`", columnDefinition = "varchar(255) comment '用户的id'")
    private String userId;
}

AclRole.java

package com.sunxiaping.acl.domain;

import com.sunxiaping.acl.domain.base.BaseModel;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.*;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 14:30
 */
@Entity
@Setter
@Getter
@Table(name = "`acl_role`")
@org.hibernate.annotations.Table(appliesTo = "`acl_role`", comment = "角色表")
@SQLDelete(sql = "UPDATE `acl_role` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@SQLDeleteAll(sql = "UPDATE `acl_role` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@Where(clause = "`been_deleted` = 0")
@DynamicInsert
@DynamicUpdate
public class AclRole extends BaseModel {

    @Column(name = "`role_name`", columnDefinition = "varchar(255) comment '角色名'")
    private String roleName;

    @Column(name = "`role_code`", columnDefinition = "varchar(255) comment '角色编码'")
    private String roleCode;

}

AclRolePermission.java

package com.sunxiaping.acl.domain;

import com.sunxiaping.acl.domain.base.BaseModel;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.*;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 14:49
 */
@Entity
@Setter
@Getter
@Table(name = "`acl_role_permission`")
@org.hibernate.annotations.Table(appliesTo = "`acl_role_permission`", comment = "角色权限表")
@SQLDelete(sql = "UPDATE `acl_role_permission` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@SQLDeleteAll(sql = "UPDATE `acl_role_permission` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@Where(clause = "`been_deleted` = 0")
@DynamicInsert
@DynamicUpdate
public class AclRolePermission extends BaseModel {

    @Column(name = "`role_id`", columnDefinition = "varchar(255) comment '角色的id'")
    private String roleId;


    @Column(name = "`permission_id`", columnDefinition = "varchar(255) comment '权限的id'")
    private String permissionId;

}

AclPermission.java

package com.sunxiaping.acl.domain;

import com.sunxiaping.acl.domain.base.BaseModel;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.*;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 14:39
 */
@Entity
@Setter
@Getter
@Table(name = "`acl_permission`")
@org.hibernate.annotations.Table(appliesTo = "`acl_permission`", comment = "权限表")
@SQLDelete(sql = "UPDATE `acl_permission` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@SQLDeleteAll(sql = "UPDATE `acl_permission` SET `been_deleted` = 1 WHERE id = ? and version = ?")
@Where(clause = "`been_deleted` = 0")
@DynamicInsert
@DynamicUpdate
public class AclPermission extends BaseModel {

    @Column(name = "`name`", columnDefinition = "varchar(255) comment '权限名称'")
    private String name;

    @Column(name = "`type`", columnDefinition = "int comment '类型'")
    private int type;

    @Column(name = "`permission_value`", columnDefinition = "varchar(255) comment '权限值'")
    private String permissionValue;

    @Column(name = "`path`", columnDefinition = "varchar(255) comment '访问路径'")
    private String path;

    @Column(name = "`component`", columnDefinition = "varchar(255) comment '组件路径'")
    private String component;

    @Column(name = "`status`", columnDefinition = "int comment '状态'")
    private int status;

    @Column(name = "`pid`", columnDefinition = "varchar(255) comment '所属上级'")
    private String pid;
    
}

dao层接口：

AclUserRepository.java

package com.sunxiaping.acl.repository;

import com.sunxiaping.acl.domain.AclUser;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 16:19
 */
@Repository
public interface AclUserRepository extends JpaRepository<AclUser, String>, JpaSpecificationExecutor<AclUser> {

}

AclUserRoleRepository.java

package com.sunxiaping.acl.repository;

import com.sunxiaping.acl.domain.AclUserRole;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 16:20
 */
@Repository
public interface AclUserRoleRepository extends JpaRepository<AclUserRole, String>, JpaSpecificationExecutor<AclUserRole> {
}

AclRoleRepository.java

package com.sunxiaping.acl.repository;

import com.sunxiaping.acl.domain.AclRole;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 16:21
 */
@Repository
public interface AclRoleRepository extends JpaRepository<AclRole, String>, JpaSpecificationExecutor<AclRole> {
}

AclRolePermissionRepository.java

package com.sunxiaping.acl.repository;

import com.sunxiaping.acl.domain.AclRolePermission;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 16:21
 */
@Repository
public interface AclRolePermissionRepository extends JpaRepository<AclRolePermission, String>, JpaSpecificationExecutor<AclRolePermission> {
}

AclPermissionRepository.java

package com.sunxiaping.acl.repository;

import com.sunxiaping.acl.domain.AclPermission;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-03 16:24
 */
@Repository
public interface AclPermissionRepository extends JpaRepository<AclPermission, String>, JpaSpecificationExecutor<AclPermission> {
}

业务层接口和实现类：

AclUserService.java

package com.sunxiaping.acl.service;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-04 09:13
 */
public interface AclUserService {
}

AclUserServiceImpl.java

package com.sunxiaping.acl.service.impl;

import com.sunxiaping.acl.service.AclUserService;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-04 09:26
 */
@Service
@Transactional
public class AclUserServiceImpl implements AclUserService {

}

AclRoleService.java

package com.sunxiaping.acl.service;

import com.sunxiaping.acl.domain.AclRole;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-04 09:13
 */
public interface AclRoleService {

    /**
     * 添加角色
     *
     * @param aclRole
     */
    void add(AclRole aclRole);
}

AclRoleServiceImpl.java

package com.sunxiaping.acl.service.impl;

import com.sunxiaping.acl.domain.AclRole;
import com.sunxiaping.acl.repository.AclRoleRepository;
import com.sunxiaping.acl.service.AclRoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-06 13:09
 */
@Service
@Transactional
public class AclRoleServiceImpl implements AclRoleService {

    @Autowired
    private AclRoleRepository aclRoleRepository;

    @Override
    public void add(AclRole aclRole) {
        aclRoleRepository.save(aclRole);
    }
}

UserDetailsServiceImpl.java

package com.sunxiaping.acl.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.google.common.collect.Lists;
import com.sunxiaping.acl.domain.AclPermission;
import com.sunxiaping.acl.domain.AclRolePermission;
import com.sunxiaping.acl.domain.AclUser;
import com.sunxiaping.acl.domain.AclUserRole;
import com.sunxiaping.acl.repository.AclPermissionRepository;
import com.sunxiaping.acl.repository.AclRolePermissionRepository;
import com.sunxiaping.acl.repository.AclUserRepository;
import com.sunxiaping.acl.repository.AclUserRoleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.AuthorityUtils;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Component;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Predicate;
import javax.transaction.Transactional;
import java.util.Collection;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-04 09:27
 */
@Component(value = "userDetailsService")
@Transactional
public class UserDetailsServiceImpl implements UserDetailsService {

    @Autowired
    private AclUserRepository aclUserRepository;

    @Autowired
    private AclUserRoleRepository aclUserRoleRepository;

    @Autowired
    private AclRolePermissionRepository aclRolePermissionRepository;

    @Autowired
    private AclPermissionRepository aclPermissionRepository;

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {

        Optional.ofNullable(username).orElseThrow(() -> new RuntimeException("用户名不能为空"));

        AclUser aclUserExample = new AclUser();
        aclUserExample.setBeenDeleted(0);
        aclUserExample.setUsername(username);

        Optional<AclUser> optional = aclUserRepository.findOne(Example.of(aclUserExample));

        if (!optional.isPresent()) {
            throw new RuntimeException("用户在数据库中不存在");
        }

        AclUser aclUser = optional.get();

        Collection<? extends GrantedAuthority> authorities = Lists.newArrayList();

        AclUserRole aclUserRoleExample = new AclUserRole();
        aclUserRoleExample.setBeenDeleted(0);
        aclUserRoleExample.setUserId(aclUser.getId());

        List<AclUserRole> aclUserRoleList = aclUserRoleRepository.findAll(Example.of(aclUserRoleExample));

        if (CollUtil.isNotEmpty(aclUserRoleList)) {
            List<String> roleIdList = aclUserRoleList.stream().map(AclUserRole::getRoleId).collect(Collectors.toList());

            List<AclRolePermission> aclRolePermissionList = aclRolePermissionRepository.findAll((Specification<AclRolePermission>) (root, criteriaQuery, criteriaBuilder) -> {
                List<Predicate> predicateList = Lists.newArrayList();
                CriteriaBuilder.In<String> in = criteriaBuilder.in(root.get("roleId"));
                for (String id : roleIdList) {
                    in.value(id);
                }
                predicateList.add(in);

                return criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()])).getRestriction();
            });

            if (CollUtil.isNotEmpty(aclRolePermissionList)) {
                List<String> permissionIdList = aclRolePermissionList.stream().map(AclRolePermission::getPermissionId).collect(Collectors.toList());
                List<AclPermission> aclPermissionList = aclPermissionRepository.findAll((Specification<AclPermission>) (root, criteriaQuery, criteriaBuilder) -> {

                    List<Predicate> predicateList = Lists.newArrayList();
                    CriteriaBuilder.In<String> in = criteriaBuilder.in(root.get("id"));
                    for (String id : permissionIdList) {
                        in.value(id);
                    }
                    predicateList.add(in);

                    return criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()])).getRestriction();
                });

                authorities = AuthorityUtils.commaSeparatedStringToAuthorityList(aclPermissionList.stream().map(AclPermission::getPermissionValue).filter(StrUtil::isNotEmpty).collect(Collectors.joining(",")));
            }
        }

        UserDetails userDetails = new User(aclUser.getUsername(), aclUser.getPassword(), true, true, true, true, authorities);

        return userDetails;
    }
}

工具类：

RsaUtils.java

package com.sunxiaping.acl.utils;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.security.*;
import java.security.spec.InvalidKeySpecException;
import java.security.spec.PKCS8EncodedKeySpec;
import java.security.spec.X509EncodedKeySpec;
import java.util.Base64;

/**
 * RSA算法
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 15:31
 */
public class RsaUtils {

    private static final int DEFAULT_KEY_SIZE = 2048;

    private static final String SECRET = "Hello World";

    private static String PRIVATE_KEY_FILE = new StringBuilder(System.getProperty("user.home")).append(File.separator).append("auth_key").append(File.separator).append("id_key_rsa").toString();

    private static String PUBLIC_KEY_FILE = new StringBuilder(System.getProperty("user.home")).append(File.separator).append("auth_key").append(File.separator).append("id_key_rsa.pub").toString();

    /**
     * 从文件中读取公钥
     *
     * @param filename 公钥保存路径，相对于classpath
     * @return 公钥对象
     * @throws Exception
     */
    public static PublicKey getPublicKey(String filename) throws Exception {

        byte[] bytes = readFile(filename);
        return getPublicKey(bytes);
    }


    /**
     * 获取默认的公钥
     *
     * @return
     * @throws Exception
     */
    public static PublicKey getDefaultPublicKey() throws Exception {
        generateDefaultKey();
        return getPublicKey(PUBLIC_KEY_FILE);
    }


    /**
     * 从文件中读取密钥
     *
     * @param filename 私钥保存路径，相对于classpath
     * @return 私钥对象
     * @throws Exception
     */
    public static PrivateKey getPrivateKey(String filename) throws Exception {
        byte[] bytes = readFile(filename);
        return getPrivateKey(bytes);
    }


    /**
     * 获取默认生成的私钥
     *
     * @return
     * @throws Exception
     */
    public static PrivateKey getDefaultPrivateKey() throws Exception {
        generateDefaultKey();
        return getPrivateKey(PRIVATE_KEY_FILE);
    }


    /**
     * 获取公钥
     *
     * @param bytes 公钥的字节形式
     * @return
     * @throws Exception
     */
    private static PublicKey getPublicKey(byte[] bytes) throws Exception {
        bytes = Base64.getDecoder().decode(bytes);
        X509EncodedKeySpec spec = new X509EncodedKeySpec(bytes);
        KeyFactory factory = KeyFactory.getInstance("RSA");
        return factory.generatePublic(spec);
    }

    /**
     * 获取密钥
     *
     * @param bytes 私钥的字节形式
     * @return
     * @throws Exception
     */
    private static PrivateKey getPrivateKey(byte[] bytes) throws NoSuchAlgorithmException, InvalidKeySpecException {
        bytes = Base64.getDecoder().decode(bytes);
        PKCS8EncodedKeySpec spec = new PKCS8EncodedKeySpec(bytes);
        KeyFactory factory = KeyFactory.getInstance("RSA");
        return factory.generatePrivate(spec);
    }

    /**
     * 根据密文，生存rsa公钥和私钥,并写入指定文件
     *
     * @param publicKeyFilename  公钥文件路径
     * @param privateKeyFilename 私钥文件路径
     * @param secret             生成密钥的密文
     */
    private static void generateKey(String publicKeyFilename, String privateKeyFilename, String secret) throws Exception {

        KeyPairGenerator keyPairGenerator = KeyPairGenerator.getInstance("RSA");
        SecureRandom secureRandom = new SecureRandom(secret.getBytes());
        keyPairGenerator.initialize(DEFAULT_KEY_SIZE, secureRandom);
        KeyPair keyPair = keyPairGenerator.genKeyPair();
        // 获取公钥并写出
        byte[] publicKeyBytes = keyPair.getPublic().getEncoded();
        publicKeyBytes = Base64.getEncoder().encode(publicKeyBytes);
        writeFile(publicKeyFilename, publicKeyBytes);
        // 获取私钥并写出
        byte[] privateKeyBytes = keyPair.getPrivate().getEncoded();
        privateKeyBytes = Base64.getEncoder().encode(privateKeyBytes);
        writeFile(privateKeyFilename, privateKeyBytes);
    }


    /**
     * 生成默认的公钥和私钥文件
     *
     * @throws Exception
     */
    private static void generateDefaultKey() throws Exception {

        //如果不存在公钥和私钥，就生成对应的文件
        if (!Files.exists(Paths.get(PUBLIC_KEY_FILE)) || !Files.exists(Paths.get(PRIVATE_KEY_FILE))) {

            if (!Files.exists(Paths.get(PUBLIC_KEY_FILE).getParent())) {
                Files.createDirectory(Paths.get(PUBLIC_KEY_FILE).getParent());
            }
            if (!Files.exists(Paths.get(PRIVATE_KEY_FILE).getParent())) {
                Files.createDirectory(Paths.get(PRIVATE_KEY_FILE).getParent());
            }
            Files.createFile(Paths.get(PUBLIC_KEY_FILE));
            Files.createFile(Paths.get(PRIVATE_KEY_FILE));
        }

        generateKey(PUBLIC_KEY_FILE, PRIVATE_KEY_FILE, RsaUtils.SECRET);


    }


    private static byte[] readFile(String fileName) throws IOException {
        return Files.readAllBytes(new File(fileName).toPath());
    }

    private static void writeFile(String destPath, byte[] bytes) throws IOException {
        File dest = new File(destPath);
        if (!dest.getParentFile().exists()) {
            dest.getParentFile().mkdirs();
        }
        if (!dest.exists()) {
            dest.createNewFile();
        }
        Files.write(dest.toPath(), bytes);
    }
}

Result.java

package com.sunxiaping.acl.utils;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.Setter;

import java.util.HashMap;
import java.util.Map;

/**
 * 封装统一返回结果
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 14:19
 */
@Setter
@Getter
@AllArgsConstructor
public class Result {
    private Boolean success;

    private Integer code;

    private String message;

    private Map<String, Object> data = new HashMap<String, Object>();

    //把构造方法私有
    private Result() {
    }

    //成功静态方法
    public static Result ok() {
        Result r = new Result();
        r.setSuccess(true);
        r.setCode(20000);
        r.setMessage("成功");
        return r;
    }

    //失败静态方法
    public static Result error() {
        Result r = new Result();
        r.setSuccess(false);
        r.setCode(20001);
        r.setMessage("失败");
        return r;
    }

    public Result success(Boolean success) {
        this.setSuccess(success);
        return this;
    }

    public Result message(String message) {
        this.setMessage(message);
        return this;
    }

    public Result code(Integer code) {
        this.setCode(code);
        return this;
    }

    public Result data(String key, Object value) {
        this.data.put(key, value);
        return this;
    }

    public Result data(Map<String, Object> map) {
        this.setData(map);
        return this;
    }
}

ResponseUtil.java

package com.sunxiaping.acl.utils;

import cn.hutool.http.HttpStatus;
import com.fasterxml.jackson.databind.ObjectMapper;


import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 14:24
 */
public interface ResponseUtil {

    static void out(HttpServletResponse response, Result r) {
        ObjectMapper mapper = new ObjectMapper();
        response.setStatus(HttpStatus.HTTP_OK);
        response.setContentType("application/json; charset=UTF-8");
        try {
            mapper.writeValue(response.getWriter(), r);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

UnAccessDeniedHandler.java

package com.sunxiaping.acl.utils;

import org.springframework.security.access.AccessDeniedException;
import org.springframework.security.web.access.AccessDeniedHandler;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-11-02 02:07
 */
public class UnAccessDeniedHandler implements AccessDeniedHandler {
    @Override
    public void handle(HttpServletRequest request, HttpServletResponse response, AccessDeniedException e) throws IOException, ServletException {
        ResponseUtil.out(response, Result.error());
    }
}

UnAuthenticationEntryPoint.java

package com.sunxiaping.acl.utils;

import org.springframework.security.core.AuthenticationException;
import org.springframework.security.web.AuthenticationEntryPoint;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * 未认证端点
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 16:50
 */
public class UnAuthenticationEntryPoint implements AuthenticationEntryPoint {
    @Override
    public void commence(HttpServletRequest request, HttpServletResponse response, AuthenticationException e) throws IOException, ServletException {
        ResponseUtil.out(response, Result.error());
    }
}

JwtTokenManager.java

package com.sunxiaping.acl.utils;

import io.jsonwebtoken.CompressionCodecs;
import io.jsonwebtoken.Jwts;
import io.jsonwebtoken.SignatureAlgorithm;
import org.springframework.stereotype.Component;

import java.sql.Date;
import java.time.LocalDateTime;
import java.time.ZoneId;

/**
 * Jwt的token管理器，根据用户名生成token，从token中解析出用户名
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 15:31
 */
@Component
public class JwtTokenManager {

    private static final int EXPIRE = 7 * 24 * 60 * 60;

    /**
     * 生成Jwt token
     *
     * @param username
     * @return
     * @throws Exception
     */
    public String createJwtToken(String username) throws Exception {
        return Jwts.builder()
                .setSubject(username)
                .setExpiration(Date.from(LocalDateTime.now().plusSeconds(EXPIRE).atZone(ZoneId.systemDefault()).toInstant()))
                .signWith(RsaUtils.getDefaultPrivateKey(), SignatureAlgorithm.RS256)
                .compressWith(CompressionCodecs.GZIP)
                .compact();
    }

    /**
     * 从Jwt token中提取username
     *
     * @param token
     * @return
     */
    public String parseJwtToken(String token) throws Exception {
        return Jwts.parserBuilder().setSigningKey(RsaUtils.getDefaultPublicKey()).build().parseClaimsJws(token).getBody().getSubject();
    }


}

JwtTokenLogoutHandler.java

package com.sunxiaping.acl.utils;

import cn.hutool.core.util.StrUtil;
import lombok.AllArgsConstructor;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.security.core.Authentication;
import org.springframework.security.web.authentication.logout.LogoutHandler;

/**
 * 退出的处理器
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 15:27
 */
@AllArgsConstructor
public class JwtTokenLogoutHandler implements LogoutHandler {

    private JwtTokenManager jwtTokenManager;

    private RedisTemplate redisTemplate;

    @Override
    public void logout(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response, Authentication authentication) {
        //从请求头Header中获取JwtToken
        String jwtToken = request.getHeader("Authorization");
        //解析JwtToken，查询Redis，如果存在对应的值，则删除
        if (StrUtil.isNotEmpty(jwtToken)) {
            try {
                String username = jwtTokenManager.parseJwtToken(jwtToken);

                if (redisTemplate.hasKey(username)) {
                    redisTemplate.delete(username);
                }
                ResponseUtil.out(response, Result.ok());
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }
}

Jwt相关的过滤器

JwtTokenFilter.java

package com.sunxiaping.acl.filter;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.sunxiaping.acl.domain.AclUser;
import com.sunxiaping.acl.utils.JwtTokenManager;
import com.sunxiaping.acl.utils.ResponseUtil;
import com.sunxiaping.acl.utils.Result;
import lombok.AllArgsConstructor;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.security.authentication.AuthenticationManager;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.AuthenticationException;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter;

import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Collection;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 生成JwtToken的过滤器
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 16:55
 */
@AllArgsConstructor
public class JwtTokenFilter extends UsernamePasswordAuthenticationFilter {

    private JwtTokenManager jwtTokenManager;
    private RedisTemplate redisTemplate;
    private AuthenticationManager authenticationManager;

    /**
     * 试图认证的方法
     *
     * @param request
     * @param response
     * @return
     * @throws AuthenticationException
     */
    @Override
    public Authentication attemptAuthentication(HttpServletRequest request, HttpServletResponse response) throws AuthenticationException {

        try {
            AclUser user = new ObjectMapper().readValue(request.getInputStream(), AclUser.class);

            UsernamePasswordAuthenticationToken authRequest = new UsernamePasswordAuthenticationToken(user.getUsername(), user.getPassword());

            return authenticationManager.authenticate(authRequest);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 认证成功之后，生成token
     *
     * @param request
     * @param response
     * @param chain
     * @param authResult
     * @throws IOException
     * @throws ServletException
     */
    @Override
    protected void successfulAuthentication(HttpServletRequest request, HttpServletResponse response, FilterChain chain, Authentication authResult) throws IOException, ServletException {
        try {
            //认证成功后，得到认证成功之后的用户信息，UserDetails的实现类
            User user = (User) authResult.getPrincipal();
            //根据用户名生成token信息
            String username = user.getUsername();
            String jwtToken = jwtTokenManager.createJwtToken(username);
            //将用户名和权限列表放入到Redis中
            Collection<GrantedAuthority> authorities = user.getAuthorities();
            List<String> permissionValueList = authorities.stream().map(GrantedAuthority::getAuthority).collect(Collectors.toList());
            redisTemplate.opsForValue().set(username, permissionValueList);
            //将token返回
            ResponseUtil.out(response, Result.ok().data("token", jwtToken));
        } catch (Exception e) {
            ResponseUtil.out(response, Result.error());
        }
    }

    /**
     * 认证失败
     *
     * @param request
     * @param response
     * @param failed
     * @throws IOException
     * @throws ServletException
     */
    @Override
    protected void unsuccessfulAuthentication(HttpServletRequest request, HttpServletResponse response, AuthenticationException failed) throws IOException, ServletException {
        ResponseUtil.out(response, Result.error());
    }
}

JwtVerifyFilter.java

package com.sunxiaping.acl.filter;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.google.common.collect.Lists;
import com.sunxiaping.acl.utils.JwtTokenManager;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.security.authentication.AuthenticationManager;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.AuthorityUtils;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.web.authentication.www.BasicAuthenticationFilter;

import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 验证JwtToken的过滤器
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 16:56
 */
public class JwtVerifyFilter extends BasicAuthenticationFilter {
    private JwtTokenManager jwtTokenManager;
    private RedisTemplate redisTemplate;

    public JwtVerifyFilter(AuthenticationManager authenticationManager, JwtTokenManager jwtTokenManager, RedisTemplate redisTemplate) {
        super(authenticationManager);
        this.redisTemplate = redisTemplate;
        this.jwtTokenManager = jwtTokenManager;
    }

    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain chain) throws IOException, ServletException {
        //获取当前认证成功的用户的权限信息
        UsernamePasswordAuthenticationToken authRequest = getAuthentication(request);
        //如果有权限信息，放到权限的上下文中
        if (null != authRequest) {
            SecurityContextHolder.getContext().setAuthentication(authRequest);
        }
        chain.doFilter(request, response);
    }

    private UsernamePasswordAuthenticationToken getAuthentication(HttpServletRequest request) {
        //从Header头中获取token
        String jwtToken = request.getHeader("Authorization");
        if (StrUtil.isNotEmpty(jwtToken)) {
            try {
                String username = jwtTokenManager.parseJwtToken(jwtToken);
                //从Redis中获取权限信息
                List<String> permissionValueList = (List<String>) redisTemplate.opsForValue().get(username);

                List<GrantedAuthority> authorities = Lists.newArrayList();

                if (CollUtil.isNotEmpty(permissionValueList)) {
                    String authorityString = permissionValueList.stream().collect(Collectors.joining(","));
                    authorities = AuthorityUtils.commaSeparatedStringToAuthorityList(authorityString);
                }
                return new UsernamePasswordAuthenticationToken(username, jwtToken, authorities);
            } catch (Exception e) {

                throw new RuntimeException(e.getMessage());
            }
        }


        return null;
    }
}

配置类：

RedisConfig.java

package com.sunxiaping.acl.config;

import com.fasterxml.jackson.annotation.JsonAutoDetect;
import com.fasterxml.jackson.annotation.PropertyAccessor;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.redis.cache.RedisCacheConfiguration;
import org.springframework.data.redis.cache.RedisCacheManager;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.serializer.Jackson2JsonRedisSerializer;
import org.springframework.data.redis.serializer.RedisSerializationContext;
import org.springframework.data.redis.serializer.RedisSerializer;
import org.springframework.data.redis.serializer.StringRedisSerializer;

import java.time.Duration;

/**
 * Redis的配置类
 *
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 14:33
 */
@EnableCaching
@Configuration
public class RedisConfig {

    @Bean
    public RedisTemplate redisTemplate(RedisConnectionFactory redisConnectionFactory) {
        RedisTemplate<Object, Object> redisTemplate = new RedisTemplate<>();
        redisTemplate.setConnectionFactory(redisConnectionFactory);
        redisTemplate.setDefaultSerializer(new Jackson2JsonRedisSerializer<>(Object.class));
        //开启事务
        redisTemplate.setEnableTransactionSupport(true);
        return redisTemplate;
    }


    /**
     * 自定义Redis缓存管理器
     *
     * @param factory
     * @return
     */
    @Bean
    public RedisCacheManager redisCacheManager(RedisConnectionFactory factory) {
        RedisSerializer<String> redisSerializer = new StringRedisSerializer();
        Jackson2JsonRedisSerializer jackson2JsonRedisSerializer = new Jackson2JsonRedisSerializer<>(Object.class);

        //解决查询缓存转换异常的问题
        ObjectMapper om = new ObjectMapper();
        om.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
        om.enableDefaultTyping(ObjectMapper.DefaultTyping.NON_FINAL);
        jackson2JsonRedisSerializer.setObjectMapper(om);

        // 配置序列化（解决乱码的问题）
        RedisCacheConfiguration config = RedisCacheConfiguration.defaultCacheConfig()
                .entryTtl(Duration.ZERO)
                .serializeKeysWith(RedisSerializationContext.SerializationPair.fromSerializer(redisSerializer))
                .serializeValuesWith(RedisSerializationContext.SerializationPair.fromSerializer(jackson2JsonRedisSerializer))
                .disableCachingNullValues();

        RedisCacheManager cacheManager = RedisCacheManager.builder(factory)
                .cacheDefaults(config)
                .build();
        return cacheManager;
    }
}

SpringSecurityConfig.java

package com.sunxiaping.acl.config;

import com.sunxiaping.acl.filter.JwtTokenFilter;
import com.sunxiaping.acl.filter.JwtVerifyFilter;
import com.sunxiaping.acl.utils.JwtTokenLogoutHandler;
import com.sunxiaping.acl.utils.JwtTokenManager;
import com.sunxiaping.acl.utils.UnAccessDeniedHandler;
import com.sunxiaping.acl.utils.UnAuthenticationEntryPoint;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.builders.WebSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.config.http.SessionCreationPolicy;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

/**
 * @author 许大仙
 * @version 1.0
 * @since 2020-10-30 15:35
 */
@Configuration
@EnableWebSecurity
@EnableGlobalMethodSecurity(securedEnabled = true, prePostEnabled = true, jsr250Enabled = true)
public class SpringSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private JwtTokenManager jwtTokenManager;

    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    @Qualifier(value = "userDetailsService")
    private UserDetailsService userDetailsService;

    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userDetailsService).passwordEncoder(passwordEncoder());
    }

    /**
     * 配置那些请求不拦截
     *
     * @param web
     * @throws Exception
     */
    @Override
    public void configure(WebSecurity web) throws Exception {
        web.ignoring().antMatchers( "/favicon.ico", "*/v2/api-docs", "*/v3/api-docs", "*/webjars/**", "/*/api-docs", "/swagger**/**", "/doc.html", "/v3/**");
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {

        http.cors().and().csrf().disable()
                .authorizeRequests()
                .antMatchers("/login").permitAll()
                .anyRequest()
                .authenticated()
                .and()
                .logout().logoutUrl("/logout").addLogoutHandler(new JwtTokenLogoutHandler(jwtTokenManager, redisTemplate)).permitAll()
                .and()
                .addFilter(new JwtTokenFilter(jwtTokenManager, redisTemplate, authenticationManager()))
                .addFilter(new JwtVerifyFilter(authenticationManager(), jwtTokenManager, redisTemplate))
                //设置Session策略
                .sessionManagement().sessionCreationPolicy(SessionCreationPolicy.STATELESS)
                .and().exceptionHandling()
                //认证错误
                .authenticationEntryPoint(new UnAuthenticationEntryPoint())
                //无权访问
                .accessDeniedHandler(new UnAccessDeniedHandler());
    }
}

posted @ 2020-11-06 13:58 许大仙阅读(4790) 评论(4) 编辑收藏举报

刷新页面返回顶部

Loading

不为往事扰，余生只爱笑。

SpringSecurity 微服务权限方案

1 什么是微服务

1.1 微服务的由来

1.2 微服务的优势

1.3 微服务本质

2 微服务认证和授权实现思路

2.1 认证授权过程分析

2.2 权限管理模型

3 微服务认证和授权

3.1 实现功能

3.2 技术选型

3.3 准备工作

3.3.1 sql脚本

3.3.2 搭建项目工程

3.4 Spring Security 微服务权限方案图示

3.5 项目工程

3.5.1 总工程acl-parent的pom

3.5.2 acl-common模块

3.5.3 api-gateway模块

3.5.4 service-acl模块

公告