Preserving Custom Roles, Secured Actions, and Assignments in Infor CRM (formerly Saleslogix) Upgrades

When upgrading between levels of Infor CRM one of the tasks that is commonly needed is to run a batch/exe file to populate the web Role Security.  In doing this it clears out the existing tables, before re-populating which can cause you to lose any custom Roles/ Secured Actions, or their assignments.  Lets take a look at a process to preserve these things.

There are 4 tables involved in the Role and Secured Actions:

  1. ROLE stores the roles
  2. USERROLE stores which users are a part of the role
  3. SECUREDACTION defines the secured actions
  4. SECUREDACTIONROLE identifies which secured actions are in which role

To preserve your custom actions, roles, and assignments the first thing we need to do before upgrading is to make copies of the tables involved so that they are not lost in the upgrade.

Running this SQL script will create separate copies of each table, each suffixed with “_bak”:

select * into sysdba.role_bak from sysdba.role
select * into sysdba.userrole_bak from sysdba.userrole
select * into sysdba.securedaction_bak from sysdba.securedaction
select * into sysdba.securedactionrole_bak from sysdba.securedactionrole

Now that you have a backup of your custom items, you can now go ahead and run the upgrade steps to populate the secured actions and roles.

After the upgrade we can then utilize a comparison between our backup tables and the newly populated tables to find those items that only now exist in the backup and add those back in.

This set of SQL scripts will do just that, populating the standard tables from the backup tables with the data that only exists in the backup tables. In this sample new IDs are created by changing the first character of the ID from what it is to an “F”:

insert into sysdba.role
select
‘F’ + right(roleid,11) ROLEID,
createuser, createdate, modifyuser, modifydate,
rolename, roledescription
from sysdba.role_bak where rolename not in (select rolename from sysdba.role)
GO
insert into sysdba.securedaction
select
‘F’ + right(securedactionID,11) securedactionID,
createuser, createdate, modifyuser, modifydate,
NAME, DESCRIPTION, PARENT
from sysdba.securedaction_bak where name not in (select name from sysdba.securedaction)
GO
insert into sysdba.userrole
select
‘F’ + right(a.userroleid,11) userroleid,
a.createuser, a.createdate, a.modifyuser, a.modifydate,
a.userid, ‘F’ + right(a.roleid,11) roleid
from sysdba.userrole_bak a left join sysdba.userrole b
on a.userid=b.userid and right(a.roleid,11)=right(b.roleid,11)
where b.userroleid is null
GO
insert into sysdba.securedactionrole
select
‘F’ +  + right(a.securedactionroleid,11) securedactionrole,
a.createuser, a.createdate, a.modifyuser, a.modifydate,
‘F’ + right(a.actionid,11) actionid,
‘F’ + right(a.roleid,11) roleid
from sysdba.securedactionrole_bak a left join sysdba.securedactionrole b
on right(a.actionid,11)=right(b.actionid,11) and right(a.roleid,11)=right(b.roleid,11)
where b.securedactionroleid is null
GO

Finally, now that we have the custom data back in, and have done your diligent testing, we can get rid of our backup tables.

    drop table sysdba.role_bak
drop table  sysdba.userrole_bak
drop table  sysdba.securedaction_bak
drop table  sysdba.securedactionrole_bak

Boiler plate warning:  Running SQL statements against your database can be dangerous.  Do this only if you understand what you are doing.  Always perform these kind of things in a test environment first!

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

2 Comments

  1. Thanks, Kris. Line 6 has a syntax error:
    where rolename not in (select sysdba.rolename from role)

    Another common use for this kind of script would be comparing databases directly where a=TESTDB and b=PRODUCTIONDB

    In this case, it would be necessary to use an adapter to insert the missing roles, so the updates are properly synced to each remote DB.

    Either way, it would be nice to see some sort of export functionality built into the role management area of the web client.

    Reply
    • Thanks for pointing out the syntax error. Fixed now. There is an export feature for roles and secured actions. You can modify the RoleSecurityInstall.bat and change the switches in the command to do an export of data rather than a load of the data. If you run the RoleSecurityUtility.exe directly it will open with a command window showing you the possible switches you can put into the batch file. Using those you could export data from a test system and then import that into a production system, for instance.

Leave a Reply to Marcus Cancel reply

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!