Dataverse Security Role and Performances
In this blog post, I want to share with you the pros and cons of the security role configuration in Dynamics and Dataverse.
We will see what a security role is and how the security role can help performances in some cases.
Table of contents
What is a security role?
A security role is a combination of access level and capability. For example, a capability can be the ability to read “leads”. This is not enough to define the security role because we need to understand the depth of the permission. In Power Platform, the depth of permission has been inherited directly from Dynamics Customer Engagement. We have 4 level of depth (From larger to narrow):
- Organization
- Parent: Child Business Unit
- Business Unit
- User
image from Power Platform Admin Center
The new way to create and manage security roles is through the Power Platform Admin Center.
You can reach it through https://aka.ms/ppac
The access levels explained
As said, the access levels come directly from Dynamics heritage, that’s why we have something strictly related to the corporate world, like “business units”. But what does it mean to have business unit level on a permission?
To answer this question, we have to introduce two concepts: “visibility cone” and “ownership”.
The ownership is one of the most important concepts in power platform and dynamics. Even if the name has changed and we refer to “Tables” the concept of ownership is better linked to the concept of “Entity” (as we used to call tables in the old days). That’s because an entity is a metaphysical representation of a physical concept, a thing. As we know from experience, every thing has an owner. So all the records in Dynamics/Dataverse (which are entities and so things) have it (there is an exception, but we’ll see it later on).
Another heritage of Dynamics is that everybody is part of a group, and groups can have relationships with other groups. For example, I’m part of the group “Italians” which is part of “Europe” which is part of “Planet Earth”.
How does this apply to records in Dynamics/Dataverse and to access levels?
Let’s suppose we have in our dataverse the table named “Home appliance” and in it we have the record “Fridge”, which has an owner: Me. We want to grant visibility to the record, so the “read” capability (later we’ll see what the capabilities are in Dynamics/Dataverse, but read is the very basics). Let’s see who will see my fridge (and who can steal my lunch from it) based on the access level granted:
- User: Only the owner sees the record (only me; nobody else can steal my lunch).
- Business Unit: Every Italian can see my fridge
- Parent: Child Business Unit: Every Italian and every European Union diplomat (so not part of a specific country) can see the fridge.
- Organization: Everybody in the world can see my fridge!
This is the visual representation of the so-called visibility cone
As you probably notice, there is a big difference between Parent and Organization, since the parent refers to a specific BU and all the underlying BU do not have visibility on the siblings (Spanish people can’t see my fridge). Instead, the Organization visibility permits everybody in the organization to see all the records.
The capabilities (privileges)
From the documentation (here) we can see that the privileges are:
| Privilege | Description |
|---|---|
| Create | Required to make a new record. Which records can be created depends on the access level of the permission defined in your security role. |
| Read | Required to open a record to view the contents. Which records can be read depends on the access level of the permission defined in your security role. |
| Write | Required to make changes to a record. Which records can be changed depends on the access level of the permission defined in your security role. |
| Delete | Required to permanently remove a record. Which records can be deleted depends on the access level of the permission defined in your security role. |
| Append | Required to associate the current record with another record. For example, a note can be attached to an opportunity if the user has Append rights on the note. The records that can be appended depends on the access level of the permission defined in your security role. |
| Append To | Required to associate a record with the current record. For example, if a user has Append To rights on an opportunity, the user can add a note to the opportunity. The records that can be appended to depends on the access level of the permission defined in your security role. |
| Assign | Required to give ownership of a record to another user. Which records can be assigned depends on the access level of the permission defined in your security role. |
| Share | Required to give access to a record to another user while keeping your own access. Which records can be shared depends on the access level of the permission defined in your security role. |
While create, read, write, delete, and assign are easy to understand, the others are worth an explanation.
“Append” and “append to” work as a couple, one is for the appended, the other for the appendee. The example cited in the documentation works because the two privileges are granted at the same time. If I have only “append” to Note and no “append to” opportunity, I won’t be able to do the append action and the other way round. They have to exist at the same time on the appended and the appendee.
Share is the most misterious privilege of Dynamics/Dataverse and so we’ll analyze it in a specific paragraph.
Share privilege
Share privilege allow the user to “share” a record; “sharing” in Dynamics/Dataverse is a little bit different concept than the usual. When we share a record in dynamics, we allow the user to whom we shared the record to use its own privileges on a record that otherwise would be invisible to them. Basically, what happens is that we virtually add the user to whom we are sharing a record as owner of that record, but we only grant to use its privileges to a specific subset of operations.
The sharing pane of dynamics/dataverse
In the image, we are granting the user to leverage it’s privilege on read and write but not to the other privileges. Please note that with the new sharing pane, we can only grant permission to privileges that the user can leverage.
The sharing issue
To allow this sharing mechanism to work properly during the “read” of a record, the system has to go through all the visibility “cone” and all the records that have been shared to me. The query that the SQL has to manage to allow me to see all the records I’m allowed to see will have to check the owner of the records and “join” the table that contains the information about the sharing mechanism. This table is called “Principal Object Access” (POA). Since this table contains all the sharing information, if we use sharing massively, we can have a multi-million record table that will be joined in every query.
Let’s add complexity (why not?)
There are some “special” tables whose records don’t have an owner. These tables are “Organization only” tables, and they have less access levels (only none or organization) and do not have share and assign privileges. So the records in this kind of table are not joined with POA when retrieved.
The role “matrix” and why it is important also for performances
So we can see the security role as a matrix:
A security role
For a specific table and privilege, we have to define the access level. The security roles can be more than one per user, and every security role assigned to a team in which the user is in are added to the user itself. If we have different access levels for a specific table and privilege, the less restrictive wins.
With this principle in mind, once the system has determined the access level of the user, it has to decide if it’s worth computing the join to the POA or not, if it is not needed, the POA won’t be part of the query. The consequence is that, if we have an “organization” access level, all the records should be visible, and so POA won’t be joined.
Here come the performances and the importance of a high access level for application users (see my in-depth article on application users). Since the POA can easily be huge, joining it is a big computation issue on the SQL side, and since SQL is the least scalable component in the Dynamics/Dataverse architecture, it’s easy to understand that not joining the table is a big boost to performance.
The application users that have to perform read operations should have organization access level to the tables they have to read from, and the data have to be filtered through a query filter rather than depending on the out-of-the-box visibility cone. For this reason, it is better to have a special role for application users who have to perform a specific task based on their needs.