Tuesday, May 12, 2009

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....!

10 comments:

  1. Great blog. But can't read the UM1.jpg SQL statements you have entered. Would be great if posting in readable text. Thx.

    ReplyDelete
  2. I'm sorry. I will adjust it soon.

    Thanks for your comment.
    Yumani

    ReplyDelete
  3. Yumani,
    If you can put the high resolution images or actually add clear text queries it will help us all a lot.
    THanks

    ReplyDelete
  4. Seconded, this would be extremely useful to me right now.

    Thanks

    ReplyDelete
  5. I'm sorry, is it better now? I've uploaded an enlarged print of UM1. Please check it out and let me know...

    Regards,
    Yumani

    ReplyDelete
  6. This is close to what we need. How does one handle a situation where there's a preexisting database and the user credentials are not plaintext passwords but SHA1 hash values? Can one extend the existing JDBC external user store code or is one forced to write their very own UserRealm implementation from scratch? If so, how does one go about that? Curious.

    ReplyDelete
  7. I actually have a similar requirement to John's -- need to authenticate against an existing data store where the passwords are stored in SHA1.

    ReplyDelete
  8. Hi Yumanni, what is the Identity Server release you are using in this post? Can't find User Manager component in 3.0.1. Thanks

    ReplyDelete
    Replies
    1. obviously operation ui is changed from IS 3.0 and on

      Delete
    2. in fact IS config UI is changed from 3.0 on??

      Delete