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
KC
Level 8

I can't speak to Excel but in the case of SharePoint I use the Flow SharePoint Action "Send an HTTP Request to SharePoint" along with a call to the SharePoint API's to change the item level permissions to Read Only, this along with the permissions you set on the list from within SharePoint will prevent the submitter from making changes but allow you to designate other privs for admins of the list.

 

A great tutorial that helped me with this can be found on Serge Luca's blog post https://sergeluca.wordpress.com/2018/05/03/assign-unique-permissions-to-a-document-with-the-new-send...

 

As for SQL, if you create your app in the Default Environment then from what I understand anyone with App creator permissions will be able to reuse your connection but if you create your app in another environment then only the people you have added to your environment will be able to reuse your connection in their app. But you can setup User Roles in your admin center to help control this.

 

Unfortunately for On-Premise SQL, the data Gateway is only available in the Default Environment. Therefore at this time I cannot use On-Premise SQL.

Flow Staff
Status changed to: Under Review
 
Level: Powered On

Powerapps seems so cool especially how easy it is to create an app that is able to link to various data sources however I'm one of many that agrees the app user should not be required to have access to the data sources.

 

 

Sure there are varous workarounds to restrict access however it's just inviting more loopholes for those challenge seeking minds.

 

The data sources should be sandboxed within the powerapp itself which restrict end-users to access them directly.

 

It's that simple, end-users access the app and the app is the only way of accessing the data.

 

This is nothing new for any sorts of applications that we have currently, I hope Microsoft Powerapps team is able to accomplish this soon and make powerapps truly powerful again.

Level: Powered On

I am just adding my support in to the orignal request. I had a very similar experience to several on this post - after spending quite a bit of time building a PowerApp to manage data for our school's department, I found out that my data source had to be shared as well to my users. Not so bad for the faculty involved, but the student population was going to be tricky - both from a practical and a security standpoint. I pretty much put a hold on the whole project until I could find a more secure way to protect the data source. I hope something is in the pipline soon!

Level: Powered On

This is certainly not a good situation! 

Either powerapps needs it's own user or SharePoint needs a way to hide the list in site contents for certain user groups.

 

I may have a workaround for now....

Turn off permission inheritance for the list, set all the users to 'Contribute', have only 1 view and set the filters to 'Author/Creator' equal to [Me].  Contribute users dont seem to be able to create their own views.

 

Of course this still sucks if you want some users to be able to view/edit in Sharepoint, but it will keep PowerApps usable until MS provides a proper solution.

Level: Powered On

I prefer PowerApps as a design platform, but I previously used AppSheets and frankly I feel they've nailed this solution hands-down.  Their default security setting allows all App users to access the data source with the permissions of the App creator WITHOUT requiring the creator to directly share any of the source data.  Then, there's also an option to also access data as their default user account which requires direct sharing of the data. 

 

My apologies if mentioning a competitor's product is considered bad form, but it's a method I think Microsoft should consider and it's as easy for the app designer as selecting a toggled button to set the access mode.

 

https://help.appsheet.com/security/cloud-storage-access-control/access-mode-as-app-creator-or-app-us...

 

Level: Powered On

Have we got any update on this @Audrie-MSFT? Kind of wanting a solution around this for an app I am currently building...

Level: Powered On

I commented a month ago with a workaround if SharePoint online is your datasource.

 

If your powerapp only needs users to submit forms (but doesn't allow users to edit or browse using the powerapps gallery) then you may be able to acheive the same thing using Item-Level Permissions for your SharePoint List.

 

item level.PNG

 

Item-Level Permissions (under List Settings > Advanced) should do the trick. You can set users to one read their own items and edit none.

Level: Powered On

@Daniel_Pipe, thanks for that however but the data needs to be able to be accessed by 500+ people, and gallery view's/edits are necessary.

 

Thanks for the workaround though!

Level: Powered On

@NickGrant no problem. It sounds like my workaround will still work for you. Set all user groups (aside from yourself) to Contribute so that they can't create their own views then set the default view to filter the data to show only items created by that user. Remove all other public views and create your own private view to show all items.

I'm using this for ~100 users. You loose the ability for users to edit items via sharepoint but if your app allows users to view, edit and submit items then you can forget editing on sharepoint directly anyway.

Hope it helps out!