MySQL Bugs: #7905: Creating user with GRANT fails with password but works without

Bug #7905	Creating user with GRANT fails with password but works without
Submitted:	14 Jan 2005 13:31	Modified:	19 Mar 2005 11:45
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Linux (linux)
Assigned to:	Jani Tolonen	CPU Architecture:	Any

Description:
As 'root' i created a user with SELECT privilege on a single table and with GRANT OPTION.

The new user can grant the SELECT privilege to other users not yet existing just fine if not providing a password with IDENTIFIED BY. If a password is specified i get an "access denied" error for database "mysql"

While it might be arguable whether the user should be allowed to create new users or
whether he should only be able to grant rights to existing users i'd expect consistency.

How to repeat:
# we start from a minimal setup where only user root@localhost exists

# log in as root

# first we create user foo, foo has select access to table i in db test
# only and may give other users the same access rights for that table
GRANT SELECT ON test.i TO foo@localhost IDENTIFIED BY 'bar' WITH GRANT OPTION;

# log out and log in as foo using databas 'test'
SHOW TABLES; -- only table i is visible

SELECT * FROM i; -- works

DELETE FROM i;
# ERROR 1142 (42000): delete command denied to user 'foo'@'localhost' for table 'i'
# as expected

# now try to pass on the torch
GRANT SELECT ON test.i TO bar@localhost IDENTIFIED BY 'foo';
# ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'mysql'
# WTF?

# lets try it without password
GRANT SELECT ON test.i TO bar@localhost;
# this works

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23002

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23126

Improved error message in 4.1.

Fixed in 5.0, following the new logic:

# If you have GRANT OPTION to a database, but no privileges to mysql privilege database, you can only grant privileges to already existing users. You will not be able to change passwords for other users.

# If you have UPDATE privilege to mysql database, you can also change passwords for other users. UPDATE privilege does not give you right to create any user.

# If you have INSERT privilege to mysql database, you can create new users with or without password. INSERT privilege does not give you right to modify an existing user.

# If you have GRANT privilege to mysql database, you can modify existing users or create new ones.

Is this fix going to be back ported to 4.0 as we are currently unable to upgrade and have this very issue?