How to give MySQL users CREATE DB rights?

Question

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:

CALL createdb('newtestdb');

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?

Answer 1

this isn't a rights problem. mysql doesn't support variables as object names (where objects are tables, databases, columns, etc.). you will have to use prepared statements and build your command as a string. see http://forums.mysql.com/read.php?98,152150,152161#msg-152161

for example, your PREPARE statements need to look like:

PREPARE stmt1 FROM concat("CREATE DATABASE ", @dbname);
PREPARE stmt2 FROM concat("GRANT ALL ON newdb.* TO ", @username);

not that this is NOT injection safe. make sure your have sanitized your "parameters" (database name, user name) before using these statements.

also, you're granting your privilege wrong. TO '@newdbuser' assigns the privilege to users connecting from a computer called newdbuser. you need to put the @ sign after the username.

Answer 2

There is no such privilege as CREATE DATABASE. Only the CREATE privilege grants the creation of database, tables, and indexes

• Under MySQL 5.5
  • CREATE (<-- This is what you want)
  • CREATE TEMPORARY TABLE
  • CREATE VIEW
  • CREATE ROUTINE
  • CREATE TABLESPACE
  • CREATE USER
• Under MySQL 5.1
  • CREATE (<-- This is what you want)
  • CREATE TEMPORARY TABLE
  • CREATE VIEW
  • CREATE ROUTINE
  • CREATE USER
• Under MySQL 5.0
  • CREATE (<-- This is what you want)
  • CREATE TEMPORARY TABLE
  • CREATE VIEW
  • CREATE ROUTINE
  • CREATE USER