Scalable Security Modeling with Microsoft Dynamics CRM 2013

Did you spend enough time modeling the security structure of your CRM solution? Unfortunately, most CRM implementations overlook the need to identify and build the appropriate model as the first step to designing a stable system. This misguided approach will often lead to complex administration and overall system slowness.


Today we’ll review the article “Scalable Security Modeling with Microsoft Dynamics CRM 2013” by Roger Gilchrist, which can be downloaded here:
In this article, Gilchrist discusses the wide range of security modeling features included in CRM 2013, and how to choose the most appropriate approach to implementing your security model. Even though there are several different security mechanisms that can play together when designing your security model, the best results are derived from a clean and simple approach. This document should be reviewed by any CRM professional, from sales thru implementation to service. Take some time to review this article, and share your thoughts and experience here. Our development team took a deep dive into this article, and I’d like to share a couple reviews from some of our brightest minds.


Cristina’s Review

In CRM, most security can be set up using a combination of record ownership, security roles and business units. However, every so often we will receive a request to implement a custom sharing solution to allow granular and explicit privileges to select records. In CRM 2011, the only way of accomplishing this was to use the SDK’s GrantAccessRequest class which allows you to share records to a security principal (user or team) via custom code. For each security principal that exists in the system, a new row is added to the SystemUserPrincipal table and for each sharing rule between a security principal and an individual entity instance, a new row is added to the PrincipalObjectAccess table. This table can grow quickly if you are sharing several thousands of records, each to multiple users and cascade sharing is set up through parent-child entity relationships(which it is, by default, for most out of the box entities). If the business need doesn’t require cascade sharing to all child entity instances, disabling this can dramatically help mitigate growth of this table. The chart below displays how quickly the # of shares recorded in the PrincipalObjectAccess table can grow when cascade sharing is enabled:


If the users require the same access rights to each record, another common approach to mitigating PrincipalObjectAccess table growth rate is to create teams of users and share the records to each team. So instead of a new row being added to the PrincipalObjectAccess table per user that is shared to an entity instance, a new row would be added per Team that is shared.


So why should we be so concerned with the size of this table? For one, it can take up considerable storage space on the server. Secondly, as the PrincipalObjectAccess table grows, it can start to cause performance issues. The magnitude of these issues depends on the level of optimization of the SQL server. Why? Every time records are accessed, whether it be a user opening a record, looking at a view, running a report or the retrievemultiple query is running in a custom workflow, there are SQL queries happening in the background to verify the users access rights to those records and child records. One join it makes is to the SystemUserPrincipal table to determine the different principals that the user is a member of and then it joins the PrincipalObjectAccess table to determine the access rights for each of those principals. This chart demonstrates those joins for a single user that is a member of multiple teams which have shared access rights to multiple records in the system:


The system must also make joins to determine the rights granted via the security roles and business unit assigned to the user and each team they are a member of. Therefore, having millions of rows in the SystemUserPrincipal and PrincipalObjectAccess tables can cause a bottleneck for access. This chart shows the complexity of various access checks that must be performed to determine the privileges for a single user belonging to a single team each time they make an attempt to access records. You can imagine how complicated it can get if the user belongs to multiple ownership teams:


Each time a record is accessed, those access rights, which include ownership rights via a users business unit, security role, rights granted via shares, ownership teams they are a member of and the security role and business unit of those ownership teams to are all cached on the application server. This is to help optimize subsequent access to data and each time a user or team memberships change, the application server is notified with each change to flush the related info from the cache.  If there are changes made regularly to users or team memberships, this can impact performance.


To help eliminate some of these performance issues, Microsoft has included a new type of team called “Access Teams” in CRM 2013.  They are a lightweight version of the CRM 2011’s version of Teams (now called Ownership Teams) made specifically to accommodate high volume sharing scenarios.


Here are some key points:

  • They do not require a security role or business unit.  This eliminates the need for the system to query additional security roles and business units when a user is accessing record(s).
  • They don’t get cached at the application server (because access is not derived using a privilege or ownership check). So if your teams are dynamically formed and dissolved, this eliminates the need for access cache flushing by the application server which can slow down overall performance.
  • Can be enabled at the entity level in which they will automatically be created/deleted by the system as members are added/removed.  This helps to reduce and optimize your code since the system will handle half of the team sharing process for you. However, you do have the ability to create the teams manually or programmatically.


