Secure your SharePoint data in Power Apps using Power Automate

Although Dataverse is available many of us still use SharePoint to store their data. But how do you secure your SharePoint data? Or are you just happy for people to edit your data outside your app?

SharePoint is not a database!?

I often hear that SharePoint is not a database.

Our friends from Oracle use the following definition of a database:

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS).

Well SharePoint can be a collection of structured information and it stores its data electronically.

One other typical thing that I find with databases is that you wouldn’t let everybody just directly edit data in an uncontrolled manner.

Shall we take these principles to Power Apps?

My SharePoint setup

So I want an app that stores data in SharePoint, however I don’t want my users be able to go to SharePoint and see the data.

As my development account I’m creating a SharePoint site, however when my users are going to ths URL they will get the You need permissions to access this site.

If I now create an app that updates data in my SharePoint site, it will work as my account that can access the SharePoint site, however as a normal user the updates will fail.

This could result in an error like:

You don’t have permissions to create this row. Server Response: My List failed: The response is not in a JSON format

Using Power Automate to update the data

How about we do the update using a flow in Power Automate. This flow can be fairly simple. A create item action will do the trick.

Once again this will work as a user with permissions to the SharePoint data but it will not work for my other users. This might then result in Connection not configured for this service.

Secure data with Run only users

Within my settings for my flow there is an option Run only users.

Within the Run only users we can see that by default the Connection used is the one that is provided by the run-only user. This setting can be switched over to a connection that you are using as the developer of the solution (or when you deploy this to other environment maybe a service account owned connection).

As we switch it over a helpful warning will appear.

And now when we try to run the flow as a user ( My test user is called Lucy), we will find that the SharePoint list is updated. Lucy still can’t see the data i the SharePoint list but she can update the list through this flow.

Registering the the actual author

As we can see with the above example all records are now created by the account that owns the connection. Sometimes it might still be useful to see the actual person who ran the app.

Using the following expression in the create item can give us the user’s name:

triggerOutputs()?['headers/x-ms-user-name']

With this small change I can now see who was the actual author of the item in SharePoint.

Reading data from SharePoint

Quite often apps don’t just need to create or update data, reading data will also be needed. So now all we need to do is give users read access to the SharePoint site or if you want to be more specific control read permissions on the items that each user should be allowed to see.

If however you don’t want users to see data in SharePoint , you could still disable the viewing of the data by removing access to the application pages or by hiding the lists from the UI. But that is a whole different story.