MySQL Bugs: #3309: Netmask matching for 'Host' doesn't work.

Bug #3309	Netmask matching for 'Host' doesn't work.
Submitted:	26 Mar 2004 17:59	Modified:	7 Mar 2005 19:10
Reporter:	Joe Rhett	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23, 4.0 and up	OS:	Any (any)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
GRANT doesn't appear to be modifying the tables appropriately to allow access.

Do a grant to allow a given user within a given IP range:

mysql> grant select,insert,update,delete on Test.Testy to myself@'205.217.156.0/255.255.252.0' identified by 'removed';
Query OK, 0 rows affected (0.00 sec)

This appears to work -- manual verification of the tables:
mysql> select * from tables_priv;
+-----------------------------+-------+----------+---------------+----------------+----------------+----------------------------------------------------------------+-------------+
| Host                        | Db    | User     | Table_name    | Grantor        | Timestamp      | Table_priv                                                     | Column_priv |
+-----------------------------+-------+----------+---------------+----------------+----------------+----------------------------------------------------------------+-------------+
| localhost                   | none  | root     | none          | root@localhost | 20031202104748 | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter |             |
| 205.217.156.0/255.255.252.0 | Test  | myself   | Testy         | root@localhost | 20040326173559 | Select,Insert,Update,Delete                                    |             |
+-----------------------------+-------+----------+---------------+----------------+----------------+----------------------------------------------------------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from user;
+-----------------------------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host                        | User     | password         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+-----------------------------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| localhost                   | root     | <removed>        | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                |          |            |             |              |             0 |           0 |               0 |
| localhost                   |          |                  | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |
| 205.217.156.0/255.255.252.0 | myself    | <removed>       | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |
+-----------------------------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
3 rows in set (0.01 sec)

But connecting from that host fails.  However, it does echo back the appropriate rule.
$ mysql --host=testdb.isite.net --user=myself --password=removed Testy
ERROR 1044: Access denied for user: 'myself@205.217.156.0/255.255.252.0' to database 'Testy'

If the IP range doesn't match (deliberate mistype) then it echoes back the IP address of the specific host that I'm coming from.
ERROR 1045: Access denied for user: 'myself@205.217.158.41' (Using password: YES)

If I give the wrong password, then it gives me the wrong password error:
$ mysql --host=testdb.isite.net --user=myself --password=mistyped Testy
ERROR 1044: Access denied for user: 'myself@205.217.156.0/255.255.252.0' to database 'Testy'
ERROR 1045: Access denied for user: 'myself@205.217.158.41' (Using password: YES)

So by analysis of the error messages, the password is correct and the ip address is correct, but it's still not letting me in.  Using a longer netmask (158.0/255.0) has the same problem.

Why is this a bug and not a configuration error?  Because if I do the grant with the specific IP address then it works just fine:
mysql> grant select,insert,update,delete on Test.Testy to myself@205.217.158.41 identified by 'broken';
query OK, 0 rows affected (0.00 sec)

So in short, there is a problem with the ip address/netmask matching.

How to repeat:
Do a grant to allow a given user within a given IP range:

mysql> grant select,insert,update,delete on Test.Testy to myself@'205.217.156.0/255.255.252.0' identified by 'removed';
Query OK, 0 rows affected (0.00 sec)

Try connecting from a host in that network.
$ mysql --host=testdb.isite.net --user=myself --password=removed Testy

Confirm that everything else is right by specifying the exact IP:

mysql> grant select,insert,update,delete on Test.Testy to myself@205.217.158.41 identified by 'removed';

If that works fine, then you are seeing the problem I am seeing.

Suggested fix:
Determine the problem with netmask matching.

FYI, using a % wildcard works perfectly fine:

mysql> grant select,insert,update,delete on Test.Testy to mysql@'205.217.158.%' identified by 'removed';
Query OK, 0 rows affected (0.00 sec)

$ mysql --host=testdb.isite.net --user=myself --password=removed EMail
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 310 to server version: 4.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

user@hostname syntax in MySQL was never intended to support network masks and If you check with manual you will see nothing there implies support of any kind of netmasks or hostmasks.

The only (very limited) way to specify range of names or IP addresses is to use wildcards as you already know.
 

'205.217.156.0/255.255.252.0' is treated as a String and such String makes no sense as an IP address nor as a fully qualified domain name.

As we do mention support for this:

http://www.mysql.com/doc/en/Connection_access.html

we do need to re-test this.

Sorry Joe,
My reply is wrong. Manual clearly states this is supported.
As Dean says we need to test it more and see if it is bug in source or documenation problem.

Thanks for pointing this to us!

Um, yeah.  I got the idea from your documentation.

Actually, I originally got the idea from the 'MySQL' red book, which claimed that CIDR style netmasks (/22) were possible.  But when that didn't work and I couldn't find such a claim in the documentation, I switched to what your documentation did mention -- the spelled out netmasks.

mysql> grant select,insert,update,delete on Test.Testy ....

This grants access to database Test, table Testy.

ERROR 1044: Access denied for user: 'myself@205.217.156.0/255.255.252.0' to
database 'Testy'

This shows that you have been resolved to the correct login, and do not have access to database Testy.

Noting that you granted access on database Test, not Testy, I think this is not a bug.

Please update us if you correct this and still encounter difficulties.

Just an additional note. The red book did indeed discuss the
use of CIDR netmasks, because that was the original syntax.
After the book was published, the syntax as changed.

See the entry for page 457 here:

http://www.kitebird.com/cgi-perl/errata.pl?book=mysql-00-4

What you saw was a bit of bad editing on my part.  The real database and table names were changed to protect the not-so-innocent.

Did you actually try to replicate the bug, or did you just look at my syntax?  Please try and replicate the bug.

You'll also see another bit of bad editing -- user 'mysql' instead of 'myself'.  Just another mistype.  It's not in the original problem.

I'd be happy to send you the original sessions in any forum which isn't public.

I reopened this bug report, here is some more info:

C:\MySQL\MySQL4_1_9\bin>mysql -h195.225.130.68 -uvita -pvitapassword vita
ERROR 1044 (42000): Access denied for user 'vita'@'195.225.130.0/255.255.255.0'
to database 'vita'

C:\MySQL\MySQL4_1_9\bin>mysql -h195.225.130.68 -uvita -pvitapassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.9-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select current_user();
+----------------------------------+
| current_user()                   |
+----------------------------------+
| vita@195.225.130.0/255.255.255.0 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'vita'@'195.225.130.0/255.255.255.0';
+-------------------------------------------------------------------------------
------------------------------------------------+
| Grants for vita@195.225.130.0/255.255.255.0
                                                |
+-------------------------------------------------------------------------------
------------------------------------------------+
| GRANT USAGE ON *.* TO 'vita'@'195.225.130.0/255.255.255.0' IDENTIFIED BY PASSW
ORD '*272422A9CF8DBCAAEE6F224C0E9EBA809D43FFC3' |
| GRANT SELECT ON `vita`.`t1` TO 'vita'@'195.225.130.0/255.255.255.0'
                                                |
+-------------------------------------------------------------------------------
------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from vita.t1;
ERROR 1142 (42000): SELECT command denied to user 'vita'@'195.225.130.68' for ta
ble 't1'

Pushed, will be in 4.1.11 and 5.0.3.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22640

Noted in 4.1.11, 5.0.3 changelogs.