Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible to grant a MySQL user the privileges to CREATE new databases, such that they have full access to the databases they create (including the ability to DROP those databases). But other than the databases they create themselves, they should have no privileges on any other user's databases.

What would be the CREATE USER/GRANT syntax to make that happen?

SOLUTION: (as per johnshen64's response)

Say you would like the appserver user to be able to create and access new databases.

  1. As root, create the following stored procedure:
DROP PROCEDURE IF EXISTS CreateAppDB;
DELIMITER //
CREATE PROCEDURE CreateAppDB(
    IN db_name VARCHAR(50))
BEGIN
    -- Create database

    SET @s = CONCAT('CREATE DATABASE ', db_name);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Grant permissions

    SET @s = CONCAT('GRANT ALL ON ', db_name, '.* TO appserver@localhost');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//
DELIMITER ;
  1. Grant the privilege to execute this procedure to the user appserver:
grant execute on CreateAppDB to appserver@localhost;
  1. Login as appserver and execute this procedure. For example, to create a database called foo:
call CreateAppDB('foo');

The user appserver will have full rights on the newly created database foo, including the ability to drop it, but will not be able to access any other user databases in MySQL.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Although I know of no such sql statements in MySQL to let you do this, but you can write a stored procedure to manage this and grant the stored procedure to the user. Inside the procedure you can then create the db and grant all the said privileges to the user.

share|improve this answer
    
That worked great. I will update my question with code samples using this approach. Thanks! –  Gene Goykhman May 16 '12 at 1:38

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.