Authorization Server Installation (COMPASS 6.3)

-- create  tables

CREATE TABLE oauth_client_details (
    client_id VARCHAR(255) NOT NULL,
    client_secret VARCHAR(255) NOT NULL,
    scope VARCHAR(255) NULL,
    authorized_grant_types VARCHAR(255) NOT NULL,
    web_server_redirect_uri VARCHAR(255) NULL,
    access_token_validity INT NULL,
    refresh_token_validity INT NULL,
    PRIMARY KEY (client_id)
);

CREATE TABLE users (
    username VARCHAR(255) NOT NULL,
    password VARCHAR(500) NOT NULL,
    enabled TINYINT(1) NOT NULL,
    PRIMARY KEY (username)
);

CREATE TABLE role (
    id INT NOT NULL,
    name enum('CASES','RULES') NOT NULL,
    username VARCHAR(255),
    PRIMARY KEY (id),
    CONSTRAINT userRole FOREIGN KEY (username) REFERENCES `users` (`username`),
    INDEX usernameIndex (username)
);

CREATE TABLE authorization (
    id VARCHAR(255) NOT NULL,
    access_token_expires_at DATETIME(6),
    access_token_issued_at DATETIME(6),
    access_token_metadata text,
    access_token_scopes text,
    access_token_type VARCHAR(255),
    access_token_value text,
    attributes text,
    authorization_code_expires_at DATETIME(6),
    authorization_code_issued_at DATETIME(6),
    authorization_code_metadata VARCHAR(255),
    authorization_code_value text,
    authorization_grant_type VARCHAR(255),
    authorized_scopes text,
    oidc_id_token_claims text,
    oidc_id_token_expires_at DATETIME(6),
    oidc_id_token_issued_at DATETIME(6),
    oidc_id_token_metadata text,
    oidc_id_token_value text,
    principal_name VARCHAR(255),
    refresh_token_expires_at DATETIME(6),
    refresh_token_issued_at DATETIME(6),
    refresh_token_metadata text,
    refresh_token_value text,
    registered_client_id VARCHAR(255),
    state text,
    PRIMARY KEY (id)
);

create sequence oauth_client_details_seq start with 1 increment by 50;
create sequence users_seq start with 1 increment by 50;
create sequence role_seq start with 1 increment by 50;
create sequence authorization_seq start with 1 increment by 50;

-- rulemanager client
-- client_id: rulemanager-client
-- client_password: rulemanager-client
INSERT INTO oauth_client_details (client_id, client_secret, scope, authorized_grant_types, access_token_validity, refresh_token_validity, web_server_redirect_uri)
VALUES ('rulemanager-client', '$2y$10$HjUI97VndCpzAvTPEvhjee7fzwyyUo.siMoXHQ4HCOmvqvDZqYsvG', 'openid,profile,email', 'authorization_code,refresh_token', 900000, 900000,'http://localhost:8080/RuleManagerApp/');

-- case-viewer client
-- client_id: caseviewer-client
-- client_password: caseviewer-client
INSERT INTO oauth_client_details (client_id, client_secret, scope, authorized_grant_types, access_token_validity, refresh_token_validity, web_server_redirect_uri)
VALUES ('caseviewer-client', '$2y$10$eTj84Msn4ZL6dtGd8519A.g2rx9UlyVk5XNYU3pU8EmSi20x61nQS',  'openid,profile,email', 'authorization_code,refresh_token', 900000, 900000,'http://localhost:8080/CaseViewerApp/');

-- admin pwd: admin-password
INSERT INTO users (username, password, enabled)
VALUES ('admin', '$2a$10$HqXnMW9nm8p9fXVlLalu6OafEXD5i9q79GUw7RfIm2sBflNud2MpO', true);

-- userone pwd: userone
INSERT INTO users (username, password, enabled)
VALUES ('userone', '$2a$10$7CseykqiGFJT.Ulo9P0VB.8l1.WK.xjKkTU/GsK.Nr.SmdEIsT7eq', true);

-- usertwo pwd: usertwo
INSERT INTO users (username, password, enabled)
VALUES ('usertwo', '$2a$10$.f5cxF5opkEc8Ooj86J2IeZ65aM06LZhEhw3/z262C59AXtkPyQUi', true);

-- assign roles for your own users. Find below an example for assigning roles for user 'admin', 'userone', 'usertwo'.
-- If role based authentication is not required, then the below scripts can be ignored.

