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. 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`;