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.

Share
Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using contact@veenstra.me.uk.

View Comments

  • So are you advocating giving users access to the PowerApp but not the SharePoint site? I would imagine the restriction should be on the SharePoint list. Also, if the App makes changes to the list then this will not work unless you push all change to Power Automate. Perhaps I missed something.

    • It all depends a bit on the requirements. In my case data was read-only to every user in SharePoint, while in the app, users could edit their data. This meant that users, couldn't mess about with the data in SharePoint just because they could edit the same data in the app.

Recent Posts

1 step to update an item in an array in Power Automate

What do you do when you have an array of data in Power Automate and…

7 days ago

Failed to load in Azure Synapse Workspace

After last week's post about how to configure Azure Synapse Link to export data from…

1 week ago

Buckinghamshire & Northamptonshire Power Platform User Group launch

Have you been waiting for a new face to face Power Platform User Group in…

1 week ago

Configure Dataverse exports using Azure Synapse Links for Azure SQL Databases

In this post I'm looking at the Azure SQL databases option that is part of…

2 weeks ago

5 steps to implement word based search in Power Apps

A common requirement in Power Apps is to create a word based search on data…

3 weeks ago

900th post on SharePains.com, it is a time to celebrate and win!

This is the 900th post on SharePains.com and I thought this would be a good…

1 month ago
%%footer%%