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.

Secure your SharePoint data in Power Apps using Power Automate Microsoft Office 365 You need permissions to 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

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.

Secure your SharePoint data in Power Apps using Power Automate Microsoft Office 365 create item in list

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 your SharePoint data in Power Apps using Power Automate Microsoft Office 365 Flow failing to run

Secure data with Run only users

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

Secure your SharePoint data in Power Apps using Power Automate Microsoft Office 365 Run Only useers

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).

Secure your SharePoint data in Power Apps using Power Automate Microsoft Office 365 Provided by run only user

As we switch it over a helpful warning will appear.

Users with run-only access will not control or have access to these connections outside this flow.

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.

Secure your SharePoint data in Power Apps using Power Automate Microsoft Office 365 Update through flow works

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']
Secure your SharePoint data in Power Apps using Power Automate Microsoft Office 365 Get the actual author

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

Secure your SharePoint data in Power Apps using Power Automate

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.

Continue Reading Pieter Veenstra’s Article on their blog

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? I often hear that SharePoint is not a database.

Blog Syndicated with Pieter Veenstra’s Permission

Author: Pieter Veenstra

Share This Post On