java 如何在 Spring 中使用 JDBC 为 ClientDetailsS​​erviceConfigurer 添加客户端？

How to add a client using JDBC for ClientDetailsServiceConfigurer in Spring?

提问by Wim Deblauwe

I have the in memory thing working as follows:

我的内存工作如下：

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

        clients.inMemory()
               .withClient("clientapp")
               .authorizedGrantTypes("password", "refresh_token")
               .authorities("USER")
               .scopes("read", "write")
               .resourceIds(RESOURCE_ID)
               .secret("123456");
}

I would like to use the JDBC implementation. For this, I have created the following tables (using MySQL):

我想使用 JDBC 实现。为此，我创建了以下表格（使用 MySQL）：

-- Tables for OAuth token store

CREATE TABLE oauth_client_details (
  client_id               VARCHAR(255) PRIMARY KEY,
  resource_ids            VARCHAR(255),
  client_secret           VARCHAR(255),
  scope                   VARCHAR(255),
  authorized_grant_types  VARCHAR(255),
  web_server_redirect_uri VARCHAR(255),
  authorities             VARCHAR(255),
  access_token_validity   INTEGER,
  refresh_token_validity  INTEGER,
  additional_information  VARCHAR(4096),
  autoapprove             TINYINT
);

CREATE TABLE oauth_client_token (
  token_id          VARCHAR(255),
  token             BLOB,
  authentication_id VARCHAR(255),
  user_name         VARCHAR(255),
  client_id         VARCHAR(255)
);

CREATE TABLE oauth_access_token (
  token_id          VARCHAR(255),
  token             BLOB,
  authentication_id VARCHAR(255),
  user_name         VARCHAR(255),
  client_id         VARCHAR(255),
  authentication    BLOB,
  refresh_token     VARCHAR(255)
);

CREATE TABLE oauth_refresh_token (
  token_id       VARCHAR(255),
  token          BLOB,
  authentication BLOB
);

CREATE TABLE oauth_code (
  code           VARCHAR(255),
  authentication BLOB
);

Do I need to manually add a client in the MySQL tables?

我需要在 MySQL 表中手动添加客户端吗？

I tried this:

我试过这个：

clients.jdbc(dataSource).withClient("clientapp")
               .authorizedGrantTypes("password", "refresh_token")
               .authorities("USER")
               .scopes("read", "write")
               .resourceIds(RESOURCE_ID)
               .secret("123456");

Hoping that Spring would insert the correct things in the good tables, but it does not seem to do that. Why is it that you can further chain after jdbc()?

希望 Spring 会在好的表中插入正确的东西，但它似乎并没有这样做。为什么你可以进一步链接jdbc()？

采纳答案by user35934

This question is fairly old but none of the replies gave an answer to the questioner's original problem. I've stumbled over the same issue while getting myself familar with spring's oauth2 implementation and wondered why the ClientDetailsServiceConfigureris not persisting the clients that were programmatically added via the JdbcClientDetailsServiceBuilder(which is instantiated by calling the jdbc(datasource)method on the configurer), despite that all tutorials on the net showed a similar example such as that posted by Wim. After digging deeper into the code i've noticed the reason. Well, it's simply because the code to update the oauth_clients_detailstable is never called. What's missing is the following call after configuring all clients: .and().build(). So, Wim's code must actually look as follows:

这个问题已经很老了，但没有一个回复回答了提问者的原始问题。我在熟悉 spring 的 oauth2 实现时偶然发现了同样的问题，并想知道为什么ClientDetailsServiceConfigurer不保留通过编程添加的客户端JdbcClientDetailsServiceBuilder（通过调用jdbc(datasource)配置器上的方法实例化），尽管所有教程都在net 展示了一个类似的例子，例如 Wim 发布的例子。在深入研究代码后，我注意到了原因。嗯，这只是因为oauth_clients_details从未调用更新表的代码。缺少的是配置所有客户端后的以下调用：.and().build(). 因此，Wim 的代码实际上必须如下所示：

