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

if the 5 minute recursion isn't fast enough for you just use http calls instead and have it self call. With the right timings you can make it so it doesn't double call (which can happen with http calls)

Level: Powered On

@kamikaze4416, I don't think this approach would work as intended.

  1. What is stopping the user from adding an item to the editable list directly in SharePoint without PowerApps?
  2. The user still has read access to all the information in both SharePoint lists without using PowerApps.

The idea is that the user should be able to access the information only via PowerApps.

 

@PNman, "under review" for two years. Doesn't look good.

KC
Level 8

I can only speak about SharePoint.

 

When this was first posted,  on the SharePoint side I started only giving certain SharePoint groups permission to create, edit and delete, group members are mostly the admins.

 

In Power Apps, the app runs with the data connection permissions of the app creator not the user, so the user can only add and edit if I put that functionality into the app, so even if the Power App User goes directly to the list and bypasses Power Apps all together, they will not be able to create, edit or delete unless they belong to one of the SP Groups.

 

And if there is an issue you can always check who modified the record last in the SP List Modified by field or contact an SP admin for the list history.

 

To restrict who can import my apps and use my data connections, I stopped creating apps in the Default Environment, where everyone in the company is a creator.

 

Instead I created a new environment, A Department Environment. This new environment at the moment only has one creator, me.  If we do add more department creators it will be a small number and easier to track.

 

I also stopped creating Power Automate Flows in the default Environment and instead I use the new Department Environment for all Power Automate(s) or Flows ? where I'm the only creator in the Environment.

 

Have not had any issues.

Level 8

@AIUYM19 

Sorry for late response had the flu...

  1. What is stopping the user from adding an item to the editable list directly in SharePoint without PowerApps? They can add directly but it stops any unauthorized modifications, can have flow filters. It was mainly to stop certain users changing specific data in that list that they shouldn't (e.g. so they can only edit their own data unless they're admin etc)
  2. The user still has read access to all the information in both SharePoint lists without using PowerApps.
    They will have read access but can't edit the data in the main sharepoint, its not a solution but a workaround that works for people who need to block certain changes but allow others.
Level: Powered On

@kamikaze4416 

I understand what you're trying to do, but I still don't think it prevents someone from changing data directly in SharePoint. Consider this scenario...

  1. User finds an item in the non-editable list they want to edit.
  2. User creates a new entry in the empty editable list to match the item in the non-editable list, with their edits.
  3. Workflow (Power Automate?) modifies the non-editable list based on the entry in the editable list, then wipes the editable list.

The way PowerApps and SharePoint currently work, Power Automate is not going to know the difference between a change made by a user in PowerApps or by the same user directly in SharePoint. In either case, the edited data will appear in the PowerApps app, and the user has edited the information outside of your controls in PowerApps.

 

Also, while this is an attempt to prevent people from editing information outside of PowerApps, that kind of misses the point of this thread. The ask is to remove access from outside PowerApps. That is, the user can neither write nor read the data except through PowerApps.

Level: Powered On

there are definitely work-arounds, but creating two SP lists, two databases, two flows, whatever, is time-consuming and increasing the complexity just makes things more difficult.   You can't truly be an enterprise-grade application when you have to leave your data wide open, or create APIs just to connect to SQL-Server in your own MS environment.

Level 8

@MCCLabelFlow , I don't see why you have to go through as much effort as you just stated?

You need 2 lists, in 1 database and 1 simple, small, but effective flow. And it would work enterprise wide, it might even be a safer way to do it without exposing unnecessary data. Both lists can be in the same database with different permissions. The flow will simply be read, check if user permission, submit on other list if so, delete.

Also @AIUYM19 for the points you just mentioned but I can't see, idk if you deleted but wanted to clarify jic anyone else is concerned regarding them:

If the user finds a non-editable item that they want to change and directly edits the editable list their is a field that exists in default to monitor who edits (that's what you use for the user permission - think its called changed by or something)

And if you REALLY WANT TO BE FANCY, then have a field that is an auto generated keycode (not that hard to do) but make it generate only from the formula in the app, highly doubt someone will be able to figure out how to generate their own keycode 🙃

Level: Powered On

@kamikaze4416  - if I want to connect to a SQL Server DB, I can put a SQL Server database in between the production one and PowerApps, which I then have to give the end-user permission for, and then a flow from that database to my SQL production database, or create an API in logic apps ($$) for powerapps to call (and then I have to configure that), all while maintaining this convoluted mess.

 

Or in c#, I just call the database from the app, with the app having explicit permissions.

 

Now, which one is simpler?

Level 8

@MCCLabelFlow ,I think you misread, both lists are in the same database, no extra user permissions need to be added except a very short read on one and write on another.

No extra databases.

No huge mess.

No attachments to anything except that one original database.

And i think you also didn't notice this is a workaround not a solution this post is several years old and is very unlikely to be solved soon, so since permissions cannot be added to a specific app I have given an alternative that would work.

If you figure out a way to make the app able to hold permissions (which it currently cant - thus the route problem of this thread) then please go ahead.

But could you kindly open your mind to relatively simple workarounds that some people viewing this thread may find useful?

To explain the user permissions again:

The sharepoint database will contain two lists, one admin one anyone - short and simple.

The flow is only attached to the admin user and self runs on a short frequency (I've managed up to every 10 seconds - which is faster than most would need to be)

The app which is open to all your users connects to both lists, it views the admin one and updates to the open one.

The flow then checks, then updates the admin one if correct.

Done 🕺

Sure it's not as simple as giving an app permission but we can't do that between sharepoint and powerapps so here is a solution that people can do! 😄

Level: Powered On

I've been working with PowerApps for about a year now, and I was in despair when I discovered this security issue. But it seems that there is an answer currently available. You need the right license, but it is do-able. It was explained by Shane Young here:

 

https://www.youtube.com/watch?v=NqQGysNCac0

 

Update: the gist of it is this. You create a new Environment in PowerApps. Environments have two user roles, Admins and Makers. In the default Environment, everyone in our tenant (a large university campus) is a Maker. But in the new, secure Environment you create, nobody is a Maker. Those you share the PowerApp with can still use it. But because they're not Makers in the new Environment, they're only able to make new Apps in the default Environment, which does not have the Gateway with the connection to the underlying data source. Therefore, they can't use it in a new App.