Authorize accounts to manage tables, views, and fields

Procedure

1. Use a privileged account to connect to the RDS instance by using a client or the CLI.

2. Execute SQL statements to create an account and authorize the account to manage tables, views, and fields in a database.

   Note

   The created account does not have permissions to view its authorized databases in the ApsaraDB RDS console.

   1. Create an account.

      CREATE USER `<Username of the account>`@`%` IDENTIFIED BY '<Password of the account>';

   2. Grant global permissions, such as PROCESS and replication-related permissions, to the account.

      GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '<Username of the account>'@'%';

   3. Grant full permissions on tables in a specific database to the account.

      GRANT ALL PRIVILEGES ON `<Database name>`.`<Table name>` TO '<Username of the account>'@'%';

   4. Grant read-only permissions on commonly used tables in the mysql system database to the account.

      GRANT SELECT ON `mysql`.`help_topic` TO '<Username of the account>'@'%';
      GRANT SELECT ON `mysql`.`func` TO '<Username of the account>'@'%';
      GRANT SELECT ON `mysql`.`time_zone` TO '<Username of the account>'@'%';
      GRANT SELECT ON `mysql`.`slow_log` TO '<Username of the account>'@'%';
      GRANT SELECT ON `mysql`.`proc` TO '<Username of the account>'@'%';
      GRANT SELECT ON `mysql`.`general_log` TO '<Username of the account>'@'%';
      -- Grant permissions on other system tables based on your business requirements.

      Examples

      • Authorize an account to manage a table: Create an account named test01 and authorize the account to manage the test100 table in the rds001 database.

        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'@'%';

        Note

        If you replace test100 with an asterisk (*), the account has permissions on all tables in the rds001 database and can view the database to which the account belongs in the ApsaraDB RDS console.

      • Authorize an account to manage multiple tables: Create an account named test02 and authorize the account to manage the test100, test200, and test300 tables in the rds001 database.

        CREATE USER `test02`@`%`IDENTIFIED BY 'passwd';
        GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test02'@'%';
        GRANT ALL PRIVILEGES ON `rds001`.`test100`  TO 'test02'@'%';
        GRANT ALL PRIVILEGES ON `rds001`.`test200`  TO 'test02'@'%';
        GRANT ALL PRIVILEGES ON `rds001`.`test300`  TO 'test02'@'%';
        GRANT SELECT ON `mysql`.`help_topic` TO 'test02'@'%';
        GRANT SELECT ON `mysql`.`func` TO 'test02'@'%';

   5. Authorize the account to query views from a database.

      GRANT SELECT ON <Database name>.<View name> to <Username of the account>;

      Examples

      • Authorize an account to manage a view: Authorize the test01 account to query the view_test1 view from the rds001 database.

        GRANT SELECT ON rds001.view_test1 to test01;

      • Authorize an account to manage multiple views: Authorize the test01 account to query the view_test1 and view_test2 views from the rds001 database.

        GRANT SELECT ON rds001.view_test1 to test01;
        GRANT SELECT ON rds001.view_test2 to test01;

   6. Authorize the account to update or query fields in a table from a database.

      GRANT UPDATE (<Field name>) ON TABLE <Table name> to <Username of the account>;     -----Authorize the account to update fields in a table.
      GRANT SELECT (<Field name>) ON TABLE <Table name> to <Username of the account>;     -----Authorize the account to query fields in a table.

      Examples

      • Authorize an account to manage a field: Authorize the test01 account to update the testid field in the testtable table.

        GRANT UPDATE (testid) ON TABLE testtable to test01;

      • Authorize an account to manage multiple fields: Authorize the test01 account to update the test_id and test_name fields in the testtable table.

        GRANT UPDATE (test_id) ON TABLE testtable to test01;
        GRANT UPDATE (test_name) 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.