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

This really needs to happen ASAP. This is a gigantic security hole.

TedBabcock
Advocate I

Lauri, there is a way around this using Power Automate. The basic idea is that the Power App calls a first flow (the parent flow), passing any needed parameters. The parent flow then calls a second flow (the child flow) with an HTTP request. The child flow actually retrieves the data and passes it back to the parent flow, which passes it back to Power Apps. 

 

Why two flows? Because the flow that Power Apps connects to is in the data list for the app, and if there's a direct data call in the flow, then that data source is treated as if it's part of the Power App: if it requires user permission in the data source, like SharePoint, then the user still needs it; if it's an implicitly shared data source, like SQL Server, then it's still implicitly shared with the user.

 

When the data connections are in the child flow, then they don't find their way into the app, and therefore the user will not have access to the data source via explicit (SharePoint) or implicit (SQL Server) sharing.

 

It's more tedious to handle the CRUD operations this way, but it does solve the security issue.

TedBabcock
Advocate I

Here is Brian Dang's explanation of the Response method for data requests:

 

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

TedBabcock
Advocate I

The difference is that this has to be separated into two flows.

 

Parent Flow:

- Trigger: Power Apps

- Initialize variables from passed parameters, if any

- HTTP request to child 

- Response to Power Apps, if any

 

Child Flow:

- Trigger: When an HTTP request is received

- Data connection: I use Execute a SQL Server Stored Procedure

- Response to the Parent Flow

 

I do all the CRUD operations in SQL Server stored procedures. With SharePoint, which I don't have much experience with, there are specific actions the child flow will use: Get Row, Update Row, etc.

AIUYM19
Helper V

@TedBabcock has it right. The only thing to watch out for here is the HTTP request to SharePoint (if using SharePoint as your data source; Ted's using SQL Server). If you make a GET call to SharePoint, then you can't put anything in the query or body fields in that Power Automate action—completely counter-intuitive, I know. Instead, append your query to the end of the URI that you're calling, like this...

[SharePoint REST URI]&queryField1=queryValue1&queryField2=queryValue2&...

Keep in-mind the values' escaped characters when appending to the URI (space, ampersand, punctuation, etc.).

petrinnn
Frequent Visitor

I dont know if someone said this solution, any way, i will say 😃

 

You can let everyone to get access to you sharepoint site.

 

Then, you can remove some permissions.

 

They will still having permission to Creating, Reading, Updating and Deleting but, cant see any list.

 

 

 

1.png2.png

 

TedBabcock
Advocate I

This is an interesting approach. I don't use SharePoint -- my data source is almost exclusively SQL Server -- so I don't know how much work is involved in setting the permissions within SharePoint, and I imagine it could get tedious if you have a lot.

 

The advantage of the "flow calling a flow" method, as far as I can see, is that it's the same for all data sources, whether they normally require explicit permission (like SharePoint), or they would get implicitly shared (like SQL Server). I write the flows to access the data, and I don't ever have to deal with permissions in the data source.

 

Have you used your system long? Do you have a lot of users you have to manage this way? I'd be interested in hearing about your experiences.

petrinnn
Frequent Visitor

@TedBabcock,

 

I'm using this almost one year. It's working fine.

And, about the "tedios" 😃 , it's easy, you dont need to do this permission in each user. You can create a group, or you can select ALL USERS in you AD.

TedBabcock
Advocate I

That's great! I hope others who use SharePoint can take advantage of this system. Thank you for posting!

ElectricLlama2
Frequent Visitor

I read through most of this thread. If I understand correctly the security issue against SQL Server still exists. i.e. anyone given access to the App needs access granted to the data source. Then they can then create a PowerApp using that data source to do whatever they want through it. Is there an option to deny end users access to create/edit apps? This still wouldn't solve any issue around a PA developer that happens to also need to use a production PA.