MySQL User with Host set to a Range of IP Addresses

Question

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

Answer 1

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.

You can see more info at mysql doc account names

Answer 2

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

Answer 3

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.)

Answer 4

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