Figuring out shares in the PrincipalObjectAccess POA table in CRM

Not a long article, this one is to support my recent presentation at the UK CRM User Group about security design and performance. I mentioned that when you are trying to figure out  what is going on in the PrincipalObjectAccess (aka “POA”) table, it can be useful to separate out things like Users and Teams, and furthermore to separate Owner Teams from Access Teams.

I also find it helpful to join to the right tables to get friendly names rather than go blind on GUIDs. Note: in the example code below I have pulled in first names for users to keep to shorter columns for demo purposes, in larger Orgs you might need full names, but it depends on what you are trying to troubleshoot.

I am excluding some object types from the results, because every user has their own User record and User Settings record shared with them. These are inevitable and not useful to most troubleshooting scenarios.

Of course, for some scenarios you will be trying to figure out why some users have access to records of a specific entity, in which case you might want to replace “POA.[ObjectTypeCode] NOT IN (8,150)” with an IN or an equals.

SELECT TOP 1000
CASE WHEN POA.[PrincipalTypeCode] = 8 THEN ‘User’
WHEN POA.[PrincipalTypeCode] = 9 AND TEAM.[TeamType] = 0 THEN ‘Owner Team’
WHEN POA.[PrincipalTypeCode] = 9 AND TEAM.[TeamType] = 1 THEN ‘Access Team’
ELSE ‘Other’ END AS ‘PrincipalType’,

COALESCE(USERID.[FirstName],TEAM.[Name]) AS PrincipalName,
POA.[ObjectTypeCode],ENTITY.[OriginalLocalizedName], POA.[ObjectId],
POA.[AccessRightsMask],POA.[InheritedAccessRightsMask],POA.[ChangedOn],
POA.[PrincipalTypeCode],POA.[PrincipalId]
FROM [MyOrg_MSCRM].[dbo].[PrincipalObjectAccess] AS POA

LEFT OUTER JOIN [MyOrg_MSCRM].[dbo].[SystemUserBase] AS USERID
ON POA.[PrincipalId] = USERID.[SystemUserId]
LEFT OUTER JOIN [MyOrg_MSCRM].[dbo].[TeamBase] AS TEAM
ON POA.[PrincipalId] = TEAM.[TeamId]
LEFT OUTER JOIN [MyOrg_MSCRM].[MetadataSchema].[Entity] AS ENTITY
ON POA.[ObjectTypeCode] = ENTITY.[ObjectTypeCode]

WHERE POA.[PrincipalTypeCode] IN (8,9) AND POA.[ObjectTypeCode] NOT IN (8,150)
/*User and Team (8,9) shares, not including shares to User or User Settings (OTC 8,150)*/

I am sure there are SQL gurus out there who could improve on this code. I also leave it as an exercise for the reader to add a join to the SystemUserPrincipal table to reverse engineer which users belong to which teams, so you can end up with a list of which users have access as themself or as a team member, in a single query.

Decoding the bit masks

In some cases, just being able to find rows in the POA that prove why a user has some kind of shared access to a record is enough. Knowing whether it is explicit or inherited, seeing if it is the user or an owner team or access team is usually enough to point you in the right direction to check or change your configuration.

If you do need to figure out exactly what rights have been shared, you need to decode the bit mask, and the table below is a quick reference to what the bits mean. You might also like to use Scott Sewell’s "POA Decoder Ring" spreadsheet if you don’t speak binary like a native: Unmasking CRM’s PrincipalObjectAccess table.

