Bug #64313 GRANT effected 0 rows a bug or by design?
Submitted: 14 Feb 2012 1:54 Modified: 7 Mar 2012 3:01
Reporter: John Bilicki Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Windows (XP 64 Bit)
Assigned to: Paul DuBois CPU Architecture:Any

[14 Feb 2012 1:54] John Bilicki
After granting limited permissions to a user for a database MySQL responds with, "0 rows affected" which is confusing and I was forced to verify that the permissions were taken in to account by testing a query to the specified database with that user.

I'm not familiar with policies and how this may or may not effect security though I can't seem to find anything official to suggest it's a bug or by design.

How to repeat:
I executed the query...

GRANT SELECT, INSERT ON jabcre5_examplecom.* TO 'test1'@'localhost';

...to grant limited permissions to a database for a user.

MySQL responds with...

"Query OK, 0 rows affected (0.00 sec)"

Suggested fix:
If it is by design please update the documentation accordingly.
[14 Feb 2012 5:05] Valeriy Kravchuk
Yes, this should be mentioned at http://dev.mysql.com/doc/refman/5.5/en/grant.html and similar pages for other versions.

You can check GRANT results with SHOW GRANTS, like this:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant select on *.* to u2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for u2@localhost;
| Grants for u2@localhost                 |
| GRANT SELECT ON *.* TO 'u2'@'localhost' |
1 row in set (0.00 sec)
[7 Mar 2012 3:01] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

"When successfully executed from the mysql program, GRANT responds
with "Query OK, 0 rows affected". To determine what privileges result
from the operation, use SHOW GRANTS."