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.

<datasource>
            <name>JDBC_demo_user_store</name>
            <description>The datasource used for JDBC_demo_user_store</description>
            <jndiConfig>
                <name>jdbc/JDBC_demo_user_store</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/JDBC_demo_user_store</url>
                    <username>root</username>
                    <password>pushpalanka2</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
</datasource>
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/wso2server.sh. 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.

Cheers!

Popular posts from this blog

Signing SOAP Messages - Generation of Enveloped XML Signatures

Image
Digital signing is a widely used mechanism to make digital contents authentic. By producing a digital signature for some content, we can let another party capable of validating that content. It can provide a guarantee that, is not altered after we signed it, with this validation. With this sample I am to share how to generate the a signature for SOAP envelope. But of course this is valid for any other content signing as well. Here, I will sign The SOAP envelope itself An attachment  Place the signature inside SOAP header  With the placement of signature inside the SOAP header which is also signed by the signature, this becomes a demonstration of enveloped signature. I am using Apache Santuario library for signing. Following is the code segment I used. I have shared the complete sample here to to be downloaded . public static void main(String unused[]) throws Exception {         String keystoreType = "JKS";         Stri...
Read more

Tomcat JDBC Pool - Connection Leak - Catch the Culprit

Database connection leaks are something that can stay hidden unless paid specific attention and would come to the surface at the most critical stages at a peak time of the system. We would manually check if all the open connections have been closed properly. Then we have various code quality plugins that would scan and check for that. Still when the connections are passed through a complex structure of program both of these can miss a possible connection leak. Then at unit test or integration test levels, we can have checks to validate the counts in the connection pool to avoid this unfortunate situation, that would keep engineers busy at year-end, black Friday, etc. :) In the unfortunate case of hitting with a performance degrade or a total crash of the system which can be propagated via a JDBC connection leak, when we suspect a connection leak, how easily and quickly isolate the culprit. In the Tomcat connection pool, we can do this using 3 properties. removeAbandoned If a...
Read more

How to convert WSDL to Java

Image
Background Basically WSDL (Web Services Development Language) is an XML document used to describe or locate web services. WSDL stands for Web Services Description Language. The document describes a Web service. It specifies the location of the service and the operations that can be taken.  WSDL2Java is distributed with Axis2 package of Apache and need to set-up Axis2 to use this service. A  guide to set-up Axis2  can be found in apache organization documents .WSDL 2.0 is the currently using version. Obviously JDK should be properly installed too. Note: For the 3rd method you are not needed to set these things by yourself. The WSDL document includes server Java programs for Web services and generated classes using WSDL2Java feature includes client stubs, server skeletons and data types that can help in writing client side. The generated stub classes play a great role in distributed computing and if you are interested can read on  what are...
Read more