Configuring your Dataverse security roles will help you keep data from being editable to the wrong target audience. However, if you improperly configure your security role for your lookup tables, you can be in for a headache. This article discusses the problem and the solution!
I recently created a Power Apps model-driven app with a main table that had lookup columns to other tables. By using the Power Platform admin center for my Environment, I created two different security roles for the app. On the “Admin” security role I gave complete organization-level access to all the different tables (create, read, update, delete, etc.). For the “User” security role, I gave complete access to the main table and then organizational read access only to the supporting tables.
The problem
All was well and good when folks with the “Admin” role were using the app, however, when the “Users” took to the app they noticed that they didn’t have access to any of the supporting tables. Here's what folks with the "User" role began seeing:
A perplexing problem! We didn’t want the “User” role to be able to change any of the data, but we needed them to be able to input data into the main table from these supporting tables.
One of our PowerApps911 experts, Daniel LeMay, uncovered the problem during a call with the customer.
Solving the problem
Part A - Don’t start a security role from scratch!
When you create a new security role, there are a lot of permissions to various tables throughout a Dataverse environment that you’ll miss. The supporting tables have complex relationships with other tables that you may miss. Some of these permissions are User-level, some are for Business Unit, and some are Organizational access.
When you begin thinking about a new security role, think of using a standard Dataverse security role that most closely resembles your users and make a copy of that role to start your new role with that. For example, if you just want someone to have access to the app, but not do any data input, start with the App Opener role. If someone is going to be accessing the app as a user, consider the Basic User role. There are other roles such as Service Reader, Service Writer and Support User as well.
None of these roles is customizable so that you can use these as templates for your security roles. Microsoft has a list of these different roles here. Use one of these as a template.
We decided to use the Basic User role as a template. Here’s a step-by-step guide for how to use an existing Dataverse security role as a template for another security role.
1. Go to the Power Platform admin center (https://admin.powerplatform.microsoft.com)
2. Select Environments from the left-side menu.
3. Select the Environment where you want to create the security role.
4. In the Access pane there is a clickable “See all” link under Security roles.
5. Find the security role you want to emulate in the list of security roles. Select the “More actions” ellipses next to the Business unit number and select Copy. (You can do the same thing from the Copy button in the command bar.)
6. Once you select Copy, a dialog box appears where you can enter a name for your new role. Input the name for your role. Then select Copy.
7. After a few moments, your new role will appear on your Security roles list, and you can continue.
Part B - Update your supporting table permissions with Append and Append to permissions.
Once you’ve provided all of the necessary permissions for your users to access the main table, you can move to the lookup tables. Of course, you need the user to be able to read the data, however, you also need them to be able to have Append and Append to permissions for these tables.
Why is that? In Dataverse, the Append and Append To permissions are used to control the ability to associate two records in a relationship. This is particularly important when dealing with lookup tables and relationships in model-driven apps.
Append permission is necessary on a table when it has the lookup of another table on a form. This permission allows a user to add (or “append”) a related record to the table. For example, if you have a Pet Toys Order table that includes a look up to a Customer table, the user will need Append permission on the Pet Toys Order table to associate a Customer record with a Pet Toys Order record.
Append to permission is necessary on a table when it is being associated with another table. This permission allows a user to append the table to another entity. In the example above, the user would need Append to permission on the Customer table to associate it with a Sales Order record.
Back to our model-driven app, if our main table has lookup columns to supporting tables, users will need both Append and Append to permissions to select options in a form based on the main table. This is because when a user selects an option, they are essentially trying to associate a record from the supporting table (Append permission) to a record in the main table (Append To permission).
Summary
The two takeaways in my lesson were, one, to always use an existing (and working) security role to create a copy as a new role. Two, to include Append and Append To permissions for any of the tables being looked up from my main table.