[mysql] Remove privileges from MySQL database

Before you think this is a duplicate question, I believe I have a unique, even if it is somewhat dim-witted, case.

A few days ago, I upgraded the version of MySQL on my Ubuntu 10.04 server to 5.3.3 (it's ahead of the Ubuntu releases for 10.04). Today, I attempted to log into phpMyAdmin for something and discovered the somewhat dreaded Connection for controluser as defined in your configuration failed error.

After following descriptions from several SO questions on how to fix this, I have become stuck.

• I attempted to reconfigure phpMyAdmin, with no success.
• I attempted to uninstall phpMyAdmin and reinstall it, but it couldn't remove the privileges from the DB and failed.
• I then attempted to manually remove the privileges of the user - somewhat foolishly, I might add - from the DB, then dropping the db, then the user (with flush privileges).
• I dropped the whole install of phpMyAdmin completely (deleting the application and the /etc/phpmyadmin directory) and reinstalled (using apt-get) but it said the permissions for the phpmyadmin user already existed:

granting access to database phpmyadmin for phpmyadmin@localhost: already exists

So, here is what I'm left with. I have a grant that I cannot modify, nor revoke:

mysql> show grants for 'phpmyadmin'@'localhost';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for phpmyadmin@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY PASSWORD '*46CFC7938B60837F46B610A2D10C248874555C14' |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost'                                                |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.26 sec)

mysql> revoke usage on *.* from 'phpmyadmin'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'phpmyadmin' on host 'localhost'

mysql> revoke usage on *.* from 'phpmyadmin'@'localhost' identified by 'trustno1';
ERROR 1141 (42000): There is no such grant defined for user 'phpmyadmin' on host 'localhost'

(Don't worry, I do not use this password anymore, but it was the password that was used previously and it is not the password I chose for the new phpmyadmin installation).

How do I totally remove these grants/privileges? I am happy to start again from scratch if need be (phpmyadmin that is, not the DB).

As a side note, the reason revoke usage on *.* from 'phpmyadmin'@'localhost'; does not work is quite simple : There is no grant called USAGE.

The actual named grants are in the MySQL Documentation

The grant USAGE is a logical grant. How? 'phpmyadmin'@'localhost' has an entry in mysql.user where user='phpmyadmin' and host='localhost'. Any row in mysql.user semantically means USAGE. Running DROP USER 'phpmyadmin'@'localhost'; should work just fine. Under the hood, it's really doing this:

DELETE FROM mysql.user WHERE user='phpmyadmin' and host='localhost';
DELETE FROM mysql.db   WHERE user='phpmyadmin' and host='localhost';
FLUSH PRIVILEGES;

Therefore, the removal of a row from mysql.user constitutes running REVOKE USAGE, even though REVOKE USAGE cannot literally be executed.