mysql revoke root privileges carefully

问题:

I accidentally did something a bit stupid and typed this into the mysql console:

mysql> grant all on myDB.* to root@'%' identified by 'root';

... and the db configuration is open to remote logins. Now I need to remove this grant but don't want to accidentally revoke all privileges for my root user and effectively lock myself out of the db as the db admin. What should I do?

回答1:

First, verify that your root@localhost and/or root@127.0.0.1 users have access.

SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR root@127.0.0.1;

You should see within the result set a line like GRANT ALL PRIVILEGES ON *.* to... Assuming that entry exists, you can safely remove the grant for root@'%' from the mysql database:

REVOKE all on myDB.* from root@'%';
FLUSH PRIVILEGES;

Assuming you don't want the root@'%' user to exist either:

DROP USER root@'%';

回答2:

one thing you can do is to go through mysql.user to remove the offending line only, and flush privileges

回答3:

Use:

SHOW GRANTS FOR 'root'@'%';

To see all the permission that root has.

Then, to remove specific permissions:

REVOKE SELECT FROM root@'%'

There's more here.