How to share RDBMS user-stores between tenants with WSO2 IS

Megala Uthayakumar
Mar 30, 2018 · 2 min read

WSO2 products support multiple user-stores. This can be achieved by configuring secondary user-stores.

By default, secondary user-stores are bound to a tenant and even if we use the same database across different tenants, the users and roles that are added from a different tenant will not be visible in the new tenant.

In this blog, we will look into how can we use the same users and roles across multiple tenants.

  1. Add a secondary user-store using management console.
  1. After adding the secondary user-store from management console, you should be able to see a file with the name <user-store-domain>.xml in <IS_HOME>/repository/deployment/server/userstores folder, if it is super-tenant. For other tenants you could find a similar file in <IS_HOME>/repository/deployment/tenant/<tenant_id>/userstores folder.
  2. In that file, you could see many SQL queries for different tasks such as adding a user, adding role, etc. Those are the queries that will be used when doing the user-management tasks with the particular user-store. This gives the flexibility for users to specify the relevant SQL queries that need to be executed while adding/modifying/deleting users/roles from the particular user-store. The default queries added will be having UM_TENANT_ID in the reference of the SQL queries. If we remove the UM TENANT_ID from the relevant SQL queries, we can access the users and roles across tenants.
<Property name=”SelectUserSQL”>SELECT * FROM UM_USER WHERE UM_USER_NAME=?</Property><Property name=”GetRoleListSQL”>SELECT UM_ROLE_NAME, UM_SHARED_ROLE FROM UM_ROLE WHERE UM_ROLE_NAME LIKE ? AND UM_SHARED_ROLE =’0' ORDER BY UM_ROLE_NAME</Property><Property name=”GetSharedRoleListSQL”>SELECT UM_ROLE_NAME, UM_SHARED_ROLE FROM UM_ROLE WHERE UM_ROLE_NAME LIKE ? AND UM_SHARED_ROLE =’1' ORDER BY UM_ROLE_NAME</Property><Property name=”UserFilterSQL”>SELECT UM_USER_NAME FROM UM_USER WHERE UM_USER_NAME LIKE ? ORDER BY UM_USER_NAME</Property><Property name=”UserRoleSQL”>SELECT UM_ROLE_NAME FROM UM_USER_ROLE, UM_ROLE, UM_USER WHERE UM_USER.UM_USER_NAME=? AND UM_USER.UM_ID=UM_USER_ROLE.UM_USER_ID AND UM_ROLE.UM_ID=UM_USER_ROLE.UM_ROLE_ID</Property><Property name=”UserSharedRoleSQL”>SELECT UM_ROLE_NAME, UM_SHARED_ROLE FROM UM_SHARED_USER_ROLE INNER JOIN UM_USER ON UM_SHARED_USER_ROLE.UM_USER_ID = UM_USER.UM_ID INNER JOIN UM_ROLE ON UM_SHARED_USER_ROLE.UM_ROLE_ID = UM_ROLE.UM_ID WHERE UM_USER.UM_USER_NAME = ? </Property><Property name=”IsRoleExistingSQL”>SELECT UM_ID FROM UM_ROLE WHERE UM_ROLE_NAME=?</Property><Property name=”GetUserListOfRoleSQL”>SELECT UM_USER_NAME FROM UM_USER_ROLE, UM_ROLE, UM_USER WHERE UM_ROLE.UM_ROLE_NAME=? AND UM_USER.UM_ID=UM_USER_ROLE.UM_USER_ID AND UM_ROLE.UM_ID=UM_USER_ROLE.UM_ROLE_ID</Property><Property name=”GetUserListOfSharedRoleSQL”>SELECT UM_USER_NAME FROM UM_SHARED_USER_ROLE INNER JOIN UM_USER ON UM_SHARED_USER_ROLE.UM_USER_ID = UM_USER.UM_ID INNER JOIN UM_ROLE ON UM_SHARED_USER_ROLE.UM_ROLE_ID = UM_ROLE.UM_ID WHERE UM_ROLE.UM_ROLE_NAME= ? </Property><Property name=”IsUserExistingSQL”>SELECT UM_ID FROM UM_USER WHERE UM_USER_NAME=?</Property><Property name=”GetUserPropertiesForProfileSQL”>SELECT UM_ATTR_NAME, UM_ATTR_VALUE FROM UM_USER_ATTRIBUTE, UM_USER WHERE UM_USER.UM_ID = UM_USER_ATTRIBUTE.UM_USER_ID AND UM_USER.UM_USER_NAME=? AND UM_PROFILE_ID=?</Property><Property name=”GetUserPropertyForProfileSQL”>SELECT UM_ATTR_VALUE FROM UM_USER_ATTRIBUTE, UM_USER WHERE UM_USER.UM_ID = UM_USER_ATTRIBUTE.UM_USER_ID AND UM_USER.UM_USER_NAME=? AND UM_ATTR_NAME=? AND UM_PROFILE_ID=?</Property>

Likewise modify all the queries by removing UM_TENANT_ID column.

After that create a tenant and add the same database as the secondary user-store and modify the queries as above. Now you should be able to use same users and roles across tenants.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade