cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

How PREVENT edit of backend data source Excel file

I created a powerapp that is using an Excel file as the backend data source. It's on one drive.

 

I noticed that in order for another user in my org to use this powerapp I have to (1) share the app with them from powerapp AND (2) give them edit permissions to the actual Excel file (or else they can't write data).

 

Well, if I share the Excel file with them they get an email with a link that opens up the Excel file directly in office.com. And they can edit it without going through the powerapps interface.

 

Am I missing something here? I DEFINITELY don't want them editing the file outside of my app. 

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: How PREVENT edit of backend data source Excel file

Hi @webdevguy

 

Unfortunately, that's the way that it works. For Excel data sources that you want to make available through PowerApps, end users will be able to access the data source outside of PowerApps. The same applies to SharePoint lists.

 

For SQL Server, here's a useful post by @Anonymous about the quirks of securing SQL Server.

 

https://powerusers.microsoft.com/t5/General-Discussion/Making-SQL-Connector-Secure/m-p/112600

 

View solution in original post

9 REPLIES 9
Highlighted
Super User
Super User

Re: How PREVENT edit of backend data source Excel file

Hi @webdevguy

 

Unfortunately, that's the way that it works. For Excel data sources that you want to make available through PowerApps, end users will be able to access the data source outside of PowerApps. The same applies to SharePoint lists.

 

For SQL Server, here's a useful post by @Anonymous about the quirks of securing SQL Server.

 

https://powerusers.microsoft.com/t5/General-Discussion/Making-SQL-Connector-Secure/m-p/112600

 

View solution in original post

Highlighted
Helper II
Helper II

Re: How PREVENT edit of backend data source Excel file

Thanks for the info!!! Seems like a massive security hole. Essentially anyone who can use the app can bypass all the app safeguards, business logic, etc... and edit the data directly. In fact, not only can they do this, they actually get an email telling them the direct link to the datasource file after I give them r/w permissions. That's scary. I'm hoping MS can patch this. Seems like a pretty important issue.

Highlighted
Community Champion
Community Champion

Re: How PREVENT edit of backend data source Excel file

@webdevguy, I have some clarifying questions for you that may help you choose the right place to store your data.

 

  • What subscription are you using that gives you and your users access to PowerApps?
  • How large are your datasets?

 

If you and your users are getting access to PowerApps by Office 365 E3 subscription or higher, then you should have access to SharePoint as part of that offering. It would be a more robust solution to fit your needs. If your datasets consist of thousands upon thousands of records, storage on SQL database may be a better fit than Excel.

 

Brian

 

___________

Microsoft Employee
@8bitclassroom
Highlighted
Helper II
Helper II

Re: How PREVENT edit of backend data source Excel file

Office 365 Business

 

Datasets might be just a few hundred rows. Not much data. I think we do have access to Sharepoint thru the plan.

 

How would I sign up for a SQL db? Through azure service?

Highlighted
Community Champion
Community Champion

Re: How PREVENT edit of backend data source Excel file

@webdevguy, you are in luck because SharePoint sounds like the perfect solution for you--and there's lots of video/blog content by @Shanescows @darogael @Audrie-MSFT and many others on getting started with SharePoint and even going beyond. Perhaps they can share a video to build your first SharePoint list.

 

While I love SQL and would recommend it in a heartbeat, I believe it is best to use a service that fits your needs without needing to spend more $. You can reach out to me in a PM and I can get you started if you want to go that route.

 

Mr. Dang

 

__

Microsoft Employee
@8bitclassroom
Highlighted
Helper II
Helper II

Re: How PREVENT edit of backend data source Excel file

Awesome! Super helpful. 🙂 So, if using SharePoint lists as the backend, will users be able to still edit the SharePoint list directly, as in the case with Excel? That’s one of the major security flaws I see with using Excel as the backend. (They have full, direct, read/write access.)


@mr-dang wrote:

@webdevguy, you are in luck because SharePoint sounds like the perfect solution for you--and there's lots of video/blog content by @Shanescows @darogael @Audrie-MSFT and many others on getting started with SharePoint and even going beyond. Perhaps they can share a video to build your first SharePoint list.

 

While I love SQL and would recommend it in a heartbeat, I believe it is best to use a service that fits your needs without needing to spend more $. You can reach out to me in a PM and I can get you started if you want to go that route.

 

Mr. Dang

 

__


 

Highlighted
Community Champion
Community Champion

Re: How PREVENT edit of backend data source Excel file

There are many layers of permissions that you can apply on SharePoint.

 

Here's a video from Peter Kalmstrom about exporting Excel data into SharePoint, which may be the easiest way to migrate:

https://www.youtube.com/watch?v=3ExC13OjTZI

 

And here's a video from @Audrie-MSFT about creating an app from SharePoint:

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

 

There's a lot of content about SP on YouTube, but if you're having trouble searching for a precise topic, please let me know and I can suggest search terms.

 

Mr. Dang

___________________

Microsoft Employee
@8bitclassroom
Highlighted
Super User
Super User

Re: How PREVENT edit of backend data source Excel file

@webdevguy sadly yes. If they know the SharePoint URL they will be able to go right around your app. Nothing you can do about it. 😞 

 

Take a look at this video PowerApps Azure SQL to at least better understand the SQL option. 

 

Thanks for the connection @mr-dang

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
Highlighted
Frequent Visitor

Re: How PREVENT edit of backend data source Excel file

Hi all,

If you are using SharePoint List as your Data Source  to patch the data and you are the site collection administrator , please make sure you set the list permissions to only a group with "Add and Edit"  permissions only.

This will restrict the users of the Power App to access the data from backend.

Make sure you remove all other groups from the permissions of that particular list.


This should solve your problem.

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,481)