Skip to main content

Configuring a MySQL database as a secondary user store for WSO2 Identity Server

It's been almost a week since I joined WSO2. I am now part of the WSO2 Identity Server team :)
So my adventures from now on will revolve around the Identity Management area and security stuff. We are currently on our way to release Identity Server 5.2.0 beta. During alpha testing, I learnt several basics that I thought worth making a note of. Hence, this post as both a note to myself and anyone starts off with WSO2 Identity Server.

A user store is basically where WSO2 IS stores all information about users such as username, password, roles etc.WSO2 Identity Server comes with a built-in LDAP-based primary user store out of the box. This is true for other WSO2 products as well.

However, you also have the option of configuring any JDBC database,external LDAP or an Active Directory as the secondary user store in WSO2 IS and other products.

I will focus on setting up a MySQL database as the secondary user store in WSO2 IS in this post. I will divide the process into to three parts,
  1. Getting the MySQL database ready
  2. Adding the MySQL database as the secondary user store in WSO2 Identity Server
  3. Adding a User to the secondary user store

Before we get started you need to download and extract the WSO2 Identity Server. I am using the 5.2.0-beta version at the time of writing this.  You can download the latest stable version from here.
Download and extract the zip file. Let's call the root of your WSO2 Identity Server installation IS_HOME.

1. Getting the MySQL database ready

  • The first step of getting the MySQL database is to install a MySQL database. I use the PHPMyAdmin that comes with XAMPP to create and manage the databases. You can follow this to get XAMPP up and running on your Ubuntu machine. For Windows it's basically downloading the binary and letting the setup do the work :)

  • Now that you have MySQL running, go ahead and create a database, let's name it "is_test".

  • You need to create the UserStore database tables manually. You can do this easily by running the MySQL database script available at IS_HOME/dbscripts/identity/mysql.sql, where $IS_HOME is the root directory of you WSO2 Identity Server installation.

  • Now that you are done with creating the MySQL database for the user store, Let's move on to connecting it to WSO2 Identity Server.

2. Adding the MySQL database as the secondary user store

  • We need a JDBC connector(MySQL connector in our case) to connect to the MySQL database from the WSO2 Identity Server. You can download the version of the mysql-connector-java compatible with the MySQL version from here.

  • Once you download the mysql-connector-java, copy the "mysql-connector-java-<version>-bin.jar" jar to IS_HOME/repository/components/lib folder

  • Now start the WSO2 Identity server by executing the or wso2server.bat under
    IS_HOME/bin/ directory, 
          you can use the,
                     sh or  ./ commands to do this in Ubuntu

  • Once the server is started, type in the URL "https://localhost:9443/carbon/" on your browser and login using the default credentials in the management console,   
                             username : admin
                             password : admin

  • Once you are logged in, you will see the management console as shown below,

  • Under the Identity Section, You can find the User Stores section, click on Add button to start adding the secondary user store.

  • In the "Add New User Store" page, 
         Select "User Store Manager Class" as org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager

         Then your page will change to define the properties required to set up the JDBC User Store as            shown below.

        You need to enter the following properties,
    •         Domain : An identifier for your user store, eg: JDBC
    •         Connection URLjdbc:mysql://localhost:3306/<database_name>
    •         Connection Name : <username_to_connect_to_database>
    •         Connection Password : <password_to_connect_to_database>
    •         Driver Name :  com.mysql.jdbc.Driver

  • Once you enter the properties, you can test the connection by clicking on the "Test Connection Button", It should give a "Connection is healthy" or similar success message if the WSO2         Identity Server can successfully connect to the database.

  • Finish the setup by clicking on "Add", it may take a moment for the user store to get registered. You can view it by clicking on User Stores --> List

3. Adding a User to the secondary user store

Now that we have the MySQL database set up as the secondary user store, Let's play around with it.
Let's create a user and add him to the secondary user store.

  • Go to Users and Roles --> Add --> Add New User,

  • As shown above, you will notice the secondary user domain listed in the drop down the select user domain for the user. Select the secondary user domain and continue creating the user :)

The above steps can be used to add any JDBC User Store like DB2, Derby, H2, Informix and other supported SQL databases by the WSO2 Products. 

The only things that need to be changed are,
  1.  the database scripts to be executed when creating the database for the user store ( These can be found under IS_HOME/dbscripts )
  2.  the relevant JDBC connectors for each database type


  1. I think this is the best article today. Thanks for taking your own time to discuss this topic, I feel happy about that curiosity has increased to learn more about this topic. Keep sharing your information regularly for my future reference.
    Java Courses in chennai


Post a Comment

Popular posts from this blog

JWT Bearer Grant - OAuth2

Previously I wrote a post on my first step towards understanding OAuth. This post continues builds on that. OAuth has different types of flows targeting various scenarios or use cases. The main feature that differentiates each of these flows is the grant type.

What exactly is an OAuth grant type? An OAuth grant is something that a client application could exchange for an access token from an Authorization Server. An access token typically represents a user's permission for the client application to access the resources on their behalf
OAuth Grant Types The OAuth 2.0 core specification defines four types of grants,
Authorization code grantImplicit grantResource owner credentials grantClient credentials grant In addition to these the core specification also defines a refresh grant type.
There are few new additions to these as well,
Message authentication code (MAC) tokensSAML 2.0 Bearer Assertion ProfilesJSON Web Token grant
I would like to focus on the JSON Web Token Grant in this po…

Trying out OAuth2 Authorization Code grant with WSO2 Identity Server without the PlayGround2 App

The first thing I did after joining the WSO2 Identity Server team was to test the WSO2 Identity Server 5.2.0-beta pack. I had some experience playing around with OAuth so I started testing OAuth scenarios. I was able to test most grant types with ease. Then came the authorization code grant type. The usual way to test it was to setup the playground2 app and test. I wanted to look for an alternate way to test the Authorization grant type without setting up the app (partly because I was lazy to download tomcat etc. :P )

So with the help of my team member Pushpalanka, I found an alternate way to get an access token by simply using a browser redirect and a curl command. So I wanted to make a note in case someone wanted to do the same :)

1. First, log in to the Identity Server management console.
       the defaults are,
                  username = admin                   password = admin

2. Go to the Service Provider configuration page and create a Service Provide, let's say SP_lazy…

OAuth2 Authorization Code flow without client secret using WSO2 Identity Server

Quoting from

Single-page apps (or browser-based apps) run entirely in the browser after loading the source code from a web page. Since the entire source code is available to the browser, they cannot maintain the confidentiality of their client secret, so the secret is not used in this case. The flow is exactly the same as the authorization code flow above, but at the last step, the authorization code is exchanged for an access token without using the client secret.  Note: Previously, it was recommended that browser-based apps use the "Implicit" flow, which returns an access token immediately and does not have a token exchange step. In the time since the spec was originally written, the industry best practice has changed to recommend that the authorization code flow be used without the client secret. This provides more opportunities to create a secure flow, such as using the state parameter. References: RedhatDeutsche TelekomS…