Spring Boot OAuth2 provider database tables explained

Question

I am trying to implement an OAuth2 server with JWT and Spring Boot 2. There are some good examples on the internet, like this or this. They are using some database tables (oauth_client_details, oauth_client_token, oauth_code, oauth_approvals, ClientDetails) with a bunch of fields. Some of them are easy to understand, others are not. I couldn't find anywhere an explanation on what tables and fields are required and what they mean:

create table oauth_client_details (      /*Stores client details*/
  client_id VARCHAR(255) PRIMARY KEY,
  resource_ids VARCHAR(255),             /*Q1: is this comma separated list of resources?*/
  client_secret VARCHAR(255),
  scope VARCHAR(255),
  authorized_grant_types VARCHAR(255),
  web_server_redirect_uri VARCHAR(255),
  authorities VARCHAR(255),              /*Q2: what it this for?*/
  access_token_validity INTEGER,         /*Q3: Is this the validity period in seconds?*/
  refresh_token_validity INTEGER,
  additional_information VARCHAR(4096),  /*Q4: Can I omit this field if I don't need any additional information?*/
  autoapprove VARCHAR(255)               /*Q5: What does this mean?*/
);  

create table if not exists oauth_client_token ( /*Q6: What is this table for?*/
  token_id VARCHAR(255),
  token LONGVARBINARY,
  authentication_id VARCHAR(255) PRIMARY KEY,
  user_name VARCHAR(255),
  client_id VARCHAR(255)
);  

create table if not exists oauth_access_token ( /*Q7: Do I need this table if I use JWT?*/
  token_id VARCHAR(255),
  token LONGVARBINARY,
  authentication_id VARCHAR(255) PRIMARY KEY,
  user_name VARCHAR(255),
  client_id VARCHAR(255),
  authentication LONGVARBINARY,
  refresh_token VARCHAR(255)
);  

create table if not exists oauth_refresh_token ( /*Q8: Do I need this table if I use JWT?*/
  token_id VARCHAR(255),
  token LONGVARBINARY,
  authentication LONGVARBINARY
);  

create table if not exists oauth_code (
  code VARCHAR(255), authentication LONGVARBINARY
);  

create table if not exists oauth_approvals ( /*Q9: What it this for?*/
  userId VARCHAR(255),
  clientId VARCHAR(255),
  scope VARCHAR(255),
  status VARCHAR(10),
  expiresAt TIMESTAMP,
  lastModifiedAt TIMESTAMP
);  

create table if not exists ClientDetails ( /*Q10: Yet another client details???*/
  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)
);

I am also looking for some explanations. Is there any official docs for the above schema from Spring? I searched and couldn't find any. Can anybody help? — Kishor .M, Feb 18 '20 at 09:53
Unfortunately I couldn't find any official docs from Spring, I don't believe it exists. — Archie, Feb 19 '20 at 12:48

Afridi · Answer 1 · 2018-06-11T10:40:00.730

6

In case of JWT token, there is no need for oauth_access_token & oauth_refresh_token tables. check implementation of JwtTokenStore for more info.

Which tables are necessary, totally depends on OAuth Grant type you are using. Tables like oauth_code & oauth_approvals will be required if you are using Authorization code grant type.

different between scope and authorities, check OAuth Scope vs Authorities

edited Jun 11 '18 at 10:40

answered Jun 11 '18 at 10:34

Afridi

6,753
2
18
27

score 3 · Answer 2 · answered Dec 24 '19 at 10:48

Here are some answers for the questions which are not answered.

Q4 : Yes, you can omit

Q7 , Q8 : No need of oauth_access_token, oauth_refresh_token tables if use jwt,

when a token is requested from the client, the access token and relevant details are stored in the oauth_access_token table. the details about this token is removed after the validity seconds expired. The oauth_refresh_token table stores details of refresh token. after the access token get expired, you can use this refresh token to achieve a new access token

score 1 · Answer 3 · answered Aug 01 '18 at 11:02

First of all, I, unfortunately, can't answer all of your questions, but I can answer some at least:

Q1: Yes it is a comma-separated list. Q3: Yes that's right.

Several tables are only needed with certain authorization grant types, as Afridi already mentioned in his answer. I found this particular page pretty helpful in understanding how which grant-type works and what tables I might need for it.

Q10: I don't think that you need that table.

score 0 · Answer 4 · answered May 15 '21 at 04:14

Q1: Yes
Q2: N/A
Q3: Yes
Q4: Yes. Note: oauth_client_details supports for JdbcClientDetailsService. You can create your ClientDetailsService implementation with your customized table.
Q5: Client scopes which are automatically approved by authorization server, supports for Authorization Code flow
Q6: This table is used by JdbcClientTokenServices
Q7: Yes, this table supports for JwtTokenStore
Q8: Yes, this table supports for JwtTokenStore
Q9: Store approval status, supports for Authorization Code flow
Q10: Just a customized oauth_client_details if you want

Spring Boot OAuth2 provider database tables explained

4 Answers4