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 II

@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.

Frequent 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...

Frequent Visitor

Since this idea is under review. Does anybody have any idea if this has been presented for a fix in the roadmap?

Helper II

Just an update to my novella on 26 June...

As I've been using this app-calling-a-flow-calling-a-flow setup for several months now, I've noticed that the flow run counts have gone through the roof on my account. That's to be expected, since every time a user interacts with the app or data within the app, at least two flow runs are instantiated. I have a premium account, but I don't know how many flow runs that includes, or the allowed frequency of flow runs. I've been averaging about 750 flow runs per month, though (i.e., a lot). So, if you use this method of data access and flow, make sure you have a beefy PowerAutomate license.

Advocate III

What's the latest on this? This is a major concern. 

New Member

@AIUYM19   I'm trying to implement almost the exact process you've described right now! Do you have a blogpost or anything detailing how you achieved this? Otherwise do you have any little hints/tips to share that may help me find my way? I've managed to set up the initial flow to create the SharePoint item under a service account but I'm working on the other way around; retrieving the correct item(s) based on if the logged on user is in the "Employee" or "Manager" columns so they can edit/update the record (trying to build a "Performance Review" app). I'm relatively new to PowerApps with a rather tight deadline so I'm struggling!

Frequent Visitor

We've implemented a solution for our internal processes with power apps. Unfortunately, we cannot use power app without the user roles delegation as we don't want users to be able to directly change the source (it' obvious, isn't it?). 

Frequent Visitor

I believe I have a solution for our data source, SQL Server, which addresses all the issues arising from implicit sharing. No data source is added in Power Apps, so there's no implicit sharing.

 

When I want to accomplish a CRUD operation in SQL Server, I invoke a Flow: "Execute SQL Server stored procedure (V2)," sending the correct parameters. To return data from SQL Server, there's a "Response" action in Flow to do that. This action is explained by Mr. Dang here (though he uses "Execute a SQL Server query," which can't be done with an on-premises db through a gateway, as I have):

 

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

 

Rather than using a Power Apps form, I just use ordinary text input controls to collect the parameters. The parameters have to be initialized in the Flow, a separate step for each parameter.

 

Simple example: allow users to enter, edit, or delete additional alternative email addresses for themselves, kept in a SQL Server table, tEmployeeEmailAddresses, that has two fields, EmplID and EmailAddress. (A view would relate this table with an Employee table that has the person's name, via the EmplID.)

- The user opens the Power App, and is identified with an EmplID, obtained from the Office365 connector (which doesn't cause a security problem because it's already available to everyone).
- A gallery shows all the email addresses assigned to that user.
- The user can Add a new one, Edit a current one, or Delete a current one.

 

The Power App will send parameters vEmplID, vNewEmailAddress, and vOldEmailAddress. (Not all parameters will be used in every action.)

 

In SQL Server, a SQL statement in a Stored Procedure can perform any of these actions:

 

- Show user's current email addresses:
SELECT EmplID, EmailAddress from tEmployeeEmailAddresses WHERE EmplID = vEmplID

 

- Enter a new email address:
INSERT INTO tEmployeeEmailAddresses VALUES (vEmplID, vNewEmailAddress)

 

- Edit a current email address:
UPDATE tEmployeeEmailAddresses SET EmailAddress = vNewEmailAddress WHERE EmplID = vEmplID AND EmailAddress = vOldEmailAddress

 

- Delete a current email address:
DELETE FROM tEmployeeEmailAddresses WHERE EmplID = vEmplID AND EmailAddress = vOldEmailAddress

 

These can be in separate Stored Procedures, or they can be in one SP with a CASE statement taking an additional parameter, vAction, to determine which action to perform. vAction would be something like "Read," "Add," "Edit," or "Delete." [Correction: this would require IF...ELSE, because CASE cannot be used to control flow of execution.]

 

Again, no SQL Server data connection is made in the Power App, and so there are no security problems arising from implicit sharing, because nothing is implicitly shared. I believe that something like this would also work for other relational databases, such as MySQL.

 

I also believe -- but am not positive -- that the "flow calling a flow" procedure mentioned elsewhere is specific to SharePoint and its particular qualities, and is not required for SQL Server. However, if that extra step is needed, it could be done. Do you think it's needed, @AIUYM19 and @skylitedave ?

I should mention that I have not completely implemented this solution (if that's what it is). I have a lot of apps that use data connections, and will be changing those when I'm convinced via testing that it works. But all indications from preliminary testing is that it works.

 

Please let me know if you think I'm missing something here, or have questions.

Frequent Visitor

Don't know what's happening with my posts (see below); still can't see two previous ones. But here's an update: I used the "flow calling a flow" method to get data from SQL Server, and it seems to have worked as described: I get no Implicit Sharing warning messages when I open it for editing or when I share it. Yay! Thanks for your help!

 

Hmmm, I posted something on Sat, Nov 28. It shows up in my profile, but I can't see the post here. Anyone know what's happening? I looked for a way to ask an admin of this forum, but I can't find one. Any admins out there? Thanks.