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
New Member

@TedBabcock Try to open power apps from power apps application for Windows which you may download it from Microsoft Store. Then change the environment to non-default environment. Then click New. VIOLA!! you will be redirected to a browser which logged in into those non-default environment which you don't have any access both maker or admin. Yes you cannot open the connection, but you can create new app which able to connect to the data source on those non-default environment. EPIC!!

Regular Visitor

@RplUser1  I will give that a try. I emailed our campus IT department and am still awaiting a reply. They usually don't take this long to get back to an inquiry, so I'm thinking there are bigger subscription issues to be dealt with. Another PA user on campus tried to create a new environment and was unable to.

 

Thanks for your advice! If I don't hear from IT soon, I may try your steps to see if it works. I'll keep this page posted on what I find out.

Helper IV

I am also facing the similar issue with my existing apps.

I fully support this idea. Kindly implement it ASAP.

 

Regular Visitor

I did try to create a new environment, and our license won't support it. So I'll have to upgrade somehow, either the entire tenant or (most likely) just add the upgrade to my subscription. (Though I still haven't heard from our IT. They've got other things going on: our campus has shut down because of COVID19 and all instruction will move to online-only until at least April 10, starting after spring break this week.)

Continued Contributor
Continued Contributor

No details on how this was implemented, but our IT department informed me yesterday that now, any app developed in the Default Environment, new and existing, the Edit, Delete and Export permissions are now restricted to the original Creator. Other Creators can no longer Edit,Delete nor Export my apps.

 

The Power Apps Environment already has this in place.

If the Creator chooses to export the app, then they can choose to have each connection set to Create As New , then when another Creator imports the app, they will be required to choose their own credentials to associate with the connection, then at that point the connection permissions will be determined by the new Creators permission set.

Helper I

Perhaps I misunderstand, but I think we're getting a bit off-topic. The ask is that users of the PowerApp should be allowed to access the underlying data only via the app. That is, the user should not be able to access the data from outside the PowerApp interface.

Creating a situation where other users cannot edit the permissions on your app is fine, but that doesn't address the original issue, that users are still able to access the data sources directly (i.e., not via the PowerApps interface).

As far as I'm aware, in order for a user to have access to a data source within the PowerApps interface, you need to provide them direct access to the data source itself. Therefore, the PowerApp—and any data controls or logic you've configured in there—can easily be bypassed by the user. This idea aims to stop this.

Regular Visitor

Thanks for the info, KC. I can't tell, though, if this addresses another source of the security risk: the implicit sharing of the data connections means that when another user in the default environment creates a new app by themselves, they can still connect to the source data; they don't have to use a copy of your app.

 

I tested this in our environment by having another staff member go in and create a new app and see if they could connect to the data source. If you could test this way in your setup, that would give us the answer.

Kudo Kingpin

You are running into this problem because you are using the wrong tool.  You can do impersonation using Power Automate in conjunction with PowerApps.  You need to create a flow that will accomplish the needed functionality and then invoke that flow as an instant flow by adding it to the form or to a button on the form using the action drop down in PowerApps.  Once you do that, it will appear as a connector and you can even invoke the flow inline with logic rather than using a button click or a form submission...

 

When you add a Power Automate flow this way, it will be invoked using the credentials of the AUTHOR of the Power Automate Flow.  So you create the flow as a service account or user with elevated permissions and let the Power Automate Flow accomplish the impersonation.  

 

This is VERY similar to how we have always done this in SharePoint.  We used SharePoint workflows with impersonation step to do similar things.  

 

Use Power Automate Instant Flows AND PowerApps to meet this need. We have built several apps and deployed into production and works great.  User can ONLY access the data through the app … Use the right tool for the job - Power Automate with PowerApps will get you what you want...

Regular Visitor

@skylitedave Thanks for this info! I've googled it but can't find a simple, step-by-step guide. Do you know of one? 

 

UPDATE: I'm not a DB pro, just an advanced user trying to get our department to be able to gather our data together into a single source. My previous understanding of the permissions went like this:

 

[User] -------(a) permission to use PowerApp--------> [PowerApps] --------(b) data connector-------> [Data Source]

 

I thought those were two separate permissions, but implicit sharing makes it one long permission line running from User to Data Source. That's obviously wrong.

 

Your solution affects (b), the data connector, correct? And makes it so that the Data Source is no longer implicitly shared?

 

Would you agree that the way implicit sharing works is counter-intuitive, and that in an ideal world this kind of breach in data security would never be allowed? Or is there something in the nature of a data connector that makes it impossible not to implicitly share a data source with an end user?

Helper I

@skylitedave 

"When you add a Power Automate flow this way, it will be invoked using the credentials of the AUTHOR of the Power Automate Flow."

Except that's not what's happening. I have a flow that I authored using an admin account, and then that flow is invoked from Power Apps. When another user runs the Power App, the flow is kicked-off, but it is run under that user's credentials, and the flow fails due to insufficient privileges on the SharePoint list. If I run the Power App under my admin account, everything runs fine.