MySQL Bugs: #62089: Password is dropped when granting privileges to a user at another host

Bug #62089	Password is dropped when granting privileges to a user at another host
Submitted:	5 Aug 2011 7:32	Modified:	5 Aug 2011 8:09
Reporter:	Anne Schuth	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.45-log	OS:	Linux (2.6.18-92.1.22.el5)
Assigned to:		CPU Architecture:	Any
Tags:	grant, password, privileges, Security, user

Description:
When creating a user with a password, and then granting privileges on that user but with a different host, the password is dropped; see below. It seems to me that that should not happen, or at least a warning should be issued.

How to repeat:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT ON 'mydabase'.* to 'myuser'@'localhost';
GRANT SELECT ON 'mydabase'.* to 'myuser'@'another.host';

SELECT Host, User, Password FROM mysql.user WHERE User = 'myuser';
+------------------+------------+-------------------------------------------+
| Host             | User       | Password                          |
+------------------+------------+-------------------------------------------+
| localhost      | myuser   | *A234MMN4415MNMF8B   | 
| another.host | myuser   |                                          | 
+------------------+------------+-------------------------------------------+

Suggested fix:
Issue a warning or keep the password.

Please, check if the same problem still happens with a newer version, 5.0.92. Also it would be nice to check password value before GRANT, like this:

mysql> create user myuser@'%' identified by 'pass';
Query OK, 0 rows affected (0.13 sec)

mysql> select host, user, password  from mysql.user where user = 'myuser';
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| localhost | myuser | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| %         | myuser | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+-----------+--------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant select on *.* to myuser@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> select host, user, password  from mysql.user where user = 'myuser';
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| localhost | myuser | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| %         | myuser | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+-----------+--------+-------------------------------------------+
2 rows in set (0.00 sec)

As you can see, there is no problem with recent versions.

Im sorry, I do not have time now to install another version, but this is what I observe:

mysql> create user myuser@'localhost' identified by 'pass';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, password  from mysql.user where user = 'myuser';
+-----------+--------+------------------+
| host      | user   | password         |
+-----------+--------+------------------+
| localhost | myuser | 29bad1457ee5e49e | 
+-----------+--------+------------------+
1 row in set (0.00 sec)

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

mysql> select host, user, password  from mysql.user where user = 'myuser';
+-----------+--------+------------------+
| host      | user   | password         |
+-----------+--------+------------------+
| localhost | myuser | 29bad1457ee5e49e | 
+-----------+--------+------------------+
1 row in set (0.00 sec)

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

mysql> select host, user, password  from mysql.user where user = 'myuser';
+-------------+--------+------------------+
| host        | user   | password         |
+-------------+--------+------------------+
| localhost   | myuser | 29bad1457ee5e49e | 
| anotherhost | myuser |                  | 
+-------------+--------+------------------+
2 rows in set (0.00 sec)

I did not expect that last password to be empty..

Looks like your GRANT statement just creates new account, with same user name but other host, as previously this account had not exist. Our manual, http://dev.mysql.com/doc/refman/5.0/en/grant.html, explains this:

"If the NO_AUTO_CREATE_USER SQL mode is not enabled and the account named in a GRANT statement does not exist in the mysql.user table, GRANT creates it. If you specify no IDENTIFIED BY clause or provide an empty password, the user has no password. This is very insecure."

So, a bit surprising and unsecure, but documented and, thus, not a bug formally.

Thank you Valeriy for being so fast!

Ok, not a bug then but definitely a security issue. NO_AUTO_CREATE_USER should probably be enabled by default I would say.