How to Access and Manipulate the Embedded DB in WSO2 Identity Server

Hello everyone... After a long long time. I was writing another article about some interesting tricks in the Google Firestore, how my life went with my final year research, and why I couldn't write an article for a year. But had to postpone writing that article and will publish it as soon as possible. So here we are. Let's start the blogging journey again with this article!

I started to work in the Identity Access Management (IAM) team at WSO2 after my graduation. So... we have an amazing product called 'Identity Server (IS)' to help organizations and developers to integrate user authentication and authorization facilities into their softwares very easily. New domain for me but glad to get that experience. In the Identity Server product, we can use its H2 embedded database for testing purposes and we can view its data tables by using DBeaver. Today we are going to view the 'users' table in WSO2 IS through the DBeaver tool.

What is DBeaver?

DBeaver is a free and open-source universal database tool for anyone who needs to work with databases. It supports all popular databases and it supports any database having a JDBC driver. Some popular databases DBeaver supports are MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, H2, Sybase, MS Access, MariaDB, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc. You can download the free version of the DBeaver from here.

What is H2 Database?

H2 is an open-source, lightweight, and fast Java SQL database engine. The H2 database is initially released in 2005 by Thomas Mueller. It can be embedded in Java applications or run in the client-server mode. As H2 is an embedded DB, it's not recommended in production environments. But highly recommended for development and testing environments. That's why WSO2 Identity Server has this embedded H2 DB. Therefore, anyone can download the product and test it without worrying about the DB configurations initially.

WSO2 Identity Server Default Databases

In WSO2 IS database directory, you can find the following default databases.

1. Identity database: The database, WSO2IDENTITY_DB.mv.db consists of identity related data.
2. Shared database: The database, WSO2SHARED_DB.mv.db contains the registry and user management data.
3. Carbon database: The database, WSO2CARBON_DB.mv.db has the internal data related to the product. This data is stored in the embedded H2 database.
4. Workflow database: The database, jpadb.mv.db contains workflow related data.

Accessing the H2 Database in WSO2 IS with DBeaver

Download the latest WSO2 identity server, run it, and create some sample users. Click here for a step by step guideline to install and run WSO2 IS. You can click here if you need to know how to add a new user. Then stop the identity server because this H2 DB is getting locked by a single connection.

Then open the DBeaver application and create a new database connection. Select 'H2 Embedded v.2' in the 'SQL' category as the database type. Then copy the path to the 'WSO2SHARED_DB.mv.db' file in your downloaded WSO2 IS and paste it as the {path} section in the JDBC URL. In my MacBook, the path for 'WSO2CARBON_DB.mv.db' file in the identity server is '/Users/ravinduperera/Downloads/wso2is-6.0.0/repository/database/WSO2SHARED_DB.mv.db'.

'wso2carbon' is the default username and password for database authentication. Use that default username and password. Then finish creating the connection. Here we use `WSO2SHARED_DB` because we are going to view the newly added users in the database. As we mentioned above, there are 4 default databases and you can connect to any of them. As an example, if you need to view service providers in the database, you have to connect to `WSO2IDENTITY_DB`.

After connecting to the H2 DB, you can see the list of tables in the sidebar of the database navigator as below.

Right click on the 'UM_USER' table and click on the 'View Data' option in the menu. Then you can view the newly added users through DBeaver. Here you can add, edit, and delete data. And you can even edit table structures if you want for some testing purposes.

Hope you got it completely... If you need any further clarification on this or if you have any concerns about this tutorial, you can post them as comments below. Cheers...