Creating a JDBC External User Store using WSO2 User Management component

The WSO2 User Manager is a library that lets developers handle user authentication and authorization in applications. It authenticates users from different types of user stores and currently has the capability to easily plug-in to JDBC, LDAP and AD.

Given below are the steps to tryout JDBC user store. I'll be using a MySql database for this.

1. Create a MySQL database

• In my database I have following structure;

To create this easily rather than entering MySql statements interactively we can put the SQL statements in a file and then tell mysql to run it in batch mode. Simply copy and paste the SQL statements below in a note pad and save it with .txt extension.

/*Create a new usermgmt database */
create database usermgmt;

/*Create the users table */
create table usermgmt.users (username varchar(10) primary key,password varchar(10)) engine=innodb;

/*Create the roles table */
create table usermgmt.roles (rolename varchar(10) primary key,description varchar(15)) engine=innodb;

/*Create the userroles table */
create table usermgmt.userroles (
username varchar(10),
rolename varchar(10),
constraint un foreign key (username) references usermgmt.users(username),
constraint rn foreign key (rolename) references usermgmt.roles(rolename)
) engine=innodb;

/*Create the userprofiles table */
create table usermgmt.userprofiles (
username varchar(10),
profilename varchar(10),
email varchar(25),
postalCode varchar(5),
country varchar(10),
firstname varchar(10),
lastname varchar(10),
homephone varchar(10),
constraint un1 foreign key (username) references usermgmt.users(username)
) engine=innodb;

/*Describe the tables that were created above */
describe usermgmt.users;
describe usermgmt.roles;
describe usermgmt.userroles;

/*Insert data into the users table */
insert into usermgmt.users (username, password) values ("tamara", "tamara");
insert into usermgmt.users (username, password) values ("saman", "saman");
insert into usermgmt.users (username, password) values ("yumani", "yumani");

/*Insert data into the roles table */
insert into usermgmt.roles (rolename, description) values ("admin", "Administrator");
insert into usermgmt.roles (rolename, description) values ("guest", "Guest User");
insert into usermgmt.roles (rolename, description) values ("tester", "Tester");

/* Insert data into the userroles table */
insert into usermgmt.userroles (username, rolename) values ("tamara", "admin");
insert into usermgmt.userroles (username, rolename) values ("saman", "guest");
insert into usermgmt.userroles (username, rolename) values ("yumani", "tester");

/*Insert data into the userprofile table */
insert into usermgmt.userprofiles (username, profilename, email, postalCode, country, firstname, lastname, homephone) values ("tamara", "default", "tamara@yahoo.com", "123", "Sri Lanka", "Tamara", "Cuttilan", "1111111111");
insert into usermgmt.userprofiles (username, profilename, email, postalCode, country, firstname, lastname, homephone) values ("saman", "home", "saman@yahoo.com", "234", "SriLanka", "Saman", "Peries", "2222222222");
insert into usermgmt.userprofiles (username, profilename, email, postalCode, country, firstname, lastname, homephone) values ("yumani", "default", "yumani@yahoo.com", "345", "USA", "Yumani", "Ranaweera", "3333333333");
insert into usermgmt.userprofiles (username, profilename, email, postalCode, country, firstname, lastname, homephone) values ("yumani", "home", "yumani@gmail.com", "333", "USA", "Yumani", "Ranaweera", "4444444444");
insert into usermgmt.userprofiles (username, profilename, email, postalCode, country, firstname, lastname, homephone) values ("tamara", "home", "tamara@gmail.com", "444", "Sri Lanka", "Tamara", "Cuttilan","5555555555");

/*Retrieve table data */
select * from usermgmt.users;
select * from usermgmt.roles;
select * from usermgmt.userroles;
select * from usermgmt.userprofiles;

• Next, open a command prompt, navigate to the location where you saved the above file.
• Type at the prompt;
  
  mysql -h localhost -u root -p -t -vv< {filename}.txt

(e.g. mysql -h localhost -u root -p -t -vv <>
NOTE: -vv was put to echo the commands to the output. You can remove that if its not required.

2. Install WSO2 ESB\WSAS\IS which has User Manager component in them

• Download a binary distribution and unzip it to your local file system.
• I will demonstrate with WSO2 Identity Server (IS).
  

3. Copy the Connector/J JDBC Driver

• This is to be able to connect to MySQL from our application.
• Download the MySQL JDBC Driver
• Copy and paste it to WSO2_IS_HOME\repository\components\extensions folder.
  

4. Start the server

• Go to WSO2_IS_HOME\bin and execute wso2server.bat file.
  
  Now that we have the prerequisite ready, let's setup the external user store and tryit out.
  

5. Setup the external user store from WSO2 Identity Server

• Once the server is started open a browser and access, https://localhost:9443/carbon/. You get WSO2 IS admin console.
• Sign-in using the default admin username\password (admin\admin).
• In the console click on 'User Management' menu option and in 'User Management' page click on 'Add External User Store' link.

• In the 'Add External User Store' page, select to create a 'JDBC' type external user store.
• Use following information for the rest of the fields.

(Note: Replace database name, user name, password with you information).

 

• Now we are done with creating the external user store. The user store connections can be validated via 'Test connection' option in the External user store menu as shown below.

Let's tryout the User Management functionality in another blog post....!