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 116 · Accepted Answer · edited Apr 10 '20 at 01:29

116

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;

edited Apr 10 '20 at 01:29

Leahkim

175
6

answered Feb 28 '13 at 10:31

nickgrim

4,336
1
17
27

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
1

For a production database, I would be careful with %, it can be a security risk. If you have multiple webservers, you can also use wildcarded hosts like '192.168.0.%', or ''%.example.com''. Another option is to add the same user multiple times for each host, or create a separately named user per webserver. – okdewit Feb 28 '17 at 11:22
If, like me, tried to allow root access on a local test machine (RasPi on my case), this won't work since MySQL 5.7. See [this thread](https://askubuntu.com/questions/763336/cannot-enter-phpmyadmin-as-root-mysql-5-7) – Raul Pinto Oct 29 '17 at 11:28
@RaulPinto: that thread seems to be about phpMyAdmin, which isn't totally relevant here, but good to note for people who only use that. – nickgrim Oct 30 '17 at 12:16
1

Better to use the well documented statement *RENAME USER* – Antonio Bardazzi Jun 11 '18 at 13:54
`FLUSH PRIVILEGES` is not sufficient for some reason, so you might need to restart the server. – Artur INTECH Apr 28 '19 at 15:11

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

14

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

271
2
5

CrazyRabbit · Answer 3 · 2022-05-29T16:20:32.197

6

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 May 29 '22 at 16:20

answered Sep 01 '21 at 17:14

CrazyRabbit

119
1
7

you save my life ... its work best ! – Yogi Arif Widodo Aug 09 '22 at 00:50

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

2

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;

answered Apr 19 '16 at 15:46

Roman Haefeli

21
1

And did "it" work? – Pierre.Vriens Apr 19 '16 at 17:09

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,949
12
16

Thats exactly my problem, I can't revoke the permission because of the password. Please read my question. – f00860 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 03 '13 at 07:18

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