Manage MySQL database account permissions

Function introduction

You can manage common permission sets, such as read-only, read/write, DML only, and DDL only, in the consoles for RDS for MySQL and PolarDB for MySQL. For more complex scenarios, such as creating custom permission sets or granting table-level permissions, you can use the database account permission management feature in DMS. For example:

• Grant Account A the global SELECT and UPDATE permissions.

  Note

  Global permissions apply to the entire database instance. For more information, see MySQL global permissions.

• Grant Account B the SELECT permission on a single table or the UPDATE permission on a specific column.

  Note

  Object permissions can apply to all database objects or to one or more specific database objects. For more information, see MySQL object permissions.

Create an account

1. Log in to DMS 5.0.

2. Log on to the destination database. For more information, see Log on to a database.

3. On the home page, click the database instance in the navigation pane on the left. In the instance list, right-click the destination instance name and click Database Account Management.

4. On the Database Account Management page, click Create Database Account in the upper-left corner.

5. In the dialog box that appears, configure the following parameters.

   1. Click the Basic settings tab and configure the parameters.

      

      Configuration item	Description
      Database Account	The account used to log on to the database.
      Host	The IP address from which the account is allowed to access the database. Note If you leave this blank, the account can access the database from any IP address. The default value is %.
      Password	Enter the logon password.
      Confirm Password	Enter the logon password again.

      Note

      The SQL statement for the preceding configuration is CREATE USER 'username'@'host' IDENTIFIED BY 'password';.

      To configure Advanced Options, click the Advanced Options button and configure the parameters.

      For example, the SQL statement for the configuration in the following figure is:

      GRANT USAGE ON *.* TO 'username'@'host' 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 the required permissions.

      

      Note

      If you use a standard account and cannot find the required permission, it may be because the permission is not supported by the instance type or your account does not have the required permissions. If your account permissions are insufficient, you can retry the operation using a privileged account.

   3. Click the Object permissions tab and configure the parameters.

      For example, the SQL statement for the configuration in the following figure is:

      GRANT SELECT,INSERT ON `rds_db`.* TO 'username'@'host'; 
       GRANT DELETE ON `rds_db`.`rds_table` TO 'username'@'host';

      

6. Click Confirm.

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

   Note

   Database instances in Security Collaboration mode are subject to security rules. If the execution fails, follow the on-screen instructions or contact a DBA or an administrator for confirmation. For more information about how to adjust the rules, see the FAQ section.

Edit or delete an account

You can edit an account to modify its username, password, and MySQL global and object permissions.

1. Log in to DMS 5.0.

2. On the home page, click the database instance in the navigation pane on the left. In the instance list, right-click the destination instance name and click Database Account Management.

3. Find the target account and click Edit or Delete.

MySQL global permissions

MySQL object permissions

FAQ