Microsoft 365 developers who wanted to create solutions but do not have access to premium features like Dataverse can use SharePoint Online List as a database. For some complex and secured solutions, developers may need to implement row level security. SharePoint Lists has basic row level security features based on SharePoint access levels.
Let’s imagine a scenario where we want to create an ordering app using PowerApps with SharePoint List as the database.
In Figure 1, I had created a SharePoint List called Orders with the columns Description and Approval Status.
In Figure 2, I had also created a PowerApps called Orders App to let my users create / edit their orders with the data being stored in Orders List.
The Orders App is supposed to let users create or edit their orders until the Approval Status is changed to Approved or Rejected. After that the users are not supposed to be able to do anything to their approved or rejected orders.
All these works well and I can even implement my security by coding in custom logic or hiding the edit/delete buttons in Orders App to prevent users from editing their orders when the approval status changed.
As seen from Figure 3, developers can configure SharePoint List row level permissions by configuring the Item-level Permissions setting. There is three basic access level (Read, Create/Edit and Cancel Checkout). Developers can only configure the settings for Basic and Create/Edit access while the Cancel Checkout access level is fixed.
For my Orders app, when I add my users, they are given Contribute permission level via the Advanced Permission settings menu in SharePoint Online. The Contribute permission level has Read, Create and Edit access levels in Figure 3.
In Figure 3, I had also customized the access levels so that users can only read, create or edit list items created by themselves. They have no permission to see or edit orders created by other users.
For my scenario, I will need to create approver users to approve orders that had been submitted by users. I created a custom permission level based on Contribute but added the Override List Behaviours (Cancel Checkout permission) setting under List Permissions as shown in Figure 4.
With this permission, approver users can see all orders submitted by all users as well as their own orders.
Compare the difference between a normal user and an approver user in Figure 5. The approver user can view/edit both order #1 from the normal user and order #2 from himself while the normal user can only view his own order.