将主机访问权限重新分配给 MySQL 用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1913984/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Re-assign host access permission to MySQL user
提问by Nick Jennings
I have several thousand MySQL users all set to allow access from a specific host. The problem is that now I'm going to have two machines (more in the future) which will need to use the same account to access each of their databases.
我有几千个 MySQL 用户都设置为允许从特定主机访问。问题是现在我将有两台机器（将来会有更多）需要使用相同的帐户来访问它们的每个数据库。
I'd like a quick and easy (as automated as possible) way to run through and modify the host portion of each user account to fit an internal network wildcard. For example:
我想要一种快速简便（尽可能自动化）的方法来运行和修改每个用户帐户的主机部分以适应内部网络通配符。例如：
'bugsy'@'internalfoo' has access to the 'bugsy' DB.
'bugsy'@'internalfoo' 可以访问 'bugsy' 数据库。
I want to now allow bugsy access from anywhere on the internal network
我现在想允许从内部网络上的任何地方进行错误访问
'bugsy'@'10.0.0.%' has access to the 'bugsy' DB.
'bugsy'@'10.0.0.%' 可以访问 'bugsy' 数据库。
回答by Nick Jennings
For reference, the solution is:
作为参考，解决方案是：
UPDATE mysql.user SET host = '10.0.0.%' WHERE host = 'internalfoo' AND user != 'root';
UPDATE mysql.db SET host = '10.0.0.%' WHERE host = 'internalfoo' AND user != 'root';
FLUSH PRIVILEGES;
回答by Pedro
The accepted answer only renamed the user but the privileges were left behind.
接受的答案仅重命名了用户，但保留了权限。
I'd recommend using:
我建议使用：
RENAME USER 'foo'@'1.2.3.4' TO 'foo'@'1.2.3.5';
According to MySQL documentation:
根据MySQL 文档：
RENAME USER causes the privileges held by the old user to be those held by the new user.
RENAME USER 使旧用户拥有的权限成为新用户拥有的权限。
回答by e18r
The more general answer is
更一般的答案是
UPDATE mysql.user SET host = {newhost} WHERE user = {youruser}
回答by T.J. Crowder
I haven't had to do this, so take this with a grain of salt and a big helping of "test, test, test".
我没有必要这样做，所以带着一粒盐和“测试，测试，测试”的大帮助来接受这个。
What happens if (in a safe controlled test environment) you directly modify the Hostcolumn in the mysql.userand probably mysql.dbtables? (E.g., with an updatestatement.) I don't think MySQL uses the user's host as part of the password encoding (the PASSWORDfunction doesn't suggest it does), but you'll have to try it to be sure. You may need to issue a FLUSH PRIVILEGEScommand (or stop and restart the server).
如果（在安全受控的测试环境中）您直接修改表中的Host列mysql.user以及可能的mysql.db表中的列会发生什么情况？（例如，使用update语句。）我不认为 MySQL 将用户的主机用作密码编码的一部分（该PASSWORD函数不建议这样做），但您必须尝试确定。您可能需要发出FLUSH PRIVILEGES命令（或停止并重新启动服务器）。
For some storage engines (MyISAM, for instance), you may also need to check/modify the .frmfile any views that user has created. The .frmfile stores the definer, including the definer's host. (I havehad to do this, when moving databases between hosts where there had been a misconfiguration causing the wrong host to be recorded...)
对于某些存储引擎（例如 MyISAM），您可能还需要检查/修改.frm用户创建的任何视图文件。该.frm文件存储定义者，包括定义者的主机。（我也不得不这样做，在主机间移动数据库时，这里曾有过导致错误的主机要记录一个错误配置...）
回答by jhayton
Similar issue where I was getting permissions failed. On my setup, I SSH in only. So What I did to correct the issue was
我获得权限失败的类似问题。在我的设置中，我只使用 SSH。所以我为纠正这个问题所做的是
sudo MySQL
SELECT User, Host FROM mysql.user WHERE Host <> '%';
MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> '%';
+-------+-------------+
| User | Host |
+-------+-------------+
| root | 169.254.0.% |
| foo | 192.168.0.% |
| bar | 192.168.0.% |
+-------+-------------+
4 rows in set (0.00 sec)
I need these users moved to 'localhost'. So I issued the following:
我需要将这些用户移至“本地主机”。所以我发布了以下内容：
UPDATE mysql.user SET host = 'localhost' WHERE user = 'foo';
UPDATE mysql.user SET host = 'localhost' WHERE user = 'bar';
Run SELECT User, Host FROM mysql.user WHERE Host <> '%'; again and we see:
运行 SELECT User, Host FROM mysql.user WHERE Host <> '%'; 再次，我们看到：
MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> '%';
+-------+-------------+
| User | Host |
+-------+-------------+
| root | 169.254.0.% |
| foo | localhost |
| bar | localhost |
+-------+-------------+
4 rows in set (0.00 sec)
And then I was able to work normally again. Hope that helps someone.
然后我又可以正常工作了。希望能帮助某人。
$ mysql -u foo -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 74
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
回答by CrackerHyman9
I received the same error with RENAME USERand GRANTS aren't covered by the currently accepted solution:
我收到了相同的错误，RENAME USER并且当前接受的解决方案不包括 GRANTS：
The most reliable way seems to be to run SHOW GRANTSfor the old user, find/replace what you want to change regarding the user's name and/or host and run them and then finally DROP USERthe old user. Not forgetting to run FLUSH PRIVILEGES(best to run this after adding the new users' grants, test the new user, then drop the old user and flush again for good measure).
最可靠的方法似乎是SHOW GRANTS为旧用户运行，查找/替换您想要更改的关于用户名和/或主机的内容并运行它们，然后最后运行DROP USER旧用户。不要忘记运行FLUSH PRIVILEGES（最好在添加新用户的授权后运行它，测试新用户，然后删除旧用户并再次刷新以进行良好测量）。
> 显示 'olduser'@'oldhost' 的赠款；
+------------------------------------------------- ----------------------------------+
| 对 olduser@oldhost 的资助 |
+------------------------------------------------- ----------------------------------+
| 将 *.* 的使用权授予由密码 '*PASSHASH' 标识的 'olduser'@'oldhost' |
| GRANT SELECT ON `db`.* TO 'olduser'@'oldhost' |
+------------------------------------------------- ----------------------------------+
2 行（0.000 秒）
> 授予使用 *.* 到 'newuser'@'newhost' 由密码 '*SAME_PASSHASH' 识别；
查询正常，0 行受影响（0.006 秒）
> GRANT SELECT ON `db`.* TO 'newuser'@'newhost';
查询正常，0 行受影响（0.007 秒）
> 删除用户 'olduser'@'oldhost';
查询正常，0 行受影响（0.016 秒）
