MySQL Mistake with GRANT OPTION

Question

I am unsure reading the MySQL documentation if creating a user with the GRANT option will give them the power to create users and grant privileges, or change the privileges of other users databases.

I have been creating databases for users like this

CREATE DATABASE user;
USE user;
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password'
WITH GRANT OPTION;

Is this the best way of doing it or have I just given my users too much control? They are people I am hosting sites for. Thankfully at this point they are trustworthy. I use quotas.

Edit: I have realized I have been granting users access to all databases. This is obviously stupid I should be using this:

GRANT ALL PRIVILEGES ON database.* to 'user'@localhost' IDENTIFIED BY 'password'

What is the simplest way to revoke privileges for every user except root so I can quickly end this catastrophic rookie mistake?

Answer 1

There is no "mass revoke" statement so you best option to change this is to update the users table:

UPDATE mysql.user SET Grant_priv = 'N' WHERE user != 'root';

Not that occasionally, there are users that should have grant privileges (and should have that) which are not named root. On my system, for example, I have debian-sys-maint (which I actually wonder if they should have grant privileges, but that's how it's installed).

Do not forget to reload the privileges after changing the user table:

FLUSH PRIVILEGES;

You can find more information on http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html.