cancel
Showing results for 
Search instead for 
Did you mean: 

Removing user ability to access data source without using the app

Problem:

One of the biggest issues in PowerApps right now is that we cannot protect our data. If we connect any data source to PowerApps (Excel, SharePoint List, SQL Server Connection) it has to be shared with all users for them to be able to use the app.

This creates a problem where user has access directly to data source and can bypass the app to do direct modifications to the data source as well as see information not meant to be seen by them. If your app was built to limit users access to some data, for example:

  1. Showing users only their vacation requests and hiding other user vacations
  2. Showing user only their travel request and hiding other user travels

That means that all users can see all data as well as they can modify it without any trace.

 

In case of Excel file on OneDrive we need to give users access to this file, that means that user can just go on OneDrive and find the excel file and edit it.

In case of SharePoint List, that means that user needs to have Edit rights to that list and can just find it on the SharePoint Site and go in and edit.

In case of SQL Server Connection, that means that user can open PowerApps, click create new app, open Data Sources and the shared SQL connection will be there and he can connect to it. This will allow the user to see all tables in that SQL connection with edit rights.

 

Idea:

I believe the best way to fix it, and this will allow PowerApps to become truly powerful tool to replace most of organization applications is to give the App itself write rights to the Excel sheets, SharePoint lists or SQL Connection and not the user. This way the user will have no access to the files, SharePoint List or SQL Connection and the only way to interact with data will be through the App.

Status: Under Review
Comments
Kudo Kingpin

You need to invoke a flow then that flow needs to call another flow that was authored with the admin creds so the flow that is called does the elevated permissions capability...

 

I use an HTTP Post Action to call the flow with elevated permission... 

 

I promise you, the flow you call in this fashion will operate using the identity of the author of that flow - works great!

 

Google how to use the HTPP Post to call another Flow within a flow if you need more details

 

Hope that makes sense

 

 

Helper II

Aha. I missed the part about the flow being called by another flow. Implemented that using an HTTP POST request, and it seems to work fine now. Thanks for the idea.

New Member

Hi @skylitedave @AIUYM19.

I'm trying to use the solution skylitedave proposed but Im not used to power automation yet.

Is there any course I can take to get to that level?

Do I still have problems with calculated cells using this?

Thanks

Helper II

@EnriqueM 

I actually just started trying to put simple flows together straight away. When I encountered a function I didn't recognize, or I wanted to do something but couldn't find the proper function for it, I just Googled...a lot😆 I've found the Microsoft Power Automate Community to be very helpful when I couldn't figure something out.

If you learn more from example or step-by-step tutorials, then you might try this YouTube playlist. I watched a couple of those for the more advanced functions I ran into, and they're OK. There are 90-ish videos there, and from the ones I watched, he explains how many functions work sufficiently well. Might be a good starting point, if you aren't able to find another tutorial online somewhere.

Frequent Visitor

I'd like to back up and ask a more fundamental question that I can't see answered above. The analogy to what's going on here is SharePoint. The process is not to give anyone permission to access the SharePoint site, and then use a Flow from within PowerApps to get the data the user needs using the credentials of the Flow's creator, who does have access to the SharePoint site. Is this correct?

 

From what I've found, there's a fundamental difference between SharePoint and certain other data sources: SharePoint is not implicitly shared in PowerApps, whereas many others are (including SQL Server, the one I use).

 

So my question is: can we use Flow to get around the security problem here when the data source is implicitly shared?

Kudo Kingpin

You said

"The process is not to give anyone permission to access the SharePoint site, and then use a Flow from within PowerApps to get the data the user needs using the credentials of the Flow's creator, who does have access to the SharePoint site. Is this correct?"

 

Almost correct.  The idea is to give users access to certain secure lists or libraries that  should normally only be accessed through the PowerApp and NOT through a backdoor using the native SharePoint  interface.  Think of this example, a list of Inventory in a warehouse and you only want to be able to change the qty in stock through the app or through some admin function. You don't want to allow regular users to access the inventory list and then manually adjust the qty in stock through a "backdoor" via native access using QuickEdit or Edit from a the regular SharePoint list interface.

 

I don't see any difference in this concept between SQL Server, CDS, Oracle, or SharePoint...  All the same.... Permission management on how a user accesses the list or library in SharePoint or table in a database.  Some lists or libraries in SharePoint should only allow access for a regular user via the PowerApp using a Flow called within the PowerApp.  I explained how to do this in an earlier post in this thread so look for that...

Frequent Visitor

Thanks, @skylitedave So, since SQL Server is implicitly shared, unlike SharePoint, I would have to NOT connect SQL Server as a data source in the PowerApp, and then connect to data only through a Flow (more precisely, a Flow that calls another Flow, as you mentioned)? Like maybe create a collection in the app's OnStart, and use that to display data to the user, and then do operations through the Flow-calling-a-Flow when any changes need to be made to the data in the source?

Helper II

@TedBabcock, that's precisely it. You'd essentially build two flows that act as quasi-APIs for the PowerApp and the data source. The way I finally made my solution work was that I built two flows. One was like an API to let Power Automate interact with PowerApps, and the other was like an API to let Power Automate interact with SharePoint (or any other data source). One of the arguments in the PowerApps flow determines whether PowerApps is requesting or sending data, and the flow then needs to manipulate the data, and call the SharePoint flow using its own set of arguments. In this way, I've managed to create a PowerApp that can have a two-way conversation with SharePoint while the PowerApp users do not have direct access to the data source.

Frequent Visitor

Many thanks @AIUYM19 ! I will try this.

Frequent Visitor

There are two main issues involved in this thread:

 

First, backdoor/row-level control: you want users to view/edit data only through the PowerApp, and only their own data. You want to prevent anyone going through the back door (eg, the native SharePoint site). PowerApps can filter the data to retrieve only the user's rows, but implicit sharing would allow users to create their own PowerApp and access the raw data source. The solution is the flow-calling-a-flow method mentioned above. I have not worked specifically on this, but it does remain something I need to be able to accomplish.

 

Secondly, and potentially the most damaging, the general implicit sharing problem: you share a data source, like a SQL Server table that gathers public data about employees, but you find that the implicit sharing of this data source means that users can create their own PowerApp and use that data connection to get around whatever filtering you might do within the PowerApp. On top of this, and even worse, is that the ENTIRE data source is shared. So if you had a database that contained sensitive data, it would all be exposed by way of implicit sharing. When I first became aware of this issue, I feared that I wouldn't be able to use PowerApps at all.

 

At the suggestion of someone in our IT office (SSCC, the Social Science Computing Cooperative, at the Univ of Wisconsin-Madison), we have come up with a solution to the second problem. We use SQL Server. SSCC maintains the SQL Server instance, and I have a login using Windows authentication giving me access to my database. To avoid implicitly sharing the entire database, SSCC created a new login, campususer, and I created a db user linked to that login. The new login uses SQL Server authentication.

 

New users in SQL Server have no permissions until you grant them. (Explicit sharing? What a concept!) So I granted this campususer permission to access only a couple of schemas, which contain information related to courses and the timetable, both publicly available.

 

Then, in PowerApps, when connecting to a data source, I choose this new one, campususer. It, too, is implicitly shared, but the only thing that is shared is the public info in the two schemas it has permission for. The data source has no access to sensitive info.

 

So the second problem is solved for us. Though we use SQL Server, I suspect that any db or data source will have something similar that will allow the same ends to be met.

 

Now, on to the backdoor/row-level issues of Problem 1.