Changing host permissions for MySQL users

Question

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?

Answer 1

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 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';

and then flush to apply the privileges:

FLUSH PRIVILEGES;

Answer 2

The best option on MySQL 8+ / MariaDB 10+ would be:

RENAME USER 'username'@'oldhost' TO 'username'@'newhost';

See https://dev.mysql.com/doc/refman/8.0/en/rename-user.html

Answer 3

Best answer on Stackoverflow suggesting to use RENAME USER which copy the user privileges.

Using Data Control Language (statements as GRANT, REVOKE, RENAME and so on) does not require FLUSH PRIVILEGES; and is required in architecture like Galera or Group Replication in MySQL versions having MyISAM tables in mysql database because MyISAM tables are not replicated.

Answer 4

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;

Answer 5

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;

Answer 6

Missing a lot of the tables if you have privileges other than simply db (like tables or columns etc). Depending on what grants your user has, you may need to update all these tables or some:

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';
UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username';
UPDATE mysql.tables_priv SET Host='%' WHERE Host='localhost' AND User='username';
UPDATE mysql.columns_priv SET Host='%' WHERE Host='localhost' AND User='username';
UPDATE mysql.procs_priv SET Host='%' WHERE Host='localhost' AND User='username';
UPDATE mysql.proxies_priv SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;