November 10, 2015 2 Comments
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,
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.
|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”|
|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).