Recently I was working on a project around Power Pages where the client had records in Dataverse. These records where not associated with the logged on users but needed to be viewed by two separated groups of users. There were two groups, a Basic and Premium tier (sort of like a license). In Dataverse there was a column which identified which rows were for which group. This allowed us to show basic or premium data to the users. As you could create a filtered query to give back the correct rows to the user on the Power Page. But by using this method, with the Power Pages web API, they could get all rows unfiltered.
To solve this, we created webroles for both groups to try and filter the information, but a webrole has no rights in Dataverse. We could use the webroles to direct people to different pages or components, but users could in theory still get all the information by using the API. Since we wanted to make sure users are not able to see the premium data, we went on looking for a better solution.
Row level security is possible in Power Pages, but all documentation or blogposts we found went the route where the rows were created by the user or using different fetchXML queries. I couldn’t find any helpful information, so we started experimenting with the table permissions.
At first, we thought about contact access, by relating Users (contacts in Dataverse) to the specific rows they had access too. But when they switch between basic and premium, we had to relate and unrelate all rows to the contact, something that had a lot of impact.
Then we saw the option of using ‘Account access’, luckily the users had no account related to them. To tackle that we created an account for the basic tier and one account for the premium tier. Added the basics contacts to the basic account and the premium contacts to the premium account.
Before we could relate these accounts to the information we had to create a many-to-many relation between the two tables, to relate the information to a specific role. Then we related all information to one or both accounts. After that we had to set up table access permissions.
If you setup the table permission you can choose between the following options:
- Global access, all records.
- Contact access, records associated with signed-in contact.
- Account access, records associated with signed-in contacts account.
- Self access, own contact record.
see the Microsoft docs for more information.
In this case you can select ‘Account access’. We ended up creating two table permissions, if you have more than two roles use ‘Account access’ for all roles. In our case we gave the premium account ‘Global access’ because that role has access to all information.
When validating these settings there were no rows displayed. After some digging around there were two things wrong:
- If you edit the table permissions in the Power Pages designer you will get a HTTP 500 error from the Power Pages web API. If you set it through the Power Pages admin app (the model-driven app) the API call does work.
- There is a known issue with OData filter queries on tables with multiple levels of one-to-many or many-to-may table permissions with parent scope defined. For this to work you have to disable OData filtering or use FetchXML in your OData query. Without this setting you will get a ‘CDS Error’ message when using the API. See Microsoft docs for more information.
After these changes we validated the results and we had working Row level security in our component which used the Power Pages web API.
