Mysql – Creating a stored Proc that creates new user and password

MySQLstored-procedures

I'm new to MySQL. I was trying to create a procedure with two parameters:

  1. user_name
  2. password

…that will be generated and granted full rights:

DELIMITER $$

DROP PROCEDURE IF EXISTS `gen_user`$$

CREATE PROCEDURE `gen_user`(`user_name` VARCHAR(10), `keyword` VARCHAR(255))
BEGIN
   CREATE USER `user_name`@'localhost' IDENTIFIED BY `keyword`
   GRANT ALL PRIVILEGES ON * . * TO `user_name`@'localhost'
   FLUSH PRIVILEGES
END$$

DELIMITER ;

The error I get is:

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 '`keyword` GRANT ALL PRIVILEGES ON * . * TO `user_name`@'localhost' FLUSH P' at line 3

Best Answer

One mistake for sure: Each of the 3 statements needs a ; at the end.

Possibly the syntax calls for literals, not variables for user_name and password. So use CONCAT(), prepare, execute, and deallocate prepare to build and execute the CREATE and GRANT statements.

Related Question