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

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.

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 !

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

Answer 4

@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