OAuth2学习(三)——OAuth2信息持久化存储

在上一篇文章中我们简单介绍了OAuth2的基本案例和简单使用——OAuth2学习(二)——OAuth2实战，但是配置信息都是基于内存当中进行设置的，这在我们实际应用中很少使用，一般我们都需要进行持久化设置。今天我们就来聊聊OAuth2中信息持久化存储。

在前面的例子中我们获取到了token，然后拿到token去获取相关接口信息。在实际开发中，我们可能需要动态添加某个认证的客户端和密匙，然后对新添加的信息进行认证，所以通过将数据存入数据库能够更好实现我们的需求。

本文通过使用MySQL实现OAuth2相关信息存储，当然也可以使用Redis或其他数据库。

根据官方Spring-security-oauth2提供的创建表的schema的SQL语句，我们首先创建相关表。

注意：官方提供的SQL是HSQL的，使用MySQL需要更改一些字段，把LONGVARBINARY类型改为BLOB类型，同时把主键长度256改为128。在新版本的MySQL可以不用更改，能够支持256的长度。下面是我更改好的，可直接使用。同时我部分表添加enable字段，作为开关标志位。

-- used in tests that use HSQL
create table oauth_client_details (
  client_id VARCHAR(128) PRIMARY KEY,
  resource_ids VARCHAR(128),
  client_secret VARCHAR(128),
  scope VARCHAR(128),
  authorized_grant_types VARCHAR(128),
  web_server_redirect_uri VARCHAR(128),
  authorities VARCHAR(128),
  access_token_validity INTEGER,
  refresh_token_validity INTEGER,
  additional_information VARCHAR(4096),
  enable tinyint(1) default '1',
  autoapprove VARCHAR(128)
);

create table oauth_client_token (
  token_id VARCHAR(128),
  token BLOB,
  authentication_id VARCHAR(128) PRIMARY KEY,
  user_name VARCHAR(128),
  client_id VARCHAR(128)
);

create table oauth_access_token (
  token_id VARCHAR(128),
  token BLOB,
  authentication_id VARCHAR(128) PRIMARY KEY,
  user_name VARCHAR(128),
  client_id VARCHAR(128),
  authentication BLOB,
  refresh_token VARCHAR(128)
);


create table oauth_refresh_token (
  token_id VARCHAR(128),
  token BLOB,
  authentication BLOB
);


create table oauth_code (
  code VARCHAR(128), authentication BLOB
);

create table oauth_approvals (
	userId VARCHAR(128),
	clientId VARCHAR(128),
	scope VARCHAR(128),
	status VARCHAR(10),
	expiresAt TIMESTAMP,
	lastModifiedAt TIMESTAMP
);

-- customized oauth_client_details table
create table ClientDetails (
  appId VARCHAR(128) PRIMARY KEY,
  resourceIds VARCHAR(128),
  appSecret VARCHAR(128),
  scope VARCHAR(128),
  grantTypes VARCHAR(128),
  redirectUrl VARCHAR(128),
  authorities VARCHAR(128),
  access_token_validity INTEGER,
  refresh_token_validity INTEGER,
  additionalInformation VARCHAR(4096),
  autoApproveScopes VARCHAR(128)
);

创建好的表如上图所示。

最后生成了这七张表，关于具体字段详情的含义介绍可以查看相关文档。

接着我们添加数据库依赖如下：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<!--HikariCP-->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

如果是使用Redis，就添加Redis相关依赖：

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

前面我们的Security直接在配置文件里面进行简单配置用户名和密码实现，这节我们通过添加用户表，权限表来简单实现登录串联OAuth2。

用户与权限相互关联，最好是使用user->role->privilege，这里简单演示就忽略了role的创建，直接关联的权限表。

我们创建用户表和权限表如下：

-- # 用户表
CREATE TABLE user (
  id int(11) NOT NULL auto_increment,
  guid varchar(255) not null unique,
  enable tinyint(1) default '1',
  email varchar(255),
  password varchar(255) not null,
  phone varchar(255),
  username varchar(255) not null unique,
  default_user tinyint(1) default '0',
  last_login_time datetime ,
  PRIMARY KEY  (id)
)

