MySQL Bugs: #54208: NO_AUTO_CREATE_USER bypassed with CREATE USER

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:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	no_auto_create_user, SQL_MODE

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

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.