0

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

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.