Securing MySQL Databases with Limited Access to User-Created Databases

In a multi-user MySQL setup, ensuring data security is paramount. One common challenge is allowing users to create databases while restricting their access to only their own creations.

To resolve this issue, MySQL provides a nuanced approach using wildcard database names. Instead of granting privileges on specific databases, the solution lies in granting privileges on databases with a specific prefix or pattern.

By utilizing the GRANT command, you can grant all privileges on databases matching a particular pattern. The syntax for granting privileges on a pattern matching database names is as follows:

GRANT ALL PRIVILEGES ON `.%` TO ''@'%';

In this command:

• represents the prefix or pattern of the database names your users will create.
• is the username of the user you want to grant privileges to.

For example:

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

By executing this command, you grant the user testuser privileges on all databases beginning with the prefix testuser_. This allows testuser to create databases with names such as testuser_demo, testuser_prod, etc. However, testuser will not be able to view or access databases with names outside this pattern.

This approach ensures scalability, allowing multiple users to create databases securely while restricting their access to their own creations.