MySQL Bugs: #19857: When a user with CREATE ROUTINE priv creates a routine it results in NULL p/w

Bug #19857	When a user with CREATE ROUTINE priv creates a routine it results in NULL p/w
Submitted:	16 May 2006 16:59	Modified:	6 Jul 2006 22:31
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.21-community-nt	OS:	Windows (win32 - XP SP2)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
The first time a user who has been granted the CREATE ROUTINE priv uses that privilege to create a function or stored procedure, the password field in that user's record in the mysql.user table is set to NULL.

How to repeat:
The following code was sent to the server using the mysql client.  Simply cut and paste into the client, do not source as the delimiter statement won't work right that way.

####################
# DO as 'root' user

CREATE DATABASE priv;
use priv;
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1),(2),(3);

GRANT CREATE ROUTINE ON priv.* TO
       'create-routine'@'localhost' IDENTIFIED BY 'create-routine';

############################
# Now log in as 'create-routine' user

USE priv;
DELIMITER //
CREATE PROCEDURE test_proc()
BEGIN
   SELECT * from t1;
END//
DELIMITER ;

# log out 'create-routine' user

##########################################
Attempt to log in again as 'create-routine' user:

C:\>mysql -p -u create-routine
Enter password: **************
ERROR 1045 (28000): Access denied for user
     'create-routine'@'localhost' (using password: YES)

#####################
# do as 'root' user

SELECT user, host, password FROM mysql.user;
# user 'create-routine'@localhost no longer has a password

# clean up code
use priv;
DROP PROCEDURE test_proc;
DROP TABLE t1;
DROP DATABASE priv;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create-routine'@'localhost';
DROP USER 'create-routine'@'localhost';

Confirmed on linux, see below
----
#!/bin/bash
#-- "user.Password reset by CREATE PROCEDURE" bug
#
#--w 05/2006################################

host=localhost

#-- Configure this for superuser
pwd=super
rootcmd="mysql -h$host -uroot -p$pwd mysql -tvvv"

#-- Configure this for regular user
pwd=secret
user=pman
cpwd='*14E65567ABDB5135D0CFD9A70B3032C179A49EE7'
usercmd="mysql -h$host -u$user -p$pwd test -tvvv"

############################################################
#
function create_user() {
  cat <<EoD
    GRANT USAGE ON *.* TO $user@'$host' IDENTIFIED BY '$pwd';
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE ON test.* TO $user@'$host';
EoD
}
#---------------
function show_pwd() {
  cat <<EoD
  SELECT Host,User,Password
    FROM user
   WHERE Host='$host'
     AND User='$user'
  ;
EoD
}
#---------------
function show_user() {
  cat <<EoD
    SELECT CURRENT_USER(), DATABASE();
    SHOW GRANTS FOR CURRENT_USER();
EoD
}
#---------------
function drop_proc() {
  cat <<EoD
    DROP PROCEDURE IF EXISTS test.someproc;
EoD
}
#---------------
function create_proc() {
  cat <<EoD
    DELIMITER //
    CREATE PROCEDURE someproc() DETERMINISTIC
    BEGIN
      DECLARE a INT;
      SET a=1;
      SELECT a;
    END
    //
    DELIMITER ;
EoD
}
#---------------
function restore_pwd() {
  cat <<EoD
  UPDATE user
     SET Password='$cpwd'
   WHERE Host='$host'
     AND User='$user'
  ;
  FLUSH PRIVILEGES;
EoD
}
#---------------
function show_grants() {
  cat <<EoD
    SHOW GRANTS FOR $user@'$host';
EoD
}
############################################################
#--- Initialisation
create_user | $rootcmd
show_user   | $usercmd
show_pwd    | $rootcmd
show_grants | $rootcmd
drop_proc   | $usercmd

#--- Bug showcase
show_pwd    | $rootcmd
create_proc | $usercmd
show_pwd    | $rootcmd
show_grants | $rootcmd

#--- Show error on access attempt
cat << EoD
===============================
echo "`drop_proc`" | $usercmd
EoD
drop_proc   | $usercmd
cat << EoD
===============================
EoD

#--- Reverse bug consequences
restore_pwd | $rootcmd
show_pwd    | $rootcmd
drop_proc   | $usercmd
show_grants | $rootcmd

#--- Paranoid cleanup
drop_proc   | $rootcmd

#---------------
exit 0
############################################################

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/commits/7430

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/commits/8391

Looks good.

fixed in 5.0.24

Noted in 5.0.24 changelog.

Fix went to 5.0.25 instead.