Specifying MySQL user hosts with IPv6 addresses

Question

Inspired after making the move to IPv6 on my MySQL server, and intrigued by the lack of documentation regarding 'user'@'[IPv6]' nomenclature I proceeded to discover how to limit my users based on their IPv6 address.

My allocation is a /56 and so I wanted to limit my DB users to this prefix length. My test host was 2001:DB8:111:6601::a01:120

The following didn't work at all:

2001:DB8:111:6600::
2001:DB8:111:6600::%
2001:DB8:111:6601::
2001:DB8:111:66%::
2001:DB8:0111:6601::%
2001:DB8:111:6600::/56
2001:DB8:111:6601::a01:120/56
2001:DB8:111:.&checktime(6601,0,0,':'):a01:120/56
2001:DB8:111:6601::a01:120/128
2001:DB8:111:6601::/64
2001:DB8:111:6601::%/128
2001:DB8:111:6601::%/1
2001:DB8:111:6601::%/64
2001:DB8:111:6601::a01:0120

The following worked

2001:DB8:111:6601::% (too limiting as it's a /64, not /56)
2001:DB8:111:6601::a01:120 (too limiting as it's a host)
2001:DB8:111:66%::% (but may match 066x in the 7th and 8th bytes)
2001:DB8:111:66__::% (this appears to be the best fit)

Some notes:

• don't use leading zeros in fields
• don't bother with /prefixlen

Answer 1

(Sorry, I don't have definite answers, but these seem likely:)

:: is shorthand for any number of 0000 segments so as to end up with 8 groups. So, ::% is ill-defined.

% is the wildcard available in MySQL, not _.

Perhaps % is only applicable for a 16-bit chunk, not part of such.

With ipv4, a mask is given using /. Example: 198.51.100.0/255.255.255.0: Any host on the 198.51.100 class C network. I suspect that ipv6 would be similar. -- https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/account-names.html

So, I would guess

2001:DB8:111:6600::/FFFF:FFFF:FFFF:FF00::

• Scott: Underscore _ is definitely a wildcard that can be used in hostnames. From the manual: The % and _ wildcard characters are permitted in host name or IP address values. These have the same meaning as for pattern-matching operations performed with the LIKE operator