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 you 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:
- Create a database and tables.Edit and run the following SQL statement to create an OAuth database and table:
Run the createTables.sql file by starting ij with the following command:--- 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;
ij createTables.sql
- Configure the Liberty 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>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>
Note: TheComponentid
must be the same as the id of theoauthProvider
element in the server.xml file.
After you configure the database, you can register clients with the database to populate it with information. For more information, see Configuring an OpenID Connect Provider to accept client registration requests .