How to Grant Database Creation and Restricted Access to MySQL Users?

Granting Database Creation and Restricted Access to MySQL Users

In MySQL, there is a need to allow multiple users to create their own databases while restricting their access to only those databases they create. By default, granting all privileges on the "*" database to a user will allow them access to all databases, which may not be desirable in certain scenarios.

The solution is to utilize the MySQL privilege system, which allows for granular control over permissions. The key is to use the GRANT statement with a specific pattern that limits the scope of privileges granted to the user.

To achieve the desired behavior, the following command should be used:

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

This GRANT statement grants the user 'testuser' all privileges (e.g., CREATE, SELECT, INSERT, etc.) on all databases whose names begin with 'testuser_'. By specifying a wildcard character ('%') at the end of the pattern, we ensure that all databases matching that pattern will be included.

This approach ensures that each user can create their own databases, limited to names starting with a specific prefix. They will be unable to view or access databases created by other users, maintaining both scale and security.

The above is the detailed content of How to Grant Database Creation and Restricted Access to MySQL Users?. For more information, please follow other related articles on the PHP Chinese website!