How to authorize an account to access its authorized databases from specified IP addresses - ApsaraDB RDS

The IP address whitelists of an RDS instance take effect on all accounts that are created on the RDS instance. You cannot configure whitelists to limit the IP addresses from which each account can access its authorized databases, which exposes the RDS instance to security risks. This topic describes how to authorize an account to access its authorized databases from specified IP addresses in an ApsaraDB RDS for MySQL instance by running SQL commands.

Prerequisites

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

Usage notes

After you authorize an account to access its authorized databases from specified IP addresses, issues may occur when you manage the account in the ApsaraDB RDS console or by calling an API operation. We recommend that you use Data Management (DMS) or SQL statements to manage the account.
If the accounts that are used to log on to a database have the same username and are assigned the equivalent IP addresses, such as 192.168.% and 192.168.%.%, the system cannot guarantee the authentication order, which may cause access exceptions. For example, if the [email protected].% and [email protected].%.% accounts are created for the database, the system randomly authenticates one of the accounts. If the passwords or permissions of the two accounts are different, the logon may fail or the permissions are different after you log on to the database. We recommend that you use different usernames.

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

Connect to the RDS instance. For more information, see Use a client or the CLI to connect to an ApsaraDB RDS for MySQL instance.
Execute SQL statements to create an account on the RDS instance and authorize the account to manage databases and access its authorized databases from specified IP addresses.
Execute the following statements to create a user named test001 and authorize the user to access and manage the rds001 database from the IP address 42.120.XX.XX:
```
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.* TO 'test001'@'42.120.XX.XX';
```
Note
- You cannot view the authorized databases of the user in the RDS console unless you change the IP address 42.120.XX.XX in all the preceding statements to the wildcard %.
- You can execute the following statement to change the IP address to 42.121.XX.XX:
  RENAME USER `test001`@`42.120.XX.XX` TO `test001`@`42.121.XX.XX`;