INSERT INTO role (name, username, id)
VALUES ('CASES', 'admin', 1);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'admin', 2);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'userone', 3);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'userone', 4);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'usertwo', 5);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'usertwo', 6);

CREATE TABLE oauth_client_details (
    client_id varchar2(255 char) not null,
    client_secret varchar2(255 char) not null,
    scope varchar2(255 char),
    authorized_grant_types varchar2(255 char) not null,
    web_server_redirect_uri varchar2(255 char) not null,
    access_token_validity number(10,0),
    refresh_token_validity number(10,0),
    PRIMARY KEY (client_id)
);

CREATE TABLE users (
    username varchar2(255 char) not null,
    password varchar2(255 char) not null,
    enabled number(1,0) not null,
    PRIMARY KEY (username),
    CHECK (enabled IN (0,1))
);

CREATE TABLE ROLE (
    ID NUMBER(10) NOT NULL,
    NAME VARCHAR2(255 CHAR) NOT NULL,
    USERNAME VARCHAR2(255 CHAR),
    PRIMARY KEY (ID),
    CONSTRAINT userRole FOREIGN KEY (USERNAME) REFERENCES "USERS" ("USERNAME"),
    CHECK (name IN ('CASES','RULES'))
);

CREATE TABLE AUTHORIZATION (
    ACCESS_TOKEN_EXPIRES_AT TIMESTAMP(6) WITH TIME ZONE,
    ACCESS_TOKEN_ISSUED_AT TIMESTAMP(6) WITH TIME ZONE,
    AUTHORIZATION_CODE_EXPIRES_AT TIMESTAMP(6) WITH TIME ZONE,
    AUTHORIZATION_CODE_ISSUED_AT TIMESTAMP(6) WITH TIME ZONE,
    OIDC_ID_TOKEN_EXPIRES_AT TIMESTAMP(6) WITH TIME ZONE,
    OIDC_ID_TOKEN_ISSUED_AT TIMESTAMP(6) WITH TIME ZONE,
    REFRESH_TOKEN_EXPIRES_AT TIMESTAMP(6) WITH TIME ZONE,
    REFRESH_TOKEN_ISSUED_AT TIMESTAMP(6) WITH TIME ZONE,
    ACCESS_TOKEN_TYPE VARCHAR2(255 CHAR),
    AUTHORIZATION_CODE_METADATA VARCHAR2(255 CHAR),
    AUTHORIZATION_GRANT_TYPE VARCHAR2(255 CHAR),
    ID VARCHAR2(255 CHAR) NOT NULL,
    PRINCIPAL_NAME VARCHAR2(255 CHAR),
    REGISTERED_CLIENT_ID VARCHAR2(255 CHAR),
    ACCESS_TOKEN_METADATA CLOB,
    ACCESS_TOKEN_SCOPES CLOB,
    ACCESS_TOKEN_VALUE CLOB,
    ATTRIBUTES CLOB,
    AUTHORIZATION_CODE_VALUE CLOB,
    AUTHORIZED_SCOPES CLOB,
    OIDC_ID_TOKEN_CLAIMS CLOB,
    OIDC_ID_TOKEN_METADATA CLOB,
    OIDC_ID_TOKEN_VALUE CLOB,
    REFRESH_TOKEN_METADATA CLOB,
    REFRESH_TOKEN_VALUE CLOB,
    STATE CLOB,
    PRIMARY KEY (ID)
);

create sequence oauth_client_details_seq start with 1 increment by 50;
create sequence users_seq start with 1 increment by 50;
create sequence role_seq start with 1 increment by 50;
create sequence authorization_seq start with 1 increment by 50;

-- rulemanager client
-- client_id: rulemanager-client
-- client_password: rulemanager-client
INSERT INTO oauth_client_details (client_id, client_secret, scope, authorized_grant_types, access_token_validity, refresh_token_validity, web_server_redirect_uri)
VALUES ('rulemanager-client', '$2y$10$HjUI97VndCpzAvTPEvhjee7fzwyyUo.siMoXHQ4HCOmvqvDZqYsvG', 'openid,profile,email', 'authorization_code,refresh_token', 900000, 900000,'http://localhost:8080/RuleManagerApp/');

