How to Grant Database Creation Privileges While Restricting Access to Specific Databases in MySQL?

Granting Database Creation Privileges and Restricting Access

Problem:

Users need to be able to create databases within a MySQL instance, but each user should be granted access only to their own databases.

Solution:

Step 1: Create a Replacement Role

Create a role that includes the necessary privileges for creating and modifying databases:

CREATE ROLE db_creator;
GRANT CREATE, DROP, ALTER, DELETE, INSERT, UPDATE, SELECT ON *.* TO db_creator;

Step 2: Grant the Role to Target Users

Grant the role to the desired users, specifying that the role's privileges should be applied to databases with a specific naming convention:

GRANT db_creator TO 'testuser'@'%';

In this example, the testuser will have the privileges granted by the db_creator role, but only for databases that begin with testuser_.

Additional Notes:

• Replace testuser with the actual user name.
• Specify '%' as the host to grant access from any host (adjust as needed for your environment).
• The naming convention for databases (e.g., testuser_%) can be customized as desired.

The above is the detailed content of How to Grant Database Creation Privileges While Restricting Access to Specific Databases in MySQL?. For more information, please follow other related articles on the PHP Chinese website!