Suppose we have a mobile application that must receive data from the database. No authorization. Just getting the object by id and its description. Is it possible to create a database user who has only rights to read some database tables and that's it? Then we can connect to the database and make direct requests without spending time creating the API. What do I risk?

    3 answers 3

    Security

    For any SQL dialect, it’s easy to write a query that the server will put. To do this, it is not necessary even to have the right to read anything, a request of several dozen

    cross join (select 1 as i union 2 union 3 union 4 union 5) dt

    Put almost anything.

    The second point is that any DBMS has a limit on the number of connected clients. To bring to the DoS from this side is also very elementary. Scaling is very difficult.

    And just safety bugs. There are bugs that require just an open connection to the database to get root on the server.

    Flexibility

    If you have a public structure, even one table or one table, you will have to live with this table / storage for a very, very long time. Modified structure will equal broken backward compatibility.

    Then suddenly you are lucky and your application has become popular. Caching is not screwed, do not change the visible structure of the database.

    What if you decide to move to another DBMS? Okay, this is too unlikely a thought. Although it is very rare, but it happens.

    Work through the API structure. Make the backend clumsy, but don’t refuse to be able to replace it if necessary. An elementary middleware of the form метод_апи + параметры => SQL-запрос + плейсхолдеры will obviously cover your current tasks, very quickly and easily expand and can be easily replaced if necessary without breaking backward compatibility with the application.

      Connecting to a database is a pretty resource-intensive thing, and if there are many clients, then you are guaranteed to get a system crash. To avoid this, a layer is required that provides several connections to the database and many connections to client devices.

      Some people like to call it an application server, some middle-tier, you call it an API, but the essence does not change.

      Well, for a snack, if you give direct access to the database, you will get SQL injection. At one time, people frolicked like this:

      enter image description here

        In essence, there is nothing left to add to the previous speakers, but at the proof-of-concept stage, such simplifications are admissible, it does save time, but such liberties in production are already quite dangerous.