Updated RefreshToken implementations with separate DB tables. Allows
multiple access tokens per refresh token.
Change-Id: I5227191ed38a2f5260e1249d548f893a49108dce
diff --git a/full/src/main/resources/db/insert/V3.5__insert_oauth2_clients.sql b/full/src/main/resources/db/insert/V3.5__insert_oauth2_clients.sql
index 0d518a3..01e5099 100644
--- a/full/src/main/resources/db/insert/V3.5__insert_oauth2_clients.sql
+++ b/full/src/main/resources/db/insert/V3.5__insert_oauth2_clients.sql
@@ -48,6 +48,6 @@
-- "This is a test super public client.");
INSERT INTO oauth2_access_token(token,user_id,created_date,
-expiry_date, refresh_expiry_date, user_auth_time)
+expiry_date, user_auth_time)
VALUES("fia0123ikBWn931470H8s5gRqx7Moc4p","marlin","2018-05-30 16:25:50",
-"2018-05-31 16:25:50", "2018-08-30 16:25:50", "2018-05-30 16:23:10");
+"2018-05-31 16:25:50", "2018-05-30 16:23:10");
diff --git a/full/src/main/resources/db/new-mysql/V1.4__oauth2_tables.sql b/full/src/main/resources/db/new-mysql/V1.4__oauth2_tables.sql
index 10e069d..d5688b8 100644
--- a/full/src/main/resources/db/new-mysql/V1.4__oauth2_tables.sql
+++ b/full/src/main/resources/db/new-mysql/V1.4__oauth2_tables.sql
@@ -20,37 +20,39 @@
REFERENCES oauth2_client_url(url_hashcode)
);
-CREATE TABLE IF NOT EXISTS oauth2_authorization (
- id INTEGER PRIMARY KEY AUTO_INCREMENT,
- code VARCHAR(255) NOT NULL,
- client_id VARCHAR(100) NOT NULL,
- user_id VARCHAR(100) NOT NULL,
- redirect_uri TEXT DEFAULT NULL,
- created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- expiry_date TIMESTAMP NULL,
- is_revoked BOOLEAN DEFAULT 0,
- total_attempts INTEGER DEFAULT 0,
- user_auth_time TIMESTAMP NULL,
- nonce TEXT DEFAULT NULL,
- FOREIGN KEY (client_id)
- REFERENCES oauth2_client(id),
- UNIQUE INDEX authorization_index(code, client_id)
-);
-
CREATE TABLE IF NOT EXISTS oauth2_access_scope (
id VARCHAR(255) PRIMARY KEY NOT NULL
);
-CREATE TABLE IF NOT EXISTS oauth2_authorization_scope (
- id INTEGER PRIMARY KEY AUTO_INCREMENT,
- authorization_id INTEGER NOT NULL,
- scope_id VARCHAR(100) NOT NULL,
- FOREIGN KEY (authorization_id)
- REFERENCES oauth2_authorization(id),
- FOREIGN KEY (scope_id)
- REFERENCES oauth2_access_scope(id),
- UNIQUE INDEX authorization_scope_index(authorization_id, scope_id)
-);
+-- authorization tables are not needed if using cache
+
+--CREATE TABLE IF NOT EXISTS oauth2_authorization (
+-- id INTEGER PRIMARY KEY AUTO_INCREMENT,
+-- code VARCHAR(255) NOT NULL,
+-- client_id VARCHAR(100) NOT NULL,
+-- user_id VARCHAR(100) NOT NULL,
+-- redirect_uri TEXT DEFAULT NULL,
+-- created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+-- expiry_date TIMESTAMP NULL,
+-- is_revoked BOOLEAN DEFAULT 0,
+-- total_attempts INTEGER DEFAULT 0,
+-- user_auth_time TIMESTAMP NULL,
+-- nonce TEXT DEFAULT NULL,
+-- FOREIGN KEY (client_id)
+-- REFERENCES oauth2_client(id),
+-- UNIQUE INDEX authorization_index(code, client_id)
+--);
+--
+--CREATE TABLE IF NOT EXISTS oauth2_authorization_scope (
+-- id INTEGER PRIMARY KEY AUTO_INCREMENT,
+-- authorization_id INTEGER NOT NULL,
+-- scope_id VARCHAR(100) NOT NULL,
+-- FOREIGN KEY (authorization_id)
+-- REFERENCES oauth2_authorization(id),
+-- FOREIGN KEY (scope_id)
+-- REFERENCES oauth2_access_scope(id),
+-- UNIQUE INDEX authorization_scope_index(authorization_id, scope_id)
+--);
CREATE TABLE IF NOT EXISTS oauth2_access_token (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
@@ -62,10 +64,10 @@
is_revoked BOOLEAN DEFAULT 0,
user_auth_time TIMESTAMP NULL,
refresh_token VARCHAR(255) DEFAULT NULL,
- refresh_expiry_date TIMESTAMP NULL,
- is_refresh_revoked BOOLEAN DEFAULT 0,
FOREIGN KEY (client_id)
- REFERENCES oauth2_client(id)
+ REFERENCES oauth2_client(id),
+ FOREIGN KEY (refresh_token)
+ REFERENCES oauth2_refresh_token(id)
);
CREATE TABLE oauth2_access_token_scope (
@@ -74,40 +76,20 @@
CONSTRAINT primary_key PRIMARY KEY (token_id, scope_id)
);
---
----- status 1 = valid, 0 = revoked, -1 = disabled
---create table if not exists oauth2_access_token (
---id INTEGER PRIMARY KEY AUTO_INCREMENT,
---access_token VARCHAR(300),
---auth_code VARCHAR(250),
---client_id VARCHAR(100),
---user_id INTEGER,
----- make boolean --
---status INTEGER DEFAULT 1,
----- in case of code authorization, should match auth code scopes!
----- use scopes for levelaccess descriptor level[rw],level[r]
---scopes VARCHAR(350),
---expiration TIMESTAMP,
---FOREIGN KEY (user_id)
---REFERENCES korap_users(id)
---ON DELETE CASCADE,
---FOREIGN KEY (client_id)
---REFERENCES oauth2_client(client_id)
---ON DELETE CASCADE
---);
---
---
----- also scopes?
---create table if not exists oauth2_refresh_token (
---id INTEGER PRIMARY KEY AUTO_INCREMENT,
---client_id VARCHAR(100),
---user_id INTEGER,
---expiration TIMESTAMP,
---scopes VARCHAR(350),
---FOREIGN KEY (user_id)
---REFERENCES korap_users(id)
---ON DELETE CASCADE,
---FOREIGN KEY (client_id)
---REFERENCES oauth2_client(client_id)
---ON DELETE CASCADE
---);
\ No newline at end of file
+CREATE TABLE IF NOT EXISTS oauth2_refresh_token (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ token VARCHAR(255) NOT NULL,
+ user_id VARCHAR(100) DEFAULT NULL,
+ client_id VARCHAR(100) DEFAULT NULL,
+ created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ expiry_date TIMESTAMP NULL,
+ is_revoked BOOLEAN DEFAULT 0,
+ FOREIGN KEY (client_id)
+ REFERENCES oauth2_client(id)
+);
+
+CREATE TABLE oauth2_refresh_token_scope (
+ token_id INTEGER NOT NULL,
+ scope_id VARCHAR(100) NOT NULL,
+ CONSTRAINT primary_key PRIMARY KEY (token_id, scope_id)
+);
diff --git a/full/src/main/resources/db/new-sqlite/V1.4__oauth2_tables.sql b/full/src/main/resources/db/new-sqlite/V1.4__oauth2_tables.sql
index 45fa1f4..f11cac8 100644
--- a/full/src/main/resources/db/new-sqlite/V1.4__oauth2_tables.sql
+++ b/full/src/main/resources/db/new-sqlite/V1.4__oauth2_tables.sql
@@ -22,40 +22,42 @@
CREATE UNIQUE INDEX client_id_index on oauth2_client(id);
-CREATE TABLE IF NOT EXISTS oauth2_authorization (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- code VARCHAR(255) NOT NULL,
- client_id VARCHAR(100) NOT NULL,
- user_id VARCHAR(100) NOT NULL,
- redirect_uri TEXT DEFAULT NULL,
- created_date TIMESTAMP NOT NULL,
- expiry_date TIMESTAMP NOT NULL,
- is_revoked BOOLEAN DEFAULT 0,
- total_attempts INTEGER DEFAULT 0,
- user_auth_time TIMESTAMP NOT NULL,
- nonce TEXT DEFAULT NULL,
- FOREIGN KEY (client_id)
- REFERENCES oauth2_client(id)
-);
-
-CREATE UNIQUE INDEX authorization_index on oauth2_authorization(code, client_id);
-
CREATE TABLE IF NOT EXISTS oauth2_access_scope (
id VARCHAR(255) PRIMARY KEY NOT NULL
);
-CREATE TABLE IF NOT EXISTS oauth2_authorization_scope (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- authorization_id INTEGER NOT NULL,
- scope_id VARCHAR(100) NOT NULL,
- FOREIGN KEY (authorization_id)
- REFERENCES oauth2_authorization(id),
- FOREIGN KEY (scope_id)
- REFERENCES oauth2_access_scope(id)
-);
+-- authorization tables are not needed if using cache
-CREATE UNIQUE INDEX authorization_scope_index on
- oauth2_authorization_scope(authorization_id, scope_id);
+--CREATE TABLE IF NOT EXISTS oauth2_authorization (
+-- id INTEGER PRIMARY KEY AUTOINCREMENT,
+-- code VARCHAR(255) NOT NULL,
+-- client_id VARCHAR(100) NOT NULL,
+-- user_id VARCHAR(100) NOT NULL,
+-- redirect_uri TEXT DEFAULT NULL,
+-- created_date TIMESTAMP NOT NULL,
+-- expiry_date TIMESTAMP NOT NULL,
+-- is_revoked BOOLEAN DEFAULT 0,
+-- total_attempts INTEGER DEFAULT 0,
+-- user_auth_time TIMESTAMP NOT NULL,
+-- nonce TEXT DEFAULT NULL,
+-- FOREIGN KEY (client_id)
+-- REFERENCES oauth2_client(id)
+--);
+--
+--CREATE UNIQUE INDEX authorization_index on oauth2_authorization(code, client_id);
+--
+--CREATE TABLE IF NOT EXISTS oauth2_authorization_scope (
+-- id INTEGER PRIMARY KEY AUTOINCREMENT,
+-- authorization_id INTEGER NOT NULL,
+-- scope_id VARCHAR(100) NOT NULL,
+-- FOREIGN KEY (authorization_id)
+-- REFERENCES oauth2_authorization(id),
+-- FOREIGN KEY (scope_id)
+-- REFERENCES oauth2_access_scope(id)
+--);
+--
+--CREATE UNIQUE INDEX authorization_scope_index on
+-- oauth2_authorization_scope(authorization_id, scope_id);
CREATE TABLE IF NOT EXISTS oauth2_access_token (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -67,10 +69,10 @@
is_revoked BOOLEAN DEFAULT 0,
user_auth_time TIMESTAMP NOT NULL,
refresh_token VARCHAR(255) DEFAULT NULL,
- refresh_expiry_date TIMESTAMP NOT NULL,
- is_refresh_revoked BOOLEAN DEFAULT 0,
FOREIGN KEY (client_id)
REFERENCES oauth2_client(id)
+ FOREIGN KEY (refresh_token)
+ REFERENCES oauth2_refresh_token(id)
);
CREATE TABLE oauth2_access_token_scope (
@@ -78,3 +80,22 @@
scope_id VARCHAR(100) NOT NULL,
primary key (token_id, scope_id)
);
+
+CREATE TABLE IF NOT EXISTS oauth2_refresh_token (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ token VARCHAR(255) NOT NULL,
+ user_id VARCHAR(100) DEFAULT NULL,
+ user_auth_time TIMESTAMP NOT NULL,
+ client_id VARCHAR(100) DEFAULT NULL,
+ created_date TIMESTAMP NOT NULL,
+ expiry_date TIMESTAMP NULL,
+ is_revoked BOOLEAN DEFAULT 0,
+ FOREIGN KEY (client_id)
+ REFERENCES oauth2_client(id)
+);
+
+CREATE TABLE oauth2_refresh_token_scope (
+ token_id INTEGER NOT NULL,
+ scope_id VARCHAR(100) NOT NULL,
+ CONSTRAINT primary_key PRIMARY KEY (token_id, scope_id)
+);
diff --git a/full/src/main/resources/default-config.xml b/full/src/main/resources/default-config.xml
index f9ce666..ac243e3 100644
--- a/full/src/main/resources/default-config.xml
+++ b/full/src/main/resources/default-config.xml
@@ -141,6 +141,10 @@
<prop key="hibernate.cache.region.factory_class">${hibernate.cache.region.factory}</prop>
<prop key="hibernate.jdbc.time_zone">${hibernate.jdbc.time_zone}</prop>
<!-- <prop key="net.sf.ehcache.configurationResourceName">classpath:ehcache.xml</prop> -->
+
+ <prop key="connection.autoReconnect">true</prop>
+ <prop key="connection.autoReconnectForPools">true</prop>
+ <prop key="connection.is-connection-validation-required">true</prop>
</props>
</property>
</bean>