Limit permissions of a specific IP address on a database

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.

1. Log on to the ApsaraDB for RDS console.
2. In the upper-left corner of the page, select the region where the target RDS instance resides.
3. Find the target RDS instance and click its ID.
4. In the upper-right corner, click Log On to DB. Then, use the privileged account to log on to the target database in the RDS instance. For more information, see Log on to an RDS MySQL instance through DMS.
5. In the top navigation bar, choose SQL Operations > SQL Window.
6. 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`;