Let MySQL users create databases, but allow access to only their own databases

I want to have multiple a MySQL users to be able to issue commands like

CREATE DATABASE dbTest;

But I also want each of these users to be able to see and access only their own databases.

All I could find was how to either create the databases by a DBA and grant the privileges on this database to the specific user:

GRANT ALL PRIVILEGES ON dbTest.* TO 'user';

or grant privileges on all databases to a user:

GRANT ALL PRIVILEGES ON *.* TO 'user';

But neither is what I want, because it needs to scale and be secure.

You can use

GRANT ALL PRIVILEGES ON `testuser\_%` .  * TO 'testuser'@'%';

to grant the user testuser privileges on all databases with names beginning with testuser_.

This allows the testuser to create databases limited to names starting with testuser_

You can use

GRANT ALL PRIVILEGES ON `testuser_%` . * TO 'testuser'@'%';

to grant the user testuser privileges on all databases with names beginning with testuser_.

EDIT: I'm not sure if this user is now also allowed to create databases.

Yes, this allows the testuser to create databases limited to names starting with testuser_

Create a stored procedure that is defined by the admin user and invokes with the admin user privileges by using SQL SECURITY DEFINER. In the stored procedure,

• Create the database.
• Set the privileges on the database so only the current user has access.
• Execute FLUSH PRIVILEGES to reload the privileges from the grant tables.

Use USER() to get the current user login details.

Find out more about SQL SECURITY DEFINER.

It is impossible to do this using permissions only .

The workaround as suggested in another answer: GRANT ALL PRIVILEGES ON testuser_% . * TO 'testuser'@'%'; has the problem that the users must then be very careful in naming their databases.

For example if user aaa creates database bbb_xyz, it can then be accessed exclusively by user bbb but not by user aaa.