Connect WSO2 Identity Server with MySQL User Store

With the current advancement in the information technology nearly every application deals with data and resources that need to be protected. So having secure authentication and authorization is a must in most of the applications.

WSO2 Identity Server enables enterprise architects and developers to improve the user’s experience by reducing identity provisioning time, guaranteeing secure on-line interactions, and delivering a reduced single sign-on (SSO) environment. WSO2 Identity Server is fully open source and is released under Apache Software License Version 2.0.

By default all the wso2 products uses embedded H2 databases to store Users, Roles and Permissions. But it is possible to change this as follows.

• Store Users and Roles in one Repository ( User Store) - This an be RDBMS, an LDAP or an external Active Directory
• Store Permissions in a separate repository. - This should always be a RDBMS.

The embedded H2 databases is suitable for development, testing, and for some production environments. But it is recommended to use an industry-standard RDBMS such as Oracle, PostgreSQL, MySQL, MS SQL etc for most production environments. Setting up the databases is the first step in configuring the Identity server basic steps are as follows.

1. Creating database
2. Configure the data sources to point to the databases created.
3. Creating various tables required

This post gives information on how to configure Identity server to work with external MySQL database. For this I'm using Ubuntu server with Identity Server 5.1.0. Download Identity server from [1] and extract to a location on computer. (It will refer as PRODUCT_HOME here after.)


Creating the Database

1) Download and Install MySQL
   
    sudo apt-get install mysql-server mysql-client

2) Start the MySQL service. (Default port is 3306)
   
    sudo /etc/init.d/mysql start
   
3) Log in to the MySQL client as the root userby providing the root password.

    mysql -u root -p
   
4) Create the database with name regdb.

    create database regdb;
   
5) Crete a user named 'rgeadmin' with password 'regadmin' and grant all privillages on the created database.

    GRANT ALL ON regdb.* TO regadmin@localhost IDENTIFIED BY "regadmin";
   
6) Reload all privillages so that the changes will be in effect.

    FLUSH PRIVILEGES;

   
Configure the data sources in IS

1. Download the MySQL Java connector JAR file from [2], and copy it to the   PRODUCT_HOME/repository/components/lib/ directory.
2.  Edit the default datasource configuration in the PRODUCT_HOME/repository/conf/datasources/master-datasources.xml. Change the following parameters under datasources tab. 

<url>jdbc:mysql://localhost:3306/regdb</url>
<username>regadmin</username>
<password>regadmin</password>
<driverClassName>com.mysql.jdbc.Driver</driverClassName>

     3. Change the primary connection configuration for the User store as follows.
    

        Update the PRODUCT_HOME/repository/conf/user-mgt.xml as follows.

•  Uncomment the UserStoreManager with the name "org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager" under Realm tag.
• comment the existing default UserStoreManager with the name "org.wso2.carbon.user.core.ldap.ReadWriteLDAPUserStoreManager" 

Creating the tables Required

Restart the IS with follwing command with PRODUCT_HOME/bin.The setup option will create the required tables in the MySQL db for the user store.

./wso2server.sh -Dsetup 

Verify the Data store creation.

Check the data in the MySQL db tables. 

Ex: select * from UM_STORE; will show the admin user details.

[1]. WSO2 Identity Server

[2]. MySQL Connector