If you have already created a solution using Ownership Teams and you want to start utilizing Access Teams, you have two options:

  1. Convert all Ownership Teams to Access Teams programmatically via the SDK using the ConvertOwnerTeamToAccessTeamRequest.  You would then continue to use the GrantAccessRequest to grant the Access Team privileges, and the AddMembersTeamRequest and RemoveMembersTeamRequest to add and remove members. When creating new Access Teams, you will need to specify the Team.TeamType attribute to “Access”.  This method may best be used if you already have a defined set of teams that are shared to records.
  2. Enable the shared entity for Access teams, create an Access Team Template to define the access rights the users will have and then add a subgrid for the team on the entity form. Then, use the AddUserToRecordTeamRequest and RemoveUserFromRecordTeamRequest to add and remove members programmatically to each entity instance and then dissolve the Ownership Teams.  The system will automatically create the teams for you once a member is added to each entity instance.  This method is best to use if you have a unique set of users shared to each entity instance that dynamically change, users belong to hundreds or thousands of teams, and/or you want to give users the ability to easily add/remove members on the fly.  When in doubt, test it out to be sure it meets the business expectations.


So if Access Teams are so great, why do Ownership Teams still exist?  Ownership Teams do have benefits over Access Teams. If owning records by multiple users is a requirement and you have a defined set of teams known at the time of design, use Ownership Teams.  Record ownership generally has less of an impact on system performance than record sharing, whether it be user owned or team owned. If owning records by a single user is a requirement and you have a requirement to grant granular and explicit access to an unknown number of records or you have teams which must be dynamically formed and dissolved, then use Access Teams to share the records.  In this scenario, using Access Teams instead of Ownership Teams will reduce the querying needed by the system to determine a users access rights and they aren’t cached and flushed on the application server each time membership changes. Therefore, Access Teams can have a much lighter impact on system performance than Ownership Teams.


Mitch’s Review

Authorization is a key part to any security system.  It is often defined as the methods used to determine whether the user is entitled to access the system, and what actions within the system they are permitted perform.  Without a proper security structure or a method of authorization, it is difficult to ensure that the user will only see the information that is relevant to their title.


In Microsoft Dynamics CRM 2013, the approach to security is hierarchical in nature.  A structure like the one below allows for restricted access to the system depending on a user’s role and position in the company:


In this example, the Corp block represents the entire Corporation and encompasses access to the entire company database.  Going down in the diagram, the larger is split into smaller blocks, which are sectioned off by things such as region and different company branches.  The smaller sections are often represented in CRM as Business Units.  Business Units are hierarchical just as the required security model for authorization is. Business Units can be related by a Parent and Child relationship.  This is easy to see in the diagram above where the children are resting at the bottom with restricted access and a user with authority over the entire corporation would have full access at the top.  The less access a user has to the system, the smaller the portion of the user’s related Business Unit.


In short, CRM uses these Business Units to model the hierarchical access to data that real world security methods use.  The Parent-Child relationship between each Business Unit allows for a proper sectioning of the corporate data, where a CRM system designer can place specific users and teams into an appropriate part of the Corporation so that they have access to only relevant data.


Take Away Items

 Designate a CRM security specialist in your organization.  It’s critical that all system designers truly understand how to properly set up a security structure, but you also want to provide them an internal resource they can query.  Do they really need to share the bulk of their accounts with every user, or is there a better organizational structure that can efficiently serve the same purpose?


 Share this document with your client.  During your project planning phase, you are challenged with learning the client’s business.  Identify the key technical contact who truly understands their business structure, and challenge them to learn all nuances of this CRM security structure.  Empowering the client to help you build the proper model is the recommended approach.


– Monitor the systems.  Have your team record a baseline, then stress test it & see the differences.  This is incredibly important when implementing resource expensive activities, like sharing records with cascading relationships.   Do they really need to see every activity related to a shared record?  The client will probably insist ‘yes’ when initially designing the system.  Once you show them the performance hit and reveal the cost of additional resources to support it, they will be better informed to scale back their requests.


Homework Time!

Yep, everyone gets assigned homework around here.  The best way to succeed in life is to always keep yourself physically, mentally & emotionally healthy, so use this opportunity to exercise your mind.

  • Take a look at your existing deployment’s security structure.  What works best?   What doesn’t?
  • Do you see way to optimize the system by separating historical and active data?
  • Can you recognize usage patterns that weren’t accounted for in the initial design of the system?
  • Are you sharing too many records?  Remember that sharing a record with single users is incredibly inefficient, and team sharing is a great approach that will pay immediate dividends.  Double check your Cascading Actions and the PrincipalObjectAccess table.
  • BONUS Code Time
    Did you notice system lag when you upgraded your team sharing code from v.4.0 to v.2011?  The CRM 4 teams acted like the lightweight Access teams, but a direct code upgrade makes CRM 2011 teams act like heavy Owner Teams.  CRM 2013 now gives you the option to choose with this one line of code:
    TeamType= new OptionSetValue(1) //Owner=0, Access=1


When you access the CRM 2013 SDK, you will find several examples of this code.  Play with the code, time stamp the process and find the performance delta between these two types of teams.  Post your findings here.


Keep learning, my friends 🙂