Remove privileges from MySQL database
mysql, phpmyadmin, privileges, sql-grant, ubuntu
Solution
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.
Problem
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).