-- case-viewer client
-- client_id: caseviewer-client
-- client_password: caseviewer-client
INSERT INTO oauth_client_details (client_id, client_secret, scope, authorized_grant_types, access_token_validity, refresh_token_validity, web_server_redirect_uri)
VALUES ('caseviewer-client', '$2y$10$eTj84Msn4ZL6dtGd8519A.g2rx9UlyVk5XNYU3pU8EmSi20x61nQS',  'openid,profile,email', 'authorization_code,refresh_token', 900000, 900000,'http://localhost:8080/CaseViewerApp/');

-- admin pwd: admin-password
INSERT INTO users (username, password, enabled)
VALUES ('admin', '$2a$10$HqXnMW9nm8p9fXVlLalu6OafEXD5i9q79GUw7RfIm2sBflNud2MpO', 1);

-- userone pwd: userone
INSERT INTO users (username, password, enabled)
VALUES ('userone', '$2a$10$7CseykqiGFJT.Ulo9P0VB.8l1.WK.xjKkTU/GsK.Nr.SmdEIsT7eq', 1);

-- usertwo pwd: usertwo
INSERT INTO users (username, password, enabled)
VALUES ('usertwo', '$2a$10$.f5cxF5opkEc8Ooj86J2IeZ65aM06LZhEhw3/z262C59AXtkPyQUi', 1);

-- assign roles for your own users. Find below an example for assigning roles for user 'admin', 'userone', 'usertwo'.
-- If role based authentication is not required, then the below scripts can be ignored.

INSERT INTO role (name, username, id)
VALUES ('CASES', 'admin', 1);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'admin', 2);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'userone', 3);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'userone', 4);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'usertwo', 5);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'usertwo', 6);

-- create  tables

CREATE TABLE oauth_client_details (
                                      client_id VARCHAR(255) NOT NULL,
                                      client_secret VARCHAR(255) NOT NULL,
                                      scope VARCHAR(255) NULL,
                                      authorized_grant_types VARCHAR(255) NOT NULL,
                                      web_server_redirect_uri VARCHAR(255) NULL,
                                      access_token_validity INT NULL,
                                      refresh_token_validity INT NULL,
                                      PRIMARY KEY (client_id)
);

CREATE TABLE users (
                       username VARCHAR(255) NOT NULL,
                       password VARCHAR(500) NOT NULL,
                       enabled boolean NOT NULL,
                       PRIMARY KEY (username)
);

CREATE TABLE role (
                      id INT NOT NULL,
                      name VARCHAR(10) NOT NULL,
                      username VARCHAR(255),
                      PRIMARY KEY (id),
                      CHECK (name IN ('CASES', 'RULES')),
                      CONSTRAINT userRole FOREIGN KEY (username) REFERENCES users (username)
);
CREATE INDEX usernameIndex on role(username);


CREATE TABLE authorization (
                               id VARCHAR(255) NOT NULL,
                               access_token_expires_at TIMESTAMP,
                               access_token_issued_at TIMESTAMP,
                               access_token_metadata VARCHAR(32600),
                               access_token_scopes VARCHAR(32600),
                               access_token_type VARCHAR(255),
                               access_token_value VARCHAR(32600),
                               attributes VARCHAR(32600),
                               authorization_code_expires_at TIMESTAMP,
                               authorization_code_issued_at TIMESTAMP,
                               authorization_code_metadata VARCHAR(255),
                               authorization_code_value VARCHAR(32600),
                               authorization_grant_type VARCHAR(255),
                               authorized_scopes VARCHAR(32600),
                               oidc_id_token_claims VARCHAR(32600),
                               oidc_id_token_expires_at TIMESTAMP,
                               oidc_id_token_issued_at TIMESTAMP,
                               oidc_id_token_metadata VARCHAR(32600),
                               oidc_id_token_value VARCHAR(32600),
                               principal_name VARCHAR(255),
                               refresh_token_expires_at TIMESTAMP,
                               refresh_token_issued_at TIMESTAMP,
                               refresh_token_metadata VARCHAR(32600),
                               refresh_token_value VARCHAR(32600),
                               registered_client_id VARCHAR(255),
                               state VARCHAR(32600),
                               PRIMARY KEY (id)
);

create sequence oauth_client_details_seq start with 1 increment by 50;
create sequence users_seq start with 1 increment by 50;
create sequence role_seq start with 1 increment by 50;
create sequence authorization_seq start with 1 increment by 50;

