Bug #43938 Weird error when NO_AUTO_CREATE_USER sql mode is active, creating user w/o pass
Submitted: 29 Mar 2009 9:11 Modified: 29 Mar 2009 9:13
Reporter: Geert Vanderkelen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1.32 OS:Any
Assigned to: CPU Architecture:Any
Tags: grant, SQL_MODE

[29 Mar 2009 9:11] Geert Vanderkelen
Description:
When NO_AUTO_CREATE_USER sql mode is active, it's not possible to create users without passwords. This is fine, but the error is quite cryptic and does say what the error is, when creating users (by mistake!) with empty password.

(See also bug #15704)

How to repeat:
mysql> SHOW VARIABLES LIKE '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | NO_AUTO_CREATE_USER | 
+---------------+---------------------+

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 foo.* to 'geert12'@'localhost' IDENTIFIED BY 'foo';
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
Change the error message:

IF sql_mode ~= NO_AUTO_CREATE_USER and password is empty:
  print "Can not create users with empty passwords"

Error 1133 message is useful when a password is changed for a user that doesn't exists, although it might be better to say then that their is no such user, rather than saying there is no 'matching row':

mysql> SET PASSWORD FOR 'geert12'@'localhost' = PASSWORD('foo');
ERROR 1133 (42000): Can't find any matching row in the user table

So, new error message?
[29 Mar 2009 9:13] Geert Vanderkelen
Verified using MySQL 5.1.32 on Linux and Windows.

(Note that most if not all default installs on Windows will have this SQL Mode set by default. This is how I ran into the cryptic error message.)