A guide to help you determine the best solutions for you.
Unmasking CRM’s PrincipalObjectAccess table – with a free Secret Decoder Ring!
The machinations of CRM Security is a huge topic – and there are a lot of moving parts needed in order to grant permission for a user to see/edit/delete a record in CRM.
One way in which those permissions are granted is through either Direct or Inherited shares. Direct Shares are when a user clicks on the ‘share’ button in the ribbon of a record and ‘shares’ it with another user or team. Inherited Shares are the result of a number of different configuration rules – such as the shares that cascade to an object based on the ownership of its parent record. (These can be very tricky to unwind – they are there to help smooth out the usability of CRM, but can sometimes lead to surprising results.)
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 Table
The 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 Fields
Recently 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.)
I definitely recommend that you *not* go mucking around with the POA table – there are a ton of things going on and a SQL Lock on the table would interrupt almost all access to the system, but if you need to understand what rights have been granted and more importantly if you need to parse out what rights have been inherited, this information and ‘decoder ring’ should help you as you research.
BTW – I locked the spreadsheet just so it would be obvious as to which fields you update (clear fields) and which are ‘results’ (blue fields) – If you want to unlock the spreadsheet and look further or enhance it – the password is ‘dynamics’