cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webdevguy 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
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 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.

mr-dang 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
webdevguy 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?

mr-dang 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
webdevguy 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

 

__


 

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

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (7,799)