Spring Authorization Server（AS）如何从Mysql数据库中提取客户端和用户信息？

本文共计1334个文字，预计阅读时间需要6分钟。

使用Spring Boot 2.7.0和JDK 17，结合Spring Authorization Server 0.3.0和MySQL 8.x，在Spring Authorization Server授权中心中实现了基础演示功能。内容包括：在MySQL数据库中执行相关操作。

Spring AS 持久化

jdk version: 17 spring boot version: 2.7.0 spring authorization server:0.3.0 mysql version: 8.x

在 [[spring authorization server 实现授权中心]] 中实现了基础的演示功能。本文包含的内容有：

1. 在 mysql 中保存客户端信息
2. 在 mysql 中保存用户信息

创建数据表

查看 [[spring authorization server 实现授权中心#AuthorizationServerConfig]] 可以看到以下配置，这里定义了一个嵌入数据 Bean，包含 3 条数据库脚本。分别用于创建

• oauth2_registered_client
• oauth2_authorization_consent
• oauth2_authorization

@Bean public EmbeddedDatabase embeddedDatabase() { return new EmbeddedDatabaseBuilder() .generateUniqueName(true) .setType(EmbeddedDatabaseType.H2) .setScriptEncoding("UTF-8") .addScript("org/springframework/security/oauth2/server/authorization/oauth2-authorization-schema.sql") .addScript("org/springframework/security/oauth2/server/authorization/oauth2-authorization-consent-schema.sql") .addScript("org/springframework/security/oauth2/server/authorization/client/oauth2-registered-client-schema.sql") .build(); } oauth2_registered_client

CREATE TABLE oauth2_registered_client ( id varchar(100) NOT NULL, client_id varchar(100) NOT NULL, client_id_issued_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, client_secret varchar(200) DEFAULT NULL, client_secret_expires_at timestamp DEFAULT NULL, client_name varchar(200) NOT NULL, client_authentication_methods varchar(1000) NOT NULL, authorization_grant_types varchar(1000) NOT NULL, redirect_uris varchar(1000) DEFAULT NULL, scopes varchar(1000) NOT NULL, client_settings varchar(2000) NOT NULL, token_settings varchar(2000) NOT NULL, PRIMARY KEY (id) );

打开 mysql，创建 auth-center 数据库，执行 [[#oauth2_registered_client]] 脚本。

oauth2_authorization

用户认证时需要此表。

/* IMPORTANT: If using PostgreSQL, update ALL columns defined with 'blob' to 'text', as PostgreSQL does not support the 'blob' data type. */ CREATE TABLE oauth2_authorization ( id varchar(100) NOT NULL, registered_client_id varchar(100) NOT NULL, principal_name varchar(200) NOT NULL, authorization_grant_type varchar(100) NOT NULL, attributes blob DEFAULT NULL, state varchar(500) DEFAULT NULL, authorization_code_value blob DEFAULT NULL, authorization_code_issued_at timestamp DEFAULT NULL, authorization_code_expires_at timestamp DEFAULT NULL, authorization_code_metadata blob DEFAULT NULL, access_token_value blob DEFAULT NULL, access_token_issued_at timestamp DEFAULT NULL, access_token_expires_at timestamp DEFAULT NULL, access_token_metadata blob DEFAULT NULL, access_token_type varchar(100) DEFAULT NULL, access_token_scopes varchar(1000) DEFAULT NULL, oidc_id_token_value blob DEFAULT NULL, oidc_id_token_issued_at timestamp DEFAULT NULL, oidc_id_token_expires_at timestamp DEFAULT NULL, oidc_id_token_metadata blob DEFAULT NULL, refresh_token_value blob DEFAULT NULL, refresh_token_issued_at timestamp DEFAULT NULL, refresh_token_expires_at timestamp DEFAULT NULL, refresh_token_metadata blob DEFAULT NULL, PRIMARY KEY (id) ); 配置 application.yml

1. build.gradle 中依赖更改如下所示

   • 添加 mysql 驱动
   • 去掉 H2 相关依赖

   ... dependencies{ implementation 'org.springframework.boot:spring-boot-starter-web' implementation 'org.springframework.boot:spring-boot-starter-security' implementation 'org.springframework.boot:spring-boot-starter-data-jdbc' implementation 'org.springframework.security:spring-security-oauth2-authorization-server:0.3.0' implementation 'org.springframework.boot:spring-boot-starter-actuator' compileOnly 'org.projectlombok:lombok' developmentOnly 'org.springframework.boot:spring-boot-devtools' runtimeOnly 'mysql:mysql-connector-java' annotationProcessor 'org.springframework.boot:spring-boot-configuration-processor' annotationProcessor 'org.projectlombok:lombok' testImplementation 'org.springframework.boot:spring-boot-starter-test' testImplementation 'org.springframework.security:spring-security-test' } ...

2. 更改 application.yml 如下

   

server: port: 9000 logging: level: root: INFO org.springframework.web: INFO org.springframework.security: INFO org.springframework.security.oauth2: INFO spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/auth-center?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 123456 port: 9000 logging: level: root: INFO org.springframework.web: INFO org.springframework.security: INFO org.springframework.security.oauth2: INFO spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/auth-center?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 123456 client: registers: - client-id: mobile-gateway-client client-secret: "123456" authentication-method: client_secret_basic grant-types: - authorization_code - refresh_token - client_credentials scopes: - openid - message.read - message.write redirect-uris: - 127.0.0.1:9100/login/oauth2/code/mobile-gateway-client-oidc - 127.0.0.1:9100/authorized 读取配置 ConfigurationProperties

... @ConfigurationProperties(prefix = "client") @ConstructorBinding public record RegisterClientConfig(List<Register> registers) { public record Register(String clientId, String clientSecret, String authenticationMethod, List<String> grantTypes, List<String> scopes, List<String> redirectUris) { } } 添加 Member 对象

@Getter @Setter @ToString @AllArgsConstructor @RequiredArgsConstructor public class Member implements UserDetails { private Long id; private String loginAccount; private String password; @Transient private List<GrantedAuthority> authorities; @Override public Collection<? extends GrantedAuthority> getAuthorities() { return AuthorityUtils.createAuthorityList("read", "write"); } @Override public String getPassword() { return password; } @Override public String getUsername() { return loginAccount; } @Override public boolean isAccountNonExpired() { return true; } @Override public boolean isAccountNonLocked() { return true; } @Override public boolean isCredentialsNonExpired() { return true; } @Override public boolean isEnabled() { return true; } } 添加 MbrRepository

@Repository public interface MbrRepository extends CrudRepository<Member, Long> { Optional<Member> findByLoginAccount(String loginAccount); } MbrService

public interface MbrService extends UserDetailsService { } UserDetailsServiceImp

@Service @RequiredArgsConstructor public class UserDetailsServiceImp implements MbrService { private final MbrRepository mbrRepository; @Override public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException { return mbrRepository.findByLoginAccount(username).orElseThrow(() -> new UsernameNotFoundException("用户不存在")); } } AuthorizationServerConfig

... @Configuration(proxyBeanMethods = false) public class AuthorizationServerConfig { @Bean @Order(Ordered.HIGHEST_PRECEDENCE) public SecurityFilterChain authorizationServerSecurityFilterChain(HttpSecurity localhost:9000").build(); } } @EnableWebSecurity @Configuration(proxyBeanMethods = false) @RequiredArgsConstructor public class AuthorizationServerConfig { private final JdbcTemplate jdbcTemplate; private final RegisterClientConfig clientConfig; private final MbrService mbrService; @Bean @Order(1) public SecurityFilterChain authorizationServerSecurityFilterChain(HttpSecurity localhost:9000").build(); } @Bean public JwtDecoder jwtDecoder(JWKSource<SecurityContext> jwkSource) { return OAuth2AuthorizationServerConfiguration.jwtDecoder(jwkSource); } static class RowMapper extends JdbcOAuth2AuthorizationService.OAuth2AuthorizationRowMapper { RowMapper(RegisteredClientRepository registeredClientRepository) { super(registeredClientRepository); getObjectMapper().addMixIn(Member.class, MemberMixin.class); } } @JsonTypeInfo(use = JsonTypeInfo.Id.CLASS) @JsonAutoDetect(fieldVisibility = JsonAutoDetect.Visibility.ANY, getterVisibility = JsonAutoDetect.Visibility.NONE, isGetterVisibility = JsonAutoDetect.Visibility.NONE) @JsonIgnoreProperties(ignoreUnknown = true) @JsonDeserialize(using = MemberDeserializer.class) static class MemberMixin { } } EncoderConfig

@Configuration public class EncoderConfig { @Bean @ConditionalOnMissingBean(PasswordEncoder.class) public PasswordEncoder passwordEncoder() { return new BCryptPasswordEncoder(); } } MemberDeserializer

public class MemberDeserializer extends JsonDeserializer<Member> { @Override public Member deserialize(JsonParser jsonParser, DeserializationContext deserializationContext) throws IOException { ObjectMapper mapper = (ObjectMapper) jsonParser.getCodec(); JsonNode jsonNode = mapper.readTree(jsonParser); Long id = readJsonNode(jsonNode, "id").asLong(); String loginAccount = readJsonNode(jsonNode, "loginAccount").asText(); String password = readJsonNode(jsonNode, "password").asText(); List<GrantedAuthority> authorities = mapper.readerForListOf(GrantedAuthority.class).readValue(jsonNode.get("authorities")); return new Member(id, loginAccount, password, authorities); } private JsonNode readJsonNode(JsonNode jsonNode, String field) { return jsonNode.has(field) ? jsonNode.get(field) : MissingNode.getInstance(); } } 启动服务

@SpringBootApplication @ConfigurationPropertiesScan public class AuthCenterApplication { public static void main(String[] args) { SpringApplication.run(AuthCenterApplication.class, args); } } 总结

1. 目前 spring authorization server 版本是 0.3.0 ，在我看来仍然有诸多不完善的地方，但官方总不至于又实现一套 keycloak。
2. 0.3.0 版本发布之际，官方文档 也放出来了。