-- rulemanager client
-- client_id: rulemanager-client
-- client_password: rulemanager-client
INSERT INTO oauth_client_details (client_id, client_secret, scope, authorized_grant_types, access_token_validity, refresh_token_validity, web_server_redirect_uri)
VALUES ('rulemanager-client', '$2y$10$HjUI97VndCpzAvTPEvhjee7fzwyyUo.siMoXHQ4HCOmvqvDZqYsvG', 'openid,profile,email', 'authorization_code,refresh_token', 900000, 900000,'http://localhost:8080/RuleManagerApp/');

-- case-viewer client
-- client_id: caseviewer-client
-- client_password: caseviewer-client
INSERT INTO oauth_client_details (client_id, client_secret, scope, authorized_grant_types, access_token_validity, refresh_token_validity, web_server_redirect_uri)
VALUES ('caseviewer-client', '$2y$10$eTj84Msn4ZL6dtGd8519A.g2rx9UlyVk5XNYU3pU8EmSi20x61nQS',  'openid,profile,email', 'authorization_code,refresh_token', 900000, 900000,'http://localhost:8080/CaseViewerApp/');

-- admin pwd: admin-password
INSERT INTO users (username, password, enabled)
VALUES ('admin', '$2a$10$HqXnMW9nm8p9fXVlLalu6OafEXD5i9q79GUw7RfIm2sBflNud2MpO', 1);

-- userone pwd: userone
INSERT INTO users (username, password, enabled)
VALUES ('userone', '$2a$10$7CseykqiGFJT.Ulo9P0VB.8l1.WK.xjKkTU/GsK.Nr.SmdEIsT7eq', 1);

-- usertwo pwd: usertwo
INSERT INTO users (username, password, enabled)
VALUES ('usertwo', '$2a$10$.f5cxF5opkEc8Ooj86J2IeZ65aM06LZhEhw3/z262C59AXtkPyQUi', 1);

-- assign roles for your own users. Find below an example for assigning roles for user 'admin', 'userone', 'usertwo'.
-- If role based authentication is not required, then the below scripts can be ignored.

INSERT INTO role (name, username, id)
VALUES ('CASES', 'admin', 1);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'admin', 2);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'userone', 3);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'userone', 4);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'usertwo', 5);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'usertwo', 6);

DROP TABLE IF EXISTS `oauth_access_token`;
DROP TABLE IF EXISTS `oauth_refresh_token`;
DROP TABLE IF EXISTS `authorities`;

alter table oauth_client_details drop column resource_ids;
alter table oauth_client_details drop column authorities;
alter table oauth_client_details drop column additional_information;
alter table oauth_client_details drop column authorities;

-- rulemanager client
UPDATE oauth_client_details SET client_id='rulemanager-client', scope='openid,profile,email', authorized_grant_types='authorization_code,refresh_token', access_token_validity=900000, refresh_token_validity=900000, web_server_redirect_uri='http://localhost:8080/RuleManagerApp/' where client_id='rm-client';

-- caseviewer client
UPDATE oauth_client_details SET client_id='caseviewer-client', scope='openid,profile,email', authorized_grant_types='authorization_code,refresh_token', access_token_validity=900000, refresh_token_validity=900000, web_server_redirect_uri='http://localhost:8080/CaseViewerApp/' where client_id='case-viewer';

CREATE TABLE role (
  id INT NOT NULL,
  name enum('CASES','RULES') NOT NULL,
  username VARCHAR(255),
  PRIMARY KEY (id),
  CONSTRAINT userRole FOREIGN KEY (username) REFERENCES `users` (`username`),
  INDEX usernameIndex (username)
);

CREATE TABLE authorization (
   id VARCHAR(255) NOT NULL,
   access_token_expires_at DATETIME(6),
   access_token_issued_at DATETIME(6),
   access_token_metadata text,
   access_token_scopes text,
   access_token_type VARCHAR(255),
   access_token_value text,
   attributes text,
   authorization_code_expires_at DATETIME(6),
   authorization_code_issued_at DATETIME(6),
   authorization_code_metadata VARCHAR(255),
   authorization_code_value text,
   authorization_grant_type VARCHAR(255),
   authorized_scopes text,
   oidc_id_token_claims text,
   oidc_id_token_expires_at DATETIME(6),
   oidc_id_token_issued_at DATETIME(6),
   oidc_id_token_metadata text,
   oidc_id_token_value text,
   principal_name VARCHAR(255),
   refresh_token_expires_at DATETIME(6),
   refresh_token_issued_at DATETIME(6),
   refresh_token_metadata text,
   refresh_token_value text,
   registered_client_id VARCHAR(255),
   state text,
   PRIMARY KEY (id)
);

