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
AIUYM19
Helper V

@Anonymous wrote:

Is there an option to deny end users access to create/edit apps?


I believe the privilege to create and edit apps in the enterprise PA library is controlled by a flag in the end user's Active Directory account or group.

 

It should also be noted that while anyone with create/edit privileges can create a new Power App, they cannot edit Power Apps for which they are not marked as co-owner, unless they are in one of the system admin groups in Active Directory.

roamer7485
Advocate IV

I wish this is going to be resolved soon. Some of my wiser users now have made their own apps using the SQL/Sharepoint data sources that became visible to them. It's like MS Access all over again. 🙂

TedBabcock
Advocate I

Hi, @roamer7485 -

There are at least two solutions to this problem.

(1) The easy one is to make a new environment, then create your apps there. Users with whom you share your apps will not be makers there, and they won't be able to access your data sources by creating their own app. Shane Young has a video on this:

https://www.youtube.com/watch?v=NqQGysNCac0

Unfortunately for me, in my situation at a large university, I don't have the ability to create apps in a new environment, so I have to use a more complicated solution.

(2) See the posts above about this, referred to as a "flow calling a flow." You use the Power Automate HTTP action, which is premium, but since you mentioned SQL Server, which is also premium, you should be okay.

It's not difficult, though it can be a bit tedious. But I've done it enough that it's second nature by now. And it absolutely solves the problem, for both SharePoint (which requires explicit permission for app users) and SQL Server (which is shared implicitly with app users).

If you need this solution, I would be happy to help.

Ted, Univ of Wisconsin-Madison

[Sorry if this gets posted twice. I wrote it when I wasn't logged in, and after I logged in it was gone.]

roamer7485
Advocate IV

@TedBabcock 

Hi Ted,

 

It's same with me when I first started learning and making apps, I hadn't known about it, I was in the sandbox environment where everybody is. Then our admin finally realized it, then he created the production environment with restricted rights. Maybe the Powerapps Team at Microsoft had not thought about it that even on a sandbox environment right to a data source should be app-based.

 

Thanks for the reply.

KPawel
Regular Visitor

Hello,

 

[PowerApps + Teams] Data vulnerable for potential security issues:

We did some PowerApp Apps in newly released Dataverse for Teams and unfortunately we failed to achieve a fully secured solution. We created tables and PowerApp in one Microsoft Teams environment and then we shared them to another MS Teams Team. From the beginning it was looking good, because end-users couldn't directly access these tables. Sadly we discovered that PowerApps App is able to connect to tables from another environments, so our user could just create app and connect to our tables. Possibly he can even delete all data.

 

Is there any way to share Power App application to MS Teams users without sharing data source directly? (scenario: app for many users that should access only some objects within app and not be able to access whole data source directly like Dataverse for Teams tables).

 

Thanks for the reply.

TedBabcock
Advocate I

Hi, 

I don't use Dataverse or Dataverse for Teams. My guess is that you have to remove the data itself from Teams, and then access it via the "flow calling a flow" method described above. I don't know if the "Dataverse for Teams" you mention is different from "Dataverse." If you can put your data in plain Dataverse, there is a Dataverse connector in Power Automate. Then you can embed your app in the Team:

https://docs.microsoft.com/en-us/powerapps/teams/embed-teams-app

Then Team members can get to the app, but not have access to the data.

I hope this helps, and I'm interested in whatever solution you find.

skylitedave
Kudo Kingpin

To help you get some additional context, the concept you are trying to enable is called "Impersonation".  Impersonation means one user gets the access rights of another user by impersonating the permissions of another user.

 

The only way I have been able to successfully accomplish impersonation is to use PowerAutomate in tandem with PowerApps to read or write the data .  I have a couple of posts about this within this thread.  You need to have a a flow call another flow.  The flow that initially launces will run with the credentials/permissions of the currently logged in user in PowerApps.  The second flow that is called from that first flow will run with the credentials/permissions of the AUTHOR of that second flow.  The AUTHOR of that second flow should have access to the data you want to read/write while the person who initiates the first flow ( the one called from within PowerApps that calls the second flow) will not.

 

Hope this helps you get your arms around this challenge .... 

 

Do a search on how to enable calling a flow from within another flow.  I believe the action to call a flow within another flow DOES require a premium connector (HTTP Action )

AIUYM19
Helper V

To build on @skylitedave's explanation above, I was able to use the impersonation method to—at least partially—secure the data source. Additionally, I was successful in providing extra security through the use of (IMOW) "passive tokening." I employed a hashing algorithm, a time-sensitive passcode, and a very rudimentary version of public-private keypair encryption to allow the second flow (the flow that is called by another flow) to determine if the request actually came from an authenticated source—in my case, a specific Power App. If the token passed by the first flow to the second flow does not validate—for example, if the first flow was called from a different Power App—then the second flow simply terminates with an error message.

 

It's worked out pretty well so far. The only wrinkles come when Azure has one of its (all too frequent) seizures, and it takes too long for the first flow to call the second flow. In that case, the time-sensitive token is sometimes invalid due to expiry. Similarly, if a user is just very unlucky, the Power App might call the first flow milliseconds before the token expires. In both cases, my Power App will inform the user of the failure and ask if he/she would like to try again (with a fresh token under the hood).

skylitedave
Kudo Kingpin

Great additional info AIUYM19 

 

For me, just a flow calling a flow was enough so I would start there...  just give access (permissions) to the sensitive data ( table or SharePoint List)  to  the user who is the AUTHOR/OWNER of the embedded flow and do NOT give access (permission) to the tables to most users...  hope that makes sense

TedBabcock
Advocate I

Thanks, @AIUYM19  . You and @skylitedave are the authorities on this. I'm just a citizen developer who follows your lead.

A question: if you share the parent flow in your Power App, can users with whom you share the app also call the flow? I had been thinking of an additional layer of security in SQL Server. My idea was to just hard-code a password in the app, and have the SQL Server Stored Procedure check it. Could you do that with yours? I know the procedure you define is more sophisticated, but is it necessary? Anyone who called the flow (if that's possible) from another app wouldn't know what password to send. And they could only call the flow, right? Not edit it (and therefore see the password check)?

Thanks to both of you for leading the way on this!