ApsaraDB RDS:Authorize accounts to manage tables, views, and fields

Procedure

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 and authorize the account to manage tables, views, and fields in the target database.
   Note The created account does not have permissions to view its authorized databases in the ApsaraDB RDS console.

   • Create an account and authorize the account to manage a table in a database.

     CREATE USER `<The username of the account you want to create>`@`%`IDENTIFIED BY '<The password of the account you want to create>';
     GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '<The username of the account>'@'%';
     GRANT ALL PRIVILEGES ON `<The name of the database that you want to manage>`.`<The name of the table that you want to manage>`  TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`help_topic` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`func` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`time_zone` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`slow_log` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`time_zone_transition` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`proc` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`help_category` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`help_relation` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`help_keyword` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`general_log` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`time_zone_leap_second` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`time_zone_transition_type` TO '<The username of the account>'@'%';
     GRANT SELECT ON `mysql`.`time_zone_name` TO '<The username of the account>'@'%';

     Example:

     To create an account named test01 and authorize the account to manage the test100 table in the rds001 database, execute the following SQL statements:

     CREATE USER `test01`@`%`IDENTIFIED BY 'passwd';
     GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test01'@'%';
     GRANT ALL PRIVILEGES ON `rds001`.`test100`  TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`help_topic` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`func` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`time_zone` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`slow_log` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`time_zone_transition` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`proc` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`help_category` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`help_relation` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`help_keyword` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`general_log` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'test01'@'%';
     GRANT SELECT ON `mysql`.`time_zone_name` TO 'test01'@'%';

     Note If you change test100 in the third line of code to an asterisk (*), you grant the test01 user the permissions to view its Authorized Databases in the ApsaraDB RDS console.
   • Authorize the created account to query a view from a database.

     grant select on <The database name>.<The view name> to <The username of the account>;

     Example:

     To authorize the test01 user to query the view_test1 view from the rds001 database, execute the following SQL statement:

     grant select on rds001.view_test1 to test01;

   • Authorize the created account to update or query a field name in a table from a database.

     grant update (<The field name you want to update by using the created account>) on table <The name of the table in which you want to update a field name> to <The username of the created account>;     -----Authorize the created account to update a field name in a table from a database.
     grant select (<The field name that you want to query by using the created account>) on table <The name of the table you want to update a field name> to <The username of the created account>;     -----Authorize the created account to query a field name in a table from a database.

     Example:

     To authorize the test01 user to update the testid field in the testtable table, execute the following SQL statement:

     grant update (testid) on table testtable to test01;

   You can also execute SQL statements in Data Management (DMS). For more information, see Manage user permissions on MySQL databases.