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

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 11 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';
FLUSH PRIVILEGES;

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

Also you might need to make changes to the mysql.db table as well:

UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username';
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 '15 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

I stumbled across this one, too, and the proposed solution didn't work, since the database specific privileges wouldn't be moved as well. what I did:

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';
UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;
share|improve this answer
    
And did "it" work? – Pierre.Vriens Apr 19 at 17:09

Your Answer

 
discard

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.