Derby database for persistent OAuth services

Derby database can be used for persistent OAuth services. For convenience and reference purposes, this topic documents the steps we need to configure Derby database, either remote or local to the OAuth service, for OAuth persistent service.

To configure Derby database for persistent OAuth services, complete the following steps:

1. Create a database and tables.

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

   --- Change oauth2db to the name we 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, we 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
   );
   
   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
   );
   
   ----- 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

2. Configure the WebSphere Application Server Liberty Profile server.

   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>ssl-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>

   Note: The Componentid must be the same as the id of the oauthProvider element in server.xml.

Parent topic: Persistent OAuth service configuration