I want to give MySQL users the ability to create new databases, and have ALL privileges on these, but not on the other database. I've tried to create a STORED PROCEDURE for this, but it's not working out. This is my procedure (NOTE: UPDATED CODE BELOW):
DELIMITER // CREATE DEFINER = 'root'@'localhost' PROCEDURE createdb(IN newdb VARCHAR(255)) BEGIN CREATE DATABASE newdb; SET @newdbuser = USER(); GRANT ALL ON newdb.* TO '@newdbuser'; FLUSH PRIVILEGES; END // DELIMITER ;
I call the procedure using:
But what happens is that the database 'newdb' (variable name) is created. Trying to access that database as the mortal user who called the procedure doesn't work either: access denied.
Question: What is wrong with my PROCEDURE? Or am I attacking the problem from the wrong angle?
UPDATE Based on longneck's answer, I've revised the procedure to:
DELIMITER // CREATE DEFINER = 'root'@'localhost' PROCEDURE createdb(IN newdb VARCHAR(255)) BEGIN PREPARE stmt1 FROM "CREATE DATABASE ?"; PREPARE stmt2 FROM "GRANT ALL ON newdb.* TO '?'"; EXECUTE stmt1 USING @newdb; SET @newdbuser = USER(); EXECUTE stmt2 USING @newdbuser; FLUSH PRIVILEGES; END // DELIMITER ;
However, when calling this procedure using "CALL createdb('testing');" I get:
ERROR 1064 (42000): 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 '?' at line 1
What am I missing?