Creating An Oracle Datasource for WSO2 Identity Server with DBeaver

In this blog, I am going to explain step by step guide on creating a new schema in Oracle DB and configuring WSO2 Identity Server to use the Oracle datasource.

First let’s create the Oracle schema.

Download DBeaver from https://dbeaver.io/download/

Start DBeaver with ./debeaver. This will open up DBeaver UI.

Connect to Oracle using the client as SYSDBA.

Click create new connection from DBeaver UI.

Select oracle as connection type and enter the following details.

Host : <Host name of oracle instance>

Database: SID of Database

User name: Username of SYSDBA

Password: Password of SYSDBA

You need to add the oracle JDBC driver jar file to DBeaver by clicking Edit Driver Settings.

Under libraries, select ‘add file’ and give path to the jar file. If you haven’t already downloaded the driver, you need to download the version of the oracle driver that is compatible with your oracle server version. Click on the web site url to download the compatible jar version.

Define a proper ‘driver name’ there and Click OK.

Click next.

Click next in respective windows to go with default settings. Give a suitable name for the connection e.g. Oracle -System and click ‘Finish’.

Right click on the Oracle — System connection displayed in Database navigator and click on ‘SQL Editor’. This will open up a new SQL editor for newly created connection.

Execute the following queries in the editor to create a database user and grant privileges to the user as shown below.

Create user <USER_NAME> identified by password account unlock;
grant connect to <USER_NAME>;
grant create session, create table, create sequence, create trigger to <USER_NAME>;
alter user <USER_NAME> quota <SPACE_QUOTA_SIZE_IN_MEGABYTES> on '<TABLE_SPACE_NAME>';
commit;

Now re-connect to Oracle instance using this new user. You can do so by clicking on ‘new connection’ in DBeaver and give this new <User name> for the credentials.

In the new connection , you should be able to find <User name> under Schemas section.

Open a new SQL editor for the new connection.

You can execute the Oracle queries which can be found in <IS_HOME>/dbscripts/oracle.sql and <IS_HOME>/dbscripts/identiy/oracle.sql by copying the script content to the editor and enter alt + x command.

Now copy the Oracle queries in <IS_HOME>/dbscripts/oracle.sql to the editor.

NOTE: DBeaver will complain regarding the delimiter used in the queries to seperate each query. Which is ‘/’. The delimiter can change depending on the DB client used and DBeaver uses semicolon ‘;’ to delimit individual queries in a script. Select All in the script and go to Edit > Find and Replace and replace all occurrences of ‘/’ with ‘;’.

Execute the Queries in the editor as a script using alt + x command.

This will create all the required tables required for user and registry management.

copy the Oracle queries in <IS_HOME>/dbscripts/identity/oracle.sql to the editor.

Repeat the above step to replace delimiter with ‘;’.

Execute the queries using alt + x.

This will create all the required tables required for identity management.

Now let’s configure Identity Server to use this Oracle datasource that we just created.

Go to <IS_HOME>/repository/conf/datasources/master-datasource.xml

Do not make changes to WSO2_CARBON_DB datasource which points to the internal H2 database. This is because WSO2_CARBON_DB is used by the local registry for storing instance specific data.

Let’s add a new source similar to following.

<datasource>
 <name>WSO2_Oracle_DB</name>
 <description>The datasource used for registry, user management and identity</description>
 <jndiConfig>
 <name>jdbc/WSO2OracleDB</name>
 </jndiConfig>
 <definition type=”RDBMS”>
 <configuration>
 <url>jdbc:oracle:thin:@<oracle_instance_ip>:1521/ora11g</url>
 <username>sathya</username>
 <password>sathya</password>
 <driverClassName>oracle.jdbc.OracleDriver</driverClassName>
 <maxActive>80</maxActive>
 <maxWait>60000</maxWait>
 <minIdle>5</minIdle>
 <testOnBorrow>true</testOnBorrow>
 <validationQuery>SELECT 1 FROM DUAL</validationQuery>
 <validationInterval>30000</validationInterval>
 <defaultAutoCommit>false</defaultAutoCommit>
 </configuration>
 </definition>
 </datasource>

Now change identity.xml file and user-mgt file to use this datasource.

Update the value of property with name = “dataSource” , to jdbc/WSO2OracleDB in user-mgt.xml file in <IS_HOME>/repository/conf directory.

Follow the same for identity.xml file in <IS_HOME>/repository/conf/identity directory.

Now we need to mount the WSO2 shared regisry to Oracle database.

change the dbConfig named “sharedregistry” to following in <IS_HOME>/repository/conf/registry.xml.

<dbConfig name=”sharedregistry”>
 <dataSource>jdbc/WSO2OracleDB</dataSource>
</dbConfig>
<remoteInstance url=”https://localhost:9443/registry">
 <id>sharedregistry</id>
 <dbConfig>sharedregistry</dbConfig>
 <readOnly>false</readOnly>
 <registryRoot>/</registryRoot>
 <enableCache>true</enableCache>
 <cacheId>jdbc:oracle:thin:@<oracle_instance_ip>:1521/ora11g</cacheId>
</remoteInstance>
<mount path=”/_system/config” overwrite=”true”>
 <instanceId>sharedregistry</instanceId>
 <targetPath>/_system/config</targetPath>
</mount>
<mount path=”/_system/governance” overwrite=”true”>
 <instanceId>sharedregistry</instanceId>
 <targetPath>/_system/governance</targetPath>
</mount>

Note: do not make any changes to the dbConfig with name “wso2registry” as this is used by the local registry for instance specific server.

Add the compatible oracle jdbc driver jar file to <IS_HOME>/repository/components/lib directory.

Now restart the server and access the management console to verify the functionality.