Excessive and Systematic Use of Sharing in Microsoft Dynamics CRM
Background
The authorization model in Microsoft Dynamics CRM is sophisticated and dynamic. Traditional Access Control Lists (ACL) patterns may offer also Role-based security with a granularity down to individual records and even fields. Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online also provide this capability, but a key difference is that Microsoft Dynamics CRM relies on record ownership and the organizational hierarchy of business units. As a result, access levels are based on the owner of an individual record and the business unit to which the owner belongs.
Record-level sharing is available to grant privileges beyond those for specific records and their child records (which are applied by configurable cascading rules).
Microsoft Dynamics CRM 2011 also introduces team ownership of records as well as Field-Level Security (FLS), which can be used to restrict access to individual fields based on role.
The Need
Some enterprise customers require a complex security models to accommodate:
- A complex “security matrix” that does not adhere to a traditional organizational hierarchy.
- A strict “segregation of duties” or “need to know” model.
- Exceptions related to regulatory or other compliance standards.
The Idea
It can be tempting to use the record sharing mechanism to programmatically implement literally any desired model and “matrix.” After all, you are in control!
Your design would leverage the Microsoft Dynamics CRM security configuration and include a fairly simple organizational hierarchy, relying on custom code in plug-ins and so on to apply the record-level sharing necessary to implement the desired security model. Of course, records would not be explicitly shared with individual users. Rather, you would rely on team sharing and the native cascading rules that would apply to ensure appropriate sharing to child records.
The Impact
Whenever a record is shared to a user or a team, an entry is added to the PrincipalObjectAccess (PoA) table in the database, so that there is one per share. In addition, subject to the cascading rules set on entity relationships, an entry is added for each share of each child record. For example, sharing a customer account entity with 1:N relationships to various child entities (e-mail, phone call, task, appointment, letter, contact, case, etc.) will result in an entry for each ruled-in child that is being shared specifically. For large-scale Microsoft Dynamics CRM deployments that leverage sharing, the PoA table can grow to several million records within a short time.
The Question
Simply put, the question is “Does sharing scale”?
Good News
I worked on a project for an enterprise customer with a complex (by request) set of security requirements that is implemented by taking advantage of the extensive use of sharing, which had resulted in having a PoA table with an estimated 144M records. However, using optimization techniques and avoiding redundancy reduced the PoA table record count to 30M. Both the initial and reduced sized databases provided acceptable performance on the server-side; while it is worth noting that the SQL server was well optimized.
A large bank had performance tests conducted on a scenario in which 10% of the records in a 1 terabyte database were shared. There was no significant degradation in performance after sharing 10% of the records with a PoA table that included a few millions of records. However, the bank’s infrastructure is highly optimized, and in a less optimized environment the impact on performance would in all likelihood be more noticeable.
The “But”
Sharing has never been designed as a general large-scale means of implementing a security model:
- The product and its UI do not provide native functionally to show, report, and monitor sharing across multiple records
- Cascading rules for child records may take excessive amount of time to process if millions of records are affected
- Tests and experience indicates that sharing may not scale well beyond a PoA table exceeding a million or “a few million” entries – depending on hardware and tuning. Number of entries will be the total product of number of records shared and the times shared; i.e. a single data record shared to 10 users or teams will generate 10 PoA entries.
- Integrity of automated sharing relies on custom logic and code implementations, hence could be volatile and subject to errors in case of poor design or implantation not catering correctly for e.g. manual imports, partial data restores, manual sharing, etc.
- Bulk integrity reporting and correction to address above point would require additional custom code.
The Alternative
I would strongly suggest investigating alternative models. Often a complex security model can be implemented with Custom Teams without Sharing. That is a team owning a set of records and by the ownership gets the right privileges. Note that while a user can be member of many teams, only one team can own a record, i.e. only one set of privileges can be assigned – no option of a “read-only team” and “editor team”.
This pattern is foreseen to scale up to thousands of Custom Teams as well as for ownership assigned to Users or Default Teams. But as always, carefully consider all evaluated factors and specifically:
- Plan Custom Team structure and combinations carefully to avoid having teams for irrelevant combinations. All combinations of just 15 Business Teams with a Custom Team for each combination (A, B, C, A+B, B+C, A+C, A+B+C,…) would generate 215-1 = 32.767 Custom Teams!
- Plan and design automation and integrity assurance supported by custom code, also considering need to view, report and interact with any record’s current Custom Team ownership.
- Conduct performance and duration tests of relevant bulk changes as well as security role changes for thousands Custom Teams.
Bernt Bisgaard Caspersen
Dynamics Solutions Architect | Microsoft Center of Excellence