-- # 用户权限表
CREATE TABLE user_privilege (
  user_id int(11),
  privilege varchar(255),
  KEY user_id_index (user_id)
)

接着我们来添加一个用户数据，然后配置相应的权限，如下：

字段都很简单就不多说了，要注意的是密码为md5加密的。对应的权限表如下：

这里的权限对应到代码里面的枚举配置。

基本数据库配置准备完毕，我们开始改造代码。还是基于我们上一节的代码来进行升级。首先是OAuth2AuthorizationServer这个类，如下：

package net.anumbrella.oauth2.config;

import net.anumbrella.oauth2.handler.OauthUserApprovalHandler;
import net.anumbrella.oauth2.service.OauthService;
import net.anumbrella.oauth2.service.UserService;
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.security.authentication.AuthenticationManager;
import org.springframework.security.oauth2.config.annotation.configurers.ClientDetailsServiceConfigurer;
import org.springframework.security.oauth2.config.annotation.web.configuration.AuthorizationServerConfigurerAdapter;
import org.springframework.security.oauth2.config.annotation.web.configuration.EnableAuthorizationServer;
import org.springframework.security.oauth2.config.annotation.web.configurers.AuthorizationServerEndpointsConfigurer;
import org.springframework.security.oauth2.config.annotation.web.configurers.AuthorizationServerSecurityConfigurer;
import org.springframework.security.oauth2.provider.ClientDetailsService;
import org.springframework.security.oauth2.provider.OAuth2RequestFactory;
import org.springframework.security.oauth2.provider.approval.UserApprovalHandler;
import org.springframework.security.oauth2.provider.client.JdbcClientDetailsService;
import org.springframework.security.oauth2.provider.code.AuthorizationCodeServices;
import org.springframework.security.oauth2.provider.code.JdbcAuthorizationCodeServices;
import org.springframework.security.oauth2.provider.request.DefaultOAuth2RequestFactory;
import org.springframework.security.oauth2.provider.token.TokenStore;
import org.springframework.security.oauth2.provider.token.store.JdbcTokenStore;

import javax.sql.DataSource;

/**
 * @auther anumbrella
 * 授权服务器配置
 */
@Configuration
@EnableAuthorizationServer
public class OAuth2AuthorizationServer extends AuthorizationServerConfigurerAdapter {

    @Autowired
    private TokenStore tokenStore;


    @Autowired
    private ClientDetailsService clientDetailsService;


    @Autowired
    private OauthService oauthService;


    @Autowired
    private AuthorizationCodeServices authorizationCodeServices;


    @Autowired
    private UserService userDetailsService;


    @Autowired
    @Qualifier("authenticationManagerBean")
    private AuthenticationManager authenticationManager;


    @Override
    public void configure(ClientDetailsServiceConfigurer clients)
            throws Exception {

        // 这里配置数据在内存当中
//        clients.inMemory()
//                .withClient("client_id")
//                .secret("123456")
//                .redirectUris("http://localhost:8080/callback")
//                // 授权码模式
//                .authorizedGrantTypes("authorization_code")
//                .scopes("read_userinfo", "read_contacts");


        clients.withClientDetails(clientDetailsService);
    }

    /*
     * JDBC TokenStore
     */
    @Bean
    public TokenStore tokenStore(DataSource dataSource) {
        return new JdbcTokenStore(dataSource);
    }

    @Bean
    public ClientDetailsService clientDetailsService(DataSource dataSource) {
        return new JdbcClientDetailsService(dataSource);
    }


    @Bean
    public AuthorizationCodeServices authorizationCodeServices(DataSource dataSource) {
        return new JdbcAuthorizationCodeServices(dataSource);
    }


    @Override
    public void configure(AuthorizationServerEndpointsConfigurer endpoints) throws Exception {
        endpoints.tokenStore(tokenStore)
                .authorizationCodeServices(authorizationCodeServices)
                .userDetailsService(userDetailsService)
                .userApprovalHandler(userApprovalHandler())
                .authenticationManager(authenticationManager);
    }


    @Override
    public void configure(AuthorizationServerSecurityConfigurer oauthServer) throws Exception {
        // 允许表单认证
        oauthServer
                .tokenKeyAccess("permitAll()")
                .checkTokenAccess("permitAll()")
                .allowFormAuthenticationForClients();
    }

