Setting up Jazz Authorization Server Oracle database

Jazz® Authorization Server is configured to use the default Apache Derby database, but you might want to use an enterprise database such as Oracle in your Jazz Authorization Server environment. Apache Derby database is only supported for evaluation purpose in a POC environment, but not in the production environment. This limitation applies to all Engineering Lifecycle Management applications including Jazz Authorization Server.

Procedure

  1. Create a file called createOauthOracle.sql. Refer to the following SQL example to create the tablespaces required for Jazz Authorization Server.

    Note that these values are examples and you should change them according to your usage and environment.

    createOauthOracle.sql creates two tablespaces: OAUTH and OAUTH_TMP, and creates the tables in the OAUTH tablespace. If you have an existing tablespace that you want to use, you can change the CREATE TABLE statement to use your existing tablespace. If you want to create new tablespaces, then you should edit the DATAFILE and TEMPFILE clauses to point at the directory that you want to use for the tablespace.

    Jazz Authorization Server expects to find a user named OAUTHDBSCHEMA, and it expects to find tables in a schema named OAUTHDBSCHEMA. You must create the OAUTHDBSCHEMA user in Oracle. Customize the CREATE USER statement to specify a password for the OAUTHDBSCHEMA user. When you create the OAUTHDBSCHEMA user, you must also associate that user with the tablespace you plan to use for the OAUTH tables.

    CREATE TABLESPACE OAUTH DATAFILE '/home/oracle/tablespaces/OAUTH.dbf' SIZE 1G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TEMPORARY TABLESPACE OAUTH_TMP TEMPFILE '/home/oracle/tablespaces/OAUTH_TMP.dbf' SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE USER OAUTHDBSCHEMA IDENTIFIED BY <your password> DEFAULT TABLESPACE OAUTH QUOTA UNLIMITED ON OAUTH TEMPORARY TABLESPACE OAUTH_TMP;
GRANT CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, CREATE VIEW TO OAUTHDBSCHEMA;

---- 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 NUMBER(19,0),
LIFETIME INT,
EXPIRES NUMBER(19,0),
TOKENSTRING CLOB DEFAULT ‘{}’ 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 DEFAULT '{}' NOT NULL
)
TABLESPACE OAUTH STORAGE(INITIAL 50K);

CREATE TABLE OAuthDBSchema.OAUTH20CLIENTCONFIG 
(
COMPONENTID VARCHAR(256) NOT NULL, 
CLIENTID VARCHAR(256) NOT NULL, 
CLIENTSECRET VARCHAR(256), 
DISPLAYNAME VARCHAR(256),
REDIRECTURI VARCHAR(2048), 
ENABLED INT,
CLIENTMETADATA CLOB DEFAULT '{}' NOT NULL
) TABLESPACE OAUTH STORAGE(INITIAL 50K);

CREATE TABLE OAuthDBSchema.OAUTH20CONSENTCACHE (
CLIENTID VARCHAR(256) NOT NULL,
USERID VARCHAR(256),
PROVIDERID VARCHAR(256) NOT NULL,
SCOPE VARCHAR(1024) NOT NULL,
EXPIRES NUMBER(19,0),
EXTENDEDFIELDS CLOB DEFAULT '{}' NOT NULL
) TABLESPACE OAUTH STORAGE(INITIAL 50K);

---- 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 PRIVILEGES ----
---- UNCOMMENT THE FOLLOWING IF YOU USE AN ACCOUNT OTHER THAN ADMINISTRATOR FOR DB ACCESS ----

-- Change dbuser to the account you want to use to access your database 
GRANT ALL ON OAuthDBSchema.OAUTH20CACHE TO OAUTHDBSCHEMA;
GRANT ALL ON OAuthDBSchema.OAUTH20CLIENTCONFIG TO OAUTHDBSCHEMA;
GRANT ALL ON OAuthDBSchema.OAUTH20CONSENTCACHE TO OAUTHDBSCHEMA;

---- END OF GRANT PRIVILIGES ----
  2. After you finish customizing the createOauthOracle.sql script, open SQL Plus and log in as SYSDBA, and run the SQL script: 
    @createOauthOracle.sql
  3. After creating the database tables, you must configure Jazz Authorization Server to use the tables. Go to the Jazz Authorization Server installation directory and open the appConfig.xml file for editing. The default path to the appConfig.xml file on Windows is C:\IBM\JazzAuthServer\wlp\usr\servers\jazzop and on Linux is /opt/IBM/JazzAuthServer/wlp/usr/servers/jazzop.
  4. Comment out the following Apache Derby database section: 
    <dataSource id="OAuthFvtDataSource" jndiName="jdbc/OAuth2DB">
        <jdbcDriver libraryRef="DerbyLib" />
        <properties.derby.embedded
            databaseName="asDB"
            createDatabase="create" />
    </dataSource>

    <library id="DerbyLib">
        <fileset dir="${shared.config.dir}/lib/global" includes="derby.jar" />
    </library>
  5. Add the following section to configure Oracle. You must customize this section to work with your specific Oracle server: 
    <jdbcDriver id="oracle" libraryRef="ORAJCC4LIB"/>
<library id="ORAJCC4LIB" filesetRef="orajcc4" apiTypeVisibility="spec,ibm-api,third-party"/>
<fileset dir="${shared.config.dir}/lib/global" id="orajcc4" includes="ojdbc8.jar"/>
<dataSource id="OAUTH2ORA" jndiName="jdbc/oauthProvider" jdbcDriverRef="oracle">
<properties.oracle password="*****" databaseName="ORCL" user="OAUTHDBSCHEMA" portNumber="1521" serverName="yourOracle.com">
</dataSource>

    Ensure that you have the correct values for the following attributes:

    • For password, use the password you specified when creating the OAUTHDBSCHEMA user in Oracle.
    • For databaseName, use the Oracle SID for your database. The sample uses the default value assigned by Oracle - ORCL. Your value might be different.
    • For portNumber, use the port number that the Oracle instance is running on. The default port number is 1521.
    • For serverName, enter the host name of the Oracle server.
  6. In the oauthProvider section of the appConfig.xml file, update the databaseStore property so it specifies the Oracle data source rather than the default Apache Derby data source. The databaseStore property should specify the name of the Oracle data source, which in this example, is OAUTH2ORA: 
    <oauthProvider id="JazzOP"
	    httpsRequired="true"
		autoAuthorize="true"
		customLoginURL="/jazzop/form/login" 
		accessTokenLifetime="7201" 
		authorizationGrantLifetime="604801">
		<autoAuthorizeClient>client01</autoAuthorizeClient>
  	     <databaseStore dataSourceRef="OAUTH2ORA" /> 
	</oauthProvider>
  7. Save and close the appConfig.xml file.
  8. Copy the Oracle ojdbc8.jar JDBC driver from your Oracle server to this directory: opt/IBM/JazzAuthServer/wlp/usr/shared/config/lib/global.