Problem
The biggest problem with developing PowerApps with Azure SQL Database is that we have to share the SQL Connector with each user of the app.
What that means is that each employee can bypass the app by creating their own app and adding this connector to their app (since it is shared). They get the ability to see all the tables and views in the database. Basically, everything there is in the SQL database, on top of that they also get the ability to edit the information in any way they please.
This is not an issue for non-confidential information and simple apps. However, we have plans to develop more complex apps with data that should not be seen by everyone who will be using the app. PowerApps is great as we can build custom logic on who sees what. However, since each employee can create a fake app and throw in the SQL connector that was shared with them, this means that all the security and complex data validation built in the app becomes useless.
Idea
I think the simplest solution would be to make the SQL Connector when sharing it, the user gets โCan useโ permission, it would be great if we could give an even lower permission level like โCan use only in this Appโ. This would make it impossible for them to create fake apps and throw in this SQL Connector to see data they are not supposed to see.
OR
Another option would be that when user has โCan useโ permissions on SQL Connector they would only be allowed to use it where the owner put that SQL Connection, making it impossible for them to drop this connection in their Apps or Flows.
Either one of those solutions would make PowerApps a lot more useful for a large number of corporations. This would definitely push PowerApps adoption for more complex systems and bring it above other similar platforms out there.
on:
Allow Users to Add Columns to View in Unified Inte...
on:
Improve Print(): Implement printing of multiple pa...
on:
There is a bug in the RoundUp and RoundDown functi...
Oliviatran
on:
Ability for embedded canvas apps to auto-set heigh...
on:
Calling a SQL Function in PowerApps (parameters be...
on:
Disable Microsoft Feedback Survey
on:
Have the ability to post a Teams message directly ...
Catching up to this discussion and updating the status. We are looking at adding additional auth models for SQL. In the meantime, as many posters have pointed out here, the solution is to create your app in an environment other than the default environment, where you can control who can build apps and thus reuse the connection. Separating apps by environments is a best practice regardless.
Regarding discussion here on using Gateways in the non-default environment, per comments here this is possible today by filiing a support ticket and giving us some context to evaluate the request.