Database Access – Who Should Have Direct Access to a Database and Why?

databasedesign

I am developing a chat application as my the first project. I have never had experience of establishing a connection to the remote database server before so I don't really understand who should have access to that DB.

I have a user part (with GUI), server part deployed at the remote machine (to send messages from one user to another) and MySql server deployed at the same machine as a server part.

After user logs in to the system, user data stored in a database should be sent to the user part. My question is if the database should be connected to the user part directly to provide that data or the server part should only have access to the database and in order to get data from it user part should send requests to the server part?

Best Answer

Direct connection of the end user part to the database part is not the recommendable way, because if you allow such a connection:

  • you must expose the database to the outside world, with the risk of admin account being hijacked.
  • you will allow the user to connect with other tools than your app, like for example with an SQL engine
  • hence you have to protect the data at the SQL level in a way that the user can only access data he/she is entitled to view (this might require creation of separate views for every user).
  • hence you need to activate triggers and integrity constraints to ensure that a user is not inserting inconsistent data into the database
  • you would have to update all the client parts if you update the database engine or switch to another one
  • you might have to update all the clients at once, if you would change the database schema
  • if you have many users, the database listener (the component that allows user to connect to the engine) might become a bottleneck
  • firewalls between the users and the servers may block the ports that are needed by the database listener
  • ...

For all these reasons, it's much better to let the client parts communicate with the server part. Only the server part would then connect to the database. The database should be configured to accept only connections from the server and isolated form any other trafic.

The server part, which is under your control, would then manage the access to the data, and enforce that a user will get access only to relevant data. As you control the server part, you may also assume that the database requests are legitimate and let the server ensure consistency. Whenever the server becomes a bottleneck, you may well run additional server instances connecting to the same database engine (3 tier approach)

Related Topic