113

I can easily grant access to one IP using this code:

$ 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;

But i need to allow the whole subnet 192.168.1.* to access the database remotely.

How can i do that?

skystar7
  • 4,419
  • 11
  • 38
  • 41

8 Answers8

153

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

GRANT ... TO 'user'@'192.168.0.0/255.255.255.0' IDENTIFIED BY ...
Malvineous
  • 25,144
  • 16
  • 116
  • 151
  • 8
    This should be the accepted answer as it is the only technical correct one. Supported since at least 5.5: http://dev.mysql.com/doc/refman/5.5/en/account-names.html – lifeofguenter Aug 25 '16 at 16:13
  • 12
    Do not try to use CIDR notation like "172.16.0.0/16", this does not work. Always use the full netmask. – Oliver R. Apr 13 '17 at 08:27
  • 1
    It appears you have to use the first IP address in the range; Using for example 192.168.0.34/255.255.255.0 will fail! – Saa Jun 01 '17 at 08:35
  • @SanderBackus: `192.160.0.34/255.255.255.0` is the same as `192.168.0.34/24` which doesn't really make sense (`/24` means the last number can be from 0 to 255, ignoring your value of 34). Does it work if you use a valid mask for the IP, like `192.168.0.34/255.255.255.252`? – Malvineous Jun 02 '17 at 05:11
  • @Malvineous That was my point. If you use 192.160.0.34/255.255.255.0 mysql does not allow 192.160.0.34. I meant to allow full /24 (yes I used full netmask, as documented), but apparently If you have to use 192.160.0.0/255.255.255.0 to allow full range. – Saa Jun 02 '17 at 08:41
  • @SanderBackus: Right sorry, I thought you were trying to allow a /25 or smaller by using `.34`. – Malvineous Jun 07 '17 at 00:16
  • @ArianFaurtosh From the link in the answer "Netmask notation cannot be used for IPv6 addresses." – Malvineous Sep 14 '21 at 06:41
  • Netmasks are also supported in MariaDB: https://mariadb.com/kb/en/create-user/#host-name-component – Per Lundberg Aug 09 '23 at 10:19
115

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.

p0lar_bear
  • 2,203
  • 2
  • 21
  • 31
  • 7
    I just did it like this, but, say, what makes it not apply to: user_name@'192.168.1.my-hacked-rnds.killing.mysql.com'???? i'll now walk on, since this being MySQL I sense I don't want to know the answer. – Florian Heigl Apr 24 '15 at 19:24
  • +Florian, I figure it beats simply using '%' as it does add a layer of security that must be overcome, but I think you do make a good point about its weakness. I certainly supplement this configuration by other means, such as locking the database server in a separate subnet that I can control access to via a firewall to negate your concerns, among other things. – Josiah Jun 02 '15 at 15:28
  • 26
    Examples like '192.168.1.my-hacked-rnds.killing.mysql.com'' are specifically addressed in https://dev.mysql.com/doc/refman/5.5/en/account-names.html : _To foil such attempts, MySQL disallows matching on host names that start with digits and a dot. ... An IP wildcard value can match only IP addresses, not host names._ – Stefan Lasiewski Sep 11 '15 at 23:15
35

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

GRANT ALL ON *.* to root@'192.168.1.%' IDENTIFIED BY 'your-root-password';
Mike Brant
  • 70,514
  • 10
  • 99
  • 103
17
mysql> GRANT ALL ON *.* to root@'192.168.1.%' IDENTIFIED BY 'your-root-password';

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

David Allen
  • 760
  • 6
  • 18
9

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.

site80443
  • 190
  • 5
  • 10
  • 2
    I am not sure this is an answer to the question. If it doesn't provide a direct answer to the original question it should be posted as a comment. – Kmeixner Jun 02 '16 at 13:23
  • 16
    Kmeixner , you expect him to write so much in a comment? Apply some common sense and stop being a robot. This community is for helping the developers not to make their life difficult. – user1735921 Aug 31 '16 at 05:52
  • Thanks for this invaluable tip. – TenG Nov 30 '20 at 11:41
3

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.

Patric
  • 31
  • 1
2

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 ...
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
0

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
murat delen
  • 17
  • 3