Bug #54208 NO_AUTO_CREATE_USER bypassed with CREATE USER
Submitted: 3 Jun 2010 16:14 Modified: 7 Jun 2010 6:58
Reporter: Ronald Bradford Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.39 OS:Mac OS X
Assigned to: CPU Architecture:Any
Tags: no_auto_create_user, SQL_MODE

[3 Jun 2010 16:14] Ronald Bradford
Description:
When using SQL_MODE=NO_AUTO_CREATE_USER the expected behavior even with cryptic message as per Bug 43938  should be you cannot grant permissions with an empty password as per manual.

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_auto_create_user

Detailed description at http://ronaldbradford.com/blog/best-practices-additional-user-security-2010-06-03/

How to repeat:
mysql> GRANT SELECT ON foo.* TO 'geert12'@'localhost';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> GRANT SELECT ON *.* TO geert12@localhost IDENTIFIED BY 'foobar';
Query OK, 0 rows affected (0.00 sec)
 

You can bypass this security when creating the user first.

mysql> CREATE USER ronald12@localhost;

mysql> GRANT SELECT ON foo.* TO ronald12@localhost;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
CREATE USER should also check the SQL_MODE
[7 Jun 2010 6:58] Sveta Smirnova
Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_auto_create_user:

----<q>----
NO_AUTO_CREATE_USER

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified. 
----</q>----

This clearly says this SQL mode prevents GRANT statement from automatically creating users, but does not say anything about GRANT should not allow to add grants to existent users independently if they have password or not.