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

---

Answer 1

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

Answer 2

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