    @Bean
    public OAuth2RequestFactory oAuth2RequestFactory() {
        return new DefaultOAuth2RequestFactory(clientDetailsService);
    }


    @Bean
    public UserApprovalHandler userApprovalHandler() {
        OauthUserApprovalHandler userApprovalHandler = new OauthUserApprovalHandler();
        userApprovalHandler.setOauthService(oauthService);
        userApprovalHandler.setTokenStore(tokenStore);
        userApprovalHandler.setClientDetailsService(this.clientDetailsService);
        userApprovalHandler.setRequestFactory(oAuth2RequestFactory());
        return userApprovalHandler;
    }
}

通过clients.withClientDetails(clientDetailsService);我们使用数据库服务来加载client的详细信息，然后其他配置就是数据库注入相关配置，比较重要的是userApprovalHandler方法，实现自己自定义的认证handler处理操作，然后oauthService服务为我们自己的服务实现。

具体的OauthUserApprovalHandler如下：

package net.anumbrella.oauth2.handler;

import net.anumbrella.oauth2.entity.OauthClientDetails;
import net.anumbrella.oauth2.service.OauthService;
import org.springframework.security.core.Authentication;
import org.springframework.security.oauth2.provider.AuthorizationRequest;
import org.springframework.security.oauth2.provider.approval.TokenStoreUserApprovalHandler;

public class OauthUserApprovalHandler extends TokenStoreUserApprovalHandler {

    private OauthService oauthService;

    public OauthUserApprovalHandler() {
    }


    @Override
    public boolean isApproved(AuthorizationRequest authorizationRequest, Authentication userAuthentication) {
        if (super.isApproved(authorizationRequest, userAuthentication)) {
            return true;
        }
        if (!userAuthentication.isAuthenticated()) {
            return false;
        }

        // 自定义方法，根据请求内容的clientId 获取数据库中client中的详情信息
        OauthClientDetails clientDetails = oauthService.loadOauthClientDetails(authorizationRequest.getClientId());
        return clientDetails != null && clientDetails.isEnable();

    }

    public void setOauthService(OauthService oauthService) {
        this.oauthService = oauthService;
    }
}

然后我们自定义自己的OauthService，并实现它的接口。

package net.anumbrella.oauth2.service;

import net.anumbrella.oauth2.entity.OauthClientDetails;

public interface OauthService {

    OauthClientDetails loadOauthClientDetails(String clientId);
}

OauthServiceImpl具体的OauthService实现类，在实现类里面我们通过OauthDao去操作具体的数据库操作。

package net.anumbrella.oauth2.service.impl;

import net.anumbrella.oauth2.dao.OauthDao;
import net.anumbrella.oauth2.entity.OauthClientDetails;
import net.anumbrella.oauth2.service.OauthService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service("oauthService")
public class OauthServiceImpl implements OauthService {

    private static final Logger LOG = LoggerFactory.getLogger(OauthServiceImpl.class);

    @Autowired
    private OauthDao oauthDao;

    @Override
    @Transactional(readOnly = true)
    public OauthClientDetails loadOauthClientDetails(String clientId) {
        return oauthDao.findOauthClientDetails(clientId);
    }
}

然后我们定义OauthDaoJdbc去实现OauthDao声明的findOauthClientDetails接口，如下：

@Repository("oauthDaoJdbc")
public class OauthDaoJdbc implements OauthDao {

    private static OauthClientDetailsRowMapper oauthClientDetailsRowMapper = new OauthClientDetailsRowMapper();

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public OauthClientDetails findOauthClientDetails(String clientId) {
        final String sql = " select * from oauth_client_details where  client_id = ? ";
        final List<OauthClientDetails> list = this.jdbcTemplate.query(sql, new Object[]{clientId}, oauthClientDetailsRowMapper);
        return list.isEmpty() ? null : list.get(0);
    }
}

注意这里有一个OauthClientDetailsRowMapper，是做表相关自动映射的。如下：

package net.anumbrella.oauth2.entity.mapper;

