CODEWITHSUNDEEP
springspring-bootspring-securityoauth-2.0spring-security-oauth2
Archie
Archie

Reputation: 982

Spring Boot OAuth2 provider database tables explained

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

Upvotes: 9

Views: 13599

Answers (4)

yuen26
yuen26

Reputation: 1038

  • 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

Upvotes: 0

lakru-one
lakru-one

Reputation: 141

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

Upvotes: 3

TsTiX
TsTiX

Reputation: 385

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.

Upvotes: 1

Afridi
Afridi

Reputation: 6922

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

Upvotes: 6

Related Questions