Providing MySQL users with just the minimum privileges

Question

For a web application, when creating the user which will connect to the MySQL database, you have the choice of privileges. Assuming that the only actions intended to be done by that user are SELECT/INSERT/UPDATE/DELETE, it seems to make sense to only provide those privileges, however I've never seen that recommended anywhere - what are the reasons for and against this method?

Answer 1

There are other privileges that a user might need during an ordinary application, for example:

• CREATE TEMPORARY TABLE
• EXECUTE (stored procedures)
• FILE (for SELECT INTO and LOAD DATA)
• LOCK TABLES

There's also the possibility that minimal privileges could mean only SELECT on certain tables, and only SELECT and UPDATE on other tables, etc. This is subject to change any time the application's functionality is enhanced. And there are weird cases, like the need to have SELECT privilege on a table you never query, because it's referenced by the foreign keys in a table you UPDATE. So tracking minimal privileges is a royal pain.

What are you trying to restrict by using SQL privileges? You're the one who wrote all the code, so managing SQL privileges at a fine granularity shouldn't be necessary. Frankly, if your users are able to upload and run SQL statements that you haven't vetted, you have bigger problems:

SELECT * FROM mytable, mytable, mytable, mytable, mytable ORDER BY 1;

The real tasks you want to govern aren't at the database level, they're at the application business level. For example, a CMS has operations like create a page, edit a page, administer comments, etc. These tasks are higher-level than SQL privileges. You could mimic them with SQL roles (which are groups of privileges), but SQL roles aren't widely supported.

I don't know anyone who maps their application users to distinct MySQL users. They're users you authenticate in your application, after the app has connected to the database (the users are just rows of data in the database).

So you're probably better off having your web app use a single MySQL user with full privileges.

Answer 2

A web app usually uses just one user to access the DB, rather than a user per actual user account. Applying minimal privileges is good practice. The username and password is going to be coded into your script (does anyone obfuscate this?) so there's room for compromise if your scripts aren't managed properly.

In my experience, I very, very rarely have the app delete rows - much better to flag a row as deleted as you then have an audit of what is there rather than not knowing what was there! This approach also helps keep tables and indexes optimised.

Therefore, I would suggest allowing only INSERT, UPDATE and SELECT - it will quickly become apparent if parts of your app need to be relaxed a bit!

Allowing more privileges can only broaden the possibility for DoS attacks by issuing resource intensive commands, or allowing malicious data attacks.