syntax error from CREATE USER with variables giving username and password
mysql
Solution
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.
Problem
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.