Skip to main content

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.
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.
Labels:

Comments

  1. TejutejuDecember 4, 2018 at 11:13 PM

    Really I enjoy your blog with an effective and useful information. Very nice post with loads of information. Thanks for sharing with us..!!..
    Oracle SOA Online Training Hyderabad

    ReplyDelete

Post a Comment

Popular posts from this blog

How to protect your APIs with self contained access token (JWT) using WSO2 API Manager and WSO2 Identity Server

In a typical enterprise information system, there is a high chance that people will use different types of systems built by different vendors to implement certain types of functionalities. The APIs might be hosted in an API Manager developed by vendor A and the user management can be implemented using a different vendor (vendor B). In this type of a situation, one system will not be able to directly contact the other system but they want to use both systems in tandem. Self-contained access tokens are used in these types of situations where applications can get the token from one system and use that in another system to access protected resources. In this scenario, the second system does not need to make a contact to the first system over the network to validate the user information since the token is self-contained and it has relevant details about the user. This will improve the token processing time significantly since it completely removes the network interaction. The below fig...
1 comment
Read more

WSO2 ESB creating a response for a "GET" request

When you are creating REST APIs with WSO2 ESB, you may need to send some response message back to the user when something goes wrong. In this kind of scenario, you can create a payload inside the ESB and send it back. For doing this, you can use the below configuration. <api xmlns=" http://ws.apache.org/ ns/synapse " name="LoopBackProxy" context="/loopback">    <resource methods="POST GET">       <inSequence>          <property name="NO_ENTITY_BODY" scope="axis2" action="remove"></property>          <log level="full"></log>          <payloadFactory media-type="xml">             <format>                <m:messageBeforeLoopBack xmlns:m=" http://services...
Post a Comment
Read more

WSO2 ESB usage of get-property function

What are Properties? WSO2 has a huge set of mediators but property mediator is mostly used mediator for writing any proxy service or API. Property mediator is used to store any value or xml fragment temporarily during life cycle of a thread for any service or API. We can compare “Property” mediator with “Variable” in any other traditional programming languages (Like: C, C++, Java, .Net etc). There are few properties those are used/maintained by ESB itself and on the other hand few properties can be defined by users (programmers). In other words, we can say that properties can be define in below 2 categories: ESB Defined Properties User Defined Properties. These properties can be stored/defined in different scopes, like: Transport Synapse or Default Axis2 Registry System Operation Generally, these properties are read by get-properties() function. This function can be invoked with below 2 variations. get-property(String propertyName) get-property(String scop...
Post a Comment
Read more