In this post, I will explain how to plug a JDBC userstore (database) as a secondary userstore to a WSO2 server. In this demonstration, I am using WSO2 Identity Server 5.1.0 version which is the latest released version at the time of this writing. As the database, I am using MySQL, but referring the same steps, you can plug any database such as IBM DB2, Oracle, Microsoft SQL Server, PostgreSQL etc.
First step is to create the database and the required tables. The schema can be found in WSO2_SERVER_HOME/dbscripts directory. There you will find several database scripts. If you want to create a mysql userstore, then the schema is in mysql.sql file. If the database should be Oracle, then you have to look at oracle.sql.
In this database script file, there are registry related tables (table names starting with REG_) and also user management tables (table names starting with UM_). For a JDBC userstore, we only need UM_ tables. Following are the UM_ tables in Identity Server 5.1.0 version.
UM_TENANT
UM_DOMAIN
UM_USER
UM_SYSTEM_USER
UM_ROLE
UM_MODULE
UM_MODULE_ACTIONS
UM_PERMISSION
UM_ROLE_PERMISSION
UM_USER_PERMISSION
UM_USER_ROLE
UM_SHARED_USER_ROLE
UM_ACCOUNT_MAPPING
UM_USER_ATTRIBUTE
UM_DIALECT
UM_CLAIM
UM_PROFILE_CONFIG
UM_HYBRID_ROLE
UM_HYBRID_USER_ROLE
UM_SYSTEM_ROLE
UM_SYSTEM_USER_ROLE
UM_HYBRID_REMEMBER_ME
However out of these tables, some of the tables are not used. But just to be in the safe side, let’s select all the UM_ tables. You can refer [1] and get to know more about the usage of these tables.
What you simply have to do is create a database and create these UM_ tables inside the database.
Here I copy all the create table commands of UM_ tables and put it in a file called mysql_user_tables.sql. Then I am going to execute this SQL script against a database so that the tables would be automatically created.
I create the MySQL database with the command “create database <database name>”. As the database name, I have given is_secondary. You can give any name. Then I am changing the current database to that database from the “use <database name>” command.
Then I am creating all the UM_ tables in this database. In MySQL, you can simply execute an external database script using the “source <file path>” command. Then you should see that the tables are getting created. You can even use MySQL workbench and get the tables created. Or else you can even copy and paste the create table commands in MySQL console and get the tables created. If you are using a different database, just get the UM_ tables created in your database.
I can verify that the tables are created using “show tables” command in MySQL.
Next step is to plug this database as a secondary userstore in WSO2 server. WSO2 servers are developed using Java. Therefore the server needs the particular JDBC driver to communicate with the database. However these JDBC drivers are not shipped with WSO2 products out of the box. Therefore we need to manually add the particular JDBC driver to the WSO2 server.
Here I am using MySQL database. Therefore I am downloading MySQL JDBC Driver. If you are using Oracle, then you need to download Oracle JDBC Driver. If you are using Microsoft SQL Server, then you can use either JTDS or Microsoft JDBC Driver.
Then in the downloaded ZIP file of JDBC Driver, I get mysql-connector-java-5.1.39-bin.jar file. (When you download, the version might be different).
Next step is to copy this JDBC Driver jar file to WSO2 server.
When copying any external JAR file to WSO2 servers, if the JAR file is an OSGI compatible bundle, you have to copy it to WSO2_SERVER_HOME/repository/components/dropins directory. If it is not compatible with OSGI framework, you have to copy it to WSO2_SERVER_HOME/repository/components/lib directory. (For more information on using external JAR files in WSO2 servers, refer [2])
I copy the MySQL JDBC Driver jar file to WSO2_SERVER_HOME/repository/components/lib directory.
Then start/restart the server so that the JDBC Driver would be picked up by the server.
After that, login to the Management Console of the WSO2 server.
Then add new UserStore. (If you are using some older versions of WSO2 servers, the link to add a new userstore would be coming under the Configure tab. In latest versions, it is coming under the Main tab.
As the User Store Manager class, select org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager class. This is the particular java class that manages the logic for handling all the user management operations in the JDBC userstore. (This is the default implementation of JDBC userstore that WSO2 provides. You may extend this and write your own custom implementation of JDBC userstore and add that as a secondary userstore).
Provide a domain name for the userstore and add connection details.
The database connection URL would differ with the database vendor. So you have to find the particular connection string that matches with your database type. Similarly, Driver Name also would differ with different JDBC Drivers.
Once you fill the details, click on Add button to add the secondary user store. It will take few seconds to configure the userstore. You can refresh the page after few seconds to verify that the userstore is successfully created.
During this process, WSO2 server will create a configuration file that contains all the properties of the userstore. This file will be created in WSO2_SERVER_HOME/repository/deployment/server/userstores directory (if the secondary userstore is added in Super Tenant). All the configuration changes you do in the UI of the Management Console would be persisted in this file. Apart from that, you can even directly modify this file and the changes will be hot deployed and applied without a server restart. If you have a cluster of WSO2 servers, you will have to copy this file to other nodes in order to make the userstore available to other nodes as well (deployment synchronization).
Now we have successfully plugged the database we created previously, to the WSO2 server. Next step is to test if it is working.
First, let’s create a new role in this secondary userstore. When you add the role, you have to select the Domain as the secondary userstore which you have added. In my case, the domain name of the secondary userstore is WSO2.
Then I grant login permission to this role, so that the users having this role can login to the WSO2 server.
If the role is successfully added, you can view it where the role would follow the pattern <domain name>/<role name>
In the default JDBC userstore of WSO2 servers, the roles are added to UM_ROLE table. (See [1] for more information). If I query the database and view the records of this table, I can see that the role is successfully created.
Then I create a new user in the secondary userstore.
Then I assign the previously created role to this user so that the user would get the login permission granted.
You can see the user is successfully created. The username would follow the pattern <domain name>/<username>.
You can query the database and view the record for the created user.
Now let’s check if the user can successfully login. Go to the Management Console and enter the credentials of the newly created user who is in the secondary userstore. The username should follow the pattern <domain name>/<username> so that WSO2 server can look up for the user in the particular userstore.
Now we have successfully logged in as the user in the secondary userstore !
References
Tharindu Edirisinghe
Platform Security Team
WSO2
No comments:
Post a Comment