import net.anumbrella.oauth2.entity.OauthClientDetails;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class OauthClientDetailsRowMapper implements RowMapper<OauthClientDetails> {


    public OauthClientDetailsRowMapper() {
    }

    @Override
    public OauthClientDetails mapRow(ResultSet rs, int i) throws SQLException {
        OauthClientDetails clientDetails = new OauthClientDetails();

        clientDetails.setClientId(rs.getString("client_id"));
        clientDetails.setResourceIds(rs.getString("resource_ids"));
        clientDetails.setClientSecret(rs.getString("client_secret"));

        clientDetails.setScope(rs.getString("scope"));
        clientDetails.setAuthorizedGrantTypes(rs.getString("authorized_grant_types"));
        clientDetails.setWebServerRedirectUri(rs.getString("web_server_redirect_uri"));

        clientDetails.setAuthorities(rs.getString("authorities"));
        clientDetails.setAccessTokenValidity(getInteger(rs, "access_token_validity"));
        clientDetails.setRefreshTokenValidity(getInteger(rs, "refresh_token_validity"));

        clientDetails.setAdditionalInformation(rs.getString("additional_information"));

        clientDetails.setEnable(rs.getBoolean("enable"));
        clientDetails.setAutoApprove(rs.getString("autoapprove"));

        return clientDetails;
    }

    private Integer getInteger(ResultSet rs, String columnName) throws SQLException {
        final Object object = rs.getObject(columnName);
        if (object != null) {
            return (Integer) object;
        }
        return null;
    }

}

资源配置也做了权限相关配置，如下：

package net.anumbrella.oauth2.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.http.SessionCreationPolicy;
import org.springframework.security.oauth2.config.annotation.web.configuration.EnableResourceServer;
import org.springframework.security.oauth2.config.annotation.web.configuration.ResourceServerConfigurerAdapter;
import org.springframework.security.oauth2.config.annotation.web.configurers.ResourceServerSecurityConfigurer;

/**
 * @auther anumbrella
 * 资源服务配置
 */
@Configuration
@EnableResourceServer
public class OAuth2ResourceServer extends ResourceServerConfigurerAdapter {

    public static final String RESOURCE_ID = "test-resource";

    @Override
    public void configure(ResourceServerSecurityConfigurer resources) {
        resources.resourceId(RESOURCE_ID).stateless(false);
    }


    @Override
    public void configure(HttpSecurity http) throws Exception {
//        http.authorizeRequests()
//                .anyRequest()
//                .authenticated()
//                .and()
//                .requestMatchers()
//                //配置api访问控制，必须认证过后才可以访问
//                .antMatchers("/api/**");

        http
                // Since we want the protected resources to be accessible in the UI as well we need
                // session creation to be allowed (it's disabled by default in 2.0.6)
                .sessionManagement().sessionCreationPolicy(SessionCreationPolicy.IF_REQUIRED)
                .and()
                .requestMatchers().antMatchers("/api/**")
                .and()
                .authorizeRequests()
                .antMatchers("/api/**").access("#oauth2.hasScope('read') and hasRole('DEV')");

    }
}

至此OAuth2与数据库交互的实现完成，接着改造Security与数据的配置交互。

主要新增了WebSecurityConfigurer类，配置过滤规则和Security的自定义登录效验，密码加密等规则。

package net.anumbrella.oauth2.config;

import net.anumbrella.oauth2.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.HttpMethod;
import org.springframework.security.authentication.AuthenticationManager;
import org.springframework.security.authentication.AuthenticationProvider;
import org.springframework.security.authentication.dao.DaoAuthenticationProvider;
import org.springframework.security.authentication.encoding.Md5PasswordEncoder;
import org.springframework.security.authentication.encoding.PasswordEncoder;
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;

/**
 * @auther anumbrella
 * Web Security配置
 */
@Configuration
@EnableWebSecurity
public class WebSecurityConfigurer extends WebSecurityConfigurerAdapter {

    @Autowired
    private UserService userService;

    @Override
    @Bean
    public AuthenticationManager authenticationManagerBean() throws Exception {
        return super.authenticationManagerBean();
    }

