This topic describes how to authorize an account to access its authorized databases from specified IP addresses in an ApsaraDB RDS for MySQL instance. The IP address whitelists of an RDS instance take effect on all accounts that are created on the RDS instance. You cannot use IP address whitelists to restrict the IP addresses from which each account can access its authorized databases. If you use only IP address whitelists to control access to an RDS instance, the RDS instance may be exposed to security risks.

Prerequisites

A privileged account is created. For more information, see Create an account on an ApsaraDB RDS for MySQL instance.

Use DMS to authorize an account to access its authorized databases from specified IP addresses

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click Accounts. On the Accounts page, click Customize Permissions to go to the Data Management (DMS) console.
    Customize Permissions
  3. In the left-side navigation pane of the DMS console, right-click the instance that you want to manage and select Account Management.
  4. Click Create User in the upper-left corner of the page. Alternatively, click Edit in the Actions column for the account.
  5. On the Basic settings tab, configure the Host parameter. Create User dialog box
    Note
    • The Host parameter specifies the IP address from which the account can access its authorized databases. You can specify more than one IP address. Multiple IP addresses must be separated by commas (,). If you do not specify this parameter, the account is not authorized to access its authorized databases from specified IP addresses. The default value of this parameter is %.
    • The IP addresses that are specified by the Host parameter must be added to an IP address whitelist of the RDS instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.
    • DMS allows you to grant more permissions to accounts. For more information, see Manage user permissions on MySQL databases.
  6. Click Confirm.
  7. 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.

Use SQL statements to authorize an account to access its authorized databases from specified IP addresses

  1. Connect to the RDS instance on which you want to create an account. For more information, see Connect to an ApsaraDB RDS for MySQL instance.
  2. Execute SQL statements to create an account on the RDS instance and authorize the account to access its authorized databases from specified IP addresses. You cannot view the authorized databases of the created account in the ApsaraDB RDS console.

    In the following example, you create an account named test001 and authorize the account to access the rds001 database from the 42.120.XX.XX IP address.

    CREATE USER `test001`@`42.120.XX.XX`IDENTIFIED BY 'passwd';
    GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test001'@'42.120.XX.XX';
    GRANT ALL PRIVILEGES ON `rds001`.* TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`help_topic` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`func` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`time_zone` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`slow_log` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`time_zone_transition` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`event` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`proc` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`help_category` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`help_relation` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`help_keyword` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`general_log` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'test001'@'42.120.XX.XX';
    GRANT SELECT ON `mysql`.`time_zone_name` TO 'test001'@'42.120.XX.XX';
    Note
    • If you change the IP address from42.120.XX.XX to %, the created account is similar to an account that is created in the ApsaraDB RDS console. You can view the authorized database of the created account in the ApsaraDB RDS console.
    • You can execute the following statement to change the IP address to 42.121.XX.XXX:
      RENAME USER `test001`@`42.120.XX.XX` TO `test001`@`42.121.XX.XX`;