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?

migrated from serverfault.com Oct 20 '13 at 10:03

This question came from our site for system and network administrators.

  • 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 '13 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 '13 at 9:09
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