clients.jdbc(dataSource).withClient("clientapp")
           .authorizedGrantTypes("password", "refresh_token")
           .authorities("USER")
           .scopes("read", "write")
           .resourceIds(RESOURCE_ID)
           .secret("123456").and().build();

Et voila, the client clientappis now persisted into the database.

瞧，客户端clientapp现在被持久化到数据库中。

回答by AndroidLover

Please fallow this steps:

请休耕此步骤：

1. put this schema.sql inside your resource folder to be detected by SpringBoot once you start your server. If you don't use spring boot no worries just import this script from any Mysql App Client (phpmyadmin,HeidiSQL,Navicat..)

   drop table if exists oauth_client_details; create table oauth_client_details ( client_id VARCHAR(255) PRIMARY KEY, resource_ids VARCHAR(255), client_secret VARCHAR(255), scope VARCHAR(255), authorized_grant_types VARCHAR(255), web_server_redirect_uri VARCHAR(255), authorities VARCHAR(255), access_token_validity INTEGER, refresh_token_validity INTEGER, additional_information VARCHAR(4096), autoapprove VARCHAR(255) ); drop table if exists oauth_client_token; create table oauth_client_token ( token_id VARCHAR(255), token LONG VARBINARY, authentication_id VARCHAR(255) PRIMARY KEY, user_name VARCHAR(255), client_id VARCHAR(255) ); drop table if exists oauth_access_token; create table oauth_access_token ( token_id VARCHAR(255), token LONG VARBINARY, authentication_id VARCHAR(255) PRIMARY KEY, user_name VARCHAR(255), client_id VARCHAR(255), authentication LONG VARBINARY, refresh_token VARCHAR(255) ); drop table if exists oauth_refresh_token; create table oauth_refresh_token ( token_id VARCHAR(255), token LONG VARBINARY, authentication LONG VARBINARY ); drop table if exists oauth_code; create table oauth_code ( code VARCHAR(255), authentication LONG VARBINARY ); drop table if exists oauth_approvals; create table oauth_approvals ( userId VARCHAR(255), clientId VARCHAR(255), scope VARCHAR(255), status VARCHAR(10), expiresAt TIMESTAMP, lastModifiedAt TIMESTAMP ); drop table if exists ClientDetails; create table ClientDetails ( appId VARCHAR(255) PRIMARY KEY, resourceIds VARCHAR(255), appSecret VARCHAR(255), scope VARCHAR(255), grantTypes VARCHAR(255), redirectUrl VARCHAR(255), authorities VARCHAR(255), access_token_validity INTEGER, refresh_token_validity INTEGER, additionalInformation VARCHAR(4096), autoApproveScopes VARCHAR(255) );

2. Inject your DataSource, authenticationManager,UserDetailsService inside your OthorizationServer

   @Autowired private MyUserDetailsService userDetailsService; @Inject private AuthenticationManager authenticationManager; @Autowired private DataSource dataSource;

3. You will need to create this two beans

   @Bean public JdbcTokenStore tokenStore() { return new JdbcTokenStore(dataSource); } @Bean protected AuthorizationCodeServices authorizationCodeServices() { return new JdbcAuthorizationCodeServices(dataSource); }

   and please don't forget about the @Configuration on top of your AuthorizationServer class

4. Configure your clients apps to be created in your mysql database: clients.jdbc(dataSource).withClient("clientapp") .authorizedGrantTypes("password", "refresh_token") .authorities("USER") .scopes("read", "write") .resourceIds(RESOURCE_ID) .secret("123456");

   you've already done this.

5. the most important thing ( and I think that you forgot about it ..) is: to configure your endpoints with the AuthorizationServerEndpointsConfigurer:

   endpoints.userDetailsService(userDetailsService) .authorizationCodeServices(authorizationCodeServices()).authenticationManager(this.authenticationManager).tokenStore(tokenStore()).approvalStoreDisabled();

