syntax error from CREATE USER with variables giving username and password

Question

Stored procedure code:

CREATE DEFINER = `root` @`localhost` PROCEDURE `P_CreateUser3` (
  IN _Username NVARCHAR(30), IN _Password NVARCHAR(32), IN _DBName VARCHAR(20))
BEGIN
  CREATE USER _Username @'localhost' IDENTIFIED BY _Password ;
  GRANT SELECT, UPDATE, DELETE, INSERT 
  ON    _DBName.* 
  TO    _Username @'localhost' 
  WITH GRANT OPTION ;
END $$

Error Code: 1064 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 '_Password ; GRANT SELECT, UPDATE, DELETE, INSERT ON `pr' at line 6

help me please.

Answer 1

Unfortunately, the use of stored procedure input parameters as passwords in a CREATE USER or GRANT statement is documented in this bug as unsupported. So you cannot actually do what you attempted.

It would be possible to PREPARE and EXECUTE a statement which is built by CONCAT() to concatenate in the new password, but this is not a secure method and is therefore not recommended. You lose all the security benefits of the stored procedure if you were to do it that way.