ApsaraDB RDS:Authorize an account to access its authorized databases from specified IP addresses

Prerequisites

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

Usage notes

• If you perform the operations in this topic to authorize an account to access its authorized databases from specified IP addresses and then manage the account in the ApsaraDB RDS console or by calling an API operation, issues may occur. 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 Accounts 192.168.% and 192.168.%.%, the system cannot guarantee the authentication order, which may cause access exceptions. For example, the user@192.168.% and user@192.168.%.% accounts can be used to log on to the database. When you log on to the database by using one of the accounts, 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

1. Connect to the RDS instance. For more information, see Use a database client or the CLI to 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 manage databases and access its authorized databases from specified IP addresses. You cannot view the authorized databases of the created account in the ApsaraDB RDS console.

   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

   • If you change the IP address 42.120.XX.XX in all the preceding statements to the wildcard %, the created user functions the same as a database account you create in the ApsaraDB RDS console. This means that you can view the authorized databases of the created user in the ApsaraDB RDS console.

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