Understanding the database scripts of WSO2 products

WSO2 Carbon based products require some databases to be created for operation. Every WSO2 product comes with a folder named dbscripts (CARBON_HOME/dbscripts) and that folder contains database scripts for different types of databases. These scripts creates 3 set of tables by default.

1) Registry tables -These are related to registry artifacts of the WSO2 product.

 2) User management tables – These tables are related to user management of the server. All the user permissions and roles related information will be saved in these tables. By default these are created in internal H2 database. ( We can create a separate database schema for this if we want)

3) User Store tables – These tables are related to creating actual users which are create in the server. These tables will not be used if we have pointed to external LDAP user store.

From the above 3 set of tables, we will only need to focus on user management tables since we have created other tables in external LDAP and database. What we can do is, update the SQL script such that the required permissions and roles are created during the startup time or otherwise we can run a separate SQL query after the server has started. 

You can browse the internal H2 database by doing a small configuration change.

Open the carbon.xml file (ESB_HOME\repository\conf\carbon.xml) and edit the <H2DatabaseConfiguration> as given below.

<H2DatabaseConfiguration>
<property name=”web” />
<property name=”webPort”>8083</property>
<property name=”webAllowOthers” />
<!–property name=”webSSL” />
<property name=”tcp” />
<property name=”tcpPort”>9092</property>
<property name=”tcpAllowOthers” />
<property name=”tcpSSL” />
<property name=”pg” />
<property name=”pgPort”>5435</property>
<property name=”pgAllowOthers” />
<property name=”trace” />
<property name=”baseDir”>${carbon.home}</property–>
</H2DatabaseConfiguration>

Then restart the ESB server. Now you can access the H2 database from the H2 browser by accessing the following URL.

http://localhost:8083

Once you go in to the browser page, you need to give the location of the H2 database.

url:                ESB_HOME/repository/database/WSO2CARBON_DB

username:    wso2carbon

password:     wso2carbon

Now you can see the User Management tabled with the prefix UM_. You can use this browser for experimenting with the permissions and then write the required sql script which can be executed after the server is started.

If we point this WSO2CARBON_DB to an external database like Oracle (Which is the preferred way for a production setup) we can do the same for that database as well.