MySQL Bugs: #7000: GRANT to nonexistent user returns confusing message

Bug #7000	GRANT to nonexistent user returns confusing message
Submitted:	3 Dec 2004 23:56	Modified:	5 May 2006 21:14
Reporter:	Trudy Pelzer	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
A new feature added to MySQL is that a GRANT to a 
non-existent user no longer automatically adds  
that user to the mysql.user table. But the error 
message returned is very misleading, because it 
implies that the GRANT fails because the grantor 
may not create a new user. A less misleading text 
message should be returned instead. 

How to repeat:
I am signed on as root: 
 
mysql> set sql_mode='traditional'; 
mysql> create table t1 (col1 int ); 
mysql> insert into t1 values (10); 
 
mysql> grant select on t1 to xxx; 
ERROR 1211 (42000): 'root'@'localhost' is not allowed to create new users 
-- This implies that GRANT fails because root can't 
create new users. But since that's not what I think 
I'm trying to do, I'm confused. The message doesn't 
tell me what the real problem is. 
 
mysql> create user xxx; 
Query OK, 0 rows affected (0.00 sec) 
-- This shows me that root can, of course, create a new 
user. So I'm still confused. 
 
The GRANT should return a message that says: 
Can't GRANT to non-existing user 'xxx'

I get now a different server response
   grant <something> to <non existent user>; 
   ERROR 42000: Can't find any matching row in the user table
It is not so misleading like the old response. But some users
might think we suggest that they insert an appropriate record 
into the table mysql.user.
I assume the perfect user reaction is to create the user needed
via "CREATE USER ....". 
Therefore "Can't GRANT to non-existing user 'xxx'" is the best
server response.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1989.2.1, 2005-06-23

So what is the status of this "bug"?
Does GRANT still create users or not?
Or is this behavior now controlled by some sql mode?

Yes, it's a particular SQL mode, NO_AUTO_CREATE_USER. The
initial example uses TRADITIONAL, but that's not the mode itself
that controls the behavior, it's a composite mode that includes
NO_AUTO_CREATE_USER.

But note that this mode does not actually forbid all account creation
with GRANT.  It forbids it only if no password is given, to prevent
inadvertent creation of insecure accounts:

mysql> set sql_mode = 'no_auto_create_user';
Query OK, 0 rows affected (0.00 sec)

mysql> grant usage on *.* to 'no-such-user'@'localhost';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant usage on *.* to 'no-such-user'@'localhost' identified by 'x';
Query OK, 0 rows affected (0.00 sec)

So the real error in this case is, I suppose, that no password was given.

See: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

This includes setting the password to an empty string explicitly. This entire behaviour needs to be (more prominently?) documented.

I do not find that it includes specifying an empty password:

mysql> grant usage on *.* to 'no-such-user'@'localhost' identified by '';
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant usage on *.* to 'no-such-user'@'localhost' identified by 'x';
Query OK, 0 rows affected (0.00 sec)

Sorry .. that is what I meant. That setting the password to an empty password explicitly makes things behave like you did not set a password at all, which means this sql mode is triggered.

Current 5.0.22 reports the following:

mysql> grant select on t1 to xxx;
ERROR 1133 (42000): Can't find any matching row in the user table

Do we need a different error message or is this only a documentation issue?

Current error message in 5.0 and 5.1 isn't as misleading as original bug report.

I can still reproduce in 5.0.37 community edition!

mysql> create database sps740 ;
Query OK, 1 row affected (0.00 sec)
mysql> use sps740 ;
Database changed
mysql> grant all on sps740.* to 'spsadmin740'@'localhost' ;
ERROR 1133 (42000): Can't find any matching row in the user table
mysql> grant all on sps740.* to 'spsadmin740'@'localhost' identified by '' ;
ERROR 1133 (42000): Can't find any matching row in the user table

What really puzzled me is that it seems to be platform related! (the above happened on Windows). I tried it on a Debian sporting a newer version:

:~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 287
Server version: 5.0.38-Debian_1-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON sps74.* TO 'spsadmin740'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>

Indeed I was able to repeat on Windows 5.0.37 but not anymore with
Windows server built from source some days ago:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database sps740 ;
Query OK, 1 row affected (0.05 sec)

mysql> use sps740 ;
Database changed
mysql> grant all on sps740.* to 'spsadmin740'@'localhost' ;
ERROR 1133 (42000): Can't find any matching row in the user table
mysql>

Microsoft Windows [versão 6.0.6000]
Copyright (c) 2006 Microsoft Corporation. Todos os direitos reservados.

c:\>cd build

c:\build>cd 5.0

c:\build\5.0>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.40 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database sps740 ;
Query OK, 1 row affected (0.01 sec)

mysql> use sps740 ;
Database changed
mysql> grant all on sps740.* to 'spsadmin740'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)

mysql>

Thank you Miguel! So what does this mean: is this a regression in this particular version? What should be then the default behavior (i.e. what should we expect for future versions?)

This is even more confusing!

I got "ERROR 1133 (42000) at line 3: Can't find any matching row in the user table" when using Grant with a password when the user was *already* in the user table!

I took me half an hour to figure out what the problem was!

In this case the message is the opposite of what it should be.