Derby database for persistent OAuth services

Edit and run the following SQL statement to create an OAuth database and table:

--- Change oauth2db to the name you want for the database
--- Connect to Derby, choose one connection option to uncomment
--- if connecting to Derby as network server
--- CONNECT 'jdbc:derby://localhost:1527/oauth2db;create=true';

--- if connecting to embedded derby, you can change D:\oauth2db to location of database
--- CONNECT 'jdbc:derby:D:\oauth2db;create=true';

--- if creating tables in existing Derby database, remove the create=true parameter.

----- CREATE TABLES -----
CREATE TABLE OAuthDBSchema.OAUTH20CACHE (
  LOOKUPKEY VARCHAR(256) NOT NULL,
  UNIQUEID VARCHAR(128) NOT NULL,
  COMPONENTID VARCHAR(256) NOT NULL,
  TYPE VARCHAR(64) NOT NULL,
  SUBTYPE VARCHAR(64),
  CREATEDAT BIGINT,
  LIFETIME INT,
  EXPIRES BIGINT,
  TOKENSTRING VARCHAR(2048) NOT NULL,
  CLIENTID VARCHAR(64) NOT NULL,
  USERNAME VARCHAR(64) NOT NULL,
  SCOPE VARCHAR(512) NOT NULL,
  REDIRECTURI VARCHAR(2048),
  STATEID VARCHAR(64) NOT NULL,
  EXTENDEDFIELDS CLOB NOT NULL DEFAULT '{}'
);

CREATE TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG (
  COMPONENTID VARCHAR(256) NOT NULL,
  CLIENTID VARCHAR(256) NOT NULL,
  CLIENTSECRET VARCHAR(256),
  DISPLAYNAME VARCHAR(256) NOT NULL,
  REDIRECTURI VARCHAR(2048),
  ENABLED INT,
  CLIENTMETADATA CLOB NOT NULL DEFAULT '{}'
);

CREATE TABLE OAuthDBSchema.OAUTH20CONSENTCACHE (
  CLIENTID VARCHAR(256) NOT NULL,
  USERID VARCHAR(256),
  PROVIDERID VARCHAR(256) NOT NULL,
  SCOPE VARCHAR(1024) NOT NULL,
  EXPIRES BIGINT,
  EXTENDEDFIELDS CLOB NOT NULL DEFAULT '{}'
);

----- ADD CONSTRAINTS -----
ALTER TABLE OAuthDBSchema.OAUTH20CACHE
  ADD CONSTRAINT PK_LOOKUPKEY PRIMARY KEY (LOOKUPKEY);

ALTER TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG
  ADD CONSTRAINT PK_COMPIDCLIENTID PRIMARY KEY (COMPONENTID,CLIENTID);

----- CREATE INDEXES -----
CREATE INDEX OAUTH20CACHE_EXPIRES ON OAUTHDBSCHEMA.OAUTH20CACHE (EXPIRES ASC);

DISCONNECT CURRENT;

Run the createTables.sql file by starting ij with the following command:

ij createTables.sql

The following example is a sample server.xml file for an OAuth provider that uses a Derby database store:

<server>

  <featureManager>
    <feature>oauth-2.0</feature>
    <feature>transportSecurity-1.0</feature>
    <feature>jdbc-4.0</feature>
    <feature>jndi-1.0</feature>
  </featureManager>

  <keyStore password="keyspass" />

  <oauth-roles>
    <authenticated>
      <user>testuser</user>
    </authenticated>
  </oauth-roles>

  <oauthProvider id="OAuthConfigDerby" filter="request-url%=ssodemo"
                 oauthOnly="false">
    <databaseStore dataSourceRef="OAuthDerbyDataSource" />
  </oauthProvider>

  <jdbcDriver id="DerbyEmbedded" libraryRef="DerbyLib" />

  <library id="DerbyLib" filesetRef="DerbyFileset" />

  <fileset id="DerbyFileset" dir="${DERBY_JDBC_DRIVER_PATH}"
           includes="derby.jar" />

  <dataSource id="OAuthDerbyDataSource" jndiName="jdbc/OAuth2DB"
              jdbcDriverRef="DerbyEmbedded">
    <properties.derby.embedded databaseName="D:\oauth2db"
                               createDatabase="create" />
  </dataSource>

  <webAppSecurity allowFailOverToBasicAuth="true" />

  <basicRegistry id="basic" realm="BasicRealm">
    <user name="testuser" password="testuserpwd" />
  </basicRegistry>
</server>