1. 把这个 schema.sql 放在你的资源文件夹中，一旦你启动你的服务器，SpringBoot 就会检测到它。如果您不使用 spring boot 不用担心，只需从任何 Mysql 应用程序客户端（phpmyadmin、HeidiSQL、Navicat ..）导入此脚本

   drop table if exists oauth_client_details; create table oauth_client_details ( client_id VARCHAR(255) PRIMARY KEY, resource_ids VARCHAR(255), client_secret VARCHAR(255), scope VARCHAR(255), authorized_grant_types VARCHAR(255), web_server_redirect_uri VARCHAR(255), authorities VARCHAR(255), access_token_validity INTEGER, refresh_token_validity INTEGER, additional_information VARCHAR(4096), autoapprove VARCHAR(255) ); drop table if exists oauth_client_token; create table oauth_client_token ( token_id VARCHAR(255), token LONG VARBINARY, authentication_id VARCHAR(255) PRIMARY KEY, user_name VARCHAR(255), client_id VARCHAR(255) ); drop table if exists oauth_access_token; create table oauth_access_token ( token_id VARCHAR(255), token LONG VARBINARY, authentication_id VARCHAR(255) PRIMARY KEY, user_name VARCHAR(255), client_id VARCHAR(255), authentication LONG VARBINARY, refresh_token VARCHAR(255) ); drop table if exists oauth_refresh_token; create table oauth_refresh_token ( token_id VARCHAR(255), token LONG VARBINARY, authentication LONG VARBINARY ); drop table if exists oauth_code; create table oauth_code ( code VARCHAR(255), authentication LONG VARBINARY ); drop table if exists oauth_approvals; create table oauth_approvals ( userId VARCHAR(255), clientId VARCHAR(255), scope VARCHAR(255), status VARCHAR(10), expiresAt TIMESTAMP, lastModifiedAt TIMESTAMP ); drop table if exists ClientDetails; create table ClientDetails ( appId VARCHAR(255) PRIMARY KEY, resourceIds VARCHAR(255), appSecret VARCHAR(255), scope VARCHAR(255), grantTypes VARCHAR(255), redirectUrl VARCHAR(255), authorities VARCHAR(255), access_token_validity INTEGER, refresh_token_validity INTEGER, additionalInformation VARCHAR(4096), autoApproveScopes VARCHAR(255) );

2. 在你的 OthorizationServer 中注入你的 DataSource、authenticationManager、UserDetailsS​​ervice

   @Autowired private MyUserDetailsService userDetailsService; @Inject private AuthenticationManager authenticationManager; @Autowired private DataSource dataSource;

3. 您将需要创建这两个 bean

   @Bean public JdbcTokenStore tokenStore() { return new JdbcTokenStore(dataSource); } @Bean protected AuthorizationCodeServices authorizationCodeServices() { return new JdbcAuthorizationCodeServices(dataSource); }

   并且请不要忘记 AuthorizationServer 类顶部的 @Configuration

4. 配置要在 mysql 数据库中创建的客户端应用程序： clients.jdbc(dataSource).withClient("clientapp") .authorizedGrantTypes("password", "refresh_token") .authorities("USER") .scopes("read", "write") .resourceIds(RESOURCE_ID) .secret("123456");

   你已经这样做了。

5. 最重要的事情（我认为您忘记了它..）是：使用 AuthorizationServerEndpointsConfigurer 配置您的端点：

   endpoints.userDetailsService(userDetailsService) .authorizationCodeServices(authorizationCodeServices()).authenticationManager(this.authenticationManager).tokenStore(tokenStore()).approvalStoreDisabled();

and that's it man , now it should work ;)

就是这样，伙计，现在应该可以了；)

And feel free to ask for more... I'll be happy to help

并随时要求更多......我很乐意提供帮助

I have sent you a message from tweeter !

我已经从推特上给你发了一条消息！

回答by haozhechen

@AndroidLover 's answer is good, but it could be simplified. You don't need to create tables like oauth_access_token, oauth_refresh_token, etc. unless you need a jdbc token store.

@AndroidLover 的回答很好，但可以简化。您不需要创建像 oauth_access_token、oauth_refresh_token 等表，除非您需要 jdbc 令牌存储。

Since you only need a jdbc client detail service, all you need to do is:
1. create a client detail table oauth_client_details, for example:

