(WAS v8.5.0.1)

Use IBM DB2 for persistent OAuth service

IBM DB2 can be used for persistent OAuth services. For convenience and reference purposes, this topic documents the steps we need to configure DB2 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  CREATE DATABASE oauth2db USING CODESET UTF8 TERRITORY US;
   CONNECT TO oauth2db;
    ----- 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);
    ----- GRANT PRIVILIGES -----
   ----- UNCOMMENT THE FOLLOWING IF YOU USE ANOTHER ACCOUNT OTHER THAN ADMINISTRATOR FOR DB ACCESS -----
    -- Change dbuser to the account to use to access the database  -- GRANT ALL ON OAuthDBSchema.OAUTH20CACHE TO USER dbuser;
   -- GRANT ALL ON OAuthDBSchema.OAUTH20CLIENTCONFIG TO USER dbuser;
    ----- END OF GRANT PRIVILIGES -----
    DISCONNECT CURRENT;

   The default DB2 listening port is 50000. To find it, run the following command and find the value of the SVCENAME parameter. If it is a number, then it is the port number. If it is a name, look for the name in the /etc/services file or the Windows equivalent if you are using Windows.

   Linux/Unix: db2 get dbm cfg | grep SVCENAME
   Windows:    db2 get dbm cfg | findstr SVCENAME

   We can create a database and tables in DB2 by running the following statement:
   db2 -tvf createTables.sql

2. Configure the data source.

   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: DB2

      • Provider Type: DB2 Universal JDBC Driver Provider

      • Implementation Type: Connection pool data source

   4. Click Next.

   5. Set the following parameters:

      • DB2_UNIVERSAL_JDBC_DRIVER_PATH: /home/ldapdb2/sqllib/java

      • DB2_UNIVERSAL_JDBC_DRIVER_NATIVEPATH: /home/ldapdb2/sqllib/lib

   6. Click Next.

   7. Click Finish.

   8. Save the configuration.

   9. Go to Security > Global security > Java Authentication and Authorization Service > J2C authentication data.

   10. Click New.

   11. Set the following parameters:

       • Alias: oauthalias

       • UserID: dbuser

         The dbuser user is the operating system user that you originally created.gotcha

       • Password: <password for dbuser>

   12. Click OK.

   13. Save the configuration.

   14. Go to Resources > JDBC > Data Sources.

   15. Pick a scope. This topic uses server.

   16. Click New. A wizard starts.

   17. Set the following parameters:

       • Data source name: OAuth JDBC Service

       • JNDI name: jdbc/oauthProvider

       • Component-managed authentication alias: <scope>/oauthalias

   18. Click Next.

   19. Select an existing JDBC Provider, which should be the DB2 Universal JDBC Driver Provider.

   20. Click Next.

   21. Set the following parameters:

       • Database name: oauth2db

       • Driver type: 4

       • Server name: <DB2 server>

       • Port number: <see the previous information about the SVCENAME parameter>

       • Container Managed Persistence: Checked

   22. Click Next.

   23. Click Finish.

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

The following example adds a client to DB2:

INSERT INTO OAuthDBSchema.OAUTH20CLIENTCONFIG  (
  COMPONENTID,    CLIENTID,    CLIENTSECRET,    DISPLAYNAME,    REDIRECTURI,    ENABLED
)  VALUES  (
  '1',    'key',    'secret',    'My Client',    'https://localhost:9443/oauth/redirect.jsp',    1
)