Grant MySQL user ability to create databases, and only allow them to access those databases

Question

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 ;

2. Grant the privilege to execute this procedure to the user appserver:

grant execute on CreateAppDB to appserver@localhost;

3. 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.

Answer 1

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.