This is my third blog post about WSO2 Identity Server user realm ? . This one is about connecting WSO2 Identity Server (WSO2 Carbon) in to existing JDBC user store (read/write mode).
Assume that existing JDBC user store would be as follows. It is just a table with two columns of user name and password entries. Passwords are stored as plain text values. Sample schema would be as follows
CREATE TABLE SAMPLE_USER (USERNAME char(50), PASSWORD char(50), PRIMARY KEY (USERNAME));
Therefore we can not directly connect this JDBC user store with WSO2 Carbon and We need to write a new JDBC user store to connect to it.
Lets see how we can do this.
Existing user store only contains users and their passwords. But WSO2 Carbon default realm always assumes that there are roles (or group) and user attributes in the user store. If you just go through the “UserStoreManager” interface, you can see it. Therefore to solve this, we have three options
1. Writing a new user realm by forgetting roles and user attributes.
2. Writing a new user store manager by implementing “UserStoreManager” interface and with bear minimum role implementation ( at least admin role and everyone role). If we do not use user attributes, claim management and user profile functions may not work properly.
3. Writing a new user store manager by extending “JDBCUserStoreManager” class by reusing its functions. Here we can create role and attribute related tables using default WSO2 Carbon schema. (But we do not want to use them) . We can create the following three tables according to the WSO2 Carbon schema . But we need to do little change to them to deal with “SAMPLE_USER” table as follows
// UM_ROLE table to store role names
CREATE TABLE UM_ROLE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_NAME VARCHAR(255) NOT NULL,
PRIMARY KEY (UM_ID),
UNIQUE(UM_ROLE_NAME)
);
// UM_USER_ROLE table to store user and role mapping
CREATE TABLE UM_USER_ROLE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ROLE_ID INTEGER NOT NULL,
UM_USER_ID VARCHAR(255),
UNIQUE (UM_USER_ID, UM_ROLE_ID),
FOREIGN KEY (UM_ROLE_ID) REFERENCES UM_ROLE(UM_ID),
FOREIGN KEY (UM_USER_ID) REFERENCES SAMPLE_USER(USERNAME),
PRIMARY KEY (UM_ID)
);
// UM_USER_ATTRIBUTE table to store user attributes. any attribute id and attribute value can be store here.
CREATE TABLE UM_USER_ATTRIBUTE (
UM_ID INTEGER NOT NULL AUTO_INCREMENT,
UM_ATTR_NAME VARCHAR(255) NOT NULL,
UM_ATTR_VALUE VARCHAR(1024),
UM_PROFILE_ID VARCHAR(255),
UM_USER_ID VARCHAR(255),
FOREIGN KEY (UM_USER_ID) REFERENCES SAMPLE_USER(USERNAME),
PRIMARY KEY (UM_ID)
);
Now our existing user store contains four tables. :). Only one table is differ from default WSO2 Carbon user store schema.
Finally deployment would be as follows.
Lets see how our new user store manager class must be designed.
1. First we need to define new SQL queries to deal with the user store. You can override default SQLs by defining them as the user store properties in the user-mgt.xml file. Default SQLs of JDBCUserStoreManager can be found at here
2. We have only one change with the default WSO2 Carbon user store schema. i.e UM_USER table has been changed to SAMPLE_USER. Therefore we can assume that we only need to override following methods.
- authenticate()
- addUser()
- updateCredentials()
But this is not the actual story. WSO2 Carbon can operated as multi tenant and JDBCUserStoreManager is tenant aware by default. But we do not want to have multi tenancy for our new user store manager. So we need to override some more methods other than the above three method. However all override methods are seem to be same as the existing implementation with minor changes.
Sample source can be found at here. Also you can try out this project with released Carbon 3.2.3 products. README file contains all necessary details…