+

Search Tips   |   Advanced Search

(WAS v8.5.0.1)

SQL statements for persistent OAuth service

WebSphere Application Server supports persistent OAuth 2.0 service by persisting OAuth tokens and clients in a database. With persistent OAuth 2.0 services, an authorized client can access OAuth 2.0 service after OAuth services are restarted.

To configure persistent OAuth 2.0 services, follow the following steps:

  1. Configure the OAuth 2.0 service provider.

    The OauthConfigSample.xml template provider configuration file contains example parameters for configuring a JDBC database store for registering OAuth clients.

    1. Comment out the following lines: 
      <parameter name="oauth20.client.provider.classname" type="cc" customizable="false">
  <value>com.ibm.ws.security.oauth20.plugins.BaseClientProvider</value>
</parameter>
<parameter name="oauth20.token.cache.classname" type="cc" customizable="false">
  <value>com.ibm.ws.security.oauth20.plugins.BaseCache</value>
</parameter>
<parameter name="oauth20.token.cache.jndi.tokens" type="ws" customizable="false">
  <value>services/cache/OAuth20MemTokenCache</value>
</parameter>
<parameter name="oauth20.token.cache.jndi.users" type="ws" customizable="false">
  <value>services/cache/OAuth20MemTokenOwnerCache</value>
</parameter>

    2. Uncomment the following lines: 
      <!-- Example parameters for JDBC database stores -->
<parameter name="oauth20.client.provider.classname" type="cc" customizable="false">
  <value>com.ibm.ws.security.oauth20.plugins.db.CachedDBClientProvider</value>
</parameter>
<parameter name="oauth20.token.cache.classname" type="cc" customizable="false">
  <value>com.ibm.ws.security.oauth20.plugins.db.CachedDBTokenStore</value>
</parameter>
<parameter name="oauthjdbc.JDBCProvider" type="ws" customizable="false">
  <value>jdbc/oauthProvider</value>
</parameter>
<parameter name="oauthjdbc.client.table" type="ws" customizable="false">
  <value>OAuthDBSchema.OAUTH20CLIENTCONFIG</value>
</parameter>
<parameter name="oauthjdbc.token.table" type="ws" customizable="false">
  <value>OAuthDBSchema.OAUTH20CACHE</value>
</parameter>
<parameter name="oauthjdbc.CleanupInterval" type="ws" customizable="true">
  <value>3600</value>
</parameter>
<parameter name="oauthjdbc.CleanupBatchSize" type="ws" customizable="true">
  <value>250</value>
</parameter>
<parameter name="oauth20.db.token.cache.jndi.tokens" type="ws" customizable="false">
  <value>services/cache/OAuth20DBTokenCache</value>
</parameter>
<parameter name="oauth20.db.token.cache.jndi.clients" type="ws" customizable="false">
  <value>services/cache/OAuth20DBClientCache</value>
</parameter>

  2. Set up a database and table to store the OAuth token and client.

    1. Create a database for persistent OAuth service. See the vendor documentation for database creation. In this topic, we assume the database name created for OAuth is D:\oauth2db.

    2. Create two OAuth tables as defined by the following SQL statements: 
      ----- 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);

  3. Configure WebSphere Application Server.

    Configure the WAS data source. Set the data source JNDI name to be jdbc/oauthProvider. The JNDI name must match the oauthjdbc.JDBCProvider parameter value in the provider configuration file. Choose a database name to match what created in the first step, for example, D:\oauth2db.

    The configuration of DB2 or Derby for OAuth persistent services are included. We can use them as a sample template to configure other databases.

  4. Add the registered OAuth clients to the database.

    To persist a client in a database, you must save the client to the database. The following SQL statements add the dbclient01 and dbclient02 OAuth clients to a Derby database: 

    CONNECT 'jdbc:derby:D:\oauth2db';
INSERT INTO OAuthDBSchema.OAUTH20CLIENTCONFIG VALUES  (
  'OAuthConfigDerby',    'dbclient01',    'secret',    'dbclient01',    'http://localhost:9080/oauthclient/redirect.jsp',    1
), (
  'OAuthConfigDerby',    'dbclient02',    'secret',    'dbclient02',    'http://localhost:9080/oauthclient/redirect.jsp',    1
);
DISCONNECT CURRENT;


Subtopics