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
Helper I

@TedBabcock 

I've been quietly working on improving this flow-calling-a-flow method of ensuring user access only within the Power App. I've gotten it to a point where—and it isn't fully tested yet, but it seems to work in most cases—I have completely removed the data source (a SharePoint list) from the PowerApp. The users have no access at all—not even read access—to the data source, and the Power App doesn't connect directly to the data source.

I've set up a canvas app such that when the app is loaded, it calls a flow asking for information from a user list in SharePoint. That flow calls another flow which actually retrieves the requested information from the user list, then returns that information to the calling flow, which then returns an answer to the app. If the app user matches a user from this list, then the app gives the user access to parts of itself that are specified in the user list (which the user never sees and doesn't have access to).

Once in a part of the app specified by the user list, the app then calls a flow asking for information from the appropriate data source. Again, that flow calls another flow which actually retrieves the data, and returns it to the calling flow, which passes it to the app. The user can then use functions in the app to do CRUD operations.

"But couldn't they just build an app of their own to interface with those flows?" I thought of that same problem. First, the flow called by the app will do its thing only when called by a specific set of Power Apps. That's the first safeguard. Then, when the flow calls the secondary flow that interacts with the data source, that secondary flow will only do its thing if the app user is both on that user list and has the appropriate permissions to do what it's being asked to do. Since the user has access to neither the data source nor the user permissions list, and since they cannot edit the app or the flows, there's no way they can build their own circumventing solution.

Like I said, this hasn't been fully tested yet, but it seems to be doing the trick nicely. It took a lot of logic and code (and whisky) to figure it out and build it, but it seems to be a rather smooth experience for the users.

Regular Visitor

@AIUYM19  : " a lot of logic and code (and whisky) to figure it out"

 

Yeah, that's what I'm afraid of. 🙂 I will contact you when/if I find I need to work it out this way. Thanks!

Kudo Kingpin

You got it right.. you need to have a flow call a flow.  The flow that is called from another flow hits data in the context of the author of the called flow.  This is how you implement impersonation.  Of course the author of the called flow would have access to that data while the original user who invoked the flow calling that flow would not.  This way, user only can access the data via the app.

 

The only negative is I have to use an HTTP request to have a flow call another flow and that means a premium license...

 

Maybe there is a way for a flow to call another flow without a requiring a premium license now...  have not looked at this in a while so if anyone knows how to call a Flow from another Flow without a premium license please chime in...