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.
FAQ
Veelgestelde vragen
Die beantwoorden we graag alvast. Staat je vraag er niet tussen, neem dan gerust contact op.
Een webrole heeft geen rechten in Dataverse en stuurt alleen door naar andere pagina's of componenten. Gebruikers kunnen via de Power Pages web API nog steeds alle onderliggende data ophalen, ook data die eigenlijk alleen voor een andere gebruikersgroep bedoeld is.
Je maakt voor elke gebruikersgroep een eigen account aan, koppelt de bijbehorende contacten daaraan en relateert de data via een many-to-many-relatie aan het juiste account. Daarna stel je de tabelrechten in op 'Account access', met eventueel 'Global access' voor de groep die alles mag zien.
Vier opties: 'Global access' voor alle records, 'Contact access' voor records van de ingelogde contact, 'Account access' voor records van het account van die contact en 'Self access' voor alleen het eigen contactrecord. Voor gescheiden gebruikersgroepen werkt 'Account access' meestal het best.
Bewerk je tabelrechten in de Power Pages designer, dan geeft de web API een HTTP 500-fout terug. Doe je dezelfde aanpassing via de Power Pages admin app, de model-driven app, dan werkt de API-call gewoon.
Bij tabellen met meerdere niveaus van one-to-many- of many-to-many-tabelrechten met een parent scope geeft een OData-filterquery een CDS-foutmelding. De oplossing is OData-filtering uitschakelen of in plaats daarvan met FetchXML te werken in je query.
