Spring Security OAuth2 Provider 之 数据库存储

默认配置都是InMemory的，比如授权码，令牌，客户端信息等，实际应用时，应该是存入数据库里的。这里以PostgreSQL为例。

相关文章：
Spring Security OAuth2 Provider 之 最小实现
Spring Security OAuth2 Provider 之 数据库存储
Spring Security OAuth2 Provider 之 第三方登录简单演示
Spring Security OAuth2 Provider 之 自定义开发
Spring Security OAuth2 Provider 之 整合JWT

（1）修改代码
基于前一篇最小化实现，需要改动以下代码：

pom.xml

<dependency>  
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
</dependency>

application.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/oauth2
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=user
spring.datasource.password=pass

@Configuration
@EnableAuthorizationServer
static class OAuthAuthorizationConfig extends AuthorizationServerConfigurerAdapter {
    @Autowired
    private Environment env;
    
    @Bean
    public DataSource dataSource() {
        final DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.datasource.url"));
        dataSource.setUsername(env.getProperty("spring.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.password"));
        return dataSource;
    }
    
    @Bean
    public ApprovalStore approvalStore() {
        return new JdbcApprovalStore(dataSource());
    }
    @Bean
    protected AuthorizationCodeServices authorizationCodeServices() {
        return new JdbcAuthorizationCodeServices(dataSource());
    }
    @Bean
    public TokenStore tokenStore() {
        return new JdbcTokenStore(dataSource());
    }
    
    @Override
    public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
        clients.jdbc(dataSource()); // oauth_client_details
    }
    
    @Override
    public void configure(AuthorizationServerEndpointsConfigurer endpoints) throws Exception {
		endpoints.approvalStore(approvalStore()) 							// oauth_approvals
		         .authorizationCodeServices(authorizationCodeServices()) 	// oauth_code
				 .tokenStore(tokenStore()); 								// oauth_access_token & oauth_refresh_token
    }
}

（2）数据库表

总共有5张表：

• oauth_access_token：访问令牌
• oauth_refresh_token：更新令牌
• oauth_client_details：客户端信息
• oauth_code：授权码
• oauth_approvals：授权记录

*** oauth_client_token表比较特殊不用于Provider，是客户端用的

CREATE TABLE oauth_access_token
(
  token_id character varying(256), -- MD5加密的access_token的值
  token bytea, -- OAuth2AccessToken.java对象序列化后的二进制数据
  authentication_id character varying(256), -- MD5加密过的username,client_id,scope
  user_name character varying(256), -- 登录的用户名
  client_id character varying(256), -- 客户端ID
  authentication bytea, -- OAuth2Authentication.java对象序列化后的二进制数据
  refresh_token character varying(256) -- MD5加密果的refresh_token的值
);
COMMENT ON COLUMN oauth_access_token.token_id IS 'MD5加密的access_token的值';
COMMENT ON COLUMN oauth_access_token.token IS 'OAuth2AccessToken.java对象序列化后的二进制数据';
COMMENT ON COLUMN oauth_access_token.authentication_id IS 'MD5加密过的username,client_id,scope';
COMMENT ON COLUMN oauth_access_token.user_name IS '登录的用户名';
COMMENT ON COLUMN oauth_access_token.client_id IS '客户端ID';
COMMENT ON COLUMN oauth_access_token.authentication IS 'OAuth2Authentication.java对象序列化后的二进制数据';
COMMENT ON COLUMN oauth_access_token.refresh_token IS 'MD5加密果的refresh_token的值';

CREATE TABLE oauth_approvals
(
  userid character varying(256), -- 登录的用户名
  clientid character varying(256), -- 客户端ID
  scope character varying(256), -- 申请的权限
  status character varying(10), -- 状态（Approve或Deny）
  expiresat timestamp without time zone, -- 过期时间
  lastmodifiedat timestamp without time zone -- 最终修改时间
);
COMMENT ON COLUMN oauth_approvals.userid IS '登录的用户名';
COMMENT ON COLUMN oauth_approvals.clientid IS '客户端ID';
COMMENT ON COLUMN oauth_approvals.scope IS '申请的权限';
COMMENT ON COLUMN oauth_approvals.status IS '状态（Approve或Deny）';
COMMENT ON COLUMN oauth_approvals.expiresat IS '过期时间';
COMMENT ON COLUMN oauth_approvals.lastmodifiedat IS '最终修改时间';