由于您只需要一个 jdbc 客户端详细信息服务，因此您需要做的就是：
1. 创建一个客户端详细信息表oauth_client_details，例如：

drop table if exists oauth_client_details;
    create table oauth_client_details (
    client_id VARCHAR(255) PRIMARY KEY,
    resource_ids VARCHAR(255),
    client_secret VARCHAR(255),
    scope VARCHAR(255),
    authorized_grant_types VARCHAR(255),
    web_server_redirect_uri VARCHAR(255),
    authorities VARCHAR(255),
    access_token_validity INTEGER,
    refresh_token_validity INTEGER,
    additional_information VARCHAR(4096),
    autoapprove VARCHAR(255)
    );

2. create a user model that implements the UserDetail interface, for example(I'm using spring jpa in this case, you could use mybatis, jdbc, whatever):

2.创建一个实现UserDetail接口的用户模型，例如（我在这个例子中使用的是spring jpa，你可以使用mybatis、jdbc等等）：

@Entity
@Table(name = "users")
public class User implements UserDetails {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "user_id", nullable = false, updatable = false)
private String id;

@Column(name = "username", nullable = false, unique = true)
private String username;

@Column(name = "password", nullable = false)
private String password;

@Column(name = "enabled", nullable = false)
@Type(type = "org.hibernate.type.NumericBooleanType")
private boolean enabled;

public String getId() {
    return id;
}

public void setId(String id) {
    this.id = id;
}

public void setUsername(String username) {
    this.username = username;
}

public void setPassword(String password) {
    this.password = password;
}

public void setEnabled(boolean enabled) {
    this.enabled = enabled;
}

@Override
public Collection<? extends GrantedAuthority> getAuthorities() {
    List<GrantedAuthority> authorities = new ArrayList<GrantedAuthority>();
    authorities.add((GrantedAuthority) () -> "ROLE_USER");
    return authorities;
}

@Override
public String getPassword() {
    return this.password;
}

@Override
public String getUsername() {
    return this.username;
}

@Override
public boolean isAccountNonExpired() {
    return true;
}

@Override
public boolean isAccountNonLocked() {
    return true;
}

@Override
public boolean isCredentialsNonExpired() {
    return true;
}

@Override
    public boolean isEnabled() {
        return this.enabled;
    }
}

3. create a custom user detail service. notice that in your implementation, you should inject your dao service(in my case, I injected a jpaRepository.) and your dao service MUSThave a method to find user by username.:

3.创建自定义用户详细信息服务。请注意，在您的实现中，您应该注入您的 dao 服务（在我的情况下，我注入了一个 jpaRepository。）并且您的 dao 服务必须具有通过用户名查找用户的方法。：

@Service("userDetailsService")
public class UserService implements UserDetailsService {

@Autowired
UserRepository userRepository;

@Override
public UserDetails loadUserByUsername(String userName) throws 
UsernameNotFoundException {
    return userRepository.findByUsername(userName);
}
}

4. finally, config you authentication server:

4. 最后，配置你的认证服务器：

@Configuration
@EnableAuthorizationServer
public class AuthServerConfig extends AuthorizationServerConfigurerAdapter {

@Autowired
@Qualifier("dataSource")
DataSource dataSource;

@Autowired
@Qualifier("userDetailsService")
private UserDetailsService userDetailsService;


@Autowired
private AuthenticationManager authenticationManager;

@Override
public void configure(AuthorizationServerEndpointsConfigurer configurer) {
    configurer
            .authenticationManager(authenticationManager)                
            .approvalStoreDisabled()
            .userDetailsService(userDetailsService);
}


@Override
public void configure(ClientDetailsServiceConfigurer clients) throws Exception 
{
    clients
            .jdbc(dataSource)
            .inMemory().withClient("my-trusted-
client").secret("secret").accessTokenValiditySeconds(3600)
            .scopes("read", "write").authorizedGrantTypes("password", 
"refresh_token").resourceIds("resource");
}
}

回答by swapnil kadam

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

