Hi, with the update of IAM 2021.2 tenants are introduced. We’ve already had some kind of custom tenant structure (outside IAM) and I would like to apply the tenant structure as well into IAM. It might be interesting for us, so the external tooling could disappear.
However, I want to upgrade to 2021.2 including a proper tenant structure, unfortunately there is no task to move a user and user groups to it's own (new) tenant, it's all to “Default”. Is this somehow possible?
Best answer by Mark Jongeling
Hi René,
We have made functionality for moving users and copying user groups to another tenant in the upcoming 2021.3 version of our platform. Since that version is not coming out soon, we have created a script for you to use.
In the script you'll need to input a couple of things, namely:
The script will automatically copy all user groups that are assigned to the named users to the new tenant. Users will be moved to the new tenant thereafter and automatically be assigned to the new user groups according to their previous user group assignments.
Only thing left you to do is deleting the old user groups in the Default tenant. That way you can make sure everything is copied correctly before the old user groups are deleted.
The script is below:
/* TARGET IAM */
use [IAM_NAME]
go
declare @target_tenant_id int = 1 --Input the tenant ID the following users and their user groups need to be moved to
--Enter here which users to copy to the target_tenant_id. User groups of these users will automatically be copied.
declare @users_to_move table (
tenant_id tenant_id,
usr_id usr_id
)
insert into @users_to_move (tenant_id, usr_id)
select tenant_id, usr_id
from usr
where usr_id in (
'',
'',
'',
''
)
--Select users to move
select * from @users_to_move
/**********/
/* CODE */
/**********/
declare @user_groups_to_move table (
tenant_id tenant_id,
usr_grp_id usr_grp_id
)
insert into @user_groups_to_move (tenant_id, usr_grp_id)
select u.tenant_id, u.usr_grp_id
from usr_grp_usr u
join @users_to_move m
on m.tenant_id = u.tenant_id
and m.usr_id = u.usr_id
group by u.tenant_id, u.usr_grp_id
--User groups to move
select * from @user_groups_to_move
--Copy user groups to target tenant
declare @include_authorization bit = 1,
@include_usr_grp_pref bit = 1
begin try
--Copy the main table
insert into usr_grp
(
tenant_id,
usr_grp_id,
usr_grp_type,
usr_grp_description,
usr_grp_pref_priority,
usr_grp_resource_claim_priority,
allow_inactive_gui_appls,
active_directory_domain_name,
active_directory_grp_name,
net_bios_domain_name,
active_directory_updated_on,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t1.usr_grp_type,
t1.usr_grp_description,
t1.usr_grp_pref_priority,
t1.usr_grp_resource_claim_priority,
t1.allow_inactive_gui_appls,
t1.active_directory_domain_name,
t1.active_directory_grp_name,
t1.net_bios_domain_name,
t1.active_directory_updated_on,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp t1
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
where not exists (select 1 from usr_grp t2 where t2.tenant_id = @target_tenant_id and t2.usr_grp_id = u.usr_grp_id)
--Copy authorization
if @include_authorization = 1
begin
insert into gui_appl_authorization
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
role_id,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.role_id,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from gui_appl_authorization t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
where not exists (select 1 from gui_appl_authorization t3 where t3.tenant_id = @target_tenant_id and t3.usr_grp_id = u.usr_grp_id and t3.project_id = t2.project_id and t3.project_vrs_id = t2.project_vrs_id and t3.gui_appl_id = t2.gui_appl_id and t3.role_id = t2.role_id)
end
if @include_usr_grp_pref = 1
begin
--Copy user group preferences
insert into usr_grp_pref_gui_appl
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
load_on_start_up,
order_no,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.load_on_start_up,
t2.order_no,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp_pref_gui_appl t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
insert into usr_grp_pref_platform
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
platform,
theme_id,
default_menu_id,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.platform,
t2.theme_id,
t2.default_menu_id,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp_pref_platform t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
insert into usr_grp_pref_start_object
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
start_object_id,
start_object_type,
tab_id,
tab_variant_id,
task_id,
task_variant_id,
report_id,
report_variant_id,
process_id,
order_no,
abs_order_no,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.start_object_id,
t2.start_object_type,
t2.tab_id,
t2.tab_variant_id,
t2.task_id,
t2.task_variant_id,
t2.report_id,
t2.report_variant_id,
t2.process_id,
t2.order_no,
t2.abs_order_no,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp_pref_start_object t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
insert into usr_grp_pref_tab_prefilter_status
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
type_of_path,
path,
tab_id,
tab_variant_id,
tab_prefilter_id,
default_prefilter,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.type_of_path,
t2.path,
t2.tab_id,
t2.tab_variant_id,
t2.tab_prefilter_id,
t2.default_prefilter,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp_pref_tab_prefilter_status t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
end
end try
begin catch
throw;
end catch
--Declare temp name for user
declare @temp_usr_id guid = newid(),
@to_first_name name,
@to_sur_name name,
@to_email email_address,
@usr_id usr_id,
@from_tenant_id int
declare cursor_move_usr cursor for
select tenant_id, usr_id
from @users_to_move
open cursor_move_usr;
fetch next from cursor_move_usr into @from_tenant_id, @usr_id
while @@fetch_status = 0
begin
--Get user info
select @to_first_name = u.first_name,
@to_sur_name = u.sur_name,
@to_email = u.email,
@temp_usr_id = newid() --Refresh temp username
from usr_general u
where u.tenant_id = @from_tenant_id
and u.usr_id = @usr_id
begin try
begin transaction
--Update email of the user to null to prevent error on duplicate email
update usr_general
set email = null
where tenant_id = @from_tenant_id
and usr_id = @usr_id
--Make a copy of the user, name it GUID
exec task_copy_usr_general @from_tenant_id, @usr_id, @target_tenant_id, @temp_usr_id, @to_first_name, @to_sur_name, @to_email
--Delete the original user
exec task_delete_usr_general @from_tenant_id, @usr_id
--Rename the GUID user back to original user
exec task_rename_usr_general @target_tenant_id, @temp_usr_id, @usr_id, @to_first_name, @to_sur_name
commit transaction;
end try
begin catch
rollback transaction;
declare @msg nvarchar(500)
select @msg = '<text>'+@usr_id+'</text><text>'+tenant_name+'</text>'
from tenant
where tenant_id = @target_tenant_id
exec dbo.tsf_send_message 'move_usr_to_tenant_failed', @msg, 1
return;
end catch
fetch next from cursor_move_usr into @from_tenant_id, @usr_id;
end;
close cursor_move_usr;
deallocate cursor_move_usr;
--Final result
select u.tenant_id as from_tenant, @target_tenant_id as target_tenant, u.usr_id, uu.usr_grp_id
from @users_to_move u
join usr_grp_usr uu
on uu.tenant_id = @target_tenant_id
and uu.usr_id = u.usr_id
+23Hi René,
There is no task that moves users to a new Tenant but what you can use is the Mass update functionality of the GUI inside the Users list to update the selected users’ Tenant. Alternatively you can create a SQL script to move users to the correct Tenant based on their email address for example.
The mass update feature cannot be used here. Although it looks like it could be used, the tenant_id isn't updated.
The tenant (and user id) should not be updateable via this method, as it is in the regular form also isn't editable. Maybe a bug in the mass update feature. The fields cannot be updated because they are part of the primary key. Changing those values need to update a lot of other tables if it's not cascading update.
However then a script should be created. Probably based on the task “Rename user”. Sounds like a lot of work :-(.
+5Hi René,
The tenant is a strong entity within IAM and it indeed isn’t trivial to move a user to another tenant.
I’ll check with the team to see if we can add this feature to the next version and provide you with a script for version 2021.2 as well.
How would you expect it to deal with user groups? Would it re-add them in the target tenant if they do not yet exists based on the name? And would it assign roles to those user groups?
Hi René,
The tenant is a strong entity within IAM and it indeed isn’t trivial to move a user to another tenant.
I’ll check with the team to see if we can add this feature to the next version and provide you with a script for version 2021.2 as well.
How would you expect it to deal with user groups? Would it re-add them in the target tenant if they do not yet exists based on the name? And would it assign roles to those user groups?
Hi,
A script or feature would really help us. We haven't evaluated the added value of tenants yet (if it fits for what we think we need), but what I read about it I almost assume it's targeted for ISV users like us. So we should prepare our IAM environment as early as possible I think. For now we can migrate all users and user groups to the ‘Default’ tenant, as nothing changes for an end user perspective. However when we add new customers we should add the tenant immediately.
The user groups… I see there is a strong relation between usr, usr_grp and usr_grp_usr. Now I understand why moving can be a bit harsh, because the usr_grp can have a different tenant than the usr_grp_usr when migrating. In our situation this isn't a problem because we already have user groups per company (tenant). But for generic purposes, when moving a user to another tenant, I should copy the user group to the new tenant and add the user to that group and remove it from the old one.
I think we should upgrade despite this, but start adding new customers in their own tenant, and wait for the existing customers to be moved after there's a fix available. There is no direct need for tenants, but I do expect so in the near future.
Br, René
+5Hi René,
We’ll keep this use case in mind when developing the script. If I understand you correctly, ideally the assigned roles for a usr_grp should also be duplicated when it is moved to another tenant?
Hi René,
We’ll keep this use case in mind when developing the script. If I understand you correctly, ideally the assigned roles for a usr_grp should also be duplicated when it is moved to another tenant?
Hi, In our case the assigned roles also should be copied. I think the table below should explain a bit more what I would expect:
| Tenant | User group | Application (gui_appl) | Roles | |
|---|---|---|---|---|
| Current | Default | Company X - Order picking | Our App - Company X | Read orders, pick order |
| Current | Default | Company Y - Order picking | Our App - Company Y | Read orders, pick order |
| New | Company X | Order picking | Our app - Company X | Read orders, pick orders |
| New | Company Y | Order picking | Our app - Company Y | Read orders, pick orders |
| New* | Default | Company X - Order picking | Our App - Company X | - |
| New* | Default | Company Y - Order picking | Our App - Company Y | - |
*= these groups could be removed because they're copied to new groups and don't have any users in.
+23Hi René,
We have made functionality for moving users and copying user groups to another tenant in the upcoming 2021.3 version of our platform. Since that version is not coming out soon, we have created a script for you to use.
In the script you'll need to input a couple of things, namely:
The script will automatically copy all user groups that are assigned to the named users to the new tenant. Users will be moved to the new tenant thereafter and automatically be assigned to the new user groups according to their previous user group assignments.
Only thing left you to do is deleting the old user groups in the Default tenant. That way you can make sure everything is copied correctly before the old user groups are deleted.
The script is below:
/* TARGET IAM */
use [IAM_NAME]
go
declare @target_tenant_id int = 1 --Input the tenant ID the following users and their user groups need to be moved to
--Enter here which users to copy to the target_tenant_id. User groups of these users will automatically be copied.
declare @users_to_move table (
tenant_id tenant_id,
usr_id usr_id
)
insert into @users_to_move (tenant_id, usr_id)
select tenant_id, usr_id
from usr
where usr_id in (
'',
'',
'',
''
)
--Select users to move
select * from @users_to_move
/**********/
/* CODE */
/**********/
declare @user_groups_to_move table (
tenant_id tenant_id,
usr_grp_id usr_grp_id
)
insert into @user_groups_to_move (tenant_id, usr_grp_id)
select u.tenant_id, u.usr_grp_id
from usr_grp_usr u
join @users_to_move m
on m.tenant_id = u.tenant_id
and m.usr_id = u.usr_id
group by u.tenant_id, u.usr_grp_id
--User groups to move
select * from @user_groups_to_move
--Copy user groups to target tenant
declare @include_authorization bit = 1,
@include_usr_grp_pref bit = 1
begin try
--Copy the main table
insert into usr_grp
(
tenant_id,
usr_grp_id,
usr_grp_type,
usr_grp_description,
usr_grp_pref_priority,
usr_grp_resource_claim_priority,
allow_inactive_gui_appls,
active_directory_domain_name,
active_directory_grp_name,
net_bios_domain_name,
active_directory_updated_on,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t1.usr_grp_type,
t1.usr_grp_description,
t1.usr_grp_pref_priority,
t1.usr_grp_resource_claim_priority,
t1.allow_inactive_gui_appls,
t1.active_directory_domain_name,
t1.active_directory_grp_name,
t1.net_bios_domain_name,
t1.active_directory_updated_on,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp t1
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
where not exists (select 1 from usr_grp t2 where t2.tenant_id = @target_tenant_id and t2.usr_grp_id = u.usr_grp_id)
--Copy authorization
if @include_authorization = 1
begin
insert into gui_appl_authorization
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
role_id,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.role_id,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from gui_appl_authorization t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
where not exists (select 1 from gui_appl_authorization t3 where t3.tenant_id = @target_tenant_id and t3.usr_grp_id = u.usr_grp_id and t3.project_id = t2.project_id and t3.project_vrs_id = t2.project_vrs_id and t3.gui_appl_id = t2.gui_appl_id and t3.role_id = t2.role_id)
end
if @include_usr_grp_pref = 1
begin
--Copy user group preferences
insert into usr_grp_pref_gui_appl
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
load_on_start_up,
order_no,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.load_on_start_up,
t2.order_no,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp_pref_gui_appl t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
insert into usr_grp_pref_platform
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
platform,
theme_id,
default_menu_id,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.platform,
t2.theme_id,
t2.default_menu_id,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp_pref_platform t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
insert into usr_grp_pref_start_object
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
start_object_id,
start_object_type,
tab_id,
tab_variant_id,
task_id,
task_variant_id,
report_id,
report_variant_id,
process_id,
order_no,
abs_order_no,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.start_object_id,
t2.start_object_type,
t2.tab_id,
t2.tab_variant_id,
t2.task_id,
t2.task_variant_id,
t2.report_id,
t2.report_variant_id,
t2.process_id,
t2.order_no,
t2.abs_order_no,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp_pref_start_object t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
insert into usr_grp_pref_tab_prefilter_status
(
tenant_id,
usr_grp_id,
project_id,
project_vrs_id,
gui_appl_id,
type_of_path,
path,
tab_id,
tab_variant_id,
tab_prefilter_id,
default_prefilter,
insert_user,
insert_date_time,
update_user,
update_date_time
)
select
@target_tenant_id,
u.usr_grp_id,
t2.project_id,
t2.project_vrs_id,
t2.gui_appl_id,
t2.type_of_path,
t2.path,
t2.tab_id,
t2.tab_variant_id,
t2.tab_prefilter_id,
t2.default_prefilter,
dbo.tsf_user(),
sysdatetime(),
dbo.tsf_user(),
sysdatetime()
from usr_grp_pref_tab_prefilter_status t2
join usr_grp t1
on t1.tenant_id = t2.tenant_id
and t1.usr_grp_id = t2.usr_grp_id
join @user_groups_to_move u
on u.tenant_id = t1.tenant_id
and u.usr_grp_id = t1.usr_grp_id
end
end try
begin catch
throw;
end catch
--Declare temp name for user
declare @temp_usr_id guid = newid(),
@to_first_name name,
@to_sur_name name,
@to_email email_address,
@usr_id usr_id,
@from_tenant_id int
declare cursor_move_usr cursor for
select tenant_id, usr_id
from @users_to_move
open cursor_move_usr;
fetch next from cursor_move_usr into @from_tenant_id, @usr_id
while @@fetch_status = 0
begin
--Get user info
select @to_first_name = u.first_name,
@to_sur_name = u.sur_name,
@to_email = u.email,
@temp_usr_id = newid() --Refresh temp username
from usr_general u
where u.tenant_id = @from_tenant_id
and u.usr_id = @usr_id
begin try
begin transaction
--Update email of the user to null to prevent error on duplicate email
update usr_general
set email = null
where tenant_id = @from_tenant_id
and usr_id = @usr_id
--Make a copy of the user, name it GUID
exec task_copy_usr_general @from_tenant_id, @usr_id, @target_tenant_id, @temp_usr_id, @to_first_name, @to_sur_name, @to_email
--Delete the original user
exec task_delete_usr_general @from_tenant_id, @usr_id
--Rename the GUID user back to original user
exec task_rename_usr_general @target_tenant_id, @temp_usr_id, @usr_id, @to_first_name, @to_sur_name
commit transaction;
end try
begin catch
rollback transaction;
declare @msg nvarchar(500)
select @msg = '<text>'+@usr_id+'</text><text>'+tenant_name+'</text>'
from tenant
where tenant_id = @target_tenant_id
exec dbo.tsf_send_message 'move_usr_to_tenant_failed', @msg, 1
return;
end catch
fetch next from cursor_move_usr into @from_tenant_id, @usr_id;
end;
close cursor_move_usr;
deallocate cursor_move_usr;
--Final result
select u.tenant_id as from_tenant, @target_tenant_id as target_tenant, u.usr_id, uu.usr_grp_id
from @users_to_move u
join usr_grp_usr uu
on uu.tenant_id = @target_tenant_id
and uu.usr_id = u.usr_id
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
