Bulk User Import in a Secondary User Store in WSO2 IS 5.7.0

Posted by Rob Blaauboer on 4 Apr, 2019
Find me on:

Bulk Import Secondary User Store WSO2 Identity Server 5.7.0Current version of WSO2 Identity Server, as well as the WSO2 Enterprise Integrator and WSO2 API Manager, knows the concept of the primary user store and a number of secondary user stores. Not all products use the user store equally, the Enterprise Integrator has a limited number of claims that it will store in the database. The reason is that for the WSO2 Enterprise Integrator the user is relatively secondary, its purpose is message mediation and transformation. The WSO2 Identity server is all about Identity and Access Management and has the capability to add additional claims to the standard dialects.

How data is stored

The data is stored in a database by default. In most cases this is H2, a flat file database. Only the Identity Server uses an embedded LDAP. Since LDAPs are a bit trickier to browse we will show the structure using DBeaver, a SQL client that we used previously. Take a look at this blog to read more about DBeaver. 

A quick setup

If you want to create a secondary user store the simplest way is to use MySQL / MariaDB. This is a lightweight and performant user store. It is also the database of choice for our training instances. Make sure that you have either MySQL or MariaDB installed. Search the internet for simple instructions like this one for MariaDB on Centos 7

The MariaDB environment is installed in the training instance. Connect to MySQL using the Command Line Interface. The command to connect to the MySql prompt is:

 mysql -u root -proot

 

WSO2 IS 5.7.0 - mysql -u root -proot

Create a database within MySQL to hold the user store. The MySql command to create the database is as follows:

create database YENLO_USERSTORE;


Select the new database using the command

use YENLO_USERSTORE;.


We will change the script that will create the tables for MariaDB/Mysql found at
[IS_Home]/dbscripts/mysql.sql. The reason is that the script creates both Registry and User tables. This is default behaviour since the scripts are also used to (re)create the primary userstore. 

Open up the mysql.sql file using a text editor and search for USER MANAGER TABLES. Delete all REG definitions and store the file as mysql_um.sql.

Run the following command:

Source [IS-HOME]/dbscripts/mysql_um.sql


WSO2 IS 5.7.0 - [IS-HOME]dbscriptsmysql_um.sql

Check the tables created with the show tables; command

MariaDB [YENLO_USERSTORE]> show tables;
+---------------------------+
| Tables_in_YENLO_USERSTORE |
+---------------------------+
| UM_ACCOUNT_MAPPING        |
| UM_CLAIM                  |
| UM_CLAIM_BEHAVIOR         |
| UM_DIALECT                |
| UM_DOMAIN                 |
| UM_HYBRID_REMEMBER_ME     |
| UM_HYBRID_ROLE            |
| UM_HYBRID_USER_ROLE       |
| UM_MODULE                 |
|  [more tables}


To see the relationships, start DBeaver and make a new connection to MariaDB.

DBeaver - MariaDB - WSO2 IS 5.7.0

Click next until the Connection is finished. Open the connection show the tables in the YENLO_USERSTORE

Yenlo UserStore - WSO2 IS 5.7.0

Select UM_USER and Click ER Diagram. You will see the UM_USER and the UM_USER_ATTRIBUTE. The user attribute table is the place where the USER_Values are stored. This allows flexibility since there is not a predefined number of fields but an relationship with a attribute name and attribute value.

Database Navigator Yenlo UserStore - WSO2 IS 5.7.0

 

Enable JDBC Connectivity

IF we want to use a MySQL database in IS we need to copy the JDBC  connector jar file to [IS_HOME]/repository/components/lib. The connector file can be downloaded from this place

Restart the Identity Server with the -DosgiConsole parameter. This will load the OSGIconsole that you can use to see if the mysql jar is loaded. When the system is started (Management URL is displayed), press enter in the terminal.

[wso2student@ip-172-31-44-127 bin]$ sh wso2server.sh -DosgiConsole


You will see a blinking cursor. When the product is started, press enter and type in:

lb mysql
osgi> osgi> START LEVEL 4
   ID|State      |Level|Name
   90|Active     |    4|mysql_connector_java_5.1.39_bin (1.0.0)

 

For windows the command is similar and needs to be entered in the command window or Powershell (in [IS-HOME]/bin: wso2server.bat -DosgiConsole

This shows the mysql connector is loaded as a bundle in IS.

Configure a Secondary User Store

Login to the Management UI of Identity Server (e.g. https://localhost:9443/carbon) and Click Main > User Stores > Add.

User Stores - WSO2 IS 5.7.0

The Add New User Store page opens.

Note: You cannot update the PRIMARY user store at run time as this is already in use.

  1. Select wso2.carbon.user.core.jdbc.JDBCUserStoreManager from the User Store Manager Class drop-down list.
  2. Enter Training as the domain name.

Add New User Store - WSO2 IS 5.7.0

Fill in the following values in the properties

jdbc:mysql://localhost:3306/YENLO_USERSTORE
root
root
com.mysql.jdbc.Driver

 

WSO2 Carbon  - WSO2 IS 5.7.0

Click Test Connection to test if the connection is valid.
Click Add to add the connection.
Refresh the page to view the newly added user store.

User Stores - carbon user coreIt should now be possible to add new users and roles into the user store. Add aMArvin to the userstore.

Add New User - Enter Username and Password

YENLO. Subsequently, edit his User Profile

Users - User profile - WSO2 IS 5.7.0

Edit the mandatory fields and press update.

Update Profile - Yenlo User Store _WSO2 IS 5.7.0

All new users and roles added to the user store should appear in the MySQL database. The values are in the YENLO_USERSTORE UM_USER_ATTRIBUTE in a 1:N relationship.
 UM_User_Attribute - WSO2 IS 5.7.0

Bulk import

WSO2 Identity Server has the capability to do a bulk import of users. This is done  with an excel (xls) file that is either uploaded using  the management UI or via the  Admin Services. 

In the Advanced properties of the User store you created, enable the Is Bulk Import Supported value. Click Update to save the change.

Advanced Properties User store - WSO2 IS 5.7.0

We will now upload 4 users from an excel file (CSV format). Download the bulkload.csv from bit.ly/WSO2GREGSAMPLES (go to directory IS570).

UserName

,

Password

JohnDoe

,

jdoepw

JaneDoe

,

pwjdoe

JackDoe

,

jpwdoe

JillDoe  

,

zaq1@WSX


Search Users - Yenlo User Store - WSO2 IS 5.7.0

There is a limit of about 500.000 users per CSV file.

As you can see you can easily bulk import users, together with claims into the WSO2 Identity Server. You can even do this programmatically using the Admin Services. Because, as we say, there is always another way to do it with WSO2.  If you have any questions regarding this blog or about other WSO2 issues, leave a comment below or send us a message via our contact page. You can also learn more about the WSO2 yourself during our trainings. Click below for more information.New Call-to-action

Topics: WSO2 IS, Identity Server, Identity and Access Management, WSO2 Identity Server

Written by Rob Blaauboer

Rob Blaauboer
Rob is a Senior Business Consultant and Solution Architect with more than twenty years experience. In addition to his work he is an active blogger working on a number of articles on the ‘Internet of Things’ and a WSO2 ‘Getting Started with …’ series (WSO2 tutorial) in which he talks about WSO2 components and their purpose especially aimed at non technical readers. Rob is a WSO2 expert and official WSO2 trainer.

Find me on: