How to Enable MySQL Users to Create Databases with Restricted Access?

Allowing MySQL Users to Create Databases with Restricted Access

MySQL provides multiple user accounts for database management. However, it also poses a challenge in controlling database access and data security. This article explores how to grant MySQL users the ability to create databases while limiting their access to only their own databases.

Problem:

Multiple MySQL users need the ability to create databases (e.g., "CREATE DATABASE dbTest"). However, it is essential to restrict each user's visibility and access to their own databases.

Solution:

To achieve this, MySQL provides the concept of user-specific database names. By using the GRANT statement with a wildcard character (%) in the database name, specific privileges can be granted on databases matching a particular naming pattern.

Implementation:

To grant a user named testuser privileges on databases starting with the prefix testuser_, the following GRANT statement can be used:

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

By using this approach, the testuser can create databases that begin with the testuser_ prefix and access only those databases. This mechanism ensures a scalable and secure solution for controlled database access.

The above is the detailed content of How to Enable MySQL Users to Create Databases with Restricted Access?. For more information, please follow other related articles on the PHP Chinese website!