Nerd Alert: This rest of this post is a deep-dive look under the covers at an internal CRM table/process that ‘normal’ people never need to worry about, but if you're interested in how CRM security works at a technical level knowledge of the POA table is a key part of that understanding.
Unmasking the PrincipalObjectAccess TableThe shares granted on objects in CRM are stored in the very important PrincipalObjectAccess system table – (also known as the POA table) – This table is not visible to CRM users, but for On-Premise deployments, the Administrator can use SQL to view the contents of the table. Since almost every access to CRM data interacts with the POA table, it can become a bottleneck for access – fortunately in several updates since the release of 4.0 and continuing in 2011, Microsoft has added optimizations and performance tweaks to the processes that interact with the POA Table. Despite those improvements, some customers struggle with performance as this table crosses the 10, 15 or 20 Million Record threshold. (The threshold for performance challenges gets pushed out with better optimized SQL servers.) – One tip is to make sure your SQL server is right-sized and the indexes are defragmented and maintained. Nothing in this table is intended for end-user or even administrator access via SQL – it’s a system table and incorrect changes to it could absolutely wipe out your system. With that disclaimer out of the way, here’s a Hitchhiker's guide to the PrincipalObjectAccess Table:
SELECT TOP 100 [PrincipalObjectAccessId] ,[PrincipalId] ,[PrincipalTypeCode] ,[ObjectId] ,[ObjectTypeCode] ,[AccessRightsMask] ,[InheritedAccessRightsMask] FROM [PrincipalObjectAccess] WITH (NOLOCK)
PrincipalObjectAccessId – The GUID of share record. PrincipalId – The GUID of the User or Team receiving the Share. PrincipalTypeCode – indicates whether it’s a User or Team. ObjectId – The GUID of the specific object being shared. ObjectTypeCode – The object type of the record being shared. AccessRightsMask – This field stores the rights granted directly via the ‘Sharing’ dialog. InheritedAccessRightsMask – The rights applied by the system through cascading or reassignment processes are stored in this field. (These are not visible in the ‘Sharing’ dialog.
Decoding the RightsMask FieldsRecently I built this quick spreadsheet to help as I was researching the POA table structure for some deep-dive fixes / performance analysis. It allows you to either *encode* OR *decode* the Rights Mask (or inherited rights mask) values from the CRM PrincipalObjectAccess (POA) Table. Here’s the link to download the Excel Spreadsheet: POA Secret Decoder.
Note that there is an “Undocumented” permission – I’m not sure I fully understand it, but thanks to a tip from @MitchMilam I know it is used in calculating the “InheritedRightsMask” to optimize the create/read/retrieval of inherited shares when the related object was already shared.
- To calculate the expected value of a particular rights mask, enter "TRUE" in the ENCODE column corresponding to the permission(s) you need to grant. (0 = no permissions ; 852023 = ALL (documented) permissions) – the value will be displayed in the blue cell under the word ENCODE.
- The opposite process can be accomplished in the ‘decode’ column. To determine the rights that are granted by a specific value, enter the whole number from the POA “AccessRightsMask” or “InheritedAccessRightsMask” field. – The rights granted by that value will appear in the blue fields in the decode column. (Note this does not validate whether or not the value is valid – I'd suggest double-checking by using the encode side of the spreadsheet to see if you get the same results.)
On-Premises vs. Cloud CRM
A guide to help you determine the best solutions for you.