eikes eikes - 5 months ago 23
MySQL Question

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.

Lex Lex
Answer

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_