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 allow a user only to read data from, or read data from and write data to, a MySQL database. You can also allow a user to execute only data manipulation language (DML) or data definition language (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. Example:

• 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 search box at the top of the left-side navigation pane, enter the name of the MySQL database whose permissions you want to manage. From the matched result, find the instance to which the database belongs.
3. Right-click the instance and select Account Management.
   
4. On the Account Management page, click Create User in the upper-left corner.
5. In the Create User dialog box, perform the following steps:
   1. On the Basic settings tab, set the parameters as 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 same 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. You can grant specific permissions on specific objects as required.
      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';

      
6. In the Create User dialog box, click OK.
7. In the Preview SQL Statement dialog box, click OK.
   Note If the database instance is 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 the DBA or DMS administrator.

Edit a user

1. Log on to the DMS console.
2. In the search box at the top of the left-side navigation pane, enter the name of the MySQL database whose permissions you want to manage. From the matched result, find the instance to which the database belongs.
3. Right-click the instance and select Account Management.
4. On the Account Management page, find the user whose information you want to edit and click Edit in the Operation column.
   

Delete a user

1. Log on to the DMS console.
2. In the search box at the top of the left-side navigation pane, enter the name of the MySQL database whose permissions you want to manage. From the matched result, find the instance to which the database belongs.
3. Right-click the instance and select Account Management.
4. On the Account Management page, find the user who you want to delete and click Delete in the Operation column.
5. In the Prompt message, click OK.

Supported global permissions

Supported fine-grained permissions