MySQL Bugs: #2953: New GRANT for an existing user deletes the user's password

Bug #2953	New GRANT for an existing user deletes the user's password
Submitted:	25 Feb 2004 3:16	Modified:	1 Mar 2004 11:22
Reporter:	David Harper	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.1-alpha-standard (Official MySQL-sta	OS:	Linux (Linux/i686 and DEC OSF/Alpha)
Assigned to:		CPU Architecture:	Any

Description:
When an existing user who has a password is granted additional privileges
without the "IDENTIFIED BY" clause, the user's existing password is lost.

How to repeat:
grant usage on *.* to snoopy@"%" identified by "CharlieBrown";
show grants for snoopy;
# User snoopy has a password
create database woodstock;
grant select,insert,update,delete on woodstock.* to snoopy@"%";
show grants for snoopy;
# User snoopy no longer has a password

Suggested fix:
All grant commands must specify the password explicitly, so the example
above must be executed as

grant usage on *.* to snoopy@"%" identified by "CharlieBrown";
show grants for snoopy;
# User snoopy has a password
create database woodstock;
grant select,insert,update,delete on woodstock.* to snoopy@"%"
  identified by "CharlieBrown";
# User snoopy still has a password
show grants for snoopy;

But this is contrary to the documentation and to Paul Dubois' book
(second edition, page 604). It's also a pain in the ***.

The password is not deleted!
The bug is in SHOW GRANTS only.

Corrected How-To-Repeat:

grant usage on *.* to snoopy@"%" identified by "CharlieBrown";
 
show grants for snoopy;

| GRANT USAGE ON *.* TO 'snoopy'@'%' IDENTIFIED BY PASSWORD '*BE744EFC752043678E 3B4F2C35A13DC778D63A8F' |
# User snoopy has a password
 
create database woodstock;
 
grant select,insert,update,delete on woodstock.* to snoopy@"%";
 
show grants for snoopy;
+-----------------------------------------------------------------------+
| Grants for snoopy@%                                                   |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'snoopy'@'%'                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `woodstock`.* TO 'snoopy'@'%' |
+-----------------------------------------------------------------------+

# User snoopy appears to be without password while it is not the case
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show grants for snoopy;

| GRANT USAGE ON *.* TO 'snoopy'@'%' IDENTIFIED BY PASSWORD '*BE744EFC752043678E 3B4F2C35A13DC778D63A8F' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `woodstock`.* TO 'snoopy'@'%'

I just did this on my 4.1.1-alpha version of MySQL:

hoyle[adh]44: mysql -h hoyle -P 24651 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.1.1-alpha-standard

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

mysql> status;
--------------
mysql  Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)

Connection id:          11
Current database:
Current user:           root@hoyle.internal.sanger.ac.uk
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.1-alpha-standard
Protocol version:       10
Connection:             hoyle via TCP/IP
Client characterset:    latin1_swedish_ci
Server characterset:    latin1_swedish_ci
TCP port:               24651
Uptime:                 1 day 22 hours 38 min 2 sec

Threads: 1  Questions: 201  Slow queries: 1  Opens: 84  Flush tables: 1  Open ta
bles: 40  Queries per second avg: 0.001
--------------

mysql> grant usage on *.* to snoopy@"%" identified by "CharlieBrown";
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for snoopy;
+------------------------------------------------------------------------------+
| Grants for snoopy@%                                                          |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'snoopy'@'%' IDENTIFIED BY PASSWORD '56d7ae524ff2faf1' |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database woodstock;
Query OK, 1 row affected (0.00 sec)

mysql> grant select,insert,update,delete on woodstock.* to snoopy@"%";
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for snoopy;
+-----------------------------------------------------------------------+
| Grants for snoopy@%                                                   |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'snoopy'@'%'                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `woodstock`.* TO 'snoopy'@'%' |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> quit
Bye

hoyle[adh]45: mysql -h hoyle -P 24651 -u snoopy 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.1.1-alpha-standard

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

mysql>

So user 'snoopy' can now gain access to the database *without* a password, as
the second "show grants for snoopy" indicates.

Thanks to your test I was able to repeat it.

When I tested it initally I wasn't able to login without password, but now I can.
Again after FLUSH PRIVILEGES password reappears and user can't login without it anymore.

Alexander,

Many thanks for your prompt response to my report.

I understand that executing FLUSH PRIVILEGES immediately after
the GRANT will fix the problem, but according to the manual,
FLUSH PRIVILEGES should only be necessary if I have altered
the privilege tables directly using INSERT.

So this is still a bug in 4.1.1 :-)

Regards

David Harper
Cambridge

A patch for this bug has been sent for reviewal and confirmation.

fix is pushed.