93

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?

f00860
  • 1,243

6 Answers6

132

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;
Leahkim
  • 175
nickgrim
  • 4,516
  • 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 – 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
26

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

CrazyRabbit
  • 539
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.

Antonio Bardazzi
  • 311
3

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;
Roman Haefeli
  • 31
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;
Abhishek Anand Amralkar
  • 1,989
  • 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
1

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;
mikew
  • 121