Solved

Move users and user groups to other tenant

  • 2 June 2021
  • 7 replies
  • 116 views
R
Rank
Userlevel 5
Badge +15

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?

icon

Best answer by Mark Jongeling 17 June 2021, 09:19

View original

7 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi 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.

"Wake me up before you low-code"
R
Rank
Userlevel 5
Badge +15

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 :-(.

Anne Buit
Rank
Userlevel 7
Badge +5

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?

R
Rank
Userlevel 5
Badge +15

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é

Anne Buit
Rank
Userlevel 7
Badge +5

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?

R
Rank
Userlevel 5
Badge +15

 

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.

Mark Jongeling
Rank
Userlevel 7
Badge +23

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:

  • On which IAM it needs to run;
  • To which existing tenant id (Integer) you would like to copy;
  • And which users to copy to the new tenant

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

 

 

1 Attachment
"Wake me up before you low-code"

Reply


Terms & ConditionsCookie settings