This topic describes how to execute SQL statements for authorizing accounts to manage tables, views, or fields in a database of an ApsaraDB for RDS instance. If you are using your Alibaba Cloud account, you have the permissions to manage all types of data in your ApsaraDB for RDS instance.

Prerequisites

You have created a privileged account in your ApsaraDB for RDS instance. For more information, see Create accounts and databases for an RDS for MySQL instance.

Procedure

  1. Connect to an ApsaraDB RDS for MySQL instance.
  2. Execute SQL statements to create an account and authorize it 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 for RDS console.
    • Create an account and authorize it to manage a table in the target 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 created account>'@'%';
      GRANT ALL PRIVILEGES ON `<The name of the target database>`. `<The name of the table you want to create with the created account>`  TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`help_topic` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`func` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`time_zone` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`slow_log` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`time_zone_transition` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`event` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`proc` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`help_category` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`help_relation` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`help_keyword` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`general_log` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`time_zone_leap_second` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`time_zone_transition_type` TO '<The username of the created account>'@'%';
      GRANT SELECT ON `mysql`.`time_zone_name` TO '<The username of the created account>'@'%';

      Example:

      To create an account named test01 and authorize it 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`.`event` 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 the * wildcard, you will give the test01 user the permissions to view its Authorized Databases in the ApsaraDB for RDS console.
    • Authorize the created account to query a view from the target database.
      grant select on <The name of the target database>. <The name of the view you want to query with the created account> to <The username of the created 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 the target database.
      grant update (<The field name you want to update with the created account>) on table <The name of the table where the field name you want to update resides> to <The username of the created account>;     -----Authorize the created account to update a field name in a table from the target database.
      grant select (<The field name you want to query with the created account>) on table <The name of the table where the field name you want to query resides> to <The username of the created account>;     -----Authorize the created account to query a field name in a table from the target 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;
    In addition to executing SQL statements, you can choose Tools > User Management from the top navigation bar to change the permissions of an account.