Thursday, September 05, 2013

Deploying Identity Server over a JDBC Based User Store

With this post I am to demonstrate how to configure WSO2 Identity Server with a JDBC user store. For the demonstration I am using a MySQL user store, but same procedure applies to any other JDBC user store as well.
My environment is,
OS - Ubuntu 12.10
Java - 1.6
WSO2 IS 4.5.0
  1. Setting up MySQL database
  2. User Store Configuration in IS - Primary
  3. User Store Configuration in IS - Secondary
(I am referring to extracted wso2is folder as CARBON_HOME in this post)

Setting up MySQL database

We need MySQL running at first. This post will be helpful in setting up the MySQL database, if it's not already done. Once MySQL is running we have to set up the database as required by the Identity Server. The server packs the necessary sql scripts within itself, which can be located at CARBON_HOME/dbscripts. 

Let's login to MySQL server and execute the following,
Create a database,
mysql> create database JDBC_demo_user_store;
Check out the creation,
mysql> show databases; 
Then use the sql script and set up the database,
mysql> use JDBC_demo_user_store;
mysql> source <path_to>/wso2is-4.5.0/dbscripts/mysql.sql; 
This will run the queries in the SQL scripts and set up the required tables.
Now if we enter the commands following outputs will be shown.
mysql> show tables;

Now we are done with setting up the database. We can go ahead and ask Identiy Server to use it.

Note: Before going into the following steps we also need to add the mysql-jdbc connector to Identity Server. You can download it from here and drop it into CARBON_HOME/repository/components/lib.

User Store Configuration in IS - Primary 

Identity Server uses embedded H2 database to keep permission details etc. and the data source details of it resides in CARBON_HOME/repository/conf/datasources/master-datasources.xml. We can add data-source details of our new JDBC user store here as well. Here is the master-datasources.xml file according to my set-up.

            <description>The datasource used for JDBC_demo_user_store</description>
            <definition type="RDBMS">
                    <validationQuery>SELECT 1</validationQuery>
The  Primary configuration for user store resides at CARBON_HOME/repository/conf/user-mgt.xml file. By default this is pointing to a embedded ReadLDAPUserStoreManager. Now we are to change it to be a JDBCUserStoreManager. So let's comment out the default one and uncomment JDBCUserStoreManager. Now we will have a user-mgt.xml file similar to this, with the <Property name="dataSource"></Property>  property being set to what is given at datasource. If we want, we can modify these properties as we want, according to the context.

Now the configurations are over. Let's start the server with bin/ Once started if we go ahead and add user to the 'Primary' domain.

Now if we go and check the UM_USER table created in our database, it will list user as well.

User Store Configuration in IS - Secondary

Now let's see how we can use that same MySQL user store as a secondary user store in IS. This is pretty easy that we can do the whole thing via UI, without any modification to the above default configurations in master-datasources.xml or user-mgt.xml. We have to add driver name, URL, user name and password here as mandatory properties which we previously gave at master-datasources.xml.

Once added it will be shown in the available user stores list. It intuitive to define a user store manager in UI, but if you want more details, you can refer this post. If we want we can also edit the optional properties too. The advanced section carries the SQL statements required for JDBC user store manager.

Advanced Option: If we are editing database structure(sql script), we need to update these SQL queries according to that schema, using this Advanced option.

Now if we go and try add a new user, we will see this secondary domain as well.

We can see the users getting added in the database as same as it was in the Primary user store, if we select this domain and add the users.



  1. Good introduction If I setup a secondary store and manage multiple tenants, how do i login as a 'tenant-administrator' where my visibility is limited to this tenant alone.

    1. Tenant admin credentials are stored at Primary domain, as a solution for this.

  2. This comment has been removed by the author.

  3. Hi Pushpalanka -

    Nice post! Running IDP 4.1.0 (custom so locked for now), how can I map a claim to a complex SQL that can be then shared with my SSO service providers via the attribute profile option? For more clarity in my case, to ascertain the role of the user we are authenticating, one cannot look at just a simple table.column within my oracle database but rather must take the username and run a sql against a multitude of tables (through a JOIN or UNION, etc). Thanks for any guidance.

    1. Hi,

      For this specific customization, I think ideal solution will be move with a customized JDBC user store. It would be helpful to refer this post,

      We can these attributes to the SP via attribute profile. If you need this role only for SAML SSO scenarioes you can just make use of this approach as well,


  4. Hi Pushpalanka,

    Thanks so much for your informative posts! I've followed your guide for setting up a primary user store using MySQL. I receive the following error when I try to login as admin via the admin website:

    Failed Administrator login attempt 'admin[-1234]' at [2015-01-08 22:31:03,647-0500]

    This is a fresh install of WSO2IS 5.0.0. The database was initialized using the -Dsetup property. The UM_USER table show the admin similar to your blog post. In addition to the steps in your guide, I have configured the datasource in master-datasource.xml, disabled the embedded ldap server in embedded-ldap.xml, and commented out the ldap tenant in tenant-mgt.xml.

    When I start the server I do not see any errors besides the login error. The database also appears to have all the information, but the admin web application does not let me login as admin.

    I also noticed that the tenant id is -1234. Is that an expected default or indicate a potential multi-tenant error?

    Any direction you can offer is greatly appreciated.

    1. Hi Bryce,

      I couldn't locate any error in the mentioned procedure you have followed. The tenant id -1234 is the default value for super tenant, which is the default tenant space we will be using. Hence there is no potential multi-tenant error there.

      If the username and password are correctly provided as well, there can be some possible error at authorization step regarding the roles and permissions. To isolate on that you can enable debug logs for the server and check.

      To enable debug logs,
      In repository/conf/ file enable DEBUG level logs for the package 'org.wso2.carbon.user.core'. This will most probably give you a hint on the root cause for the error.


    2. This comment has been removed by the author.

    3. I got it working...Thanks so much for your help!!! Kudos!

  5. Hi.

    The links to the examples xml files are down. Can you share me that files please?


    1. Hi Sergio,

      Please download the files from below links,

      master-datasource.xml -
      user-mgt.xml -