MySQL Bugs: #28406: GRANT using a stored procedure variable as PASSWORD fails

Bug #28406	GRANT using a stored procedure variable as PASSWORD fails
Submitted:	13 May 2007 20:58	Modified:	16 May 2007 17:21
Reporter:	Emery Fabrice NZEYIMANA	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.42-BK, 5.0.38-Ubuntu	OS:	Linux (Ubuntu 7.04)
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, grant, password, routine, server, stored, stored procedure, stored routine, variable

Description:
Even though I have written 5.0.38-Ubuntu as the version, I have verified this on 5.1.17-beta-community-nt 

Inside a stored routine (stored procedure or function), I can use GRANT to create new users or set permissions. 
But when I try to use a password stored in a variable, whether parameter or DECLARE-d variable, the creation of the procedure fails with the message {You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GRANT USAGE ON test.* TO 'test'@'localhost' IDENTIFIED BY varPassword;
END' at line ###}

If I replace varPassword by a string (not a variable), the creation succeeds. 

When I use INSERT/UPDATE on mysql.user table directly, I can accomplish what I was trying to do with CREATE USER ot GRANT

How to repeat:

DROP PROCEDURE IF EXISTS `test`.`spTest`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spTest`( varLogin char(16), varPassword char(64) )
BEGIN
	DECLARE varPasswordHashed CHAR(41);
	SELECT PASSWORD(varPassword) INTO varPasswordHashed;

	# Any of the following 3 lines will cause the creation to fail
	CREATE USER varLogin@'localhost' IDENTIFIED BY varPassword;
	GRANT USAGE ON test.* TO varLogin@'localhost' IDENTIFIED BY varPassword;
	GRANT USAGE ON test.* TO varLogin@'localhost' IDENTIFIED BY PASSWORD varPasswordHashed;

	## The following 3 lines won't cause any problem at create time
	CREATE USER varLogin@'localhost' IDENTIFIED BY 'AnyPassordString';
	GRANT USAGE ON test.* TO varLogin@'localhost' IDENTIFIED BY 'AnyPassordString';
	GRANT USAGE ON test.* TO varLogin@'localhost' IDENTIFIED BY PASSWORD  'AnyPassordString';  
END$$

DELIMITER ;

Suggested fix:
Let GRANT command accept variables for the Password field.

Thank you for a bug report. Verified just as described on latest 5.0.42-BK:

openxs@suse:~/dbs/5.0> bin/mysql -uroot 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 1
Server version: 5.0.42-debug Source distribution

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

mysql> delimiter //
mysql> create PROCEDURE `spTest`( varLogin char(16),
    -> varPassword char(64) )
    -> BEGIN
    -> DECLARE varPasswordHashed CHAR(41);
    -> SELECT PASSWORD(varPassword) INTO varPasswordHashed;
    ->
    -> # Any of the following 3 lines will cause the creation to fail
    -> CREATE USER varLogin@'localhost' IDENTIFIED BY varPassword;
    -> END//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CREAT
E USER varLogin@'localhost' IDENTIFIED BY varPassword;
END' at line 6
mysql> create PROCEDURE `spTest`( varLogin char(16), varPassword char(64) ) BEG
IN DECLARE varPasswordHashed CHAR(41); SELECT PASSWORD(varPassword) INTO varPas
swordHashed; CREATE USER varLogin@'localhost' IDENTIFIED BY @userVar; END//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CREAT
E USER varLogin@'localhost' IDENTIFIED BY @userVar; END' at line 1
mysql> create PROCEDURE `spTest`( varLogin char(16), varPassword char(64) ) BEG
IN DECLARE varPasswordHashed CHAR(41); SELECT PASSWORD(varPassword) INTO varPas
swordHashed; CREATE USER varLogin@'localhost' IDENTIFIED BY '123'; END//
Query OK, 0 rows affected (0.02 sec)

So, indeed, CREATE USER can be used, but NOT with parameter or user variable. If it is intended behaviour (I doubt that), it should be explicitely documented at:

http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html

I have now tried to overcome the password problem by inserting it directly into the mysql.user table. 

It doesn't work because already the GRANT command has inserted the variable NAME instead of variable VALUE. 

If varLogin contains 'nzeyimana' for example, the command 
        GRANT ALL ON test.* TO varLogin@'localhost'; 
will create user 'varLogin' instead of creating user 'nzeyimana' 

There is definitely a problem with variables with GRANT when used inside a stored procedure. 

It is incorrect that the statement varLogin@'localhost' and 'varLogin'@'localhost' are equivalent.

Thanks

Thank you for a feature request. Stored procedure variables are not allowed in many other places in the grammar.

Stefan, please assign this back when documentation is clarified with the list of places where stored procedure variables are not supported.

LIMIT clause
PREPARE stmt FROM ..
EXECUTE stmt USING ...

Any chances this will be fixed?

I´ve got the same problem with "CREATE USER ‘jane’@'localhost’ IDENTIFIED BY PASSWORD @var;" (without a stored procedure)

Syntax descriptions for GRANT, CREATE USER, etc. show a literal string where a password is required. So it *is* documented, in effect, that you cannot use a variable.

Reverting to unassigned server bug.