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?

  • What would happen if the user had direct access to your database via a MySQL prompt? Would they be able to do any damage? – user253751 Apr 26 at 3:50
  • 1
    If you accept direct connections from end users, then you need to authenticate each user individually. Can you trust those users not do something malicious with the database or guarantee that their systems won't be compromised? (hint: you generally can't be 100% sure of either of those things). On the other hand,you would have full control over the means of authentication between an app server and the database, and full control over the security of both the server and database. So in general, the fewer sources of database connections you need to worry about, the better. – Ben Cottrell Apr 26 at 5:43
  • @BenCottrell Thank you for your answer, I have got your point. – RichBirch Apr 26 at 12:43

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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