篇二：springboot2.7 OAuth2 server使用jdbc存储RegisteredClient

上一篇 <<springboot 2.7 oauth server配置源码走读一>>中简单描述了oauth2 server的配置，其中使用了内存保存 RegisteredClient，本篇改用mysql存储。

db存储需要创建表，表结构应该是什么样的呢，从spring给我们封装好的源码入手，
org.springframework.security.oauth2.server.authorization.client.JdbcRegisteredClientRepository类中：

那么字段类型呢？我们看org.springframework.security.oauth2.server.authorization.client.RegisteredClient类：

解释下为什么时间用timestamp存储以及Set数据模型用字符串存储：

解释下为什么ClientSettings和TokenSettings用json存储（当然varchar也行）：就是一个map.


至此咱们确定了表结构，如下是一个示例：

CREATE TABLE `oauth2_registered_client` (`id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL,`client_id` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,`client_id_issued_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`client_secret` varchar(100) COLLATE utf8mb4_general_ci DEFAULT '',`client_secret_expires_at` timestamp NULL DEFAULT NULL,`client_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,`client_authentication_methods` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,`authorization_grant_types` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,`redirect_uris` varchar(100) COLLATE utf8mb4_general_ci DEFAULT '',`scopes` varchar(200) COLLATE utf8mb4_general_ci NOT NULL,`client_settings` json NOT NULL,`token_settings` json NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

创建好表后，咱们处理代码：

1.在pom中引入依赖：

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.26</version>
</dependency>

2.yaml/properties配置文件中添加数据库信息，示例如下：

spring:datasource:url: jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=falseusername: <username>password: <password>

3.使用JdbcRegisteredClientRepository，它和InMemoryRegisteredClientRepository只能二选一，所以需要注释掉后者。在咱们自己的配置类OAuth2AuthorizeSecurityConfig中：

@Beanpublic RegisteredClientRepository registeredClientRepository(JdbcTemplate jdbcTemplate) {return new JdbcRegisteredClientRepository(jdbcTemplate);}

4.初始化数据到db表中：写一个测试类方法插入数据：

package com.jel.tech.auth;import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.security.oauth2.core.AuthorizationGrantType;
import org.springframework.security.oauth2.core.ClientAuthenticationMethod;
import org.springframework.security.oauth2.core.oidc.OidcScopes;
import org.springframework.security.oauth2.server.authorization.client.RegisteredClient;
import org.springframework.security.oauth2.server.authorization.client.RegisteredClientRepository;
import org.springframework.security.oauth2.server.authorization.config.ClientSettings;
import org.springframework.security.oauth2.server.authorization.config.TokenSettings;import javax.annotation.Resource;
import java.time.Duration;
import java.util.UUID;@SpringBootTest
class AuthApplicationTests {@Resourceprivate RegisteredClientRepository registeredClientRepository;@Testvoid saveRegisteredClients() {RegisteredClient loginClient = RegisteredClient.withId(UUID.randomUUID().toString()).clientId("login-client").clientSecret("{noop}openid-connect").clientAuthenticationMethod(ClientAuthenticationMethod.CLIENT_SECRET_BASIC).authorizationGrantType(AuthorizationGrantType.AUTHORIZATION_CODE).authorizationGrantType(AuthorizationGrantType.REFRESH_TOKEN).redirectUri("http://127.0.0.1:8080/login/oauth2/code/login-client").redirectUri("http://127.0.0.1:8080/authorized").scope(OidcScopes.OPENID).scope(OidcScopes.PROFILE).clientSettings(ClientSettings.builder().requireAuthorizationConsent(true).build()).build();RegisteredClient registeredClient = RegisteredClient.withId(UUID.randomUUID().toString()).clientId("messaging-client").clientSecret("{noop}secret").clientAuthenticationMethod(ClientAuthenticationMethod.CLIENT_SECRET_BASIC).authorizationGrantType(AuthorizationGrantType.CLIENT_CREDENTIALS).scope("message:read").scope("message:write")// 指定token有效期：token:30分（默认5分钟）,refresh_token:1天.tokenSettings(TokenSettings.builder().accessTokenTimeToLive(Duration.ofMinutes(30)).refreshTokenTimeToLive(Duration.ofDays(1)).build()).build();// 注意：没有设置clientName,则会把id值作为clientNameregisteredClientRepository.save(loginClient);registeredClientRepository.save(registeredClient);}
}

5.验证功能，在此我借花献佛，把官网提供的示例复制过来：

package com.jel.tech.auth;import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.http.HttpHeaders;
import org.springframework.mock.web.MockHttpServletRequest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.MvcResult;
import org.springframework.test.web.servlet.request.RequestPostProcessor;
import org.springframework.test.web.servlet.result.MockMvcResultHandlers;import java.util.Map;import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.post;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.jsonPath;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;/*** Integration tests for {@link AuthApplication}.** @author Steve Riesenberg*/
@SpringBootTest
@AutoConfigureMockMvc
@ActiveProfiles("test")
public class OAuth2AuthorizationServerApplicationITests {private static final String CLIENT_ID = "messaging-client";private static final String CLIENT_SECRET = "secret";private final ObjectMapper objectMapper = new ObjectMapper();@Autowiredprivate MockMvc mockMvc;@Testvoid performTokenRequestWhenValidClientCredentialsThenOk() throws Exception {// @formatter:offthis.mockMvc.perform(post("/oauth2/token").param("grant_type", "client_credentials").param("scope", "message:read").with(basicAuth(CLIENT_ID, CLIENT_SECRET))).andExpect(status().isOk()).andExpect(jsonPath("$.access_token").isString()).andExpect(jsonPath("$.expires_in").isNumber()).andExpect(jsonPath("$.scope").value("message:read")).andExpect(jsonPath("$.token_type").value("Bearer"));// @formatter:on}@Testvoid performTokenRequestWhenMissingScopeThenOk() throws Exception {// @formatter:offthis.mockMvc.perform(post("/oauth2/token").param("grant_type", "client_credentials").with(basicAuth(CLIENT_ID, CLIENT_SECRET))).andExpect(status().isOk()).andExpect(jsonPath("$.access_token").isString()).andExpect(jsonPath("$.expires_in").isNumber()).andExpect(jsonPath("$.scope").value("message:read message:write")).andExpect(jsonPath("$.token_type").value("Bearer"));// @formatter:on}@Testvoid performTokenRequestWhenInvalidClientCredentialsThenUnauthorized() throws Exception {// @formatter:offthis.mockMvc.perform(post("/oauth2/token").param("grant_type", "client_credentials").param("scope", "message:read").with(basicAuth("bad", "password"))).andExpect(status().isUnauthorized()).andExpect(jsonPath("$.error").value("invalid_client"));// @formatter:on}@Testvoid performTokenRequestWhenMissingGrantTypeThenUnauthorized() throws Exception {// @formatter:offthis.mockMvc.perform(post("/oauth2/token").with(basicAuth("bad", "password"))).andExpect(status().isUnauthorized()).andExpect(jsonPath("$.error").value("invalid_client"));// @formatter:on}@Testvoid performTokenRequestWhenGrantTypeNotRegisteredThenBadRequest() throws Exception {// @formatter:offthis.mockMvc.perform(post("/oauth2/token").param("grant_type", "client_credentials").with(basicAuth("login-client", "openid-connect"))).andExpect(status().isBadRequest()).andExpect(jsonPath("$.error").value("unauthorized_client"));// @formatter:on}@Testvoid performIntrospectionRequestWhenValidTokenThenOk() throws Exception {// @formatter:offthis.mockMvc.perform(post("/oauth2/introspect").param("token", getAccessToken()).with(basicAuth(CLIENT_ID, CLIENT_SECRET))).andExpect(status().isOk()).andExpect(jsonPath("$.active").value("true")).andExpect(jsonPath("$.aud[0]").value(CLIENT_ID)).andExpect(jsonPath("$.client_id").value(CLIENT_ID)).andExpect(jsonPath("$.exp").isNumber()).andExpect(jsonPath("$.iat").isNumber()).andExpect(jsonPath("$.iss").value("http://127.0.0.1:9000")).andExpect(jsonPath("$.nbf").isNumber()).andExpect(jsonPath("$.scope").value("message:read")).andExpect(jsonPath("$.sub").value(CLIENT_ID)).andExpect(jsonPath("$.token_type").value("Bearer")).andDo(MockMvcResultHandlers.print());// @formatter:on}@Testvoid performIntrospectionRequestWhenInvalidCredentialsThenUnauthorized() throws Exception {// @formatter:offthis.mockMvc.perform(post("/oauth2/introspect").param("token", getAccessToken()).with(basicAuth("bad", "password"))).andExpect(status().isUnauthorized()).andExpect(jsonPath("$.error").value("invalid_client"));// @formatter:on}private String getAccessToken() throws Exception {// @formatter:offMvcResult mvcResult = this.mockMvc.perform(post("/oauth2/token").param("grant_type", "client_credentials").param("scope", "message:read").with(basicAuth(CLIENT_ID, CLIENT_SECRET))).andExpect(status().isOk()).andExpect(jsonPath("$.access_token").exists()).andReturn();// @formatter:onString tokenResponseJson = mvcResult.getResponse().getContentAsString();Map<String, Object> tokenResponse = this.objectMapper.readValue(tokenResponseJson, new TypeReference<Map<String, Object>>() {});String access_token = tokenResponse.get("access_token").toString();System.out.println(access_token);return access_token;}private static BasicAuthenticationRequestPostProcessor basicAuth(String username, String password) {return new BasicAuthenticationRequestPostProcessor(username, password);}private static final class BasicAuthenticationRequestPostProcessor implements RequestPostProcessor {private final String username;private final String password;private BasicAuthenticationRequestPostProcessor(String username, String password) {this.username = username;this.password = password;}@Overridepublic MockHttpServletRequest postProcessRequest(MockHttpServletRequest request) {HttpHeaders headers = new HttpHeaders();headers.setBasicAuth(this.username, this.password);request.addHeader("Authorization", headers.getFirst("Authorization"));return request;}}
}