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

Question

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):

-- 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?

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() ?

Suggs

Answer 1

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 ClientDetailsServiceConfigurer is 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_details table 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:

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 clientapp is now persisted into the database.

Macrocosm

Thanks for the answer and it works. However, on every start up of the application, it tries to insert an entry to the oauth_client_details table which causes duplicated entry error. Is there any way to only build once? – Girl

@YaoLiu Yes, you have to use the workaround suggested here by the user kakawait. Note: The original webpage isn't available anymore but I've found it in the google cache – Macrocosm

Check out this answer to dynamically replace a pre-existing client without causing errors: https://mcmap.net/q/670009/-spring-oauth2-jdbc-client-configuration-add-same-client-multiple-times – Herzel

this is a super old post, but I am trying to fix an app that is using the in memory configuration, but it was causing issues because the app is running in containers on Heroku, when I change just the ClientDetails service to save into the database, it is still not working, do I have change something else as well? – Primatology

Answer 2

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();

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 !

Appropriate

I was following your answer , and configure the server as you mentioned i the idea. But I'm getting following error. Do you have any idea?{"timestamp":1458109110864,"status":401,"error":"Unauthorized","message":"Error creating bean with name 'scopedTarget.clientDetailsService' defined in class path resource [org/springframework/security/oauth2/config/annotation/configuration/ClientDetailsServiceConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: – Chandra

Failed to instantiate [org.springframework.security.oauth2.provider.ClientDetailsService]: Factory method 'clientDetailsService' threw exception; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into oauth_client_details (client_secret, resource_ids, scope, authorized_grant_types, web_server_redirect_uri, authorities, access_token_validity, refresh_token_validity, additional_information, autoapprove, client_id) values (?,?,?,?,?,?,?,?,?,?,?)]; – Chandra

nested exception is org.h2.jdbc.JdbcSQLException: Table \"OAUTH_CLIENT_DETAILS\" not found; SQL statement:\ninsert into oauth_client_details (client_secret, resource_ids, scope, authorized_grant_types, web_server_redirect_uri, authorities, access_token_validity, refresh_token_validity, additional_information, autoapprove, client_id) values (?,?,?,?,?,?,?,?,?,?,?) [42102-190]","path":"/oauth/token"} – Chandra

You have to create your own clientDetailsServiceImpl at first step. – Appropriate

Do you have an sample implementation that i can look at? – Chandra

no but if u post your code I may could help u. try to upload your code in github I may give u a pull request – Appropriate

I upload the project to here, github.com/warunacds/spring-auth-test.git also db schema is available. thanks – Chandra

ClientDetails seems to be the clone of oauth_client_details table with some columns name with different label. When writing clientDetailsServiceImpl, should I use ClientDetails table or oauth_client_details ? – Neckpiece

Answer 3

@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.

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:

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):

@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 MUST have a method to find user by username.:

@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:

@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");
}
}

Lyrate

you are storing clients both in database and in memory. I think you should remove inMemory() method. – Abbe

Answer 4

Use the following table schema for Postgres when following @AndroidLover answer.

create table IF NOT EXISTS   oauth_client_details
(
    client_id               VARCHAR(256) PRIMARY KEY,
    resource_ids            VARCHAR(256),
    client_secret           VARCHAR(256),
    scope                   VARCHAR(256),
    authorized_grant_types  VARCHAR(256),
    web_server_redirect_uri VARCHAR(256),
    authorities             VARCHAR(256),
    access_token_validity   INTEGER,
    refresh_token_validity  INTEGER,
    additional_information  VARCHAR(4096),
    autoapprove             VARCHAR(256)
);

create table IF NOT EXISTS  oauth_client_token
(
    token_id          VARCHAR(256),
    token             bytea,
    authentication_id VARCHAR(256) PRIMARY KEY,
    user_name         VARCHAR(256),
    client_id         VARCHAR(256)
);

create table IF NOT EXISTS  oauth_access_token
(
    token_id          VARCHAR(256),
    token             bytea,
    authentication_id VARCHAR(256) PRIMARY KEY,
    user_name         VARCHAR(256),
    client_id         VARCHAR(256),
    authentication    bytea,
    refresh_token     VARCHAR(256)
);

create table IF NOT EXISTS  oauth_refresh_token
(
    token_id       VARCHAR(256),
    token          bytea,
    authentication bytea
);

create table IF NOT EXISTS  oauth_code
(
    code           VARCHAR(256),
    authentication bytea
);

create table IF NOT EXISTS  oauth_approvals
(
    userId         VARCHAR(256),
    clientId       VARCHAR(256),
    scope          VARCHAR(256),
    status         VARCHAR(10),
    expiresAt      TIMESTAMP,
    lastModifiedAt TIMESTAMP
);


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

The following is my OAuth2 configuration loading and validating tokens from the database

@Configuration
@EnableAuthorizationServer
class OAuth2Config extends AuthorizationServerConfigurerAdapter {

    @Autowired
    private BCryptPasswordEncoder passwordEncoder;

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

    @Bean
    public BCryptPasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

    @Bean
    public TokenStore tokenStore() {
        return new JdbcTokenStore(dataSource);
    }

    @Override
    public void configure(AuthorizationServerSecurityConfigurer security) throws Exception {
        security
                .tokenKeyAccess("permitAll()")
                .checkTokenAccess("isAuthenticated()")
                .allowFormAuthenticationForClients();
    }

    @Override
    public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
        clients.jdbc(dataSource);
    }

    @Override
    public void configure(AuthorizationServerEndpointsConfigurer authorizationServerEndpointsConfigurer) throws Exception {
        authorizationServerEndpointsConfigurer.tokenStore(tokenStore());
    }
}

Obsequent

Answer 5

@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:

 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

Pomiferous

Would you please provide some explanation as to why and how this works? Especially for a spaghetti code like this... – Wigwag

Please edit your post instead of committing. This is only for the sake of the community and greatly increases readability. – Wigwag

Answer 6

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.

Diverticulitis