Bit value Meaning
1 Read
2 Write
4 Append
16 Append To (with shares, this always goes with Append, so treat them together as 20
32 Create. Of course this makes no sense – you can’t have permission to create a record that is already there, so you should not see this bit in use “in the wild”
65,536 Delete
262,144 Share
524,288 Assign
134,217,728 “This access is inherited” – added to the value of the share on the parent record and used for the child records when Share cascading behaviour is turned on.

One value you will see quite a lot in the InheritedRightsMask column is 135,069,719. This is basically all of the bits in the table above added together, except 32 (Create). You will see this for child records when Reparent cascading behaviour is turned on, with the owner of the parent record as the principal. It effectively allows the owner of the parent record to do anything they like to the child record, albeit always filtered by their security privileges (so if they do not have the rights to delete any records of this entity, they do not get to do that to this record either).

This same number will also show up if someone explicitly shares a record and ticks every box, then this share is cascaded down to child records. In real-world use I find that does not show up very often. Users rarely share records and give people access to do anything, it is usually more specific, eg to grant write and assign rights. Note: if a user does not have a privilege for a record, they cannot share that privilege to someone else, the check box would be disabled in the Share dialogue. So if Alice opens a record that she can read, but not delete, and shares that record with Bob, Alice will not be able to select the checkbox to share delete rights with Bob (which is good because it means it is obvious to the user that they cannot do this).

Special privileges in CRM Security Roles

There are several privileges in Dynamics CRM that control access to things like settings and user personalisation features, rather than data records. If users are missing some of these then they might not be able to sign in to CRM at all, or might not be able to use it properly. In particular, there are six privileges that can only be set at User level

Privileges that can be set at “User” Level only

There are a few privileges that you can only set to User level or None in any security role. Five of the entities for which this is true are on the Core Records tab, and you can easily find out which they are by looking at the System Administrator security role (shown below). Even this “super user” does not have global rights to these items so they stand out as the only rows not covered in green circles:

System Administrator Security Role

(Minor note: the above screenshot was taken from CRM 2015. In CRM 2013 and earlier, you will see that UserEntityInstanceData is written as one word with no spaces, and appears below User Entity UI Settings.)
Find out what these 5 settings are for, and how to configure them »

Why Use Access Teams in Dynamics CRM 2013

It seems to be “Access Teams Week”.

Larry Lentz wrote an article CRM 2013 Access Teams in a Nutshell which is a great introduction to what Access Teams are and what they do. Ben Hosking wrote a post in his series about preparing for the CRM Customization exam MB2-703: Access Teams and Access Team Templates how to use them and key facts.

Both of these are really good primers on the mechanics of using Access Teams but I think they miss addressing one really big question – why would you want to use Access Teams in the first place?

I think there are several reasons to use Access Team Templates, some in terms of usability, others from a more technical or performance perspective, and some cases for using manually-created Access Teams.

Read 4 key reasons to consider using Access Teams in Dynamics CRM 2013 »

Security Roles and Teams in CRM – An Inconvenient Half-Truth

Over the course of the last two years or so reading everything I can about Dynamics CRM, as well as teaching many classes of people how to get the most out of their CRM systems, one thing which comes up again and again is how to best structure Business Units, Users and Security Roles, and sometimes Teams as well to get the exact model you want to match your business requirements for who has access to which records and when.

Users inherit Security Roles from Teams – right?

One concept I have seen repeated many times is that “Users inherit security roles from all the Teams they are in”. And generally this seems to be a reasonable way to describe how it works, but occasionally odd behaviours seem to show up which make this appear to be less than 100% accurate.

I also had a gut feeling for a while that this was not the best way to describe the way this works. I prefer to say that “when a User is in a Team, they can act as if they are the Team, with the rights that the Team has through its Security Roles, but only while considering records in the same Business Unit as that Team”.

More on this later, and the one part of the model that this description does not do justice to.

Overall this means Security Roles use a kind of “impersonation” when Teams are involved and that the rights the User has are not only ‘borrowed’ very temporarily from the Team but they are relative to where the Team is – so access levels / depths such as “Business Unit” or “Parent / Child Business Unit” operate from the Business Unit where the Team is.

So how does this really work?

If you really want to read how security roles work in terms of determining access to a whole bunch of records (to display the results of a view) or a single record, then you need to read the white paper Scalable Security Modelling with Microsoft Dynamics CRM 2011.

42 pages later you will probably know exactly how the queries are built to actually enforce the security model, but that may not have made it much clearer from a practical, day-to-day design point of view. To be fair, the point of that white paper is to explain the underlying architecture and query methods properly so you can figure out the performance impact of different security approaches, rather than demonstrating how this informs your design from an end-result “who can see what” point of view. One thing that is never mentioned is any idea of inheritance or merging of privileges from Teams to Users. Every kind of access request is checked against User and Team permissions separately (exactly what is checked depends on things like whether the User has Global access level privileges to that entity at all, and whether the record is owned by the User or any of their Teams. These can help shortcut the otherwise brute force querying that would be necessary, especially to return all records in a view).

“You can’t handle the TRUTH!”

By now, I bet some of you are ready to shout at the screen – “we know Users don’t actually inherit the roles and keep them for themselves, but it works just as if they did, so it’s just a kind of shorthand and we all understand what we really mean, so don’t be pedantic”.

Tom Cruise in A Few Good Men - I Want the TRUTH!I always argue that I am not pedantic, I just like things to be exactly correct – “I want the TRUTH!”

In this case, it is CRM which is pedantic, and does not always behave as expected if you believe that a User can act as if they have all the Roles that their Teams have, all of the time. If you are betting your security model on it working this way then either you will end up with Users who can’t do their job, or possibly a gaping hole in your security. Neither sounds good to me.

Read more of this post