CREATE TABLE oauth_client_details
(
  client_id character varying(256) NOT NULL, -- 客户端ID
  resource_ids character varying(256), -- 资源ID集合,多个资源时用逗号(,)分隔
  client_secret character varying(256), -- 客户端密匙
  scope character varying(256), -- 客户端申请的权限范围
  authorized_grant_types character varying(256), -- 客户端支持的grant_type
  web_server_redirect_uri character varying(256), -- 重定向URI
  authorities character varying(256), -- 客户端所拥有的Spring Security的权限值，多个用逗号(,)分隔
  access_token_validity integer, -- 访问令牌有效时间值(单位:秒)
  refresh_token_validity integer, -- 更新令牌有效时间值(单位:秒)
  additional_information character varying(4096), -- 预留字段
  autoapprove character varying(256), -- 用户是否自动Approval操作
  CONSTRAINT oauth_client_details_pkey PRIMARY KEY (client_id)
);
COMMENT ON COLUMN oauth_client_details.client_id IS '客户端ID';
COMMENT ON COLUMN oauth_client_details.resource_ids IS '资源ID集合,多个资源时用逗号(,)分隔';
COMMENT ON COLUMN oauth_client_details.client_secret IS '客户端密匙';
COMMENT ON COLUMN oauth_client_details.scope IS '客户端申请的权限范围';
COMMENT ON COLUMN oauth_client_details.authorized_grant_types IS '客户端支持的grant_type';
COMMENT ON COLUMN oauth_client_details.web_server_redirect_uri IS '重定向URI';
COMMENT ON COLUMN oauth_client_details.authorities IS '客户端所拥有的Spring Security的权限值，多个用逗号(,)分隔';
COMMENT ON COLUMN oauth_client_details.access_token_validity IS '访问令牌有效时间值(单位:秒)';
COMMENT ON COLUMN oauth_client_details.refresh_token_validity IS '更新令牌有效时间值(单位:秒)';
COMMENT ON COLUMN oauth_client_details.additional_information IS '预留字段';
COMMENT ON COLUMN oauth_client_details.autoapprove IS '用户是否自动Approval操作';

CREATE TABLE oauth_client_token
(
  token_id character varying(256), -- MD5加密的access_token值
  token bytea, -- OAuth2AccessToken.java对象序列化后的二进制数据
  authentication_id character varying(256), -- MD5加密过的username,client_id,scope
  user_name character varying(256), -- 登录的用户名
  client_id character varying(256) -- 客户端ID
);
COMMENT ON COLUMN oauth_client_token.token_id IS 'MD5加密的access_token值';
COMMENT ON COLUMN oauth_client_token.token IS 'OAuth2AccessToken.java对象序列化后的二进制数据';
COMMENT ON COLUMN oauth_client_token.authentication_id IS 'MD5加密过的username,client_id,scope';
COMMENT ON COLUMN oauth_client_token.user_name IS '登录的用户名';
COMMENT ON COLUMN oauth_client_token.client_id IS '客户端ID';

CREATE TABLE oauth_code
(
  code character varying(256), -- 授权码(未加密)
  authentication bytea -- AuthorizationRequestHolder.java对象序列化后的二进制数据
);
COMMENT ON COLUMN oauth_code.code IS '授权码(未加密)';
COMMENT ON COLUMN oauth_code.authentication IS 'AuthorizationRequestHolder.java对象序列化后的二进制数据';

CREATE TABLE oauth_refresh_token
(
  token_id character varying(256), -- MD5加密过的refresh_token的值
  token bytea, -- OAuth2RefreshToken.java对象序列化后的二进制数据
  authentication bytea -- OAuth2Authentication.java对象序列化后的二进制数据
);
COMMENT ON COLUMN oauth_refresh_token.token_id IS 'MD5加密过的refresh_token的值';
COMMENT ON COLUMN oauth_refresh_token.token IS 'OAuth2RefreshToken.java对象序列化后的二进制数据';
COMMENT ON COLUMN oauth_refresh_token.authentication IS 'OAuth2Authentication.java对象序列化后的二进制数据';

手动插入一条客户端信息：

INSERT INTO oauth_client_details(client_id, resource_ids, client_secret, scope, authorized_grant_types, web_server_redirect_uri, authorities, access_token_validity, refresh_token_validity, additional_information, autoapprove)
    VALUES ('oauth_client', null, 'oauth_client_secret', 'read,write', 'authorization_code,refresh_token', 'http://default-oauth-callback.com', 'ROLE_USER', 1800, 86400, null, false);

以上DDL文修改自官方提供的测试版本：
https://github.com/spring-projects/spring-security-oauth/blob/master/spring-security-oauth2/src/test/resources/schema.sql

测试确认的过程和上一篇完全一样。每执行完一步，可以到数据库对应的表中查看确认值。

评论

3 楼 cbn_1992 2019-04-03  

博主，采用jdbctoken也就是数据库形式之后，反复点击获取token的接口，就会报duplicate key value violates unique constraint "oauth_access_token_pkey" 博主遇到过吗

2 楼 安静听歌 2018-04-02  

IT小新 写道

您好啊博主，就是为什么是这样的五张表呢，名字就是这样叫的，在哪里可以找到相关根据啊

在源码里边，可以通过工具搜索出来

1 楼 IT小新 2017-10-27  

您好啊博主，就是为什么是这样的五张表呢，名字就是这样叫的，在哪里可以找到相关根据啊