(WAS v8.5.0.1)

Use Derby database for persistent OAuth service

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.

Follow these 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 by 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 WAS.

   In the console, go to Resources > JDBC > JDBC Providers.

   1. Pick a scope. This topic uses server.

   2. Click New. A wizard starts.

   3. Select the following parameters:

      • Database Type: Derby

      • Provider Type: Derby JDBC Provider

      • Implementation Type: Connection pool data source

   4. Click Next.

   5. Click Finish.

   6. Save the configuration.

   7. Go to Resources > JDBC > Data Sources.

   8. Pick a scope. This topic uses server.

   9. Click New. A wizard starts.

   10. Select the following parameters:

       • Data source name: OAuth JDBC Service

       • JNDI name: jdbc/oauthProvider

   11. Click Next.

   12. Select the Derby JDBC Provider created.

   13. Click Next.

   14. Select the following parameters:

       • Database name: <the path to the Derby embedded database, for example, D:\oauth2db>

       • Container Managed Persistence: Checked

   15. Click Next. Leave the settings unchanged in the Setup security alias page.

   16. Click Next.

   17. Click Finish.

   18. Save the configuration.
   We can now test the connection. The component works when configured with the JDBC plug-ins for OAuth.