Description:
According to https://dev.mysql.com/doc/refman/8.4/en/connection-access.html
, "the server uses sorting rules that order rows with the most-specific Host values first."
However, the later description in that section contradicts this rule.
For example, it treats "accounts that have the host part given as an IP address with a subnet mask" as less specific than "accounts that have the host part given as an IP address using CIDR notation".
But a subnet mask and a CIDR are just two equivalent ways of representing the same network range. They should not be treated differently just because a different notation is used.
I would expect mysqld to:
- convert all subnet masks and CIDRs into a unified internal notation,
- sort by the actual specificity (where the most specific means the most precise or smallest matching IP/network), regardless of whether the host is defined as an IP address, CIDR, or subnet mask.
That would mean:
- 1.1.1.1, 1.1.1.1/32, and 1.1.1.1/255.255.255.255 are equivalent
- 1.1.1.0/24 and 1.1.1.0/255.255.255.0 are equivalent
- 1.1.1.0/255.255.255.0 is more specific than 1.1.0.0/16 because it represents a smaller network (according to the current documentation, /16 is considered more specific than 255.255.255.0 simply because CIDR notation is preferred over subnet mask notation, which does not make logical sense)
- a hostname that resolves to an IP address should be treated the same as a single IP address for sorting purposes
How to repeat:
grant different variations and compare. I'm writing this based on mentioned documentation and I assume that docs reflect real mysqld behaviour.
Suggested fix:
Like in description above.
In almost all networking and security systems, such as firewalls, routers, and access control lists, the rule that matches the most specific address or network always takes priority.
For example, if one rule allows 1.1.1.0/24 and another denies 1.1.1.5, the system will apply the more specific rule (1.1.1.5) because it exactly matches a smaller portion of the network. This is standard and well-understood behavior across virtually all network software.
MySQL should follow the same logic — the access rule that matches the most specific host or network should take precedence, regardless of whether it’s written using CIDR or subnet mask notation. Diverging from this common and predictable rule makes access control confusing, error-prone, and inconsistent with how administrators expect network matching to work.
© 2025, Oracle Corporation and/or its affiliates