-- if the below sequences are not available please create sequence with the below query
create sequence oauth_client_details_seq start with 1 increment by 50;
create sequence users_seq start with 1 increment by 50;
create sequence role_seq start with 1 increment by 50;
create sequence authorization_seq start with 1 increment by 50;

-- assign roles for your own users. Find below an example for assigning roles for user 'admin', 'userone', 'usertwo'.
-- If role based authentication is not required, then the below scripts can be ignored.

INSERT INTO role (name, username, id)
VALUES ('CASES', 'admin', 1);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'admin', 2);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'userone', 3);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'userone', 4);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'usertwo', 5);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'usertwo', 6);

DROP TABLE oauth_access_token;
DROP TABLE oauth_refresh_token;
DROP TABLE authorities;

alter table oauth_client_details drop column resource_ids;
alter table oauth_client_details drop column authorities;
alter table oauth_client_details drop column additional_information;
alter table oauth_client_details drop column authorities;

CREATE TABLE ROLE (
  ID NUMBER(10) NOT NULL,
  NAME VARCHAR2(255 CHAR) NOT NULL,
  USERNAME VARCHAR2(255 CHAR),
  PRIMARY KEY (ID),
  CONSTRAINT userRole FOREIGN KEY (USERNAME) REFERENCES "USERS" ("USERNAME"),
  CHECK (name IN ('CASES','RULES'))
);

CREATE TABLE AUTHORIZATION (
   ACCESS_TOKEN_EXPIRES_AT TIMESTAMP(6) WITH TIME ZONE,
   ACCESS_TOKEN_ISSUED_AT TIMESTAMP(6) WITH TIME ZONE,
   AUTHORIZATION_CODE_EXPIRES_AT TIMESTAMP(6) WITH TIME ZONE,
   AUTHORIZATION_CODE_ISSUED_AT TIMESTAMP(6) WITH TIME ZONE,
   OIDC_ID_TOKEN_EXPIRES_AT TIMESTAMP(6) WITH TIME ZONE,
   OIDC_ID_TOKEN_ISSUED_AT TIMESTAMP(6) WITH TIME ZONE,
   REFRESH_TOKEN_EXPIRES_AT TIMESTAMP(6) WITH TIME ZONE,
   REFRESH_TOKEN_ISSUED_AT TIMESTAMP(6) WITH TIME ZONE,
   ACCESS_TOKEN_TYPE VARCHAR2(255 CHAR),
   AUTHORIZATION_CODE_METADATA VARCHAR2(255 CHAR),
   AUTHORIZATION_GRANT_TYPE VARCHAR2(255 CHAR),
   ID VARCHAR2(255 CHAR) NOT NULL,
   PRINCIPAL_NAME VARCHAR2(255 CHAR),
   REGISTERED_CLIENT_ID VARCHAR2(255 CHAR),
   ACCESS_TOKEN_METADATA CLOB,
   ACCESS_TOKEN_SCOPES CLOB,
   ACCESS_TOKEN_VALUE CLOB,
   ATTRIBUTES CLOB,
   AUTHORIZATION_CODE_VALUE CLOB,
   AUTHORIZED_SCOPES CLOB,
   OIDC_ID_TOKEN_CLAIMS CLOB,
   OIDC_ID_TOKEN_METADATA CLOB,
   OIDC_ID_TOKEN_VALUE CLOB,
   REFRESH_TOKEN_METADATA CLOB,
   REFRESH_TOKEN_VALUE CLOB,
   STATE CLOB,
   PRIMARY KEY (ID)
);

-- rulemanager client
UPDATE oauth_client_details SET client_id='rulemanager-client', scope='openid,profile,email', authorized_grant_types='authorization_code,refresh_token', access_token_validity=900000, refresh_token_validity=900000, web_server_redirect_uri='http://localhost:8080/RuleManagerApp/' where client_id='rm-client';

-- caseviewer client
UPDATE oauth_client_details SET client_id='caseviewer-client', scope='openid,profile,email', authorized_grant_types='authorization_code,refresh_token', access_token_validity=900000, refresh_token_validity=900000, web_server_redirect_uri='http://localhost:8080/CaseViewerApp/' where client_id='case-viewer';

