Testing WSO2 Identity Server on Oracle Database using docker

This is a step-by-step guide to configure the Oracle database as the data source in the WSO2 Identity Server. Nevertheless, you can use this guide to create an Oracle DB for any of your purposes in quite an easy way.

Let's get started!!

Download the Docker image

We use Oracle Database Server Docker Image containing the Oracle Database Server 12.2.0.1 Enterprise Edition running on Oracle Linux 7 for this guide.

1. Sign in to https://hub.docker.com/ using your credentials. (If you don’t have an account yet signup now)
2. Access the Oracle database docker image from https://hub.docker.com/_/oracle-database-enterprise-edition
3. Click on “Proceed to Checkout”.

4. Provide contact information, accept the terms and conditions and click on “Get Content” button.

5. Then you will get the following instruction page. Copy the image on right-hand side panel to download the docker image.

6. Before pulling the docker image, you need to log in to the docker hub from your console. So, open a terminal on your machine and log in using the following command. Once the correct username, password are given Login Succeeded.

docker login

7. Pull Oracle database docker image. It will take some time to download. The image size is~3GB.

docker pull store/oracle/database-enterprise:12.2.0.1

Logs on your terminal when the image is downloaded

This docker image pulling is a one-time task. You can create different database instances by creating different containers.

Create the docker container

1. Execute the following command to create an oracle DB server instance named (oracle_db).

docker run --name oracle_db -p 1521:1521 -p 5500:5500 store/oracle/database-enterprise:12.2.0.1

This will take some time to set up the container. Once the container is started you will the terminal as follows. Keep that terminal as it is.

2. Open another terminal and execute the following command to check the status of the container. Its status should be up (Up 10 minutes (healthy))

docker ps -a

If the container is not started, use the following command to start the container.

docker start <CONTAINER ID>

Log on into docker container

1. Execute the following command. (Change the nameoracle_db if you changed the container name when creating)

docker exec -it oracle_db "bash"

2. Then log in as the sysdba.

sqlplus / as sysdba

Create a new user and grant permission to that user

Execute the following commands one by one.

1. Before creating the user set “_ORACLE_SCRIPT” param to true, to avoid errors such as ORA-65096: invalid common user or role name

alter session set "_ORACLE_SCRIPT"=true;

2. Create a user. (username-admin, password-password)

create user admin identified by password;

3. Grant permissions to create sessions, tables, triggers, sequences to the above-created user.

GRANT CREATE SESSION TO admin;
GRANT CREATE TABLE TO admin;
GRANT CREATE TRIGGER TO admin;
GRANT CREATE SEQUENCE TO admin;
GRANT CREATE PROCEDURE TO admin;

4. Set the user quota on USERS tablespace.

alter user admin quota 100M on USERS;

Let’s connect the created DB via the Oracle SQL Developer tool

1. If you haven’t downloaded Oracle SQL developer previously, Download it from https://www.oracle.com/tools/downloads/sqldev-downloads.html and extract the zip.
2. Execute the sqldeveloper.sh file.

sh ./sqldeveloper.sh

3. Click on the Green plus button to add a new connection, and provide the following details.

Connection Name — oracle_db (Name can be any name)
Username — admin (Created user's username)
Password — password (Created user's password)
Hostname — localhost
Port — 1521
Service Name — ORCLCDB.localdomain

Click on the “Test” button to check the connection status (fail/pass) then click on the “connect” button to create the connection with the DB.

4. You can execute the DB queries to create tables on the created SQL worksheet for the created DB connection.

oracle.sql files inside <IS_HOME>/dbscripts folder contains the DB table/ Idenx/ triggers creation scripts (i.e DDL).

Refer Docs: https://is.docs.wso2.com/en/latest/setup/working-with-databases/ to find out which files contain the specific DB scripts.

For Identity DB:

• <IS-HOME>/dbscripts/identity/oracle.sql
• <IS-HOME>/dbscripts/identity/uma/oracle.sql
• <IS-HOME>/dbscripts/consent/oracle.sql

For Shared DB:

• <IS-HOME>/dbscripts/oracle.sql

Setting up the IS with your created Oracle DB.

The following configs are written based on IS-5.11.0.

NOTE: If you are using IS-5.9.0 or above check the relevant deployement.toml configs/ a version below IS-5.9.0 check the relevant configs in relevant xml files by that product version docs.

Add the following configs to <IS_HOME>/repository/conf/deployment.toml file based on the database that you change to Oracle DB.

NOTE: Always define the service name of Oracle DB as the sid config of the following.

If you are using Oracle DB as Identity database:

[database.identity_db]
type = "oracle"
hostname = "localhost"
sid = "ORCLCDB.localdomain"
username = "admin"
password = "password"
port = "1521"

If you are using Oracle DB as the Shared DB:

[database.shared_db]
type = "oracle"
hostname = "localhost"
sid = "ORCLCDB.localdomain"
username = "admin"
password = "password"
port = "1521"

If you are using Oracle DB as the Primary User store:

[user_store]
type = "database_unique_id"
[database.user]
url = "jdbc:oracle:thin:@localhost:1521/ORCLCDB.localdomain"
username = "admin"
password = "password"
driver = "oracle.jdbc.OracleDriver"
[realm_manager]
data_source = "WSO2USER_DB"

Before starting the Identity Server

1. Download corresponding JDBC connector. from https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

2. Copy the JAR file to <PRODUCT_HOME>/repository/components/lib.

3. Start the server.

We are done!! Now, test the WSO2 IS with Oracle DB 🙂Happy Testing 🤗😉