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'@'*.*'...)

The proposition

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?
  • is it still possible to limit access down to the row row is the lesser of the problem. What about users locking tables? would it overload the database table? 7.000M potential users could indeed. But you know, there are more devices than users those hundred of thousands of users might fall short. – Laiv May 16 '19 at 15:19
  • Don't ask us what is "proper." There is no such thing. What is "proper" today will be considered passé in 5 years or perhaps even 5 minutes from now. – Robert Harvey May 16 '19 at 15:23
  • Can this help: softwareengineering.stackexchange.com/a/391020/209774 ? – Christophe May 16 '19 at 18:10

If you have database access coded in the mobile application, what stops the user from reverse engineering it and getting the connection to the database out from the source code of the app? Also, if your database server has to respond to calls from a mobile app, it means it has to be open to public and can be accessed from anywhere.

I would create an REST API that'll sit between the Client (App) and the Database. The user will authenticate with the API and make HTTP calls which will then perform whatever database actions are needed.

Additional benefit of this setup is that if you then need a browser client, or an iOS client, you can consume the same API and don't have to deal with direct database connection technology issues.

  • A carefully-crafted database with the proper constraints, suitable security precautions and judicious use of views could solve this problem. – Robert Harvey May 16 '19 at 18:08
  • What would be the benefit of that setup over a REST API? – bobek May 16 '19 at 18:11
  • Performance, for one. Flexibility, for another. Substantially reduced development time for a third. – Robert Harvey May 16 '19 at 18:11
  • Performance - agree. Flexibility - not sure: if requirements change and suddenly you need to write for a client that doesn't have a MySQL driver, you are in trouble. HTTP can be consumed by (alomst) anything. Dev time - also not sure: you'd replace time spent developing an API with time spent developing views, setting up security, constraints etc. – bobek May 16 '19 at 18:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.