Security Model findings. 

----------------------------------------------------------------------------------------------------

Teams are not depending upon BU but Security roles are BU dependent. Meaning is that if a User1 is belong to BU1 and we have created a Security Role 2 and a Team2 in BU2, then System Administrator  for User1 will able to assign the User 1 in Team2 irrespective of which BU User1 belongs but System Administrator will not able to assign Security Role 2 to User1, because SecurityRole2 doesn't come under BU1.

……………………………....................................................................................................................................................

When you do change Business Unit for a user all security roles will remove (irrespective weather the same security role belongs to earlier or new BU) but it will not remove Team and Field Security Profile from his user account.

…………………………………...............................................................................................................................................

When you assign or share a record (For Ex Account1) in CRM to other user it will create a single POA record for Account1, however the number of POA table records creation is depend upon the relationship behavior of the Parent Record which you shared and the number of Child records of the Parent record.

And suppose after assigning the Account we’ve deleted the same Account1, then it will not delete the POA table record for assign or sharing, so this leads to a problem of growing POA table.

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.

PrincipalObjectAccessId = POA table PrimaryKey

ObjectId = Record ID of Share or Assign Record

ObjectTypeCode = Entity Code from which Record exsist.

PrincipalId = The GUID of the User or Team receiving the Share.

PrincipalTypeCode = indicates whether its a User or Team. ( 8 for user and 9 for team)

AccessRightMask = 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.

--------------------

The concept of sharing or assigning a record to a user is that, whenever the owner of the record share or assign a record to another user then for the target user Principal id will be generated for that user in SystemUserPrinicpal table. Then by making join for that Principal id  with PrincipalObjectAccess table with ObjectTypeCode,ObjectId columns you will able to get Access Right for that record.

The sharing model is shown in the following graphic:

You can find the Excel sheet  for secret Mask Encode and Decode for AccessRightMask and InheritedAccessRightsMask

https://community.dynamics.com/crm/b/crmcustomereffective/archive/2012/06/01/unmasking-crm-s-principalobjectaccess-table-with-a-free-secret-decoder-ring.aspx

======================================================

Understanding related Roles and Privileges Table

The purpose of this section is to get an understanding of tables which will appear in final query to be used for role comparison.

1.    Role

This table stores all roles created in a CRM organization. Roles are generally created by System Customizer and are then deployed to other instances. But it is important to note that for every business unit created in a CRM organization, copies of all roles are created for that business unit and the field parent role stores the id of the same role but of the parent business unit. This means total records in role table will be the product of total roles in MS CRM solution and the number of business units in CRM Organization.

Below are the columns of role table which have been discussed above –

And adding below ‘where’ condition to above query will give you unique Roles –

2.    Privilege

This table stores all the possible access and privileges information possible in your CRM Organization. This table contains information of all access and possible privilege depths on System and Custom entities, special access like Export to Excel etc.

Columns like ‘Can be basic’, ‘can be local’ etc. tells if for a particular access what all privilege depths are possible, while the Access Right column tells the type of Access. Other than usual Read, Write etc. access there are certain special access like Export to Excel which have value in Access Right column as 0.

Below query gives more information about above-

Note: Privilege names retrieved from database are for specific entity. To get the name of those entities a join with 2 different tables will be applied as described in section 4 and 5.

3.    RolePrivileges

This table serves as a join between the first two tables i.e. Role and Privilege. It maintains relationship with only those roles which do not have any parent role i.e. which are at Root Business Unit level. It tells what access and privilege depths have been assigned to any role in the CRM system.

Below query will give you that detail-

Now let’s try to join all 3 tables-

4.    PrivilegeObjectTypeCodes

This table stores the relationship between object type code of entity and the privileges of privilege table. It tells for which entity a privilege has been defined.

Now let’s join the above query with the consolidated query from step 3-

This provides information of privilege along with entity code this privilege is associated with.

5.    EntityView

This table stores information about all entities/tables in CRM Organization. We will make use of this table to retrieve names of System and Custom entities corresponding to their object type codes retrieved in earlier steps.

Note: This table does not contain name for all entities corresponding to object type codes retrieved by other query hence a left outer join has been applied with this table so that access-privilege records are not removed in such cases.

Now let’s join this with our query from previous section-

Final Query

Consolidated query from above section 5 is the final query and one can apply a ‘where’ clause on name field of role table to retrieve results for any specific role/roles. This is shown below-

Below are the some of the internet links which might help you to understand the CRM Security model.

http://garethtuckercrm.com/2013/04/24/implicit-shares-in-microsoft-crm-2011/

https://community.dynamics.com/crm/b/crmhobbitsdynamicscrmblog/archive/2013/11/07/sql-query-provides-crm-2011-security-role-privilege-options-in-excel.aspx

Scalable Security Modeling with Microsoft Dynamics CRM 2013 (1).pdf