Access to WSO2 Databases with DBeaver

When you take almost all WSO2 products out of the box (OOTB) you will use a H2 embedded database. This is easily exchanged for most major RDBMS databases with help of the sql scripts in the /dbscripts directory. For more information on how to do this, read this blog on WSO2 and MariaDB. In this blog I will tell you how to access WSO2 Databases with DBeaver.

H2

As said, H2 is the embedded database for the user and registry data of WSO2 products, with the exception of Identity Server which has an Embedded LDAP (and an H2 database for registry data). The database is hardly visible, other than through the management UI of WSO2. By default, there is no view on the database, other than the fact that it is for instance in the [EI-HOME] /repository/database/WSO2CARBON_DB.

There is a possibility to browse through and change the databases but:

1. You need to add something to the configuration when browsing in a window
2. The command line interface is really only for hardcore users
3. You need to restart the WSO2 product when adding to the carbonfile
4. You need to have access to the commandline to enter the java command to enter the H2 sql tools

In order to have access and change the database you need to enter this command in the plugins directory.

java -cp h2_1.3.175.wso2v1.jar org.h2.tools.Shell

This will give you a very hardcore command line user interface.

Browsing the database

What you need to change to browse the H2 database is adding this (or partly removing the comments from the existing definition in the [Ei-HOME]/conf/carbon.xml 

For other products the carbon.xml is in the /repository/conf directory.

<H2DatabaseConfiguration>
        <property name="web"/>
        <property name="webPort">8082</property>
        <property name="webAllowOthers"/>       
</H2DatabaseConfiguration> 

Leave it to DBeaver

But there is also another possibility in the form of  the open source database manager DBeaver. This open source, Java developed tool is based on Eclipse and also easily integrates with the popular IDE.

DBeaver

But H2 is not the only database DBeaver supports. The list is quite substantial with the likes of:

• MySQL
• Oracle
• PostgreSQL
• IBM DB2
• Microsoft SQL Server
• Microsoft Access
• Sybase
• Java DB (Derby)
• Firebird (Interbase)
• Derby (JavaDB)
• SQLite
• Mimer
• HSQLDB
• H2
• IBM Informix
• Teradata
• SAP MAX DB
• Cache
• Ingres
• Linter
• Vertica
• ODBC
• Any JDBC compliant data source

This is a substantial list and is complemented by the supported NoSQL databases like MongoDB and Cassandra. As far as supported OSes are Windows (2000/XP/2003/Vista/7/8/10), Linux, Mac OS and Solaris (x86).

Eclipse

As you can see DBeaver shows its Eclipse heritage. This is the completely standalone version running on Windows. You start by creating a connection. Let’s make a connection to H2.

What is striking is that the WSO2 product does not need to be running in order to make a connection.

We will start the Enterprise Integrator once to create the admin account (done automatically) and also another user called Robert with a password. This password, let us say it is actually ‘password’ will be the core of the second part of this blog. 

We right-click on new connection and Create a new connection. Select H2 (embedded) for the version.

We enter the userid and password (wso2carbon/wso2carbon) and edit the Driver to indicate the fully qualified path to the database. We go back to the definition screen and actually test the connection.

The final screen shows some more settings but we leave them for now and click finish.

We will not show every detail of DBeaver but like to highlight some neat features.

There is an ER Diagram show the tables and relationships.

We can actually look inside the tables. We select the UM-USER table and select data.

You can see the encrypted password and the UM_SALT_VALUE.

We can now easily switch the passwords for admin and Robert and by doing that actually hack the system. We simply write over the existing password. Copy the password and salt values for robert to admin and save the changes.

Suddenly we are no longer able to login with the regular admin password.

This goes to show that we need to be careful with the UID / PASSWORD combination, even for the databases.

Changing the password

There is an opportunity to change the password. This is best done using the DBeaver connection.

We are going to add a user rather than changing the password of wso2carbon. One of the reasons is that wso2carbon is also the user for the connection from DBeaver to H2. If you do want it to change, also change the password in the connection to H2 in DBeaver.

ALTER USER wso2carbon SET PASSWORD ‘newpass’

If you want to create a new user, use a command like this:

CREATE USER wso2h2user PASSWORD ‘secret’ ADMIN

Also, do not forget to change the values in the conf/datasources/master-datasource.xml

If you forget that, you are not able to connect to the H2 instance.

If you have any questions about this blogpost contact us via the comments section below. View also our WSO2 Tutorials, webinars or white papers for more technical information. Need support? We do deliver WSO2 Product Support, WSO2 Development Support, WSO2 Operational Support and WSO2 Training Programs.