【Spring Cloud Spring Security Oauth2】Spring Security Oauth2 + Redis + Gateway网关 + Mybatis + Mysql 整合

ゝ一纸荒年。 2023-06-24 04:51 174阅读 0赞

### 目录 ###

*  一、环境要求
 *  二、源码下载地址
 *  三、参考文献
 *  四、项目预览截图说明
 *  五、oauth2.0认证授权项目
 *   *  5.1 POM依赖
     *  5.2 yml配置及启动类
     *  5.3 认证配置与资源配置
     *  5.4 自定义UserDetailsService
     *  5.5 security配置
     *  5.6 自定义无加密密码验证
     *  5.7 druid连接池配置
     *  5.8 执行数据库脚本
     *  5.9 测试认证授权
     *   *  5.9.1 获取token
         *  5.9.2 测试访问授权资源
     *  5.9.3 刷新token
 *  六、创建测试项目
 *   *  6.1 POM
     *  6.2 yml配置及启动类
     *  6.3 配置类
     *  6.4 创建测试接口
 *  七、GateWay配置
 *   *  7.1 yml修改
     *  7.2 通过gateway测试
 *  有钱的捧个钱场,没钱的点个收藏

# 一、环境要求 #

eureka注册中心  
gateway网关  
maven3.0+  
jdk1.8  
redis 3.2+  
mysql数据  
postman测试工具  
项目为Maven结构

# 二、源码下载地址 #

源码下载地址：[GitHub][]

springCloud完整版开源项目博客详细说明[请点击这里][Link 1]

springCloud入门级教程及相关疑难杂症干货[请点击这里][Link 2]

# 三、参考文献 #

建议做好前期的知识储备  
大神的oauth2.0讲解：[阮一峰-理解OAuth 2.0][-_OAuth 2.0]  
oauth2.0官方文档开发指南：[点击这里][Link 3]

security入门博客：[点击这里][Link 4]  
security简单的登录:[点击这里][Link 5]

主要参看的博客：[必看][Link 6]  
我是在他这个博客基础上改版的。因为他用的是zuul网关，我换成了gateway。拾人牙慧了 哈哈

# 四、项目预览截图说明 #

项目为Maven结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70]  
eureka 注册中心(高可用版本)  
gateway 网关gateway(不是zuul)  
oauth-common 公共java代码 jar包依赖  
oauth-member 用于测试的oauth的一个项目  
oauth-server 核心项目，认证授权

这里粘贴下公共的父级依赖：接下来的项目会用到：

<?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">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.cloud.mc</groupId>
        <artifactId>cloud</artifactId>
        <version>1.0-SNAPSHOT</version>
        <packaging>pom</packaging>
    
        <name>cloud</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
            <java.version>1.8</java.version>
            <spring-cloud.version>Finchley.SR2</spring-cloud.version>
            <fastjson.version>1.2.47</fastjson.version>
            <commons-lang.version>2.6</commons-lang.version>
            <mybatis.version>1.3.2</mybatis.version>
            <mysql.version>5.1.46</mysql.version>
            <druid.version>1.1.10</druid.version>
            <lombok.version>1.16.20</lombok.version>
            <spring.security.version>4.1.0.RELEASE</spring.security.version>
            <log4j.version>1.2.16</log4j.version>
        </properties>
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.0.5.RELEASE</version>
            <relativePath/>
        </parent>
    
        <modules>
            <module>eureka</module>
            <module>gateway</module>
            <module>config</module>
            <module>zipkin-server</module>
            <module>producer7001</module>
            <module>producer7002</module>
            <module>consumer6001</module>
            <module>consumer6002</module>
            <module>oauth-common</module>
            <module>oauth-server</module>
        </modules>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>${fastjson.version}</version>
            </dependency>
            <dependency>
                <groupId>commons-lang</groupId>
                <artifactId>commons-lang</artifactId>
                <version>${commons-lang.version}</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.version}</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>${druid.version}</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>${lombok.version}</version>
                <scope>provided</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.security</groupId>
                <artifactId>spring-security-data</artifactId>
                <version>${spring.security.version}</version>
            </dependency>
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>${log4j.version}</version>
                <scope>compile</scope>
            </dependency>
    
        </dependencies>
    
        <dependencyManagement>
            <dependencies>
                <dependency>
                    <groupId>org.springframework.cloud</groupId>
                    <artifactId>spring-cloud-dependencies</artifactId>
                    <version>${spring-cloud.version}</version>
                    <type>pom</type>
                    <scope>import</scope>
                </dependency>
            </dependencies>
        </dependencyManagement>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

# 五、oauth2.0认证授权项目 #

