This topic describes how to limit the permissions of a specific IP address on a database of an ApsaraDB RDS for MySQL instance. If a whitelist of an RDS instance contains more than one IP address, a database account created in this instance has the same permissions after logging on to this instance from one of these IP addresses. This imposes security risks. For example, if a whitelist contains the IP addresses of both the headquarters and branches of an enterprise, you can grant a database account only the permissions to access and manage a database from the IP address of the headquarters.

Prerequisites

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

Procedure

  1. Connect to an ApsaraDB RDS for MySQL instance.
  2. Create a user and grant that user permissions to access and manage a database from a specific IP address. Note that you cannot view the Authorized Databases of the created user in the ApsaraDB for RDS console.

    Example:

    Execute the following statements to create a user named test001 and grant that user permissions to access and manage the rds001 database from the IP address 42.120.74.119: 

    CREATE USER `test001`@`42.120.74.119`IDENTIFIED BY 'passwd';
GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'test001'@'42.120.74.119';
GRANT ALL PRIVILEGES ON `rds001`. * TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`help_topic` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`func` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`time_zone` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`slow_log` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`time_zone_transition` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`event` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`proc` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`help_category` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`help_relation` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`help_keyword` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`general_log` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'test001'@'42.120.74.119';
GRANT SELECT ON `mysql`.`time_zone_name` TO 'test001'@'42.120.74.119';
    Note
    • If you change the IP address 42.120.74.119 in all the preceding statements to the wildcard %, the created user functions the same as a database account you create in the ApsaraDB for RDS console. This means that you can view the Authorized Databases of the created user in the ApsaraDB for RDS console.
    • To change the IP address to 42.120.74.120, you can execute the following statement: 
      RENAME USER `test001`@`42.120.74.119` TO `test001`@`42.120.74.120`;