Skip to content

Instantly share code, notes, and snippets.

@leolin310148
Last active August 25, 2020 02:29
Show Gist options
  • Save leolin310148/3b2cb7d83ba0ec9e1d58 to your computer and use it in GitHub Desktop.
Save leolin310148/3b2cb7d83ba0ec9e1d58 to your computer and use it in GitHub Desktop.
Spring Security OAuth2 MySQL Schema
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
);
@unamanic
Copy link

I had to change autoapprove to a VARCHAR. Spring kept trying to insert an empty string.

@devium
Copy link

devium commented Mar 28, 2017

@unamanic
Same here. Error was:

{
    "timestamp": 1490703305151,
    "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: Failed to instantiate [org.springframework.security.oauth2.provider.ClientDetailsService]: Factory method 'clientDetailsService' threw exception; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [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 (?,?,?,?,?,?,?,?,?,?,?)]; SQL state [HY000]; error code [1366]; Incorrect integer value: '' for column 'autoapprove' at row 1; nested exception is java.sql.SQLException: Incorrect integer value: '' for column 'autoapprove' at row 1",
    "path": "/oauth/token"
}

Changing autoapprove to VARCHAR(255) solved it.

@yinyyang
Copy link

hello, where is oath2 sql schema ? I can't find in org/springframework/security/oauth/boot/spring-security-oauth2-autoconfigure/2.0.0.RELEASE/spring-security-oauth2-autoconfigure-2.0.0.RELEASE.jar. which jar can i find it?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment