MySQL Bugs: #12143: GRANT bug (or feature?)

Bug #12143	GRANT bug (or feature?)
Submitted:	25 Jul 2005 0:26	Modified:	23 Aug 2005 10:01
Reporter:	w mozart	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.x, 4.1.12	OS:	FreeBSD (freebsd 5.4, XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
MySQL v4.0.x
If you have user made via query:
GRANT ALL ON test.* TO user1@'%' WITH GRANT OPTION;
then you can't made another user with password (via GRANT ... IDENTIFIED BY 'password') under that account. You only can make it with no password set (or you get 'access denied to database <mysql>')

How to repeat:
on MySQL v4.0.x create user user1:
GRANT ALL ON test.* TO user1@'%' WITH GRANT OPTION;
FLUSH Privileges;

login as user1 and try to create user2:
GRANT ALL ON test.* TO user2@'%' IDENTIFIED BY 'password'

Suggested fix:
if i only knew...

I was able to reproduce this bug using 4.0.25 under Linux:

root-/usr/local# mysql -uuser1 -h 192.168.102.110 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.25

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

mysql> GRANT ALL ON test.* TO user2@'%' IDENTIFIED BY 'password';
ERROR 1044: Access denied for user: 'user1@%' to database 'mysql'
mysql> GRANT ALL ON test.* TO user2@'%';                         
Query OK, 0 rows affected (0.00 sec)

mysql>

Thanks for your bug report !

According to the manual (http://dev.mysql.com/doc/mysql/en/adding-users.html):

"The following examples show how to use the mysql client program to set up new users. These examples assume that privileges are set up according to the defaults described in Section 2.9.3, “Securing the Initial MySQL Accounts”. This means that to make changes, you must connect to the MySQL server as the MySQL root user, and the root account must have the INSERT privilege for the mysql database and the RELOAD administrative privilege."

So, I think the bug, if any, is that user1 is really allowed to create user2 just after:

grant all on test.* to user1@'%' with grant option;

This command does not say anything about privileges on mysql database!

By the way, the same behaviour is verified on 4.1.12-nt (the error message is different):

mysql> select version();
+------------+
| version()  |
+------------+
| 4.1.12a-nt |
+------------+
1 row in set (0.19 sec)

mysql> use test
Database changed
mysql> grant all on test.* to user1@'%' with grant option;
Query OK, 0 rows affected (0.18 sec)

mysql> exit
Bye

C:\Documents and Settings\openxs>mysql -u user1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.12a-nt

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

mysql> use test
Database changed
mysql> grant all on test.* to user2@'%' identified by 'user2';
ERROR 1132 (42000): You must have privileges to update tables in the mysql database to be able to change passwords for others
mysql> grant all on test.* to user2@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Documents and Settings\openxs>mysql -u user2 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.12a-nt

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

mysql>

As a workaround, I'd recommend to create users as root only (or using other tools, not mysql client).

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

see bug#7905