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: Planned

this work is now planned.   All of the connection types you discuss above are "implicit" connections.  We are currently in arch design discussions to address this. 

Comments
drwaz
Helper I

Thanks for the quick reply!

 

Good to know it's in the works

Hazeeq
Regular Visitor

@LanceDelano Hi , it's almost fall now. Any updates?

TheKlaw
Regular Visitor

Dear @LanceDelano , how is it progressing? Fingers crossed we see a green light soon!

LanceDelano
Power Apps

Closing in on it.  We had to bring a number of groups together to make this happen.  But, getting to the closing work and you should see a private preview / experimental flag for this in product soon. 

r_heibel
Regular Visitor

@LanceDelanoThanks for the update. I just had the first students try to mess with the source data (sharepoint list) for reward points in school. 🙂

DPozzi
Helper II

I agree this must be solved, maybe using a service account hidden inside the app itself.

I've solved the direct SharePoint issue by breaking all the inheritance and creating via the PnP PowerShell module a custom permission profile that has the full edit/new/add permission but no direct "view" permission so users can edit/add new items in SharePoint list but they can't discover the site thru direct search or neither access using a direct link, but this should be easier to achieve

KH_2437
Helper I

Thanks @LanceDelano 

 

Really looking forward to this, hoping status of the idea moves to green soon, aka -> 'Started'.

TedBabcock
Advocate II

I use a method explained above by @skylitedave and @AIUYM19 in which the app calls a flow (parent flow) which itself calls another (child flow). My back end is SQL Server, and all CRUD operations are performed there. And now that Power Apps has a ParseJSON function, I will eventually be able to do all data operations with just two flows, sending a stringified JSON parameter in the parent flow, and getting a stringified JSON object back, to be made into a collection with ParseJSON. I don't plan to ever use direct data connections in Power Apps -- no Patch, no forms, etc.

KH_2437
Helper I

Hi @TedBabcock ,

 

This is very interesting, do you notice any degradation in user experience, specifically a slight lag associated with the execution of the flow in the background, or is it instantaneous?

 

You are basically using Power Automate as a type of ORM - I think, e.g., I have a friend who does .NET web development and his company wrap all db tables in stored procedures through which all CRUD is carried out, so the web code never touches a table.

TedBabcock
Advocate II

Hi, @KH_2437 -

 

The flows seem to run pretty fast, and when there's an unusual delay, it's usually because of the back-end SQL Server stored procedures. For example, I have a crosstab query that runs more slowly than I'd like (30 seconds), and that's reflected in the app. But I can't say for sure because I don't have any production apps using a direct data connection. I don't notice much difference between the updates my apps do and the operations done with direct data connections in the videos by Shane Young and others, if that's any help.

 

I'm a citizen developer, so if you say that I'm using Power Automate as a kind of ORM, I will accept it, without really knowing what that is without googling it. 🙂 However, because I like the flexibility and power offered by doing all CRUD operations within SQL Server, I am going to continue doing what I'm doing, even after MS fixes the problem of implicit sharing. With the addition of the ParseJSON function, when implicit sharing is fixed, the only change I foresee thinking about making is that I might be able to use only one flow for all data operations, whereas now I need a parent and a child flow.