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
Super User
I fully agree. This sharing of datasources to user is quite dangerous. I know that it is hidden in app but users are smart! And if we have some poweruser than we have quite big chance that he will discover source of data.
Hope at least somebody is thinking about it.
P.
Level: Powered On

The issue is even worse! I was able to edit a SQL connection with a user which doesn't have permission!! Although the connection is shared, the permission is set to "Can use".

 

It's a serious risk for corporate usage. Besides that, any user can do queries within the app (any app, not only the one a user build herself) by using the developer tools from browser. Everything is plain text and it's very easy to craft a custom query and pull any data from database.

 

I know it's naive to use a connection with a login which has dbo privileges, but it's also naive to allow any query to be built with browser developer tools.

 

I tried to restrict as much as possible the access for my databases, but by design it's impossible to protect sensitive data from unauthorized access.

Level: Powered On

I agree with everything that MartynasJankus and the others have posted. I am developing an app for use by my entire institution, but I am concerned that smart users may find the SharePoint list and edit it directly. Not safe.

Level: Powered On

I am struggling with this as well with an On-Premise SQL connection, for my users to use the app I need to give them "Can Use" for the source data. In the default environment, the only one with access to the on premise data gateway. I can't remove "tenant" (All users) from the Environment Makers security role to prevent them from creating PowerApps and using the shared data connection thus exposing potentially sensitive data. 

Level: Powered On

Hi All 

 

I do have the same issue, I was planning to do powerApps but I found that user can use SQL connection and they can query it without any permission, this is a big issue and no point use powerApps Without fixed this issue 

 

Pasindu

Level 10

In SharePoint, you can set item level permisisons so that only the author can view the items out of the box.  This is available out of the box under Advanced List Settings.  I think you could create a group for people that are approvers...  Have you tried this approach? 

 

item level permissions.png

 

Anonymous
Not applicable

Hi @skylitedave

 

This still does not work. While this would at least prevent the user from editing other people records, this still allows user to edit his own records. What if my app uses some complex calculations to come up with the end result? That means that this user after getting his/her result can just go into the SharePoint List and change his/her values.

 

 

Level 10

We have faced situations like this many times.  The way to handle this is to have a workflow copy the item to a folder within the list in SharePoint with the proper permissions,   namely Read for everyone except the few that have edit or contribute permissions to items in that folder.  You delete the original once the list item or file has been copied over into the folder with the proper permissions.  

 

We do this using the Workflow Manager in Visual Studio, but  you can do it with Flow using a tool from Plumsail here

https://plumsail.com/docs/actions/v1.x/flow/how-tos/sharepoint/create-copy-move-remove-folders-cross...

 

Or you can do it manually following the instructions here

https://support.office.com/en-us/article/move-or-copy-items-from-a-sharepoint-document-library-00e2f...

 

 

Or you can do it using a Flow template available today - just add a step at the end of the Flow to delete the original file... See this link

https://us.flow.microsoft.com/en-us/galleries/public/templates/c246fffdb0aa4578b8838f96950944f6/copy...

 

 

Let me know if you need additional help!

Anonymous
Not applicable

Hi @skylitedave

 

Well in my case all my applications use SQL Server so I do not need this workaround but it is helpful for other trying to protect their data.

 

I still think that the app should have the permissions to the SP List and not users so that we would not need to setup those workarounds.

 

Level 10

Smiley Happy

 

So the PowerApp would be using Impersonation as a superuser with Admin capability...  

 

That would be interesting, but Data Connections use credentials of the current user , and that is a really good thing ...  because we can limit who see what in the PowerApps we build based upon user identity ...

 

Seems like you should be looking at a workflow approach that uses impersonation within the workflow and not within PowerApps... 

 

Have you looked hard at workflow options?