## 5.1 POM依赖 ##

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>com.cloud.mc</groupId>
            <artifactId>cloud</artifactId>
            <version>1.0-SNAPSHOT</version>
        </parent>
        <groupId>com.example</groupId>
        <artifactId>oauth-server</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>oauth-server</name>
        <packaging>war</packaging>
        <description>oauth-授权模块</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>com.cloud.mc</groupId>
                <artifactId>oauth-common</artifactId>
                <version>1.0-SNAPSHOT</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-starter-oauth2</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-starter-security</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-redis</artifactId>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-actuator</artifactId>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>${mybatis.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

注意依赖父级项目

## 5.2 yml配置及启动类 ##

server:
      port: 1202
    
    spring:
      application:
        name: oauth-server
      redis:
        database: 0
        host: localhost
        port: 6379
        password:
        jedis:
          pool:
            max-active: 8
            max-idle: 8
            min-idle: 0
      datasource:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/eshop_member?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
        username: root
        password: 123456
      druid:
        initialSize: 5 #初始化连接大小
        minIdle: 5     #最小连接池数量
        maxActive: 20  #最大连接池数量
        maxWait: 60000 #获取连接时最大等待时间，单位毫秒
        timeBetweenEvictionRunsMillis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        minEvictableIdleTimeMillis: 300000   #配置一个连接在池中最小生存的时间，单位是毫秒
        validationQuery: SELECT 1 from DUAL  #测试连接
        testWhileIdle: true                  #申请连接的时候检测，建议配置为true，不影响性能，并且保证安全性
        testOnBorrow: false                  #获取连接时执行检测，建议关闭，影响性能
        testOnReturn: false                  #归还连接时执行检测，建议关闭，影响性能
        poolPreparedStatements: false        #是否开启PSCache，PSCache对支持游标的数据库性能提升巨大，oracle建议开启，mysql下建议关闭
        maxPoolPreparedStatementPerConnectionSize: 20 #开启poolPreparedStatements后生效
        filters: stat,wall,log4j #配置扩展插件，常用的插件有=>stat:监控统计  log4j:日志  wall:防御sql注入
        connectionProperties: 'druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000' #通过connectProperties属性来打开mergeSql功能;慢SQL记录
    
    
    eureka:
      client:
        service-url:
          defaultZone: http://peer1:8000/eureka/,http://peer2:8001/eureka/,http://peer3:8002/eureka/
    
    mybatis:
      type-aliases-package: com.mc.common.entity
      configuration:
        map-underscore-to-camel-case: true  #开启驼峰命名,l_name -> lName
        jdbc-type-for-null: NULL
        lazy-loading-enabled: true
        aggressive-lazy-loading: true
        cache-enabled: true #开启二级缓存
        call-setters-on-nulls: true #map空列不显示问题
      mapper-locations:
        - classpath:mybatis/*.xml

启动类添加注解：  
@SpringBootApplication  
@EnableDiscoveryClient  
@MapperScan(“com.example.oauthserver.dao”)

## 5.3 认证配置与资源配置 ##

资源认证配置：

package com.example.oauthserver.config.oauth;
    
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.annotation.Order;
    import org.springframework.security.config.annotation.web.builders.HttpSecurity;
    import org.springframework.security.oauth2.config.annotation.web.configuration.EnableResourceServer;
    import org.springframework.security.oauth2.config.annotation.web.configuration.ResourceServerConfigurerAdapter;
    
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * 〈资源认证服务器〉
     *
     * @author Curise
     * @create 2018/12/13
     * @since 1.0.0
     */
    @Configuration
    @EnableResourceServer
    @Order(3)
    public class ResourceServerConfig extends ResourceServerConfigurerAdapter {
        @Override
        public void configure(HttpSecurity http) throws Exception {
            http
    //                .csrf().disable()
                    .exceptionHandling()
                    .authenticationEntryPoint((request, response, authException) -> response.sendError(HttpServletResponse.SC_UNAUTHORIZED))
                    .and()
                    .requestMatchers().antMatchers("/api/**")
                    .and()
                    .authorizeRequests()
                    // or可以通过access_token访问，但是and不行；经过测试，应该是hasRole()方法出了问题，这里无法通过
    //                    .antMatchers("/product/**").access("#oauth2.hasScope('select') and  hasRole('ROLE_ADMIN')")
                    .antMatchers("/api/**").authenticated()// 配置api访问控制，必须认证过后才可以访问
                    .and()
                    .httpBasic();
        }
    }

认证配置：

## 5.4 自定义UserDetailsService ##

package com.example.oauthserver.service;
    
    
    import com.example.oauthserver.dao.MemberDao;
    import com.mc.common.entity.Member;
    import com.mc.common.entity.Permission;
    import com.mc.common.entity.Role;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.security.core.GrantedAuthority;
    import org.springframework.security.core.authority.SimpleGrantedAuthority;
    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.Service;
    
    import java.util.HashSet;
    import java.util.Set;
    
    /**
     * 〈自定义UserDetailService〉
     *
     * @author Curise
     * @create 2018/12/13
     * @since 1.0.0
     */
    @Service("userDetailService")
    public class MyUserDetailService implements UserDetailsService {
    
        @Autowired
        private MemberDao memberDao;
    
        @Override
        public UserDetails loadUserByUsername(String memberName) throws UsernameNotFoundException {
            Member member = memberDao.findByMemberName(memberName);
            if (member == null) {
                throw new UsernameNotFoundException(memberName);
            }
            Set<GrantedAuthority> grantedAuthorities = new HashSet<>();
            // 可用性 :true:可用 false:不可用
            boolean enabled = true;
            // 过期性 :true:没过期 false:过期
            boolean accountNonExpired = true;
            // 有效性 :true:凭证有效 false:凭证无效
            boolean credentialsNonExpired = true;
            // 锁定性 :true:未锁定 false:已锁定
            boolean accountNonLocked = true;
            for (Role role : member.getRoles()) {
                //角色必须是ROLE_开头，可以在数据库中设置
                GrantedAuthority grantedAuthority = new SimpleGrantedAuthority(role.getRoleName());
                grantedAuthorities.add(grantedAuthority);
                //获取权限
                for (Permission permission : role.getPermissions()) {
                    GrantedAuthority authority = new SimpleGrantedAuthority(permission.getUri());
                    grantedAuthorities.add(authority);
                }
            }
            User user = new User(member.getMemberName(), member.getPassword(),
                    enabled, accountNonExpired, credentialsNonExpired, accountNonLocked, grantedAuthorities);
            return user;
        }
    
    }

## 5.5 security配置 ##

package com.example.oauthserver.config.security;
    
    import com.example.oauthserver.service.MyUserDetailService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.annotation.Order;
    import org.springframework.security.authentication.AuthenticationManager;
    import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
    import org.springframework.security.config.annotation.web.builders.HttpSecurity;
    import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
    import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
    import org.springframework.security.crypto.password.PasswordEncoder;
    
    /**
     * 〈security配置〉
     *
     * @author Curise
     * @create 2018/12/13
     * @since 1.0.0
     */
    @Configuration
    @EnableWebSecurity
    @Order(2)
    public class SecurityConfig extends WebSecurityConfigurerAdapter {
        @Autowired
        private MyUserDetailService userDetailService;
    
        @Bean
        public PasswordEncoder passwordEncoder() {
    //        return new BCryptPasswordEncoder();
            return new NoEncryptPasswordEncoder();
        }
    
        @Override
        protected void configure(HttpSecurity http) throws Exception {
            http.requestMatchers().antMatchers("/oauth/**")
                    .and()
                    .authorizeRequests()
                    .antMatchers("/oauth/**").authenticated()
                    .and()
                    .csrf().disable();
        }
    
        @Override
        protected void configure(AuthenticationManagerBuilder auth) throws Exception {
            auth.userDetailsService(userDetailService).passwordEncoder(passwordEncoder());
        }
    
        /**
         * 不定义没有password grant_type,密码模式需要AuthenticationManager支持
         *
         * @return
         * @throws Exception
         */
        @Override
        @Bean
        public AuthenticationManager authenticationManagerBean() throws Exception {
            return super.authenticationManagerBean();
        }
    }

## 5.6 自定义无加密密码验证 ##

package com.example.oauthserver.config.security;
    
    import org.springframework.security.crypto.password.PasswordEncoder;
    
    /**
     * 〈自定义无加密密码验证〉
     *
     * @author Curise
     * @create 2018/12/13
     * @since 1.0.0
     */
    public class NoEncryptPasswordEncoder implements PasswordEncoder {
    
        @Override
        public String encode(CharSequence charSequence) {
            return (String) charSequence;
        }
    
        @Override
        public boolean matches(CharSequence charSequence, String s) {
            return s.equals((String) charSequence);
        }
    }

## 5.7 druid连接池配置 ##

package com.example.oauthserver.config.druid;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import javax.sql.DataSource;
    import java.sql.SQLException;
    
    /**
     * 〈druid连接池配置〉
     *
     * @author Curise
     * @create 2018/12/13
     * @since 1.0.0
     */
    @Configuration
    public class DruidConfiguration {
    
        @Value("${spring.datasource.url}")
        private String url;
    
        @Value("${spring.datasource.username}")
        private String username;
    
        @Value("${spring.datasource.password}")
        private String password;
    
        @Value("${spring.datasource.driver-class-name}")
        private String driverClassName;
    
        @Value("${spring.druid.initialSize}")
        private int initialSize;
    
        @Value("${spring.druid.minIdle}")
        private int minIdle;
    
        @Value("${spring.druid.maxActive}")
        private int maxActive;
    
        @Value("${spring.druid.maxWait}")
        private int maxWait;
    
        @Value("${spring.druid.timeBetweenEvictionRunsMillis}")
        private int timeBetweenEvictionRunsMillis;
    
        @Value("${spring.druid.minEvictableIdleTimeMillis}")
        private int minEvictableIdleTimeMillis;
    
        @Value("${spring.druid.validationQuery}")
        private String validationQuery;
    
        @Value("${spring.druid.testWhileIdle}")
        private boolean testWhileIdle;
    
        @Value("${spring.druid.testOnBorrow}")
        private boolean testOnBorrow;
    
        @Value("${spring.druid.testOnReturn}")
        private boolean testOnReturn;
    
        @Value("${spring.druid.poolPreparedStatements}")
        private boolean poolPreparedStatements;
    
        @Value("${spring.druid.maxPoolPreparedStatementPerConnectionSize}")
        private int maxPoolPreparedStatementPerConnectionSize;
    
        @Value("${spring.druid.filters}")
        private String filters;
    
        @Value("{spring.druid.connectionProperties}")
        private String connectionProperties;
    
        @Bean
        @Primary
        public DataSource dataSource() {
            DruidDataSource datasource = new DruidDataSource();
    
            datasource.setUrl(url);
            datasource.setUsername(username);
            //这里可以做加密处理
            datasource.setPassword(password);
            datasource.setDriverClassName(driverClassName);
    
            //configuration
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
            } catch (SQLException e) {
    
            }
            datasource.setConnectionProperties(connectionProperties);
    
            return datasource;
        }
    
        @Bean
        public ServletRegistrationBean statViewServlet() {
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
            //设置ip白名单
            servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
            //设置ip黑名单，优先级高于白名单
            servletRegistrationBean.addInitParameter("deny", "192.168.0.19");
            //设置控制台管理用户
            servletRegistrationBean.addInitParameter("loginUsername", "root");
            servletRegistrationBean.addInitParameter("loginPassword", "root");
            //是否可以重置数据
            servletRegistrationBean.addInitParameter("resetEnable", "false");
            return servletRegistrationBean;
        }
    
        @Bean
        public FilterRegistrationBean statFilter() {
            //创建过滤器
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
            //设置过滤器过滤路径
            filterRegistrationBean.addUrlPatterns("/*");
            //忽略过滤的形式
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }
    
    }

