Bug #35579 show grants for error
Submitted: 26 Mar 2008 17:04 Modified: 28 Mar 2008 9:12
Reporter: bocquet denis Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: grant, privileges, show grants

[26 Mar 2008 17:04] bocquet denis
Description:
it have an error when you doing a "show grants for" on user that have a restricted connection like this user@'10.24.%'

How to repeat:
mysql> grant all privileges on test.* to test@'10.24.0.0/255.255.0.0' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test;
ERROR 1141 (42000): There is no such grant defined for user 'test' on host '%'

mysql> drop user test;
ERROR 1396 (HY000): Operation DROP USER failed for 'test'@'%'
mysql>

Suggested fix:
taht is good question...
[26 Mar 2008 17:09] Paul DuBois
What is the bug here? Your SHOW GRANTS statement is incorrect because the account name does not match the one in the GRANT statement. It should be:

mysql> show grants for test@'10.24.0.0/255.255.0.0';

which works:

+-------------------------------------------------------------------------------------------------------------------------+
| Grants for test@10.24.0.0/255.255.0.0                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'10.24.0.0/255.255.0.0' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' | 
| GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'10.24.0.0/255.255.0.0'                                                      | 
+-------------------------------------------------------------------------------------------------------------------------+
[26 Mar 2008 17:31] bocquet denis
why you must added the "@xxx" ?

a thing the user is sufficient.
[26 Mar 2008 17:43] Paul DuBois
You must add the host part because that is how the statement works. You can have any number of accounts with 'test' as the username. Without the proper host part, SHOW GRANTS cannot tell which of them you want the grants for.
[27 Mar 2008 8:05] bocquet denis
ok so we can close this one