Limit MySQL requests

Question

Context: A Java client-side application needs to access a server-side MySQL database.

Need: Limit the possible number of requests to the database for each client (based on the client's IP).

Question 1: Is it possible to do this just by changing the MySQL database settings?

Question 2: Is it a good idea to allow access to the database directly from the client-side application? Or should I rather make the client-side app communicate with a server-side app by TCP ? (and thus only allowing access to the DB by the server-side app)

Answer 1

1. You can limit the access to the db by user and host.
2. It depends on the sensitivity of the data you are passing and the network you are using (Internet or LAN).

Answer 2

Client -> Server -> Database is certainly the most secure approach, since Client -> Database requires the client application to contain database Login credentials.

Going with Client -> Server -> Database nullifies question one :].

Answer 3

1. Yes. Look at the syntax for MySQL's GRANT statement. In the with_option, you can specifiy MAX_QUERIES_PER_HOUR. You can even set limits for an existing user without affecting other privileges:

   GRANT USAGE ON db.* TO ... REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 120 MAX_UPDATES_PER_HOUR 100;

2. Sure, as long as the clients are on the internal network of the database and are only given the minimal permissions that they need to function properly. It might be somewhat difficult maintaining the MySQL grants for all of the clients, but at least you will be using a proven security system rather than attempting to re-write a security system as a server-side application, which could have bugs.

See also 5.5.4. Setting Account Resource Limits.

Answer 4

About Question 2.

I think it depends on the sensitivity of the data, the network connection between the server and the client, and the rollout schema of the clients.

• Sensitive data: I'd go with a client - server - DB solution for this one for sure. You can then implement https connections with possibly client-side certificates or card readers.
• Network connection: You don't want to expose a DB to the internet. Ever. But if it's an internal application, only accessible from a protected company network, and the data is not sensitive a direct DB connection is feasible. Do handle row locking or transaction handling correctly otherwise your users won't be happy :)
• Rollout schema: If the application is actively developed there's a fair chance that the database schema changes with it, forcing clients to update. With an intermediate server speaking, for example, SOAP/XML, you decouple database schema from data requests and you have more room to upgrade/change the database without updating all the clients.

I'd personally go for an intermediate server, as it is way simpler to maintain and optimize. There are good solutions in both Python and Java which allow you to quickly create SOAP/XML servers with database access. You can then define XML messages based on what's needed in a certain screen, with appropriate locks, and no regard (ehm) for the database schema. It decouples the database model from the MVC model in the client and allows the client to be relatively clean.