    JdbcClientDetailsService jdbcClientDetailsService = new JdbcClientDetailsService(dataSource);

    if(!jdbcClientDetailsService.listClientDetails().isEmpty() ) {          
    jdbcClientDetailsService.removeClientDetails(CLIEN_ID);     
    }

    if(jdbcClientDetailsService.listClientDetails().isEmpty() ) {
        configurer.jdbc(dataSource).withClient(CLIEN_ID).secret(encoder.encode(CLIENT_SECRET))
        .authorizedGrantTypes(GRANT_TYPE_PASSWORD, AUTHORIZATION_CODE, REFRESH_TOKEN, IMPLICIT)
        .scopes(SCOPE_READ, SCOPE_WRITE, TRUST).accessTokenValiditySeconds(ACCESS_TOKEN_VALIDITY_SECONDS)
        .refreshTokenValiditySeconds(FREFRESH_TOKEN_VALIDITY_SECONDS).and().build();                
    }       
    configurer.jdbc(dataSource).build().loadClientByClientId(CLIEN_ID); 
}

Here i am checking there is any client exist in the database table oauth_client_details. If there is any client exist I am removing that entry because the first time it will work without any error but when you restart your application then it gives primary key errors while adding entry in database. Thats why I added this code:

在这里，我正在检查数据库表 oauth_client_details 中是否存在任何客户端。如果存在任何客户端，我将删除该条目，因为它第一次可以正常工作而不会出现任何错误，但是当您重新启动应用程序时，它会在向数据库中添加条目时出现主键错误。这就是我添加此代码的原因：

 if(!jdbcClientDetailsService.listClientDetails().isEmpty() ) { 

    jdbcClientDetailsService.removeClientDetails(CLIEN_ID);

    }

After removing the client entry you need to add client here is the code for adding client :

删除客户端条目后，您需要在此处添加客户端是添加客户端的代码：

if(jdbcClientDetailsService.listClientDetails().isEmpty() ) {
        configurer.jdbc(dataSource).withClient(CLIEN_ID).secret(encoder.encode(CLIENT_SECRET))
        .authorizedGrantTypes(GRANT_TYPE_PASSWORD, AUTHORIZATION_CODE, REFRESH_TOKEN, IMPLICIT)
        .scopes(SCOPE_READ, SCOPE_WRITE, TRUST).accessTokenValiditySeconds(ACCESS_TOKEN_VALIDITY_SECONDS)
        .refreshTokenValiditySeconds(FREFRESH_TOKEN_VALIDITY_SECONDS).and().build();

    } 

In this code you can change configuration as you want because we are removing client entry each time after restarting your application.

在此代码中，您可以根据需要更改配置，因为我们每次在重新启动应用程序后都会删除客户端条目。

here we are loading all client details :

在这里，我们正在加载所有客户详细信息：

configurer.jdbc(dataSource).build().loadClientByClientId(CLIEN_ID);

It will works fine for you without any errors. Thanks

它将为您正常工作，没有任何错误。谢谢

回答by user2501323

Adding my two cents.

加上我的两分钱。

If you are initializing db structures on startup (with dropping previous), for example like this:

如果您在启动时初始化数据库结构（删除前一个），例如这样：

@Bean
public DataSourceInitializer dataSourceInitializer(DataSource dataSource) {
    //...setting dataSource and databasePopulator
}
private DatabasePopulator databasePopulator() {
    //...adding your schema script
}
@Bean
public DataSource dataSource() {
    //...setting driverclassname, url, etc
}

@Override
public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
    //...
    clients.jdbc(this.dataSource()).withClient("example").(...).build()
}

beware.

谨防。

Beans does not have to be created in some specific order, so you may catch situation when you insert lines in your old tables, and then replacing it with new, from your schema. So, you may wonder for a while, why is it still not inserting lines. I hope, this would help someone.

Bean 不必按特定顺序创建，因此当您在旧表中插入行，然后用架构中的新表替换它时，您可能会遇到这种情况。所以，你可能会疑惑，为什么它仍然不插入行。我希望，这会帮助某人。