Manage user permissions on MySQL databases

Overview

You can manage coarse-grained permissions on MySQL databases in ApsaraDB RDS for MySQL instances or PolarDB for MySQL instances in the corresponding console. For example, you can grant other users the read-only permissions or the read and write permissions on a MySQL database. You can also grant other users the permissions to execute DML or DDL statements on the MySQL database. However, you may need more flexible and finer-grained permission management. In this case, you can use the user permission management feature of DMS. Examples:

• You can grant User A the global permissions to execute SELECT and UPDATE statements.
  Note Global permissions apply to all databases in a database instance. For more information, see Supported global permissions.
• You can grant User B the permission to execute SELECT statements on a table or the permission to execute UPDATE statements on a field.
  Note Fine-grained permissions can apply to specific or all databases in a database instance. For more information, see Supported fine-grained permissions.

Create a user

1. Log on to the DMS console.
2. In the left-side navigation pane of the DMS console, right-click the instance that you want to manage and select Account Management.
3. On the Account Management tab, click Create User in the upper-left corner.
4. In the Create User dialog box, perform the following steps:
   1. On the Basic settings tab, set the parameters that are described in the following table.
      

      Parameter	Description
      User name	The name of the user to be created.
      Host	The name of the host on which the user can log on to the databases in the current instance. Default value: %.
      Password	The password that the user can use to log on to the databases in the current instance.
      Confirm Password	The password that the user can use to log on to the databases in the current instance. Enter the password again to confirm the password.

      Note After you set the preceding parameters, DMS automatically generates an SQL statement based on the parameters you set. Then, DMS executes the statement for each database in the current instance. The format of the SQL statement is CREATE USER 'Username'@'Hostname' IDENTIFIED BY 'Password';.

      You can click Advanced Options to complete more configurations.

      For example, you can set specific upper limits for the user, as shown in the following figure. In this case, DMS generates and executes the following SQL statement:

      GRANT USAGE ON *.* TO 'Username'@'Hostname' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 200 MAX_CONNECTIONS_PER_HOUR 300 MAX_USER_CONNECTIONS 400;

      
   2. Click the Global permissions tab and select one or more permissions that you want to grant to the user.
      
   3. Click the Object permissions tab and set relevant parameters.
      For example, you can allow the user to query or insert data in a database, and delete data from a table in the database, as shown in the following figure. In this case, DMS generates and executes the following SQL statements:

      GRANT SELECT,INSERT ON `rds_db`.* TO 'Username'@'Hostname'; 
       GRANT DELETE ON `rds_db`.`rds_table` TO 'Username'@'Hostname';

      
5. Click Confirm.
6. In the Preview SQL Statement message, click Confirm.
   Note If the database instance is managed in Security Collaboration mode, SQL statements can be generated based on the parameters you set. However, the SQL statements may fail to be executed due to security rules. In this case, you can perform operations as prompted or contact a database administrator (DBA) or DMS administrator.

Edit or delete a user

1. Log on to the DMS console.
2. In the left-side navigation pane of the DMS console, right-click the instance that you want to manage and select Account Management.
3. Find the user that you want to manage and click Edit in the Operation column to modify the information about the user, or click Delete in the Operation column to delete the user.

Supported global permissions

Supported fine-grained permissions