Moving users from one realm to another realm

Tips and tricks
1

Hi everyone,

Do you know what options there are for moving users from one realm to another realm? I tried to update the realm_id in the following tables: user_entity offline_user_session and username_login_failure but it seems that is not enough. Any idea is welcome. Thanks in advance.

1 Like
2

I realized that to move users from one realm to another realm it’s need first the source realm and the destination realm have the configuration in roles and groups.

The following is a script to migrate users that match a email field criteria from a source realm to a destination realm considering only the roles. Try to do something similar also for the groups if it’s needed:

# Procedure to migrate users from a realm to another realm in auth_keycloak database
# PARAMETERS:
# SOURCE_REALM: The source realm
# DESTINATION_REALM: The destination realm
# EMAIL_QUERY: The query to retrieve the users that will be migrated

DROP PROCEDURE IF EXISTS MIGRATE_USERS_BY_EMAIL;

DELIMITER //

CREATE PROCEDURE MIGRATE_USERS_BY_EMAIL(
	SOURCE_REALM VARCHAR(36),
	DESTINATION_REALM VARCHAR(36),
    EMAIL_QUERY VARCHAR(50)
    )
BEGIN
	#UPDATE USER ENTITY
	UPDATE USER_ENTITY SET REALM_ID = DESTINATION_REALM 
		WHERE REALM_ID = SOURCE_REALM AND EMAIL LIKE EMAIL_QUERY;
    
    #UPDATE USER ROLES TO USE THE NEW DESTINATION REALM ROLES ID
    UPDATE USER_ROLE_MAPPING URM
		INNER JOIN KEYCLOAK_ROLE KR_DEST
		ON URM.ROLE_ID = KR_DEST.ID
		INNER JOIN USER_ENTITY UE
		ON URM.USER_ID = UE.ID
		SET URM.ROLE_ID = (SELECT KR.ID FROM KEYCLOAK_ROLE KR WHERE KR.REALM_ID = DESTINATION_REALM AND KR.NAME = KR_DEST.NAME)
		WHERE UE.EMAIL LIKE EMAIL_QUERY AND UE.REALM_ID = DESTINATION_REALM AND KR_DEST.REALM_ID = SOURCE_REALM;
END //

DELIMITER ;

To call to the previous procedure:
CALL MIGRATE_USERS_BY_EMAIL('realmA, 'realmB', '%@realmB.com%');

1 Like