Remove privileges from MySQL database

Question

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).

Solution 1

The USAGE-privilege in mysql simply means that there are no privileges for the user 'phpadmin'@'localhost' defined on global level *.*. Additionally the same user has ALL-privilege on database phpmyadmin phpadmin.*.

So if you want to remove all the privileges and start totally from scratch do the following:

• Revoke all privileges on database level:

  REVOKE ALL PRIVILEGES ON phpmyadmin.* FROM 'phpmyadmin'@'localhost';

• Drop the user 'phpmyadmin'@'localhost'

  DROP USER 'phpmyadmin'@'localhost';

Above procedure will entirely remove the user from your instance, this means you can recreate him from scratch.

To give you a bit background on what described above: as soon as you create a user the mysql.user table will be populated. If you look on a record in it, you will see the user and all privileges set to 'N'. If you do a show grants for 'phpmyadmin'@'localhost'; you will see, the allready familliar, output above. Simply translated to "no privileges on global level for the user". Now your grant ALL to this user on database level, this will be stored in the table mysql.db. If you do a SELECT * FROM mysql.db WHERE db = 'nameofdb'; you will see a 'Y' on every priv.

Above described shows the scenario you have on your db at the present. So having a user that only has USAGE privilege means, that this user can connect, but besides of SHOW GLOBAL VARIABLES; SHOW GLOBAL STATUS; he has no other privileges.

Solution 2

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.

Comments

[Chris Wesseling]

Do you have to FLUSH PRIVILEGES after this?

[Cyntech]

I couldn't look at this until today, and your answer solved the privileges problem, thanks. However, my Connection for controluser as defined in your configuration failed error remains but that is a different issue.

[Joey T]

DROP USER is all you need to completely remove the user access from mysql.user and mysql.db tables. REVOKE simply toggles the permission flags to 'N' in one or both of those tables for the permissions specified, but leaves user intact. And yes, you need to FLUSH PRIVILEGES after a REVOKE statement, but not after a DROP operation.

[baptx]

Note that you grant "to" a user and revoke "from" a user. I thought the keyword was the same for the revoke command and did not understand why I was getting the syntax error ERROR 1064 (42000).

[baptx]

@JoeyT the FLUSH PRIVILEGES command was not needed in my case after a GRANT or REVOKE command and should not be needed for other people either: stackoverflow.com/questions/36463966/…