-- if the below sequences are not available please create sequence with the below query
create sequence oauth_client_details_seq start with 1 increment by 50;
create sequence users_seq start with 1 increment by 50;
create sequence role_seq start with 1 increment by 50;
create sequence authorization_seq start with 1 increment by 50;

-- assign roles for your own users. Find below an example for assigning roles for user 'admin', 'userone', 'usertwo'.
-- If role based authentication is not required, then the below scripts can be ignored.

INSERT INTO role (name, username, id)
VALUES ('CASES', 'admin', 1);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'admin', 2);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'userone', 3);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'userone', 4);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'usertwo', 5);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'usertwo', 6);

DROP TABLE IF EXISTS oauth_access_token;
DROP TABLE IF EXISTS oauth_refresh_token;
DROP TABLE IF EXISTS authorities;

alter table oauth_client_details drop column resource_ids;
alter table oauth_client_details drop column additional_information;
alter table oauth_client_details drop column authorities;

-- CALL SYSPROC.ADMIN_CMD('REORG TABLE schemaName.oauth_client_details');

-- rulemanager client
UPDATE oauth_client_details SET client_id='rulemanager-client', scope='openid,profile,email', authorized_grant_types='authorization_code,refresh_token', access_token_validity=900000, refresh_token_validity=900000, web_server_redirect_uri='http://localhost:8080/RuleManagerApp/' where client_id='rm-client';

-- caseviewer client
UPDATE oauth_client_details SET client_id='caseviewer-client', scope='openid,profile,email', authorized_grant_types='authorization_code,refresh_token', access_token_validity=900000, refresh_token_validity=900000, web_server_redirect_uri='http://localhost:8080/CaseViewerApp/' where client_id='case-viewer';

CREATE TABLE role (
                      id INT NOT NULL,
                      name VARCHAR(10) NOT NULL,
                      username VARCHAR(255),
                      PRIMARY KEY (id),
                      CHECK (name IN ('CASES', 'RULES')),
                      CONSTRAINT userRole FOREIGN KEY (username) REFERENCES users (username)
);
CREATE INDEX usernameIndex on role(username);


CREATE TABLE authorization (
                               id VARCHAR(255) NOT NULL,
                               access_token_expires_at TIMESTAMP,
                               access_token_issued_at TIMESTAMP,
                               access_token_metadata VARCHAR(32600),
                               access_token_scopes VARCHAR(32600),
                               access_token_type VARCHAR(255),
                               access_token_value VARCHAR(32600),
                               attributes VARCHAR(32600),
                               authorization_code_expires_at TIMESTAMP,
                               authorization_code_issued_at TIMESTAMP,
                               authorization_code_metadata VARCHAR(255),
                               authorization_code_value VARCHAR(32600),
                               authorization_grant_type VARCHAR(255),
                               authorized_scopes VARCHAR(32600),
                               oidc_id_token_claims VARCHAR(32600),
                               oidc_id_token_expires_at TIMESTAMP,
                               oidc_id_token_issued_at TIMESTAMP,
                               oidc_id_token_metadata VARCHAR(32600),
                               oidc_id_token_value VARCHAR(32600),
                               principal_name VARCHAR(255),
                               refresh_token_expires_at TIMESTAMP,
                               refresh_token_issued_at TIMESTAMP,
                               refresh_token_metadata VARCHAR(32600),
                               refresh_token_value VARCHAR(32600),
                               registered_client_id VARCHAR(255),
                               state VARCHAR(32600),
                               PRIMARY KEY (id)
);

-- if the below sequences are not available please create sequence with the below query
create sequence oauth_client_details_seq start with 1 increment by 50;
create sequence users_seq start with 1 increment by 50;
create sequence role_seq start with 1 increment by 50;
create sequence authorization_seq start with 1 increment by 50;

-- assign roles for your own users. Find below an example for assigning roles for user 'admin', 'userone', 'usertwo'.
-- If role based authentication is not required, then the below scripts can be ignored.

INSERT INTO role (name, username, id)
VALUES ('CASES', 'admin', 1);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'admin', 2);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'userone', 3);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'userone', 4);

INSERT INTO role (name, username, id)
VALUES ('CASES', 'usertwo', 5);

INSERT INTO role (name, username, id)
VALUES ('RULES', 'usertwo', 6);