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?

score 137 · Accepted Answer · edited Apr 10 '20 at 01:29

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;

CrazyRabbit · Answer 2 · 2022-12-13T15:01:13.510

37

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

edited Dec 13 '22 at 15:01

answered Sep 01 '21 at 17:14

CrazyRabbit

669

Antonio Bardazzi · Answer 3 · 2020-01-22T08:22:53.657

15

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.

edited Jan 22 '20 at 08:22

answered Oct 04 '17 at 14:16

Antonio Bardazzi

351

score 3 · Answer 4 · answered Apr 19 '16 at 15:46

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;

score 1 · Answer 5 · answered Feb 28 '13 at 09:59

1

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;

answered Feb 28 '13 at 09:59

Abhishek Anand Amralkar

1,997

score 1 · Answer 6 · answered Oct 13 '16 at 16:43

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;

Changing host permissions for MySQL users

6 Answers6