允许所有远程连接，MySQL [英] Allow all remote connections, MySQL

问题描述

我一直在使用 SQL Server，现在在一个项目中使用 MySQL.使用 SQL Server，如果我们的开发人员知道主机、用户名和密码，他们就可以连接到本地计算机上的远程数据库.然而，对于 MySQL，为了让开发人员从他们的本地机器访问，我不得不登录到 MySQL 并执行:

 GRANT ALL ON *.* to user@address IDENTIFIED BY 'password';刷新权限；

其中address是开发者机器的IP地址.当然，如果他们改变网络，我必须再次执行.有没有办法允许所有远程连接，就像我在 SQL Server 上遇到的那样，或者出于某种原因这是一个坏主意?我们还有用户名和密码..我显然有点糊涂了.

另外:这是一个开发数据库，​​只能从我们的内部网络访问.我理解为什么让每个人都可以访问生产数据库是个坏主意.

解决方案

正如上面 Ryan 所指出的，你需要的命令是

 GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';

但是，请注意文档指出，为了使此功能起作用，必须为同一用户创建来自 localhost 的另一个用户帐户；否则，由 mysql_install_db 自动创建的匿名帐户优先，因为它具有更具体的主机列.

换句话说；为了让用户 user 能够从任何服务器连接；需要按如下方式创建2个帐户:

 GRANT ALL ON *.* to user@localhost IDENTIFIED BY 'password';GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password';

在此处阅读完整文档.>

这里是相关的部分供参考:

<块引用>

以 root 身份连接到服务器后，您可以添加新帐户.这以下语句使用 GRANT 来设置四个新帐户:

mysql>创建用户 'monty'@'localhost' 由 'some_pass' 识别；mysql>将 *.* 上的所有权限授予 'monty'@'localhost'->有赠款选项；mysql>创建用户 'monty'@'%' 由 'some_pass' 识别；mysql>将 *.* 上的所有权限授予 'monty'@'%'->有赠款选项；mysql>创建用户 'admin'@'localhost';mysql>GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';mysql>CREATE USER 'dummy'@'localhost';

<块引用>

这些报表创建的账户有以下内容属性:

其中两个帐户的用户名是 monty，密码是some_pass.两个帐户都是具有完全权限的超级用户帐户做任何事.'monty'@'localhost' 帐户只能在以下情况下使用从本地主机连接.'monty'@'%' 帐户使用 '%'主机部分的通配符，因此它可用于从任何连接主持人.

monty需要两个账号才能连接从任何地方作为蒙蒂.没有本地主机帐户，由 localhost 创建的匿名用户帐户当 monty 从本地主机.因此，monty 将被视为匿名用户.这样做的原因是匿名用户帐户有更多特定的 Host 列值比 'monty'@'%' 帐户并因此而来在用户表排序顺序中较早.(讨论用户表排序在第 6.2.4 节访问控制，阶段 1:连接验证"中.)

I had been using SQL Server and am now using MySQL for a project. With SQL Server, our developers can connect to the remote database on their local machines if they know the host, username, password. With MySQL, though, to give a developer access from their local machines, I have been having to log in to MySQL and execute:

GRANT ALL ON *.* to user@address IDENTIFIED BY 'password'; 
flush privileges;

Where address is the IP address of the developer's machine. Of course, if they change networks, I have to execute it again. Is there a way to allow all remote connections like I have experienced with SQL Server, or is this a bad idea for some reason? We have username and password still.. I'm obviously a little confused.

Also: this is a development database and is only accessible from our internal network. I understand why it is a bad idea to give everyone access to a production database.

解决方案

As pointed out by Ryan above, the command you need is

GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password'; 

However, note that the documentation indicates that in order for this to work, another user account from localhost must be created for the same user; otherwise, the anonymous account created automatically by mysql_install_db takes precedence because it has a more specific host column.

In other words; in order for user user to be able to connect from any server; 2 accounts need to be created as follows:

GRANT ALL ON *.* to user@localhost IDENTIFIED BY 'password'; 
GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password'; 

Read the full documentation here.

And here's the relevant piece for reference:

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

The accounts created by these statements have the following properties:

Two of the accounts have a user name of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. The 'monty'@'localhost' account can be used only when connecting from the local host. The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

It is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 6.2.4, "Access Control, Stage 1: Connection Verification".)

这篇关于允许所有远程连接，MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！