Bug #14624 adding a function to the test database blanks the user's password
Submitted: 4 Nov 2005 6:40 Modified: 21 Jul 2006 23:12
Reporter: Bob Rintel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.16-BK, 5.0.15 OS:Linux (Linux (Fedora 1))
Assigned to: Chad MILLER CPU Architecture:Any

[4 Nov 2005 6:40] Bob Rintel
Description:
When user123@localhost creates a function in the test database,
the user's password gets blanked out.
The problem doesn't occur for root.
And the problem doesn't occur in a database for which the user has explicit access permission.

How to repeat:
~/tmp$ mysql -u root --password=pass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42 to server version: 5.0.15-max

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

mysql> select Host, User, Password from mysql.user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| localhost | user123 | *D821809F681A40A6E379B50D0463EFAE20BDD122 |
| localhost |         |                                           |
+-----------+---------+-------------------------------------------+
3 rows in set (0.01 sec)

mysql> quit
Bye
~/tmp$ mysql -u user123 --password=pw
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 5.0.15-max

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

mysql> select current_user();
+-------------------+
| current_user()    |
+-------------------+
| user123@localhost |
+-------------------+
1 row in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create function f (x int) returns int return x+1;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
~/tmp$ mysql -u root --password=pass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44 to server version: 5.0.15-max

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

mysql> select Host, User, Password from mysql.user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
| localhost | user123 |                                           |
| localhost |         |                                           |
+-----------+---------+-------------------------------------------+
3 rows in set (0.01 sec)

mysql> quit
Bye
~/tmp$ mysql -u user123 --password=pw
ERROR 1045 (28000): Access denied for user 'user123'@'localhost' (using password: YES)
~/tmp$ mysql -u user123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46 to server version: 5.0.15-max

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

mysql> select current_user();
+-------------------+
| current_user()    |
+-------------------+
| user123@localhost |
+-------------------+
1 row in set (0.01 sec)

mysql> quit
Bye
~/tmp$
[4 Nov 2005 12:38] Valeriy Kravchuk
Thank you for a bug report. 

Verified just as described on today's 5.0.16-BK (ChangeSet@1.1957.1.18, 2005-11-03 20:29:21+02:00, jani@ua141d10.elisa.omakaista.fi) on Linux Fedora Core 1:

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16

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

mysql> select host, user, password from mysql.user;
+-----------+----------+-------------------------------------------+
| host      | user     | password                                  |
+-----------+----------+-------------------------------------------+
| localhost | root     |                                           |
| Fedora    | root     |                                           |
| Fedora    |          |                                           |
| localhost |          |                                           |
| %         | unclesam |                                           |
| %         | user1    | *34D3B87A652E7F0D1D371C3DBF28E291705468C4 |
| %         | user2    |                                           |
| localhost | user2    |                                           |
| localhost | testuser | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
| localhost | dummy    |                                           |
| %         | dummy2   |                                           |
| localhost | dummy3   |                                           |
+-----------+----------+-------------------------------------------+
12 rows in set (0,16 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -utestuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.16

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

mysql> select current_user();
+--------------------+
| current_user()     |
+--------------------+
| testuser@localhost |
+--------------------+
1 row in set (0,01 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create function ff (x int) returns int return x+1;
Query OK, 0 rows affected (0,04 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16

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

mysql> select Host, User, Password from mysql.user;
+-----------+----------+-------------------------------------------+
| Host      | User     | Password                                  |
+-----------+----------+-------------------------------------------+
| localhost | root     |                                           |
| Fedora    | root     |                                           |
| Fedora    |          |                                           |
| localhost |          |                                           |
| %         | unclesam |                                           |
| %         | user1    | *34D3B87A652E7F0D1D371C3DBF28E291705468C4 |
| %         | user2    |                                           |
| localhost | user2    |                                           |
| localhost | testuser |                                           |
| localhost | dummy    |                                           |
| %         | dummy2   |                                           |
| localhost | dummy3   |                                           |
+-----------+----------+-------------------------------------------+
12 rows in set (0,01 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -utestuser -p
Enter password:
ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES)
[openxs@Fedora 5.0]$ bin/mysql -utestuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.16

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

mysql> select current_user();
+--------------------+
| current_user()     |
+--------------------+
| testuser@localhost |
+--------------------+
1 row in set (0,00 sec)
[2 Feb 2006 20:46] Wesley Craft
Same problem verified en total on SunOS 5.8 running MySQL v5.0.18. The problem applies to both the CREATE FUNCTION and CREATE PROCEDURE actions.
[2 Jul 2006 11:29] Chad MILLER
This is also Bug#19857, which, while reported later, is already farther along.  I'll close this report as a duplicate when #19857 is finished.
[21 Jul 2006 23:12] Chad MILLER
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html
[21 Jul 2006 23:13] Chad MILLER
It seems to have been fixed with another bug report.

-------------------------------------------------------
*** r/func_misc.result  2006-05-06 18:45:17.000000000 +0300
--- r/func_misc.reject  2006-07-22 02:09:26.000000000 +0300
***************
*** 130,132 ****
--- 130,156 ----
  drop table t2;
  drop table t1;
  set global query_cache_size=default;
+ create user bug14624@;
+ set password for bug14624@ = password('test');
+ show grants;
+ Grants for root@localhost
+ GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
+ grant create routine on test.* to 'bug14624'@'localhost';
+ select host, user, password from mysql.user where user='bug14624';
+ host  user    password
+       bug14624        *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
+ localhost     bug14624
+ flush privileges;
+ select current_user();
+ current_user()
+ bug14624@localhost
+ create function plusone(x int) returns int return x+1;
+ select current_user();
+ current_user()
+ bug14624@localhost
+ select host, user, password from mysql.user where user='bug14624';
+ host  user    password
+       bug14624        *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
+ localhost     bug14624
+ drop user bug14624@;
+ drop function plusone;
-------------------------------------------------------