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.

Solution 1:

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_

Solution 2:

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_

Solution 3:

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.

Solution 4:

It is impossible to do this using permissions only .

The workaround as suggested in another answer: GRANT ALL PRIVILEGES ONtestuser_%. * 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.