0

I want to permit access to a mysql server over IPv6 and limit access to the particular account to our ULA subnet we are using (fdd7:03d7:6247::/48).

The manual seems to suggest that wildcards are valid in the host when using IPv4, but how do you do this with IPv6?

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.%'.

I have tried all these values which seemed like they should be valid.

GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:3d7:6247::%'  IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:3d7:6247:%'   IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:03d7:6247:%'  IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:03d7:6247::%' IDENTIFIED BY  '...' WITH GRANT OPTION;

Using the % only in the host field works, so I know I have the password, and server setup properly.

GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'%' IDENTIFIED BY  '...' WITH GRANT OPTION;

The server has DNS resolution disabled with the skip-name-resolve, and I don't really want to enable it, and mess around with setting up reverse DNS zones for the ULA address space.

To repeat, how do I grant access for a user only if they connect from the fdd7:03d7:6247::/56 network?

  • I wish someone else would step in. There are still several unanswered questions. – Rick James Feb 11 '19 at 23:39
0

::, as I understand IPv6 indicates as many chunks of all zero as are needed to fill out the full length. The question comes as to whether MySQL treats % as a single chunk and treat fdd7:03d7:6247::% as fdd7:03d7:6247:0000:0000:0000:0000:% or interpret it some other way. (Sorry, I don't know the answer.)

A second problem is that /56 splits the middle of some chunk. I don't know if that can be represented with %.

Anyway, here's my best guess:

'fdd7:03d7:6247:00%'

This confirms PacoHope's contention that leading zeros in each segment are optional:

SELECT HEX(INET6_ATON('1111:698:2222::'));
         -->           11110698222200000000000000000000
| improve this answer | |
  • Sorry, copy and pasted the wrong mask. I meant /48 which I had been using in my examples that weren't working. I tried both including the missing zeros options. If I had been using a /56 that is needed to be fdd7:03d7:6247:00%, but I had even tried that unfortunately, and didn't work. – Zoredache Mar 5 '17 at 19:54
  • I would expect 'fdd7:03d7:6247:%' to work for /48. And my answer was addressing /120 by mistake. Doh! – Rick James Mar 5 '17 at 21:30
  • did you try backticks ` instead of ticks ' ? – Phillip -Zyan K Lee- Stockmann Mar 6 '17 at 9:20
  • I found MySQL (5.7.23) sensitive to extra leading zeroes. When I used 'username'@'2a05:d011:0698:c100:%' it did not permit a connection from the local subnet, but when I used 'username'@'2a05:d011:698:c100:%' it did work. (Note the 0698 versus 698). I'm a bit of a n00b with respect to ipv6, but I thought that was a valid just like 128.040.020.010 would be a valid (if weird) way to write the ipv4 address 128.40.20.10. – Paco Hope Feb 11 '19 at 14:38
  • @PacoHope - IPv4 uses decimal numbers in each of the 4 parts. IPv6 uses hex in each part. In 0698 (hex), the 0 represents 4 bits; in 040 the leading zero has no such meaning. (Nonetheless, I am surprised that 5.7 is sensitive to the leading zero.) – Rick James Feb 11 '19 at 23:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.