cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webdevguy
Level: Powered On

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

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

webdevguy
Level: Powered On

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.

mr-dang
Level 10

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
webdevguy
Level: Powered On

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?

mr-dang
Level 10

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
webdevguy
Level: Powered On

Re: How PREVENT edit of backend data source Excel file

Awesome! Super helpful. Smiley Happy 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

 

__


 

mr-dang
Level 10

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. Smiley Sad 

 

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

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 403 members 2,854 guests
Please welcome our newest community members: