Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
You can't use objects in placeholders. Build the string with the object name, not a ?. –  longneck Aug 20 '12 at 13:11
    
Sorry, I don't understand you entirely. Do you mean that database names need to be pre-defined? –  vvanscherpenseel Aug 21 '12 at 14:03
    
see my updated answer below. –  longneck Aug 21 '12 at 17:06

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you for your comment, but I can't figure it out entirely. I've updated my question (I couldn't do code formatting in comment). –  vvanscherpenseel Aug 20 '12 at 10:21
    
Thank you very much :) –  vvanscherpenseel Aug 23 '12 at 9:25

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

share|improve this answer
    
You are right, thanks for pointing this out. –  vvanscherpenseel Aug 20 '12 at 10:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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