    @Override
    public void configure(WebSecurity web) throws Exception {
        //Ignore, public
        web.ignoring().antMatchers("/public/**", "/static/**");

    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.csrf().ignoringAntMatchers("/oauth/authorize", "/oauth/token", "/oauth/rest_token", "/signin","/login");

        http.authorizeRequests()
                .antMatchers("/public/**").permitAll()
                .antMatchers("/static/**").permitAll()
                .antMatchers("/oauth/**").permitAll()
                .antMatchers("/login*").permitAll()
                .antMatchers(HttpMethod.GET, "/login*").anonymous()
                .anyRequest().authenticated()
                .and()
                .formLogin()
                .loginPage("/login")
                .loginProcessingUrl("/signin")
                .failureUrl("/login?error=1")
                .usernameParameter("oidc_user")
                .passwordParameter("oidcPwd")
                .and()
                .logout()
                .logoutUrl("/signout")
                .deleteCookies("JSESSIONID")
                .logoutSuccessUrl("/")
                .and()
                .exceptionHandling();

        http.authenticationProvider(authenticationProvider());
    }

    @Bean
    public AuthenticationProvider authenticationProvider() {
        // security 自定义登录效验，提供用户名和密码
        DaoAuthenticationProvider daoAuthenticationProvider = new DaoAuthenticationProvider();
        daoAuthenticationProvider.setUserDetailsService(userService);
        daoAuthenticationProvider.setPasswordEncoder(passwordEncoder());
        return daoAuthenticationProvider;
    }


    /**
     * MD5  加密
     *
     * @return PasswordEncoder
     */
    @Bean
    public PasswordEncoder passwordEncoder() {
        return new Md5PasswordEncoder();
    }
}

这里UserService和上面OauthService类似，定义接口然后实现服务通过UserDao去操作相关数据库，这里便不再复述。

然后我们更改配置文件，完成数据库和其他配置的设置，如下：

# Spring Security Setting
#security.user.name=anumbrella
#security.user.password=123

#
#
spring.application.name=oauth2
#
# MySQL
#####################
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/oauth2?autoReconnect=true&autoReconnectForPools=true&useUnicode=true&characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=123
#Datasource properties
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=2

# MVC
spring.mvc.ignore-default-model-on-redirect=false
spring.http.encoding.enabled=true
spring.http.encoding.charset=UTF-8
spring.http.encoding.force=true
spring.mvc.locale=zh_CN
spring.mvc.view.prefix=/WEB-INF/jsp/
spring.mvc.view.suffix=.jsp

security.oauth2.resource.filter-order=3

因为Security认证时候需要输入用户名和密码，所以添加了JSP页面进行交换。这里有几点需要注意，Spring boot 使用JSP需要在pom.xml添加如下依赖，并像上面那样配置Spring MVC。

    <!-- servlet依赖 -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
    </dependency>

    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>jstl</artifactId>
    </dependency>

    <dependency>
        <groupId>org.apache.tomcat.embed</groupId>
        <artifactId>tomcat-embed-jasper</artifactId>
        <!--<scope>provided</scope> 如果不能访问jsp取消注释-->
    </dependency>

如果使用IDEA，还需要设置work目录。

最后我们尝试在oauth_client_detail里面添加一条如下数据：
client_id，resources_ids，client_secret，scope，authorized_grant_types，web_server_redirect_uri，authorities分别为client_id，test-resource，123，read，authorization_code,refresh_token,implicit,password，http://localhost:8080/callback，ROLE_CLIENT的数据作为测试，没填的为空或默认。

接着的测试和上一节相同，以授权码模式为例。
我们访问http://localhost:8080/oauth/authorize?client_id=client_id&redirect_uri=http://localhost:8080/callback&response_type=code&scope=read接口， 接着输入用户名和密码：

接着我们拿到code，

最后我们在对http://localhost:8080/oauth/token?code=3FWX5U&grant_type=authorization_code&redirect_uri=http://localhost:8080/callback&scope=read发起POST请求，如下：

最后我们拿到token就可以去访问相关资源了。

在数据库我们也能查到code和token相关数据，如下：

到此，OAuth2信息持久化存储就结束了，最近有点点懒惰了，更新太慢好多计划文章还没写，后面争取尽力每月多更新几篇，不拖到月底。

代码实例：Chapter2

参考

• https://projects.spring.io/spring-security-oauth/docs/oauth2.html
• https://gitee.com/shengzhao/spring-oauth-server
• OAuth2相关数据表字段的详细说明
• https://github.com/mingyang66/spring-parent/tree/master/spring-security-oauth2-server-redis-service