MySQL多用户权限配置：允许创建数据库并仅访问自建库

Absolutely! This is totally achievable in MySQL with the right permission configuration. Let me break down the approach clearly, including steps to enforce that users can only access databases they create themselves:

Core Idea #

We need to assign two key sets of permissions to each user:

1. A global CREATE privilege to let them create new databases.
2. Full privileges on databases that follow a naming convention tied to their username (e.g., databases starting with username_), ensuring they can only interact with their own creations.

Step-by-Step Implementation #

1. Create the User

First, create your target user (replace alice with the actual username, and adjust the host % to a specific IP if you want to restrict access to certain locations):

CREATE USER 'alice'@'%' IDENTIFIED BY 'YourStrongPasswordHere!';

2. Grant Global Database Creation Permission

Give the user the ability to create new databases across the server:

GRANT CREATE ON *.* TO 'alice'@'%';

3. Grant Full Access to Their Own Databases

We’ll use a wildcard to match databases that start with the user’s username followed by an underscore (e.g., alice_projectdb, alice_testdb). This ensures they only have access to databases they create (assuming they follow the naming rule):

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

Important: The underscore _ is a wildcard character in MySQL, so we escape it with \ to match it literally. This prevents the rule from matching databases like aliceXdb (without the underscore).

4. Apply the Permission Changes

Make sure the new permissions take effect immediately:

FLUSH PRIVILEGES;

Extra Tips to Enforce the Restriction #

• Enforce Naming Rules (Optional)：If you want to make sure users can’t create databases outside their prefix, you can add a trigger (requires MySQL 8.0.21+ and SUPER privilege to create):

  DELIMITER //
  CREATE TRIGGER validate_db_name BEFORE CREATE ON SCHEMA
  FOR EACH ROW
  BEGIN
      -- Block database creation if name doesn't start with current user's username + underscore
      IF NEW.SCHEMA_NAME NOT LIKE CONCAT(SUBSTRING_INDEX(CURRENT_USER(), '@', 1), '\_%') THEN
          SIGNAL SQLSTATE '45000' 
          SET MESSAGE_TEXT = 'Error: Database name must start with your username followed by an underscore (e.g., yourname_dbname)';
      END IF;
  END //
  DELIMITER ;
  

• Avoid Over-Permissioning：Never grant global SELECT, INSERT, or other data access privileges—stick only to the CREATE global permission and the wildcarded database privileges. This ensures users can’t peek into other people’s databases.

内容的提问来源于stack exchange，提问作者rmn.nish