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.
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?
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.
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.
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:
With this small change I can now see who was the actual author of the item in 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.
Do you ever miss the start of a meeting because those reminders don't pop up…
A typical pattern for data import processes is that you first check if an item…
Sizing you apps in Power Apps isn't any more complicated than setting the height and…
Filtering records on empty values can be a real pain as there are many mistakes…
On a recent project I needed to be able to lock and wipe devices. I…
In this post I'm looking at Power Pages and how to get the base url…
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.
I am looking to leverage this but it seems that the "run only users" doesn't work with the power apps trigger anymore?
Do you have an alternative approach you're aware of that may work?
Hi Matthew, I've not noticed this at all. Can you open a chat and we can have a look at this.