Following on from my previous post (Using Plug-Ins To Modify Views), I developed a set of plug-ins to manage security at an account level in order to satisfy the requirements for one of my corporate banking clients. The scenario was quite straightforward, in that a Client Director owns a number of accounts, grouped into one or more portfolios. Each portfolio has specific team members assigned to it, and these team members need access to the account record.
Now, on first pass this would seem to be a simple case of setting up automatic sharing of account records based on portfolio membership. However, the problem with this is performance & scalability.
Why Large-Scale Sharing Is Bad
Sharing in CRM is designed to provide explicit access to records when the role-based security doesn’t allow it. Whenever a record is shared, an entry is added to the PrincipalObjectAccess table in the database. In addition, depending on the cascading options set on different entity relationships, an entry is added for each child record. For example, suppose you take the out-of-the box account entity and look 1:N relationships to the various child entities (e-mail, phone call, task, appointment, letter, fax, lead, opportunity, quote, order, invoice, contact, case, note etc.). Each time you share the parent account, an entry is added for each child record as well. For large-scale CRM deployments, it doesn’t take long for the PrincipalObjectAccess table to grow to several million records, which can have a major impact on performance.
If you want to understand this in more depth, here are some good articles on the subject:
- Dynamics CRM in The Field Blog: Excessive PrincipalObjectAccess Table Growth in CRM 4.0 from the Reparent – Cascade All setting
- Dynamics CRM in The Field Blog: PrincipalObjectAccess–Performance Recommendations
Team Ownership As An Alternative To Sharing
New to CRM 2011 is team ownership of records, and it is this feature that I believe offers a more scalable solution to sharing. In order to make this work, I decided that I would have a separate team for each account record, so for a CRM implementation with 40,000 accounts there would be 40,000 teams. I did think about having a team for each portfolio to keep the number of teams to a relatively small number, but there are some possible future requirements that will mean each account may need slightly different user access.
Designing The Solution (Part I) – Auto Create Teams
Initially, I created a plug-in that would fire whenever an account was created, and would perform the following tasks:
- Create a new team
- Associate the team with a security role which has rights to allow the team to “own” an account.
- Assign ownership of the account to the new team
This seemed sensible, until I started to notice that the team was being created and associated with the security role, but the account ownership wasn’t being set. Although I couldn’t pin down the exact reason, I suspect I was victim of a race condition. Whenever a security principle is created (such as a team or a user), a number of background operations are kicked off, and I suspect that trying to assign the account to a team before these operations have completed results in a failure.
To overcome this issue, I split the plug-in into two. As before, the first plug-in would fire whenever an account was created, but this time it would perform the following tasks:
- Create a new team
- Associate the team with a security role
- Update the account, adding the team to a custom lookup field “srh_coverageteamid” (a new N:1 relationship between the account and team entities).
A second plug-in would then fire whenever the “srh_coverageteamid” field gets updated, and would perform just one task:
- Assign ownership of the account to the team specified in the “srh_coverageteamid” field
This did the trick, and now whenever a new account is created, a corresponding team is also created, and assigned ownership. To finish off this side of the design, I created a third plug-in that would delete the team whenever the corresponding account was deleted.
Designing The Solution (Part II) – Custom Portfolio Entity
The next part of the design required a custom portfolio entity, with a custom 1:N relationship to the account entity. In addition, I defined multiple N:1 relationships with the user entity to track various portfolio roles such as Client Director, Associate Client Director and Assistant Client Director.
Designing The Solution (Part III) – Auto Team Membership
Now we have a portfolio containing several roles, I needed a series of plug-ins to modify team membership in the following circumstances:
- When an account is added or removed from a portfolio, change the account coverage team members.
- When portfolio members change, change the coverage team members for all accounts in the portfolio.
- When the Client Director changes, change the owner of the portfolio to match.
Designing The Solution (Part IV) – Non Functional Considerations
Now there is too much complexity here to dive into detail into each plug-in, but one of the challenges involved was how to make the solution perform well. For example, I knew that all the plug-ins should run asynchronously. Imagine a situation where a portfolio has 500 accounts, and you update the portfolio members. That’s 500 teams that need to be updated..!! A synchronous plug-in would take far too long to complete this operation, and this has the potential to bring the system to halt.
However, when designing asynchronous processes, you have to be very careful to avoid race-conditions where a process starts before the previous process has completed.
Another issue is security. Most plug-ins run in the security context of the user who made the change that caused the plug-in to run. However, when modifying security roles via team memberships, you need the plug-ins to run with administrator privileges as you don’t want to be granting these kind of permission to every user.
One final issue is the default cascade options set on the account entity relationships. Think what would happen if you regularly change ownership on an account. By default, all the child activities and entities would also change owners. My design has tried to bypass this issue, by creating an owning team for each account when that account is first created, and then modifying only the team members. The coverage team (i.e. the actual account owner) does not change over the life of the account.
Designing The Solution (Part V) – Bringing It All Together
For those of you who want to try this out, I have packed up the solution and made it available for download. The solution package contains the following
- A new Portfolio custom entity
- New custom entity relationships for account and team entities
- A new security role that will allow teams to own accounts
- 7 Plug-ins to handle the following events:
- Account is created
- Account is deleted
- Account is added/removed from a Portfolio
- Account coverage team is updates
- Portfolio is created
- Portfolio client director is updated
- Portfolio members are updated
You can download a.zip file here, and this contains both managed and unmanaged solution packages, as well as the Visual Studio 2010 project containing the plug-in source code.
This posting is provided “AS IS” with no warranties, and confers no rights.