Migrating User Stores¶
Before doing the actual user store migration, it is recommended to do a dry run and analyze the generated report for any recommendations related to the migration.
Dry Run¶
Dry-run capability in the user store migrator will allow the migration utility to validate the system for the user store configurations and generate a report regarding the migration compatibility. In the generated migration report, if there are any warnings in the generated migration report, it is recommended to contact the WSO2 support to identify the best migration strategy.
How to Run¶
Configure the migration report path using the value of reportPath
<IS_HOME>/migration-resources/migration-config.yaml
in the migration-config.yaml
.
This path should be an absolute path.
Run the migration utility with system property “dryRun”.
Ex:
sh wso2server.sh -Dmigrate -Dcomponent=identity -DdryRun
wso2server.bat -Dmigrate -Dcomponent=identity -DdryRun
After that analyze the generated report that resides in the provided location.
Migrating User Stores¶
If you have multiple tenants and multiple user stores and you only need to migrate a few of them, refer to the
configuration section below. If you need to migrate all of them, use the migrateAll
property (This is set to true by default).
Special Scenarios¶
If the user store is an LDAP user store and SCIM is enabled for that user store, migrating that user store is not
required.
As SCIM will create a user ID for the users in that user store, the SCIM ID can be used as the unique user ID. To do that,
change the user_id_attribute
to the value of the SCIM ID, in the <IS_HOME>/repository/conf/deployment.toml
file.
Configurations¶
Property Name | Description |
---|---|
migrateAll | Migrate all the tenants and all the user store domains in it. |
reportPath | Absolute path for the dry report. This is required in the dry run mode. |
The following configurations are only needed to migrate a few tenants. The format should be similar to the one mentioned below. Tenant-param
is a place holder name to represent each tenant uniquely, e.g., tenant1.
-
name: "UserIDMigrator"
order: 7
parameters:
<tenant-param>:
Property Name | Description |
---|---|
tenantDomain | Domain name of the tenant. (Mandatory) |
increment | Number of users to be updated in each iteration. (Optional) |
startingPoint | Where should the migration start from (Offset). This is useful if the migration stopped in the middle and needs to restart. (Optional) |
scimEnabled | Whether SCIM is enabled for user stores in this tenant. (Optional) |
migratingDomains | List of comma-separated domain names that should be migrated to this domain. (Optional) |
forceUpdateUserId | Mark whether user IDs should be updated even though there is already an existing ID. (Optional) |
Populating UserIds for the userstores¶
These steps should be carried out for the old database before the migration. A backup of UM database should be taken and database triggers should be set to update the backup database based on the updates of the live database. After performing the following steps the backup database should be migrated to the next version.
-
If you have JDBC secondary user stores with SCIM disabled, execute the following queries on the UM database. This will add a
UM_USER_ID
column to theUM_USER
table with a randomUUID
as the default value forUM_USER_ID
./* User should have the Superuser permission */ CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; ALTER TABLE UM_USER ADD COLUMN UM_USER_ID CHAR(36) DEFAULT uuid_generate_v4(), ADD CONSTRAINT UM_USER_UUID_CONSTRAINT UNIQUE(UM_USER_ID);
ALTER TABLE UM_USER ADD ( UM_USER_ID CHAR(36) DEFAULT LOWER(regexp_replace(rawtohex(sys_guid()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '\1-\2-\3-\4-\5'))) / ALTER TABLE UM_USER ADD UNIQUE (UM_USER_ID) /
ALTER TABLE UM_USER ADD ( UM_USER_ID CHAR(36) DEFAULT LOWER(regexp_replace(rawtohex(sys_guid()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '\1-\2-\3-\4-\5'))) / ALTER TABLE UM_USER ADD UNIQUE (UM_USER_ID) /
ALTER TABLE UM_USER ADD COLUMN UM_USER_ID CHAR(36) NOT NULL DEFAULT 'NONE'; UPDATE UM_USER SET UM_USER_ID = UUID(); ALTER TABLE UM_USER ADD UNIQUE(UM_USER_ID, UM_TENANT_ID);
ALTER TABLE UM_USER ADD COLUMN UM_USER_ID CHAR(36) NOT NULL DEFAULT 'NONE'; UPDATE UM_USER SET UM_USER_ID = UUID(); ALTER TABLE UM_USER ADD UNIQUE(UM_USER_ID, UM_TENANT_ID);
ALTER TABLE UM_USER ADD UM_USER_ID CHAR(36) DEFAULT LOWER(NEWID()) NOT NULL, UNIQUE(UM_USER_ID);
ALTER TABLE UM_USER ADD COLUMN (UM_USER_ID CHAR(36) DEFAULT RANDOM_UUID()); ALTER TABLE UM_USER ADD UNIQUE (UM_USER_ID);
CREATE OR REPLACE FUNCTION NEWUUID() RETURNS CHAR(36) BEGIN DECLARE @UUID CHAR(32); SET @UUID=LOWER(HEX(RAND()*255) || HEX(RAND()*255)); RETURN LEFT(@UUID,8)||'-'|| SUBSTR(@UUID,9,4)||'-'|| SUBSTR(@UUID,13,4)||'-'|| SUBSTR(@UUID,17,4)||'-'|| RIGHT(@UUID,12); END / ALTER TABLE UM_USER ADD COLUMN UM_USER_ID CHAR(36) NOT NULL DEFAULT 'NONE' / CALL SYSPROC.ADMIN_CMD('REORG TABLE UM_USER') / UPDATE UM_USER SET UM_USER_ID = NEWUUID() / CALL SYSPROC.ADMIN_CMD('REORG TABLE UM_USER') /
-
If you have JDBC secondary user stores with SCIM enabled, execute the following queries on the UM database.
SELECT DISTINCT t.ATTRIBUTE_NAME FROM IDN_CLAIM_MAPPED_ATTRIBUTE AS t WHERE t.LOCAL_CLAIM_ID IN (SELECT t2.MAPPED_LOCAL_CLAIM_ID FROM IDN_CLAIM_MAPPING AS t2 JOIN IDN_CLAIM AS t1 ON t1.ID = t2.EXT_CLAIM_ID WHERE t1.CLAIM_URI = 'urn:ietf:params:scim:schemas:core:2.0:id');
select DISTINCT t.ATTRIBUTE_NAME FROM IDN_CLAIM_MAPPED_ATTRIBUTE t WHERE t.LOCAL_CLAIM_ID IN ( SELECT t2.MAPPED_LOCAL_CLAIM_ID FROM IDN_CLAIM_MAPPING t2 JOIN IDN_CLAIM t1 ON t1.ID = t2.EXT_CLAIM_ID WHERE t1.CLAIM_URI = 'urn:ietf:params:scim:schemas:core:2.0:id' ) /
SELECT DISTINCT t.ATTRIBUTE_NAME FROM IDN_CLAIM_MAPPED_ATTRIBUTE t WHERE t.LOCAL_CLAIM_ID IN( SELECT t2.MAPPED_LOCAL_CLAIM_ID FROM IDN_CLAIM_MAPPING t2 JOIN IDN_CLAIM t1 ON t1.ID = t2.EXT_CLAIM_ID WHERE t1.CLAIM_URI ='urn:ietf:params:scim:schemas:core:2.0:id' ) ;
SELECT DISTINCT t.ATTRIBUTE_NAME FROM IDN_CLAIM_MAPPED_ATTRIBUTE AS t WHERE t.LOCAL_CLAIM_ID IN ( SELECT t2.MAPPED_LOCAL_CLAIM_ID FROM IDN_CLAIM_MAPPING AS t2 JOIN IDN_CLAIM AS t1 ON t1.ID = t2.EXT_CLAIM_ID WHERE t1.CLAIM_URI = 'urn:ietf:params:scim:schemas:core:2.0:id' );
-
If the result of the above query is
scimId
then follow this step. Otherwise, skip this and move on to step-4.If the result of the above query is
scimId
, it means the default mapped attribute(scimId) ofhttp://wso2.org/claims/userid
or the default local claim (http://wso2.org/claims/userid
) mapped to theurn:ietf:params:scim:schemas:core:2.0:id
claim is not updated in the WSO2 IS server. Hence, the following queries are used to update theUM_USER_ID
ofUM_USER
with SCIM Id based on the mapped attributescimId
.CREATE OR REPLACE FUNCTION update_um_user_id() returns int LANGUAGE plpgsql AS $$ DECLARE count_rows int; cur_um_attr cursor for select T2.um_attr_value, T1.um_id from um_user_attribute T2 join um_user T1 on T1.um_Id = T2.um_user_id where T2.um_attr_name ='scimId'; rec_um_attr RECORD; BEGIN count_rows = 0; open cur_um_attr; LOOP fetch cur_um_attr into rec_um_attr; exit when not found; update um_user set um_user_id=rec_um_attr.um_attr_value where um_id = rec_um_attr.um_id; count_rows = count_rows + 1; END LOOP; close cur_um_attr; return count_rows; END; $$; select update_um_user_id(); CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; update um_user set um_user_id=uuid_generate_v4() where um_user_id = 'N';
DECLARE v_um_attr_value varchar2(100); v_um_id number; CURSOR cur_um_attr IS select T2.um_attr_value, T1.um_id from um_user_attribute T2 join um_user T1 on T1.um_Id = T2.um_user_id where T2.um_attr_name ='scimId'; BEGIN OPEN cur_um_attr; LOOP FETCH cur_um_attr INTO v_um_attr_value, v_um_id; IF cur_um_attr%NOTFOUND THEN EXIT; END If; update um_user set um_user_id = v_um_attr_value where um_id = v_um_id; END LOOP; CLOSE cur_um_attr; END; /
ALTER TABLE UM_USER ADD COLUMN UM_USER_ID CHAR(36) NOT NULL DEFAULT 'NONE'; ALTER TABLE UM_USER ADD UNIQUE(UM_USER_ID, UM_TENANT_ID); DELIMITER $$ CREATE FUNCTION update_um_user_id() RETURNS int DETERMINISTIC BEGIN DECLARE count_rows int; DECLARE um_usr_attr_value varchar(100); DECLARE um_id int; DECLARE done INT DEFAULT FALSE; DECLARE cur_um_attr CURSOR FOR SELECT T2.UM_ATTR_VALUE, T1.UM_ID FROM UM_USER_ATTRIBUTE T2 JOIN UM_USER T1 ON T1.UM_ID = T2.UM_USER_ID WHERE T2.UM_ATTR_NAME = 'scimId'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET count_rows = 0; OPEN cur_um_attr; read_loop: loop FETCH cur_um_attr INTO um_usr_attr_value, um_id; IF done THEN LEAVE read_loop; END IF; UPDATE UM_USER SET UM_USER_ID = um_usr_attr_value WHERE UM_ID = um_id; SET count_rows = count_rows + 1; END loop; CLOSE cur_um_attr; RETURN count_rows; END $$ DELIMITER ; SELECT update_um_user_id(); UPDATE UM_USER SET UM_USER_ID = UUID() WHERE UM_USER_ID IS NULL;
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'UM_USER_ID' AND object_id = OBJECT_ID('UM_USER')) BEGIN ALTER TABLE UM_USER ADD UM_USER_ID CHAR(36) DEFAULT 'N' END BEGIN DECLARE @count_rows int DECLARE @um_attr_value varchar(100) DECLARE @um_id int DECLARE cur_um_attr CURSOR LOCAL FOR select T2.UM_ATTR_VALUE, T1.UM_ID from UM_USER_ATTRIBUTE T2 join UM_USER T1 on T1.UM_ID = T2.UM_USER_ID where T2.um_attr_name ='scimId' SET @count_rows = 0 OPEN cur_um_attr -- loop through a cursor FETCH NEXT FROM cur_um_attr INTO @um_attr_value, @um_id WHILE @@FETCH_STATUS = 0 BEGIN update UM_USER set UM_USER_ID = @um_attr_value where UM_ID = @um_id SET @count_rows = @count_rows + 1 FETCH NEXT FROM cur_um_attr INTO @um_attr_value, @um_id END CLOSE cur_um_attr END; update UM_USER set UM_USER_ID =LOWER(NEWID()) where UM_USER_ID='N' ;
-
If the result of the above query is something different from
scimId
, it means the local claim mapped to theurn:ietf:params:scim:schemas:core:2.0:id
claim is different thanhttp://wso2.org/claims/userid
or the mapped attribute for the local claimhttp://wso2.org/claims/userid
is different thanscimId
. Hence the following queries are used to update theUM_USER_ID
ofUM_USER
with SCIM Id based on the updated mapped attribute.CREATE OR REPLACE FUNCTION update_um_user_id() returns int LANGUAGE plpgsql AS $$ DECLARE count_rows int; cur_um_attr CURSOR FOR SELECT T2.um_attr_value, T1.um_id FROM um_user_attribute T2 JOIN um_user T1 ON T1.um_id = T2.um_user_id WHERE T2.um_attr_name IN ( SELECT T.attribute_name FROM idn_claim_mapped_attribute AS T WHERE T.local_claim_id IN ( SELECT T3.mapped_local_claim_id FROM idn_claim_mapping AS T3 JOIN idn_claim AS T4 ON T4.id = T3.ext_claim_id WHERE T4.claim_uri = 'urn:ietf:params:scim:schemas:core:2.0:id') ); rec_um_attr RECORD; BEGIN count_rows = 0; open cur_um_attr; LOOP fetch cur_um_attr into rec_um_attr; exit when not found; update um_user set um_user_id=rec_um_attr.um_attr_value where um_id = rec_um_attr.um_id; count_rows = count_rows + 1; END LOOP; close cur_um_attr; return count_rows; END; $$; select update_um_user_id(); CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; update um_user set um_user_id=uuid_generate_v4() where um_user_id = 'N';
DECLARE v_um_attr_value varchar2(100); v_um_id number; CURSOR cur_um_attr IS select DISTINCT t.ATTRIBUTE_NAME FROM IDN_CLAIM_MAPPED_ATTRIBUTE t WHERE t.LOCAL_CLAIM_ID IN ( SELECT t2.MAPPED_LOCAL_CLAIM_ID FROM IDN_CLAIM_MAPPING t2 JOIN IDN_CLAIM t1 ON t1.ID = t2.EXT_CLAIM_ID WHERE t1.CLAIM_URI = 'urn:ietf:params:scim:schemas:core:2.0:id' ) BEGIN OPEN cur_um_attr; LOOP FETCH cur_um_attr INTO v_um_attr_value, v_um_id; IF cur_um_attr%NOTFOUND THEN EXIT; END If; update um_user set um_user_id = v_um_attr_value where um_id = v_um_id; END LOOP; CLOSE cur_um_attr; END; /
ALTER TABLE UM_USER ADD COLUMN UM_USER_ID CHAR(36) NOT NULL DEFAULT 'NONE'; ALTER TABLE UM_USER ADD UNIQUE(UM_USER_ID, UM_TENANT_ID); DELIMITER $$ CREATE FUNCTION update_um_user_id() RETURNS int DETERMINISTIC BEGIN DECLARE count_rows int; DECLARE um_usr_attr_value varchar(100); DECLARE um_id int; DECLARE done INT DEFAULT FALSE; DECLARE cur_um_attr CURSOR FOR SELECT T2.UM_ATTR_VALUE, T1.UM_ID FROM UM_USER_ATTRIBUTE T2 join UM_USER T1 on T1.UM_ID = T2.UM_USER_ID WHERE T2.UM_ATTR_NAME in ( SELECT T.ATTRIBUTE_NAME FROM IDN_CLAIM_MAPPED_ATTRIBUTE as T WHERE T.LOCAL_CLAIM_ID in ( SELECT T3.MAPPED_LOCAL_CLAIM_ID FROM IDN_CLAIM_MAPPING as T3 JOIN IDN_CLAIM as T4 on T4.ID = T3.EXT_CLAIM_ID WHERE T4.CLAIM_URI = 'urn:ietf:params:scim:schemas:core:2.0:id' ) ) ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET count_rows = 0; OPEN cur_um_attr; read_loop: loop FETCH cur_um_attr INTO um_usr_attr_value, um_id; IF done THEN LEAVE read_loop; END IF; UPDATE UM_USER SET UM_USER_ID = um_usr_attr_value WHERE UM_ID = um_id; SET count_rows = count_rows + 1; END loop; CLOSE cur_um_attr; RETURN count_rows; END $$ DELIMITER ; SELECT update_um_user_id(); UPDATE UM_USER SET UM_USER_ID = UUID() WHERE UM_USER_ID IS NULL;
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'UM_USER_ID' AND object_id = OBJECT_ID('UM_USER')) BEGIN ALTER TABLE UM_USER ADD UM_USER_ID CHAR(36) DEFAULT 'N' END BEGIN DECLARE @count_rows int DECLARE @um_attr_value varchar(100) DECLARE @um_id int DECLARE cur_um_attr CURSOR LOCAL FOR SELECT T2.um_attr_value, T1.um_id FROM um_user_attribute T2 JOIN um_user T1 ON T1.um_id = T2.um_user_id WHERE T2.um_attr_name IN (SELECT T.attribute_name FROM idn_claim_mapped_attribute AS T WHERE T.local_claim_id IN (SELECT T3.mapped_local_claim_id FROM idn_claim_mapping AS T3 JOIN idn_claim AS T4 ON T4.id = T3.ext_claim_id WHERE T4.claim_uri = 'urn:ietf:params:scim:schemas:core:2.0:id')) SET @count_rows = 0 OPEN cur_um_attr -- loop through a cursor FETCH NEXT FROM cur_um_attr INTO @um_attr_value, @um_id WHILE @@FETCH_STATUS = 0 BEGIN UPDATE UM_USER SET UM_USER_ID = @um_attr_value where UM_ID = @um_id SET @count_rows = @count_rows + 1 FETCH NEXT FROM cur_um_attr INTO @um_attr_value, @um_id END CLOSE cur_um_attr END; UPDATE UM_USER SET UM_USER_ID =LOWER(NEWID()) WHERE UM_USER_ID='N' ;