2

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?

2
  • Restore your mysql database from backups. Or delete all the users, and create new ones with different names: the permissions will still be in the grant tables, but noone will be able to avail themselves of them as no user will match.
    – MadHatter
    Oct 20, 2013 at 9:06
  • I have been doing this for as long as I've been making backups. What about REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1, user2, user3; Does that work?
    – John Tate
    Oct 20, 2013 at 9:09

1 Answer 1

3

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.

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.