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.
- 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 ;
- Grant the privilege to execute this procedure to the user appserver:
grant execute on CreateAppDB to appserver@localhost;
- 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.