认证授权项目配置完成。

## 5.8 执行数据库脚本 ##

/* Navicat MySQL Data Transfer Source Server : localhost Source Server Version : 50726 Source Host : localhost:3306 Source Database : eshop_member Target Server Type : MYSQL Target Server Version : 50726 File Encoding : 65001 Date: 2019-12-20 11:50:01 */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for clientdetails
    -- ----------------------------
    DROP TABLE IF EXISTS `clientdetails`;
    CREATE TABLE `clientdetails` (
      `appId` varchar(128) NOT NULL,
      `resourceIds` varchar(256) DEFAULT NULL,
      `appSecret` varchar(256) DEFAULT NULL,
      `scope` varchar(256) DEFAULT NULL,
      `grantTypes` varchar(256) DEFAULT NULL,
      `redirectUrl` varchar(256) DEFAULT NULL,
      `authorities` varchar(256) DEFAULT NULL,
      `access_token_validity` int(11) DEFAULT NULL,
      `refresh_token_validity` int(11) DEFAULT NULL,
      `additionalInformation` varchar(4096) DEFAULT NULL,
      `autoApproveScopes` varchar(256) DEFAULT NULL,
      PRIMARY KEY (`appId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of clientdetails
    -- ----------------------------
    
    -- ----------------------------
    -- Table structure for es_member
    -- ----------------------------
    DROP TABLE IF EXISTS `es_member`;
    CREATE TABLE `es_member` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '会员id',
      `member_name` varchar(30) DEFAULT NULL COMMENT '会员名',
      `password` varchar(255) DEFAULT NULL COMMENT '密码',
      `email` varchar(50) DEFAULT NULL COMMENT '电子邮箱',
      `sex` tinyint(1) DEFAULT NULL COMMENT '性别 1男0女',
      `mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
      `birthday` date DEFAULT NULL COMMENT '出生日',
      `createTime` datetime DEFAULT NULL COMMENT '注册日期',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    -- ----------------------------
    -- Records of es_member
    -- ----------------------------
    INSERT INTO `es_member` VALUES ('1', '哈哈', '1', '111', '1', '1111', '2018-12-13', '2018-12-13 14:27:38');
    
    -- ----------------------------
    -- Table structure for es_member_role
    -- ----------------------------
    DROP TABLE IF EXISTS `es_member_role`;
    CREATE TABLE `es_member_role` (
      `id` int(11) NOT NULL COMMENT '主键',
      `member_id` int(11) DEFAULT NULL COMMENT '会员id',
      `role_id` int(11) DEFAULT NULL COMMENT '角色id',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    -- ----------------------------
    -- Records of es_member_role
    -- ----------------------------
    INSERT INTO `es_member_role` VALUES ('1', '1', '1');
    
    -- ----------------------------
    -- Table structure for es_permission
    -- ----------------------------
    DROP TABLE IF EXISTS `es_permission`;
    CREATE TABLE `es_permission` (
      `id` int(11) NOT NULL COMMENT '权限id',
      `method` varchar(10) DEFAULT NULL COMMENT '方法类型',
      `zuul_prefix` varchar(30) DEFAULT NULL COMMENT '网关前缀',
      `service_prefix` varchar(30) DEFAULT NULL COMMENT '服务前缀',
      `uri` varchar(100) DEFAULT NULL COMMENT '请求路径',
      `createTime` datetime DEFAULT NULL COMMENT '创建日期',
      `updateTime` datetime DEFAULT NULL COMMENT '更新日期',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    -- ----------------------------
    -- Records of es_permission
    -- ----------------------------
    INSERT INTO `es_permission` VALUES ('1', 'GET', '/api', '/auth', 'exit', '2018-12-14 09:45:35', '2018-12-14 09:45:37');
    INSERT INTO `es_permission` VALUES ('2', 'GET', '/api', '/auth', 'member', '2018-12-17 13:23:25', '2018-12-17 13:23:27');
    INSERT INTO `es_permission` VALUES ('3', 'GET', '/api', '/member', 'hello', '2018-12-17 13:23:25', '2018-12-17 13:23:25');
    INSERT INTO `es_permission` VALUES ('4', 'GET', '/api', '/member', 'current', '2018-12-17 13:23:25', '2018-12-17 13:23:25');
    INSERT INTO `es_permission` VALUES ('5', 'GET', '/api', '/member', 'query', '2018-12-17 13:23:25', '2018-12-17 13:23:25');
    
    -- ----------------------------
    -- Table structure for es_role
    -- ----------------------------
    DROP TABLE IF EXISTS `es_role`;
    CREATE TABLE `es_role` (
      `id` int(11) NOT NULL COMMENT '角色id',
      `role_name` varchar(50) DEFAULT NULL COMMENT '角色名称',
      `valid` tinyint(1) DEFAULT NULL COMMENT '是否有效 1是 0否',
      `createTime` datetime DEFAULT NULL COMMENT '创建日期',
      `updateTime` datetime DEFAULT NULL COMMENT '更新日期',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    -- ----------------------------
    -- Records of es_role
    -- ----------------------------
    INSERT INTO `es_role` VALUES ('1', 'ROLE_ADMIN', '1', '2018-12-14 09:46:01', '2018-12-14 09:46:03');
    INSERT INTO `es_role` VALUES ('2', 'ROLE_USER', '1', '2018-12-14 09:46:16', '2018-12-14 09:46:18');
    
    -- ----------------------------
    -- Table structure for es_role_permission
    -- ----------------------------
    DROP TABLE IF EXISTS `es_role_permission`;
    CREATE TABLE `es_role_permission` (
      `id` int(11) NOT NULL COMMENT '主键',
      `role_id` int(11) DEFAULT NULL COMMENT '角色id',
      `permission_id` int(11) DEFAULT NULL COMMENT '权限id',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    -- ----------------------------
    -- Records of es_role_permission
    -- ----------------------------
    INSERT INTO `es_role_permission` VALUES ('1', '1', '1');
    INSERT INTO `es_role_permission` VALUES ('2', '1', '2');
    INSERT INTO `es_role_permission` VALUES ('4', '1', '4');
    INSERT INTO `es_role_permission` VALUES ('5', '1', '5');
    
    -- ----------------------------
    -- Table structure for oauth_access_token
    -- ----------------------------
    DROP TABLE IF EXISTS `oauth_access_token`;
    CREATE TABLE `oauth_access_token` (
      `token_id` varchar(256) DEFAULT NULL,
      `token` blob,
      `authentication_id` varchar(128) NOT NULL,
      `user_name` varchar(256) DEFAULT NULL,
      `client_id` varchar(256) DEFAULT NULL,
      `authentication` blob,
      `refresh_token` varchar(256) DEFAULT NULL,
      PRIMARY KEY (`authentication_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of oauth_access_token
    -- ----------------------------
    INSERT INTO `oauth_access_token` VALUES ('3671c6520af112c2445a3e04b3b4c85a', 0xACED0005737200436F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744F4175746832416363657373546F6B656E0CB29E361B24FACE0200064C00156164646974696F6E616C496E666F726D6174696F6E74000F4C6A6176612F7574696C2F4D61703B4C000A65787069726174696F6E7400104C6A6176612F7574696C2F446174653B4C000C72656672657368546F6B656E74003F4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F636F6D6D6F6E2F4F417574683252656672657368546F6B656E3B4C000573636F706574000F4C6A6176612F7574696C2F5365743B4C0009746F6B656E547970657400124C6A6176612F6C616E672F537472696E673B4C000576616C756571007E000578707372001E6A6176612E7574696C2E436F6C6C656374696F6E7324456D7074794D6170593614855ADCE7D002000078707372000E6A6176612E7574696C2E44617465686A81014B597419030000787077080000016F1AC56CE5787372004C6F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744578706972696E674F417574683252656672657368546F6B656E2FDF47639DD0C9B70200014C000A65787069726174696F6E71007E0002787200446F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744F417574683252656672657368546F6B656E73E10E0A6354D45E0200014C000576616C756571007E0005787074002465303535303762632D326131622D343330662D383831372D3031383831646464306231327371007E000977080000016F3C3EC2E578737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65536574801D92D18F9B80550200007872002C6A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65436F6C6C656374696F6E19420080CB5EF71E0200014C0001637400164C6A6176612F7574696C2F436F6C6C656374696F6E3B7870737200176A6176612E7574696C2E4C696E6B656448617368536574D86CD75A95DD2A1E020000787200116A6176612E7574696C2E48617368536574BA44859596B8B7340300007870770C000000023F40000000000001740004726561647874000662656172657274002434313034373662392D633539632D343334322D623738612D343164316564336164393435, 'c3a4b9c039f8e1b350e9ac01945b63a2', '哈哈', 'test', 0xACED0005737200416F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E4F417574683241757468656E7469636174696F6EBD400B02166252130200024C000D73746F7265645265717565737474003C4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F70726F76696465722F4F4175746832526571756573743B4C00127573657241757468656E7469636174696F6E7400324C6F72672F737072696E676672616D65776F726B2F73656375726974792F636F72652F41757468656E7469636174696F6E3B787200476F72672E737072696E676672616D65776F726B2E73656375726974792E61757468656E7469636174696F6E2E416273747261637441757468656E7469636174696F6E546F6B656ED3AA287E6E47640E0200035A000D61757468656E746963617465644C000B617574686F7269746965737400164C6A6176612F7574696C2F436F6C6C656374696F6E3B4C000764657461696C737400124C6A6176612F6C616E672F4F626A6563743B787000737200266A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C654C697374FC0F2531B5EC8E100200014C00046C6973747400104C6A6176612F7574696C2F4C6973743B7872002C6A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65436F6C6C656374696F6E19420080CB5EF71E0200014C00016371007E00047870737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000005770400000005737200426F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E617574686F726974792E53696D706C654772616E746564417574686F7269747900000000000001F40200014C0004726F6C657400124C6A6176612F6C616E672F537472696E673B787074000A524F4C455F41444D494E7371007E000D74000763757272656E747371007E000D740004657869747371007E000D7400066D656D6265727371007E000D74000571756572797871007E000C707372003A6F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E4F41757468325265717565737400000000000000010200075A0008617070726F7665644C000B617574686F72697469657371007E00044C000A657874656E73696F6E7374000F4C6A6176612F7574696C2F4D61703B4C000B726564697265637455726971007E000E4C00077265667265736874003B4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F70726F76696465722F546F6B656E526571756573743B4C000B7265736F7572636549647374000F4C6A6176612F7574696C2F5365743B4C000D726573706F6E7365547970657371007E001C787200386F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E426173655265717565737436287A3EA37169BD0200034C0008636C69656E74496471007E000E4C001172657175657374506172616D657465727371007E001A4C000573636F706571007E001C787074000474657374737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C654D6170F1A5A8FE74F507420200014C00016D71007E001A7870737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F400000000000067708000000080000000374000A6772616E745F7479706574000870617373776F7264740009636C69656E745F696474000474657374740008757365726E616D65740006E59388E5938878737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65536574801D92D18F9B80550200007871007E0009737200176A6176612E7574696C2E4C696E6B656448617368536574D86CD75A95DD2A1E020000787200116A6176612E7574696C2E48617368536574BA44859596B8B7340300007870770C000000103F400000000000017400047265616478017371007E002D770C000000103F40000000000000787371007E00223F40000000000000770800000010000000007870707371007E002D770C000000103F40000000000000787371007E002D770C000000103F40000000000000787372004F6F72672E737072696E676672616D65776F726B2E73656375726974792E61757468656E7469636174696F6E2E557365726E616D6550617373776F726441757468656E7469636174696F6E546F6B656E00000000000001F40200024C000B63726564656E7469616C7371007E00054C00097072696E636970616C71007E00057871007E0003017371007E00077371007E000B0000000577040000000571007E000F71007E001171007E001371007E001571007E00177871007E0037737200176A6176612E7574696C2E4C696E6B6564486173684D617034C04E5C106CC0FB0200015A000B6163636573734F726465727871007E00223F400000000000067708000000080000000474000D636C69656E745F7365637265747400047465737471007E002471007E002571007E002671007E002771007E002871007E0029780070737200326F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E7573657264657461696C732E5573657200000000000001F40200075A00116163636F756E744E6F6E457870697265645A00106163636F756E744E6F6E4C6F636B65645A001563726564656E7469616C734E6F6E457870697265645A0007656E61626C65644C000B617574686F72697469657371007E001C4C000870617373776F726471007E000E4C0008757365726E616D6571007E000E7870010101017371007E002A737200116A6176612E7574696C2E54726565536574DD98509395ED875B0300007870737200466F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E7573657264657461696C732E5573657224417574686F72697479436F6D70617261746F7200000000000001F4020000787077040000000571007E000F71007E001171007E001371007E001571007E00177870740006E59388E59388, 'd8cb59d62a794ebbafe9f5415c761edd');
    INSERT INTO `oauth_access_token` VALUES ('26d505cec2cdad56ef4c394a77971262', 0xACED0005737200436F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744F4175746832416363657373546F6B656E0CB29E361B24FACE0200064C00156164646974696F6E616C496E666F726D6174696F6E74000F4C6A6176612F7574696C2F4D61703B4C000A65787069726174696F6E7400104C6A6176612F7574696C2F446174653B4C000C72656672657368546F6B656E74003F4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F636F6D6D6F6E2F4F417574683252656672657368546F6B656E3B4C000573636F706574000F4C6A6176612F7574696C2F5365743B4C0009746F6B656E547970657400124C6A6176612F6C616E672F537472696E673B4C000576616C756571007E000578707372001E6A6176612E7574696C2E436F6C6C656374696F6E7324456D7074794D6170593614855ADCE7D002000078707372000E6A6176612E7574696C2E44617465686A81014B597419030000787077080000016F1A2E5066787372004C6F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744578706972696E674F417574683252656672657368546F6B656E2FDF47639DD0C9B70200014C000A65787069726174696F6E71007E0002787200446F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744F417574683252656672657368546F6B656E73E10E0A6354D45E0200014C000576616C756571007E0005787074002437643562663363662D353939632D346565332D393130632D6362656236356330336533397371007E000977080000016F3B9A854E78737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65536574801D92D18F9B80550200007872002C6A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65436F6C6C656374696F6E19420080CB5EF71E0200014C0001637400164C6A6176612F7574696C2F436F6C6C656374696F6E3B7870737200176A6176612E7574696C2E4C696E6B656448617368536574D86CD75A95DD2A1E020000787200116A6176612E7574696C2E48617368536574BA44859596B8B7340300007870770C000000023F40000000000001740004726561647874000662656172657274002461393733383162302D363935352D346131622D616239632D303033616633656538616664, 'e85a4a2ef943bd8dbebe52c4cd207c68', '哈哈', 'android', 0xACED0005737200416F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E4F417574683241757468656E7469636174696F6EBD400B02166252130200024C000D73746F7265645265717565737474003C4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F70726F76696465722F4F4175746832526571756573743B4C00127573657241757468656E7469636174696F6E7400324C6F72672F737072696E676672616D65776F726B2F73656375726974792F636F72652F41757468656E7469636174696F6E3B787200476F72672E737072696E676672616D65776F726B2E73656375726974792E61757468656E7469636174696F6E2E416273747261637441757468656E7469636174696F6E546F6B656ED3AA287E6E47640E0200035A000D61757468656E746963617465644C000B617574686F7269746965737400164C6A6176612F7574696C2F436F6C6C656374696F6E3B4C000764657461696C737400124C6A6176612F6C616E672F4F626A6563743B787000737200266A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C654C697374FC0F2531B5EC8E100200014C00046C6973747400104C6A6176612F7574696C2F4C6973743B7872002C6A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65436F6C6C656374696F6E19420080CB5EF71E0200014C00016371007E00047870737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000005770400000005737200426F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E617574686F726974792E53696D706C654772616E746564417574686F7269747900000000000001F40200014C0004726F6C657400124C6A6176612F6C616E672F537472696E673B787074000A524F4C455F41444D494E7371007E000D74000763757272656E747371007E000D740004657869747371007E000D7400066D656D6265727371007E000D74000571756572797871007E000C707372003A6F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E4F41757468325265717565737400000000000000010200075A0008617070726F7665644C000B617574686F72697469657371007E00044C000A657874656E73696F6E7374000F4C6A6176612F7574696C2F4D61703B4C000B726564697265637455726971007E000E4C00077265667265736874003B4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F70726F76696465722F546F6B656E526571756573743B4C000B7265736F7572636549647374000F4C6A6176612F7574696C2F5365743B4C000D726573706F6E7365547970657371007E001C787200386F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E426173655265717565737436287A3EA37169BD0200034C0008636C69656E74496471007E000E4C001172657175657374506172616D657465727371007E001A4C000573636F706571007E001C7870740007616E64726F6964737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C654D6170F1A5A8FE74F507420200014C00016D71007E001A7870737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F400000000000037708000000040000000274000A6772616E745F7479706574000870617373776F7264740008757365726E616D65740006E59388E5938878737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65536574801D92D18F9B80550200007871007E0009737200176A6176612E7574696C2E4C696E6B656448617368536574D86CD75A95DD2A1E020000787200116A6176612E7574696C2E48617368536574BA44859596B8B7340300007870770C000000103F400000000000017400047265616478017371007E002B770C000000103F40000000000000787371007E00223F40000000000000770800000010000000007870707371007E002B770C000000103F40000000000000787371007E002B770C000000103F40000000000000787372004F6F72672E737072696E676672616D65776F726B2E73656375726974792E61757468656E7469636174696F6E2E557365726E616D6550617373776F726441757468656E7469636174696F6E546F6B656E00000000000001F40200024C000B63726564656E7469616C7371007E00054C00097072696E636970616C71007E00057871007E0003017371007E00077371007E000B0000000577040000000571007E000F71007E001171007E001371007E001571007E00177871007E0035737200176A6176612E7574696C2E4C696E6B6564486173684D617034C04E5C106CC0FB0200015A000B6163636573734F726465727871007E00223F400000000000067708000000080000000271007E002471007E002571007E002671007E0027780070737200326F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E7573657264657461696C732E5573657200000000000001F40200075A00116163636F756E744E6F6E457870697265645A00106163636F756E744E6F6E4C6F636B65645A001563726564656E7469616C734E6F6E457870697265645A0007656E61626C65644C000B617574686F72697469657371007E001C4C000870617373776F726471007E000E4C0008757365726E616D6571007E000E7870010101017371007E0028737200116A6176612E7574696C2E54726565536574DD98509395ED875B0300007870737200466F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E7573657264657461696C732E5573657224417574686F72697479436F6D70617261746F7200000000000001F4020000787077040000000571007E000F71007E001171007E001371007E001571007E00177870740006E59388E59388, '2c3ee8afcb131082c3d2dd7a58efabf1');
    
    -- ----------------------------
    -- Table structure for oauth_approvals
    -- ----------------------------
    DROP TABLE IF EXISTS `oauth_approvals`;
    CREATE TABLE `oauth_approvals` (
      `userId` varchar(256) DEFAULT NULL,
      `clientId` varchar(256) DEFAULT NULL,
      `scope` varchar(256) DEFAULT NULL,
      `status` varchar(10) DEFAULT NULL,
      `expiresAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `lastModifiedAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of oauth_approvals
    -- ----------------------------
    
    -- ----------------------------
    -- Table structure for oauth_client_details
    -- ----------------------------
    DROP TABLE IF EXISTS `oauth_client_details`;
    CREATE TABLE `oauth_client_details` (
      `client_id` varchar(128) NOT NULL,
      `resource_ids` varchar(256) DEFAULT NULL,
      `client_secret` varchar(256) DEFAULT NULL,
      `scope` varchar(256) DEFAULT NULL,
      `authorized_grant_types` varchar(256) DEFAULT NULL,
      `web_server_redirect_uri` varchar(256) DEFAULT NULL,
      `authorities` varchar(256) DEFAULT NULL,
      `access_token_validity` int(11) DEFAULT NULL,
      `refresh_token_validity` int(11) DEFAULT NULL,
      `additional_information` varchar(4096) DEFAULT NULL,
      `autoapprove` varchar(256) DEFAULT NULL,
      PRIMARY KEY (`client_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of oauth_client_details
    -- ----------------------------
    INSERT INTO `oauth_client_details` VALUES ('android', null, 'android', 'read', 'password,authorization_code,refresh_token', null, null, null, null, null, null);
    INSERT INTO `oauth_client_details` VALUES ('test', null, 'test', 'read', 'password,authorization_code,refresh_token', null, null, null, null, null, null);
    
    -- ----------------------------
    -- Table structure for oauth_client_token
    -- ----------------------------
    DROP TABLE IF EXISTS `oauth_client_token`;
    CREATE TABLE `oauth_client_token` (
      `token_id` varchar(256) DEFAULT NULL,
      `token` blob,
      `authentication_id` varchar(128) NOT NULL,
      `user_name` varchar(256) DEFAULT NULL,
      `client_id` varchar(256) DEFAULT NULL,
      PRIMARY KEY (`authentication_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of oauth_client_token
    -- ----------------------------
    
    -- ----------------------------
    -- Table structure for oauth_code
    -- ----------------------------
    DROP TABLE IF EXISTS `oauth_code`;
    CREATE TABLE `oauth_code` (
      `code` varchar(256) DEFAULT NULL,
      `authentication` blob
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of oauth_code
    -- ----------------------------
    
    -- ----------------------------
    -- Table structure for oauth_refresh_token
    -- ----------------------------
    DROP TABLE IF EXISTS `oauth_refresh_token`;
    CREATE TABLE `oauth_refresh_token` (
      `token_id` varchar(256) DEFAULT NULL,
      `token` blob,
      `authentication` blob
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Records of oauth_refresh_token
    -- ----------------------------
    INSERT INTO `oauth_refresh_token` VALUES ('2c3ee8afcb131082c3d2dd7a58efabf1', 0xACED00057372004C6F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744578706972696E674F417574683252656672657368546F6B656E2FDF47639DD0C9B70200014C000A65787069726174696F6E7400104C6A6176612F7574696C2F446174653B787200446F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744F417574683252656672657368546F6B656E73E10E0A6354D45E0200014C000576616C75657400124C6A6176612F6C616E672F537472696E673B787074002437643562663363662D353939632D346565332D393130632D6362656236356330336533397372000E6A6176612E7574696C2E44617465686A81014B597419030000787077080000016F3B9A854E78, 0xACED0005737200416F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E4F417574683241757468656E7469636174696F6EBD400B02166252130200024C000D73746F7265645265717565737474003C4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F70726F76696465722F4F4175746832526571756573743B4C00127573657241757468656E7469636174696F6E7400324C6F72672F737072696E676672616D65776F726B2F73656375726974792F636F72652F41757468656E7469636174696F6E3B787200476F72672E737072696E676672616D65776F726B2E73656375726974792E61757468656E7469636174696F6E2E416273747261637441757468656E7469636174696F6E546F6B656ED3AA287E6E47640E0200035A000D61757468656E746963617465644C000B617574686F7269746965737400164C6A6176612F7574696C2F436F6C6C656374696F6E3B4C000764657461696C737400124C6A6176612F6C616E672F4F626A6563743B787000737200266A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C654C697374FC0F2531B5EC8E100200014C00046C6973747400104C6A6176612F7574696C2F4C6973743B7872002C6A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65436F6C6C656374696F6E19420080CB5EF71E0200014C00016371007E00047870737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000005770400000005737200426F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E617574686F726974792E53696D706C654772616E746564417574686F7269747900000000000001F40200014C0004726F6C657400124C6A6176612F6C616E672F537472696E673B787074000A524F4C455F41444D494E7371007E000D74000763757272656E747371007E000D740004657869747371007E000D7400066D656D6265727371007E000D74000571756572797871007E000C707372003A6F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E4F41757468325265717565737400000000000000010200075A0008617070726F7665644C000B617574686F72697469657371007E00044C000A657874656E73696F6E7374000F4C6A6176612F7574696C2F4D61703B4C000B726564697265637455726971007E000E4C00077265667265736874003B4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F70726F76696465722F546F6B656E526571756573743B4C000B7265736F7572636549647374000F4C6A6176612F7574696C2F5365743B4C000D726573706F6E7365547970657371007E001C787200386F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E426173655265717565737436287A3EA37169BD0200034C0008636C69656E74496471007E000E4C001172657175657374506172616D657465727371007E001A4C000573636F706571007E001C7870740007616E64726F6964737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C654D6170F1A5A8FE74F507420200014C00016D71007E001A7870737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F400000000000037708000000040000000274000A6772616E745F7479706574000870617373776F7264740008757365726E616D65740006E59388E5938878737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65536574801D92D18F9B80550200007871007E0009737200176A6176612E7574696C2E4C696E6B656448617368536574D86CD75A95DD2A1E020000787200116A6176612E7574696C2E48617368536574BA44859596B8B7340300007870770C000000103F400000000000017400047265616478017371007E002B770C000000103F40000000000000787371007E00223F40000000000000770800000010000000007870707371007E002B770C000000103F40000000000000787371007E002B770C000000103F40000000000000787372004F6F72672E737072696E676672616D65776F726B2E73656375726974792E61757468656E7469636174696F6E2E557365726E616D6550617373776F726441757468656E7469636174696F6E546F6B656E00000000000001F40200024C000B63726564656E7469616C7371007E00054C00097072696E636970616C71007E00057871007E0003017371007E00077371007E000B0000000577040000000571007E000F71007E001171007E001371007E001571007E00177871007E0035737200176A6176612E7574696C2E4C696E6B6564486173684D617034C04E5C106CC0FB0200015A000B6163636573734F726465727871007E00223F400000000000067708000000080000000271007E002471007E002571007E002671007E0027780070737200326F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E7573657264657461696C732E5573657200000000000001F40200075A00116163636F756E744E6F6E457870697265645A00106163636F756E744E6F6E4C6F636B65645A001563726564656E7469616C734E6F6E457870697265645A0007656E61626C65644C000B617574686F72697469657371007E001C4C000870617373776F726471007E000E4C0008757365726E616D6571007E000E7870010101017371007E0028737200116A6176612E7574696C2E54726565536574DD98509395ED875B0300007870737200466F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E7573657264657461696C732E5573657224417574686F72697479436F6D70617261746F7200000000000001F4020000787077040000000571007E000F71007E001171007E001371007E001571007E00177870740006E59388E59388);
    INSERT INTO `oauth_refresh_token` VALUES ('d8cb59d62a794ebbafe9f5415c761edd', 0xACED00057372004C6F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744578706972696E674F417574683252656672657368546F6B656E2FDF47639DD0C9B70200014C000A65787069726174696F6E7400104C6A6176612F7574696C2F446174653B787200446F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E636F6D6D6F6E2E44656661756C744F417574683252656672657368546F6B656E73E10E0A6354D45E0200014C000576616C75657400124C6A6176612F6C616E672F537472696E673B787074002465303535303762632D326131622D343330662D383831372D3031383831646464306231327372000E6A6176612E7574696C2E44617465686A81014B597419030000787077080000016F3C3EC2E578, 0xACED0005737200416F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E4F417574683241757468656E7469636174696F6EBD400B02166252130200024C000D73746F7265645265717565737474003C4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F70726F76696465722F4F4175746832526571756573743B4C00127573657241757468656E7469636174696F6E7400324C6F72672F737072696E676672616D65776F726B2F73656375726974792F636F72652F41757468656E7469636174696F6E3B787200476F72672E737072696E676672616D65776F726B2E73656375726974792E61757468656E7469636174696F6E2E416273747261637441757468656E7469636174696F6E546F6B656ED3AA287E6E47640E0200035A000D61757468656E746963617465644C000B617574686F7269746965737400164C6A6176612F7574696C2F436F6C6C656374696F6E3B4C000764657461696C737400124C6A6176612F6C616E672F4F626A6563743B787000737200266A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C654C697374FC0F2531B5EC8E100200014C00046C6973747400104C6A6176612F7574696C2F4C6973743B7872002C6A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65436F6C6C656374696F6E19420080CB5EF71E0200014C00016371007E00047870737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000005770400000005737200426F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E617574686F726974792E53696D706C654772616E746564417574686F7269747900000000000001F40200014C0004726F6C657400124C6A6176612F6C616E672F537472696E673B787074000A524F4C455F41444D494E7371007E000D74000763757272656E747371007E000D740004657869747371007E000D7400066D656D6265727371007E000D74000571756572797871007E000C707372003A6F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E4F41757468325265717565737400000000000000010200075A0008617070726F7665644C000B617574686F72697469657371007E00044C000A657874656E73696F6E7374000F4C6A6176612F7574696C2F4D61703B4C000B726564697265637455726971007E000E4C00077265667265736874003B4C6F72672F737072696E676672616D65776F726B2F73656375726974792F6F61757468322F70726F76696465722F546F6B656E526571756573743B4C000B7265736F7572636549647374000F4C6A6176612F7574696C2F5365743B4C000D726573706F6E7365547970657371007E001C787200386F72672E737072696E676672616D65776F726B2E73656375726974792E6F61757468322E70726F76696465722E426173655265717565737436287A3EA37169BD0200034C0008636C69656E74496471007E000E4C001172657175657374506172616D657465727371007E001A4C000573636F706571007E001C787074000474657374737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C654D6170F1A5A8FE74F507420200014C00016D71007E001A7870737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F400000000000037708000000040000000274000A6772616E745F7479706574000870617373776F7264740008757365726E616D65740006E59388E5938878737200256A6176612E7574696C2E436F6C6C656374696F6E7324556E6D6F6469666961626C65536574801D92D18F9B80550200007871007E0009737200176A6176612E7574696C2E4C696E6B656448617368536574D86CD75A95DD2A1E020000787200116A6176612E7574696C2E48617368536574BA44859596B8B7340300007870770C000000103F400000000000017400047265616478017371007E002B770C000000103F40000000000000787371007E00223F40000000000000770800000010000000007870707371007E002B770C000000103F40000000000000787371007E002B770C000000103F40000000000000787372004F6F72672E737072696E676672616D65776F726B2E73656375726974792E61757468656E7469636174696F6E2E557365726E616D6550617373776F726441757468656E7469636174696F6E546F6B656E00000000000001F40200024C000B63726564656E7469616C7371007E00054C00097072696E636970616C71007E00057871007E0003017371007E00077371007E000B0000000577040000000571007E000F71007E001171007E001371007E001571007E00177871007E0035737200176A6176612E7574696C2E4C696E6B6564486173684D617034C04E5C106CC0FB0200015A000B6163636573734F726465727871007E00223F400000000000067708000000080000000271007E002471007E002571007E002671007E0027780070737200326F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E7573657264657461696C732E5573657200000000000001F40200075A00116163636F756E744E6F6E457870697265645A00106163636F756E744E6F6E4C6F636B65645A001563726564656E7469616C734E6F6E457870697265645A0007656E61626C65644C000B617574686F72697469657371007E001C4C000870617373776F726471007E000E4C0008757365726E616D6571007E000E7870010101017371007E0028737200116A6176612E7574696C2E54726565536574DD98509395ED875B0300007870737200466F72672E737072696E676672616D65776F726B2E73656375726974792E636F72652E7573657264657461696C732E5573657224417574686F72697479436F6D70617261746F7200000000000001F4020000787077040000000571007E000F71007E001171007E001371007E001571007E00177870740006E59388E59388);

## 5.9 测试认证授权 ##

启动注册中心  
启动redis  
启动认证授权项目  
postman测试：

### 5.9.1 获取token ###

http://localhost:1202/oauth/token?grant\_type=password&username=哈哈&password=1&client\_id=test&client\_secret=test  
注意使用POST  
返回结果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 1]  
讲解说明：  
入参说明：  
grant\_type指定了oauth2.0的授权模式，这里是密码模式，上面的参考资料中有详细的说明，4种模式  
client\_id是oauth2.0的username  
client\_secret是oauth2.0的password  
返回结果说明：  
Authorization是oauth2.0根据client\_id:client\_secret的结构进行base64编码得到的，postman会自动转换  
access\_token就是我们要的token  
refresh\_token是用来刷新token时用到的

### 5.9.2 测试访问授权资源 ###

postman请求  
http://localhost:1202/api/member?access\_token=刚才获取的token  
GET方式请求

结果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 2]

## 5.9.3 刷新token ##

POSTMAN使用post请求  
http://localhost:1202/oauth/token?grant\_type=refresh\_token&refresh\_token=获取token时的refresh\_token  
特别注意Headers中添加：Authorization 值为请求token时生成的内容 Basic dGVzdDp0ZXN0  
如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 3]

# 六、创建测试项目 #

测试项目名为 oauth-memmber

## 6.1 POM ##

## 6.2 yml配置及启动类 ##

spring:
      application:
        name: oauth-member
    
    server:
      port: 1201
    
    eureka:
      client:
        service-url:
          defaultZone: http://peer1:8000/eureka/,http://peer2:8001/eureka/,http://peer3:8002/eureka/
    security:
      oauth2:
        resource:
          id: oauth-member
          user-info-uri: http://localhost:8888/auth/api/member
          prefer-token-info: false

启动类添加注解：  
@SpringBootApplication  
@EnableDiscoveryClient  
@EnableGlobalMethodSecurity(prePostEnabled = true)//会拦截注解了@PreAuthrize注解的配置.

## 6.3 配置类 ##

package com.example.oauthmember.config;
    
    import org.springframework.context.annotation.Configuration;
    import org.springframework.security.config.annotation.web.builders.HttpSecurity;
    import org.springframework.security.oauth2.config.annotation.web.configuration.EnableResourceServer;
    import org.springframework.security.oauth2.config.annotation.web.configuration.ResourceServerConfigurerAdapter;
    
    import javax.servlet.http.HttpServletResponse;
    
    /** * 〈OAuth资源服务配置〉 * * @author Curise * @create 2018/12/14 * @since 1.0.0 */
    @Configuration
    @EnableResourceServer
    public class ResourceServerConfig extends ResourceServerConfigurerAdapter {
    
        @Override
        public void configure(HttpSecurity http) throws Exception {
            http
                    .csrf().disable()
                    .exceptionHandling()
                    .authenticationEntryPoint((request, response, authException) -> response.sendError(HttpServletResponse.SC_UNAUTHORIZED))
                    .and()
                    .requestMatchers().antMatchers("/api/**")
                    .and()
                    .authorizeRequests()
                    .antMatchers("/api/**").authenticated()
                    .and()
                    .httpBasic();
        }
    }

## 6.4 创建测试接口 ##

package com.example.oauthmember.controller;
    
    import org.springframework.security.access.prepost.PreAuthorize;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.security.Principal;
    
    /** * 〈〉 * * @author Curise * @create 2018/12/12 * @since 1.0.0 */
    @RestController
    @RequestMapping("/api")
    public class MemberController {
    
        @GetMapping("hello")
        @PreAuthorize("hasAnyAuthority('hello')")
        public String hello(){
            return "hello";
        }
    
        @GetMapping("current")
        public Principal user(Principal principal) {
            return principal;
        }
    
        @GetMapping("query")
        @PreAuthorize("hasAnyAuthority('query')")
        public String query() {
            return "具有query权限";
        }
    }

# 七、GateWay配置 #

## 7.1 yml修改 ##

routes节点下添加两段代码

routes:
            - id: oauth_member_routes
              uri: lb://oauth-member
              predicates:
              - Path=/member/**
              filters:
              - StripPrefix=1 #注意需要截取一位
    
            - id: oauth_auth_routes
              uri: lb://oauth-server
              predicates:
              - Path=/auth/**
              filters:
              - StripPrefix=1 #注意需要截取一位

## 7.2 通过gateway测试 ##

POSTMAN  
获取token:  
http://localhost:8888/auth/oauth/token?grant\_type=password&username=哈哈&password=1&client\_id=test&client\_secret=test

访问资源：  
http://localhost:8888/auth/api/member?access\_token=f64030ee-1135-4a68-b9c8-0cf3e3e8277e

刷新token：  
http://localhost:8888/auth/oauth/token?grant\_type=refresh\_token&refresh\_token=e05507bc-2a1b-430f-8817-01881ddd0b12

访问测试项目的资源：  
http://localhost:8888/member/api/current?access\_token=f64030ee-1135-4a68-b9c8-0cf3e3e8277e

OK集成完成。

# 有钱的捧个钱场,没钱的点个收藏 #

开源分享不易，求各位大哥大姐赏点饭钱。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 5]

[GitHub]: https://github.com/MaoCaiAWen/cloud_F
[Link 1]: https://blog.csdn.net/qq_33333654/article/details/103525745
[Link 2]: https://blog.csdn.net/qq_33333654/category_9436879.html
[-_OAuth 2.0]: http://www.ruanyifeng.com/blog/2014/05/oauth_2_0.html
[Link 3]: https://projects.spring.io/spring-security-oauth/docs/oauth2.html
[Link 4]: https://www.cnblogs.com/lenve/p/11242055.html
[Link 5]: http://www.imooc.com/article/287269
[Link 6]: https://blog.csdn.net/WYA1993/article/details/85050120#commentBox
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191220164957542.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191220170428317.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191220171107852.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191220171342170.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191214111006708.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191214111023275.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzMzNjU0,size_16,color_FFFFFF,t_70