16

I have a hosted MySQL DB that I need to give access to from my customers site. They have a fast broadband connection which can present them with a range of IP addresses from a fixed pool.

So I need to lock down the access to their MySQL user so that the Host setting is a range of addresses within a class. Not the whole class.

I realise I can use a wildcard such as 81.91.71.% but that is too generous. Their range is more like

81.10.20.1 --> 81.10.20.15

Is there a way to do this other than create 15 users, one for each individual IP address? thanks Mark

Mark Ogier
  • 313
  • 1
  • 2
  • 5

4 Answers4

23

you can use the grant assign to a netmask like:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'81.10.20.1/255.255.255.240';

It will give you access from 81.10.20.1 -> 81.10.20.14, just change the subnet part to fit the range you need.

But as said on StackOverflow: MySQL 8.0.23 onwards now support CIDR notation also.

You can see more info at mysql doc account names

William Desportes
  • 105
  • 5
altmannmarcelo
  • 1,207
  • 9
  • 26
5

For IP ranges MySQL only allows you to use % as a "anything" wild card or an underscore for a single digit.

Alternatively you could setup reverse DNS to have desired IPs resolved to something like mysql-%.mydomain.com

atxdba
  • 5,293
  • 5
  • 41
  • 62
4

I realize this is an old question.

Controlling access via an IP address might be better solved at a different layer in your stack. A server firewall for example (iptables,firewalld,etc..) could filter access to MySQL from a specific range.

If you often offer connections to externals folks; A VPN or SSH bastion would give you control over the ip your client is getting. Bonus: You get an encrypted connection. (MySQL's protocol isn't encrypted by default. It's extra effort to setup encrypted connections.)

txyoji
  • 151
  • 3
1

You can add the same user multiple times, but with different IP address. MySQL allows that.

Flash Thunder
  • 111
  • 3