Data Management: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 statements 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 statements 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 permissions to execute SELECT statements on a table or the permissions 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 V5.0.
2. Log on to the PostgreSQL database. For more information, see Log on to a database instance.

3. In the left-side navigation pane of the DMS console, right-click the instance that you want to manage and select Account Management.

   Note

   If you log on to the DMS console in simple mode, click Database instance in the left-side navigation pane. In the instance list that appears, right-click the instance that you want to manage 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, configure the parameters that are described in the following table.

      

      Parameter	Description
      User name	The name of the user that you want to use to log on to the database.
      Host	Indicates that the account is allowed to access the database from an IP address. Note If you do not configure this parameter, the user is not restricted to accessing the authorized databases from specific IP addresses. The default value of this parameter is %.
      Password	The password that you want to use to log on to the databases in the current instance.
      Confirm Password	Enter the password again to confirm the password.

      Note

      After you configure the preceding parameters, DMS automatically generates an SQL statement based on the parameters that you configure. Then, DMS executes the statement for each database in the current instance. The syntax 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 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.

      

      Note

      If you cannot find the permissions that you require, the permissions are not supported by the instance or your Alibaba Cloud account is not authorized to grant the permissions. If you use a standard account, log on to the DMS console by using a privileged account and retry this step.

   3. Click the Object permissions tab and configure the parameters to grant fine-grained permissions to the user.

      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. Click Confirm.

7. In the Preview SQL Statement message, click Confirm.

   Note

   SQL statements can be generated based on the parameters that you configure. If the database instance is managed in Security Collaboration mode, the SQL statements may fail to be executed due to security rules. In this case, you can perform operations by following the on-screen instructions or contact a database administrator (DBA) or DMS administrator. For information about how to modify security rules, see FAQ of this topic.

Modify or delete a user

You can modify the username, password, global permissions, and fine-grained permissions of a user that you are authorized to manage.

1. Log on to the DMS console V5.0.

2. In the left-side navigation pane of the DMS console, right-click the instance that you want to manage and select Account Management.

   Note

   If you log on to the DMS console in simple mode, click Database instance in the left-side navigation pane. In the instance list that appears, right-click the instance that you want to manage and select Account Management.

3. On the Account Management page, 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

FAQ

Q: Why does the system inform me that I cannot execute the CREATE USER statement to create a user that has specific permissions because of security rules?