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 wso2server.sh or wso2server.bat under
  IS_HOME/bin/ directory, 

          you can use the,

                     sh wso2server.sh or  ./wso2server.sh 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 URL : jdbc: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