Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have the following grants for a user/database

mysql> SHOW GRANTS FOR 'username'@'localhost';
| Grants for username@localhost                                             |
| GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY PASSWORD 'xxx' |
| GRANT ALL PRIVILEGES ON `userdb`.* TO 'username'@'localhost'              |

To enable external access to the database, I need to change localhost to %. One way to do this is REVOKE all permissions and set it again. The problem is, that there is a password set which I don't know, so if I revoke the permission, I can't set it back.

Is there a way to change the hostname localhost to % (and back again) without revoking the permission itself?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

If you've got access to the mysql database, you can change the grant tables directly:

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';

...and an analagous UPDATE-statement to change it back.

share|improve this answer
A little fix (mysql Server version: 5.7.5-m15 - MySQL Community Server): both from phpmyadmin as well as mysql command prompt - UPDATE mysql.user SET Host = 'localhost' WHERE user.Host = '%' AND user.User = 'XXXdbusr'; – Jadeye Jul 21 at 12:45

To change privileges, first revoke all the permission to user

 revoke all privileges on *.* from 'username'@'localhost';

 grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO 'username'@'%';

 flush privileges;
share|improve this answer
Thats exactly my problem, I can't revoke the permission because of the password. Please read my question. – Fu86 Feb 28 '13 at 10:32
You could set the password hash to be the same as the old one via an update. You don't need to know the actual password to do that. – drogart Mar 3 '13 at 7:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.