From the Microsoft Dynamics GP Application Level Security Series. This is the final article in this series at this time.
Microsoft Dynamics GP version 10.0 introduces a new pessimistic task and role based security model. This new model has a completely different table structure to the previous optimistic user and class based security model. Which means that when upgrading the security settings cannot just be transferred across.
There is a security conversion tool which can be used during the upgrade of the DYNAMICS database. If upgrading from v8.0 please ensure that v8.0 Service Pack 5 is installed. If planning to use the security conversion tool, please ensure that v10.0 Service Pack 1 or later is installed prior to running Microsoft Dynamics GP Utilities.
Before you use the tool, you should be aware of how it works and what the consequences of using the tool are.
In the new model, each user can be assigned multiple security roles. Each role is made up of tasks and each task is made up of operations. Operations are the actual access permissions as shown in v8.0 or v9.0 Security (Standard or Advanced).
If you perform the security conversion when you upgrade to Microsoft Dynamics GP 10.0, the following actions occur:
- For each user in a company, a task and a role are created. The name of the task and the role is CNV_USERID_COID.
For example, if you perform the security conversion for the Phyllis user
in the TWO company, the task and the role are named as CNV_PHYLLIS_TWO.
- The existing security access is assigned to the CNV_USERID_COID task. Additionally, the task is assigned to the CNV_USERID_COID role.
- The CNV_USERID_COID role is assigned to the user for the company.
- All accesses to alternate forms and to modified forms are converted to an identifier (ID) of CNV_USERID_COID. The ID is assigned to the user in each company.
- The SY02000 security table and the SY40300 security table in Microsoft Business Solutions – Great Plains 8.0 or in Microsoft Dynamics GP 9.0 are removed.
So when you use the conversion tool, it will create a new security task and matching security role as well as an alternate/modified form and report ID for each user/company combination. This can create a very large number of records in the security tables.
Maintenance would need to be handled on a individual user/company combination as there are no classes or multi-selection of users in v10.0.
While using the conversion can quickly get a system up and running with the same security as before, it does not leverage the role and task based model. Upgrading to v10.0 provides a perfect opportunity to redefine security using the new model.
To view the records created during the security conversion, you can use the following SQL scripts:
select * from DYNAMICS.dbo.SY10700 where SECURITYTASKID like ‘CNV%’
select * from DYNAMICS.dbo.SY09000 where SECURITYTASKID like ‘CNV%’
select * from DYNAMICS.dbo.SY10600 where SECURITYROLEID like ‘CNV%’
select * from DYNAMICS.dbo.SY09100 where SECURITYROLEID like ‘CNV%’
select * from DYNAMICS.dbo.SY10500 where SECURITYROLEID like ‘CNV%’
select * from DYNAMICS.dbo.SY10800 where SECMODALTID like ‘CNV%’
select * from DYNAMICS.dbo.SY09200 where SECMODALTID like ‘CNV%’
select * from DYNAMICS.dbo.SY10550 where SECMODALTID like ‘CNV%’
To remove the records created during the security conversion, you can use the user interface to manually delete the added tasks and roles or you can use the following SQL scripts:
delete from DYNAMICS.dbo.SY10700 where SECURITYTASKID like ‘CNV%’
delete from DYNAMICS.dbo.SY09000 where SECURITYTASKID like ‘CNV%’
delete from DYNAMICS.dbo.SY10600 where SECURITYROLEID like ‘CNV%’
delete from DYNAMICS.dbo.SY09100 where SECURITYROLEID like ‘CNV%’
delete from DYNAMICS.dbo.SY10500 where SECURITYROLEID like ‘CNV%’
delete from DYNAMICS.dbo.SY10800 where SECMODALTID like ‘CNV%’
delete from DYNAMICS.dbo.SY09200 where SECMODALTID like ‘CNV%’
delete from DYNAMICS.dbo.SY10550 where SECMODALTID like ‘CNV%’
Security Table Information
|Table Name||Physical Name||Description|
|sySecurityMSTRRole||SY09100||Master table for Security Roles|
|sySecurityAssignTaskRole||SY10600||Assignment table for Security Roles to Tasks|
|sySecurityMSTRTask||SY09000||Master table for Security Tasks|
|sySecurityAssignTaskOperations||SY10700||Assignment table for Security Tasks to Operations|
|sySecurityMSTRModAlt||SY09200||Master table for Alternate/Modified Groups|
|sySecurityAssignModAltOperations||SY10800||Assignment table for Alternate/Modified Operations|
|sySecurityAssignUserRole||SY10500||Assignment table for Users to Security Roles|
|sySecurityAssignUserModAlt||SY10550||Assignment table for Users to Alternate/Modified Groups|
For more information on upgrading to v10.0 and the Role based Security model, please see the following pages:
Hot Topic: Updating to Microsoft Dynamics GP 10.0
The following Knowledge Base (KB) article also answers many questions:
My personal recommendation would be to not use the security conversion, but to take the opportunity to redesign the security to use the new model. You can define and test the security settings in a test environment and then use the method described in the How to transfer Security Tasks and Roles between Systems post to transfer the settings to the live system once it is upgraded.
[Edit] There are times where you will not have the time to run the analysis required to redesign the security for a site prior to or during the upgrade project. In this case, the security conversion tool can be used to get the site upgraded and operational quickly. However, I would then recommend that a post upgrade project be run to migrate the security to the new model and eventually remove the security roles and tasks created by the conversion tool.
29-Sep-2009: I recently heard of a login performance issue which was caused by a large number of records in the security tables. When logging in, a background process runs through the menu structure and hides any menu choices for which the current user does not have access. Using the security conversion tool can create a vast number of records in the security tables which can have an adverse effect on performance during this process.
10-May-2010: Added comments about upgrading security as a second stage of the system upgrade.
Ref: Portions included in KB 951229