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!