Locking down SharePoint Lists for Power Apps

Introduction

PowerApps is a powerful platform that can be used to build robust internal applications for businesses. While you can spin up a robust application in a fraction of the time that a typical full-stack application takes, there are some inconveniences with the platform. One of those inconveniences is licensing costs. While you can grant a user unlimited access to Power Apps for about $26 CAD a month, this cost can quickly add up when your organization has hundreds of users requiring access to the app.  
 
One way of avoiding this license costs is to utilize SharePoint lists as a backend for your application. While there are other drawbacks to using SharePoint lists such as it not functioning as a true relational database, one major issue is when it comes to security. By default, you will typically grant access to edit the list to users of the app so they are able to CRUD data to the SharePoint list. This opens you up to many security vulnerabilities as these users can go ahead and open the SharePoint list - viewing the entire dataset at will as well as making modifications.  
 
In this article we will address how to tackle this issue - the solution is simpler than you may think!  

Why SharePoint Lists?

In the pyramid of recommended data sources for Power Apps, it's established that relational databases such as Dataverse would be at the top in terms of robustness but not accessibility or cost. While Dataverse is integrated very well with Power Apps, the licensing cost can dissuade many developers - especially when they are looking at potentially hundreds of users. To get around this SharePoint Lists are often used a data source.  
 
Users will already have access to SharePoint Lists and they work well alongside the Power Platform. You can connect them to Power BI, use them for a backend with Power Apps, and automate processes with them using Power Automate. Lists also provide a ton of metadata including the user who created the item, date and time it was both created and last modified, and more.

So, you can see why SharePoint Lists are a powerful alternative to premium databases when it comes to the Power Platform. Next we will explore some of the disadvantages, including the one we are addressing today.  

SharePoint Lists Drawbacks

Although very powerful, SharePoint Lists are not a true relational database so they are lacking many capabilities that traditional RDBs have.

1. Transaction support to ensure data integrity when committing to many different lists.
2. Limitations in relations when it comes to complex relations between tables/lists.
3. Query limitations - SharePoint lists are not fully delegable in PowerApps so you will reach a limit of items you can pull in a single call.
4. Performance concerns when your lists reach a large number of items.
5. Security vulnerabilities in terms of limiting access especially with features such as row-level security, although these can be built into your Power App.

All in all, while SharePoint lists can serve as a great backend for your Power Apps there are a few downsides to take into consideration when building your Power App solution.

How to lock down your SharePoint Lists

Now we can address the main point of this article which is how to lock down your SharePoint List. When you give access for users to be able to create new SP items or edit/view older items, this means that these users can open the SP list and edit/view all items in the list. This can be problematic for several reasons, so we will go through how to prevent this while still allowing these users full access through the Power App.  
 

Our recommended method is by modifying the members permissions to be able to view application pages. This means changing permissions at the site level. This won't be a problem if you have an individual site you are using exclusively for Power Apps SharePoint lists which is generally considered best practice.  
 

We are aiming to customize the 'Contribute' permissions to serve our needs.  

Start off by navigating to 'Advanced permission settings' which can be found in the 'Site permissions' side dialog:  
Next, open 'Permission levels'.

Then we will select the 'Contribute' permission level:

Once here, we will uncheck 'View Application Pages'. This is what will remove the ability for users with this permission level to open SharePoint Lists and view/edit the items. image

Note: If you are currently utilizing the 'Contribute' permission level for something else, you can hit 'Copy Permission Level' to create a custom permission level to use.

Next, go back to the permission screen, select your site members group, and hit 'Edit User Permissions'.

At this screen you will change their permission level from 'Edit' to the 'Contribute' level that we have modified:

And you're done! Users will now see an 'Access Denied' screen when attempting to open your SharePoint list, while still being able to access the list items through your Power Apps.

Please note that this means they will be able to access the SharePoint list through Power Apps, so if they can find the SP URL and create their own Power App they will be able to access this data freely. So, if you want to prevent that you will need to either specify some item-level permissions or migrate your solution to Dataverse. Otherwise, this method works well for many internal solutions.

Conclusion

In conclusion, using Power Apps with SharePoint lists can be very powerful, but be sure to implement proper security features and keep in mind the issues that you may run into. Test thoroughly before deployment and don't forget to train your end users effectively.  

Keep an eye out for more articles coming soon including more information on navigating the Power Platform.