MySQL Bugs: #25999: Modifying mysql.db host to be different than mysql.user host still allows access

Bug #25999	Modifying mysql.db host to be different than mysql.user host still allows access
Submitted:	1 Feb 2007 3:28	Modified:	1 Feb 2007 9:23
Reporter:	Matt Juszczak	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	FreeBSD (FreeBSD)
Assigned to:		CPU Architecture:	Any

Description:
Show grants returns only those permissions that have the same (user,host) combination between the two tabels.  When the user,host pair are different between mysql.user and mysql.db, permissions are still granted to the user even though show grants; returns differently.

How to repeat:
(cleaned up)

mysql> GRANT ALL ON test.* TO test@localhost IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.db SET host='%' WHERE user='test' AND host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test@localhost;
| Grants for test@localhost |
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY 
  PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |

---snip---

saturn$ mysql -u test -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5593 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table ICanCreateTables (ID int(11) auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| ICanCreateTables |
+------------------+
1 row in set (0.00 sec)

mysql> show grants;
| Grants for test@localhost |
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
1 row in set (0.00 sec)

mysql>

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

This is a documented feature. Please read carefully about Connection Verification and Request Verification process at http://dev.mysql.com/doc/refman/5.0/en/connection-access.html and http://dev.mysql.com/doc/refman/5.0/en/request-access.html