I am asking advice from the SQL graybeards. Because my following proposition goes against everything I have practiced.
In this instance
Let's assume we are developing a phone app. A social network with hundreds of thousands of users. Our technology is java for the use on the android phone and will be open sourced. Furthermore our central database is MySQL.
Also, When I say "App User" I mean the end-user. "MySQL user" I mean a typical mysql credential (ie,
CREATE USER 'john'@'*.*'...)
Is it a good idea for a phone app to have direct access to the SQL server (Data via port 3306, not HTTPS). If it were implemented this way, because the project is open source, every one would know.
Why I wouldn't automatically consider this a horrible idea
MySQL/MarinaDB has come a long way from just logging in with a user/pass set. It now can be communicated in an TLS layer with signed certificates. Each set of credentials can be restricted to roles, meaning one can be limited down to the selection of the columns. A lowgrade server can handle 50k+ queries a second.
Follow up questions
- Can you limit a mysql user's query per second?
- Should App Users share a single MySQL user or should each one (of the thousands) of App Users have their own MySQL users?
- If all App Users share a single MySQL user, is it still possible to limit access down to the row. For example, an App User can update their own password, but not someone else's.
- If all App Users had their own MySQL user, would it overload the database table? Was MySQL built to handle hundreds of thousands of MySQL users connecting at the same time?
- Assuming this is a bad idea, then what is the proper method of doing something like this? What is the proper method of minimizing development of the layer between the App User and database?