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

[13 May 2007 20:58] Emery Fabrice NZEYIMANA
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.
[14 May 2007 8:22] Valeriy Kravchuk
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
[14 May 2007 13:03] Emery Fabrice NZEYIMANA
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
[16 May 2007 17:20] Konstantin Osipov
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 ...
[2 Feb 2012 9:03] Larsen Doe
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)
[7 Mar 2016 17:33] Paul DuBois
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.