mysql revoke root privileges carefully

Question

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?

Answer 1

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.

Answer 2

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@'%';

Answer 3

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