Deploying WSO2 Identity Solution over an existing MySQL user store

WSO2 Identity Solution can be used as an Information Card provider as well as an OpenID Provider.

This post explains how you can customize WSO2 Identity Solution to expose an existing user base residing on a MySQL database - and facilitate them with Information Cards and OpenID logins.

Let me further explain this scenario.

You have a set of users with a set of attributes defined for each.

Now the requirement is your company wants you to assign each of your users an OpenID and also run an OpenID Provider your self - and you need to do minimal changes to the existing system.

I'll explain everything you need to know here in a step-by-step manner.

Setting up the existing environment

- Download WampServer 2.0 from here and install it locally.

- Start the wampserver and run MySQL service.

- Add [WAMP_INSTALLED_LOCATION]\bin\mysql\mysql5.0.51b\bin to the PATH env variable.

:\>mysqladmin -u root password mysql

:\> mysql -u root -p

[type your password : mysql]



mysql>CREATE TABLE `users` (`uid` varchar(60) NOT NULL,`name` varchar(60) NOT NULL,`pass` varchar(32) NOT NULL,`mail` varchar(64) ,`openid` varchar(60) NOT NULL, `firstName` varchar(60) NOT NULL,`lastName` varchar(60) NOT NULL,PRIMARY KEY (`uid`));

mysql>INSERT INTO users VALUES ('prabath','prabath','prabath','','http://localhost:12080/user/prabath','prabath','siriwardena');


Now we are done with setting up the existing environment.

You may have already noticed that for my convenience I created the 'users' table with an 'openid' column - which you may not have in your existing 'users' table. In that case you need to alter the table 'users', add the new column 'openid' and populate that column with values derived from the 'uid' column - which will create unique OpenIDs for all your users.

Building & deploying WSO2 Identity Solution from source

- Download the latest code from the SVN repo:

- Then, from the root directory (say [Identity] ) of the downloaded code.

[Make sure you have installed Maven2]

:\> mvn -Drelease clean install

-The above will create a zip file distribution at [Identity]\modules\distribution\target.

- Unzip the Zip file to a local folder.

- Download MySQL JDBC driver from here and copy the mysql-connector-java-5.1.6-bin.jar to [IS_INSTALLED_DIR]\lib

- You also need to download Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files 5.0 from here and copy the two jar files from the extracted jce directory (local_policy.jar and US_export_policy.jar) to $JAVA_HOME/jre/lib/security.

- Start WSO2 Identity Solution with [IS_INSTALLED_DIR]\bin\wso2is.bat

Configuring WSO2 Identity Solution to use MySQL user store

- Go to url : https://localhost:12443/admin and login with admin/admin [user/password] - then select 'User Stores'

- Click 'sampleRealm' link [Here we are using the JDBCRealm to connect to the MySQL database].

- Click 'Edit'

- Set the following properties appropriately and update.

UserCredentialColumn : pass
ConnectionPassword : mysql
ConnectionUserName : root
ColumnNames : mail,openid,firstName,lastName
DriverName : com.mysql.jdbc.Driver
UserNameColumn : uid
ConnectionURL : jdbc:mysql://localhost/COMPANY_DB
UserTable : users

- Click 'Set as Default' against 'sampleRealm'.

- Click on 'Define Claims' and select 'Given name','Surname' & 'Email address' [Dont uncheck any claims which are already selected]

- Click on 'Claim Mappings'.

- Click on 'Given name','Surname','Email address' and 'OpenID', and do the claim mapping appropriately.

- Once done the claim mapping it should look like the following.

- Try login to Identity Solution with your credentials available in MySQL database [ in our case prabath/prabath] - go to the url : https://localhost:12443

- To test your OpenID [http://localhost:12080/user/prabath], Signout first and from the Home page [https://localhost:12443], Click on OpenID and then type your OpenID.

You can find more documentation on WSO2 Identity Solution from here.