来自CREATE USER的语法错误，其中变量提供了用户名和密码 [英] syntax error from CREATE USER with variables giving username and password

问题描述

存储过程代码:

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 $$

错误代码:1064 您的SQL语法有误；检查与您的MySQL服务器版本相对应的手册，以在'_Password附近使用正确的语法； 授予 选择， 更新， 删除， 插入 在 第6行的"pr"

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

请帮助我.

推荐答案

不幸的是，在CREATE USER或GRANT语句中使用存储过程输入参数作为密码是

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.

可以PREPARE和EXECUTE由CONCAT()构建的语句连接到新密码中，但这不是安全方法，因此不建议使用.如果这样做的话，您将失去存储过程的所有安全优势.

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.

这篇关于来自CREATE USER的语法错误，其中变量提供了用户名和密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！