大网数据广告活动
服务器运维问答

Changing host permissions for MySQL users

· 52CE 我爱测

I have the following grants for a user/database mysql> SHOW GRANTS FOR 'username'@'localhost'; +---------------------------------------------------------------------------+ | Grants for username@localhost | +---------------------…

问题原文

原帖链接 · f00860 · CC BY-SA 3.0

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?

已采纳解答

原帖链接 · nickgrim · CC BY-SA 4.0

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;

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2013-02-28)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0 / CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。