如何授予对整个子网的远程访问 MySQL？

我可以很容易地授权访问一个 IP 使用这个代码:

$ mysql -u root -p
Enter password:
mysql> use mysql
mysql> GRANT ALL ON *.* to root@'192.168.1.4' IDENTIFIED BY 'your-root-password';
mysql> FLUSH PRIVILEGES;

但我需要允许整个子网192.168.1. * 远程访问数据库。

我该怎么做？

188230 次浏览
小开
最佳答案

EDIT: Consider looking at and upvoting Malvineous's answer on this page. Netmasks are a much more elegant solution.

Simply use a percent sign as a wildcard in the IP address.

From http://dev.mysql.com/doc/refman/5.1/en/grant.html

You can specify wildcards in the host name. For example, user_name@'%.example.com' applies to user_name for any host in the example.com domain, and user_name@'192.168.1.%' applies to user_name for any host in the 192.168.1 class C subnet.

小开

You would just use '%' as your wildcard like this:

GRANT ALL ON *.* to root@'192.168.1.%' IDENTIFIED BY 'your-root-password';
小开 
mysql> GRANT ALL ON *.* to root@'192.168.1.%' IDENTIFIED BY 'your-root-password';

The wildcard character is a "%" instead of an "*"

小开

Just a note of a peculiarity I faced:
Consider:

db server:  192.168.0.101
web server: 192.168.0.102

If you have a user defined in mysql.user as 'user'@'192.168.0.102' with password1 and another 'user'@'192.168.0.%' with password2,

then,

if you try to connect to the db server from the web server as 'user' with password2,

it will result in an 'Access denied' error because the single IP 'user'@'192.168.0.102' authentication is used over the wildcard 'user'@'192.168.0.%' authentication.

小开

It looks like you can also use a netmask, e.g.

GRANT ... TO 'user'@'192.168.0.0/255.255.255.0' IDENTIFIED BY ...
小开

after you connect server and you want to connect on your host, you should do the steps below:

  1. write mysql to open mysql
  2. write GRANT ALL ON . to root@'write_your_ip_addres' IDENTIFIED BY 'write_password_to_connect';
  3. press control and X to quit from mysql
  4. write nano /etc/mysql/my.cnf
  5. write # before bind-address = 127.0.0.1 in my.cnf folder
  6. #bind-address = 127.0.0.1
  7. save my.cnf folder with control + X
  8. write service mysql restart
  9. you could connect via navicat on your host
小开

Motivated by @Malvineaus answer I tried it myself and noticed that it didn't work for me.

You can specify subnet masks with '192.168.1.%' or '192.168.1.0/255.255.255.0' but the subnet must always be on complete octets. see https://mariadb.com/kb/en/create-user/#host-name-component. As result the functionality between one way of specification and the other is the same.

For example '192.168.1.0/255.255.255.128' will not work as it is not on a complete octet boundary.

小开

MySQL 8.0.23 onwards now support CIDR notation also.

So, basically:

-- CIDR Notation
GRANT ... TO 'user'@'192.168.1.0/24' IDENTIFIED BY ...


-- Netmask Notation
GRANT ... TO 'user'@'192.168.1.0/255.255.255.0' IDENTIFIED BY ...

提交答案