AnalyticDB for MySQL allows you to grant different levels of permissions to implement permission control. This topic describes the mappings between SQL statements and required permissions, and how to view and manage permissions for an AnalyticDB for MySQL cluster.

Permission levels

AnalyticDB for MySQL supports the following permission levels:
  • GLOBAL: cluster-level permissions
  • DB: database-level permissions
  • TABLE: table-level permissions
  • COLUMN: column-level permissions

    If you want an account to query the data of one specific column in a table, you can grant the SELECT permission on the column to the account. Example: GRANT SELECT (customer_id) ON customer TO 'test321'.

Operations and corresponding permissions

OperationRequired permissionSupported permission level
SELECTSELECT
  • GLOBAL
  • DB
  • TABLE
  • COLUMN
INSERTINSERT
  • GLOBAL
  • DB
  • TABLE
  • COLUMN
INSERT...SELECT...FROM...
  • INSERT
  • SELECT
  • GLOBAL
  • DB
  • TABLE
  • COLUMN
UPDATEUPDATE
  • GLOBAL
  • DB
  • TABLE
  • COLUMN
DELETEDELETE
  • GLOBAL
  • DB
  • TABLE
TRUNCATE TABLEDROP
  • GLOBAL
  • DB
  • TABLE
ALTER TABLE
  • ALTER
  • INSERT
  • CREATE
  • GLOBAL
  • DB
  • TABLE
CREATE DATABASECREATEGLOBAL
CREATE TABLECREATE
  • GLOBAL
  • DB
  • TABLE
SHOW CREATE TABLESELECT
  • GLOBAL
  • DB
  • TABLE
DROP DATABASEDROP
  • GLOBAL
  • DB
DROP TABLEDROP
  • GLOBAL
  • DB
  • TABLE
CREATE VIEW
  • CREATE VIEW

    To execute the CREATE VIEW REPLACE statement, you must also grant the DROP permission.

  • SELECT
  • GLOBAL
  • DB
  • TABLE
DROP VIEWDROP
  • GLOBAL
  • DB
  • TABLE
SHOW CREATE VIEW
  • SHOW VIEW
  • SELECT
  • GLOBAL
  • DB
  • TABLE
CREATE USER/DROP USER/RENAME USERCREATE_USERGLOBAL
SET PASSWORDSUPERGLOBAL
GRANT/REVOKEGRANTGLOBAL

View permissions

Use SQL statements to view permissions

Note You can use SQL statements to view permissions on Data Warehouse Edition (V3.0) and Data Lakehouse Edition (V3.0) clusters.
Procedure

Execute the SHOW GRANTS statement to view the permissions of a specific account. For more information, see SHOW GRANTS.

Use DMS to view permissions

Note You can use Data Management (DMS) to view permissions on Data Warehouse Edition (V3.0) and Data Lakehouse Edition (V3.0) clusters.
Prerequisites
  • An Alibaba Cloud account is used.
  • A Resource Access Management (RAM) user that has the AliyunADBReadOnlyAccess permission is used. For more information about how to grant permissions to a RAM user, see Grant permissions to the RAM user.
Procedure
  1. Use DMS to connect to an AnalyticDB for MySQL cluster. For more information, see Use DMS to connect to AnalyticDB for MySQL.
  2. In the left-side navigation pane, click Instances Connected.
  3. Right-click the cluster and select Account Management.
  4. On the Account Management page, view the permissions.

Use the AnalyticDB for MySQL console to view permissions

Note You can use the AnalyticDB for MySQL console to view permissions only on Data Lakehouse Edition (V3.0) clusters.
Prerequisites
  • An Alibaba Cloud account is used.
  • A Resource Access Management (RAM) user that has the AliyunADBReadOnlyAccess permission is used. For more information about how to grant permissions to a RAM user, see Grant permissions to the RAM user.
Procedure
  1. Log on to the AnalyticDB for MySQL console.
  2. In the upper-left corner of the page, select the region where the cluster resides.
  3. In the left-side navigation pane, click Clusters.
  4. On the Data Lakehouse Edition (V3.0) tab, find the cluster and click its Cluster ID.
  5. In the left-side navigation pane, click Accounts.
  6. On the page that appears, find the account that you want to manage and click Permissions in the Actions column, and then click View Permissions.
  7. Select the permission level to view the permissions of the account.

Edit permissions

Use SQL statements to edit permissions

Note You can use SQL statements to edit permissions on Data Warehouse Edition (V3.0) and Data Lakehouse Edition (V3.0) clusters.
Prerequisites

A database account that has the GRANT OPTION permission is used.

Procedure

Execute the GRANT statement. For more information, see GRANT.

Use DMS to edit permissions

Note You can use DMS to edit permissions on Data Warehouse Edition (V3.0) and Data Lakehouse Edition (V3.0) clusters.
Prerequisites
  • An Alibaba Cloud account is used.
  • A RAM user that has the AliyunADBFullAccess and AliyunServiceRoleForDMS permissions is used. For more information about how to grant permissions to a RAM user, see Grant permissions to the RAM user.
Procedure
  1. Use DMS to connect to an AnalyticDB for MySQL cluster. For more information, see Use DMS to connect to AnalyticDB for MySQL.
  2. In the left-side navigation pane, click Instances Connected.
  3. Right-click the cluster and select Account Management.
  4. On the Account Management page, find the database account that you want to manage and click Edit in the Operation column.
  5. In the Edit User dialog box, click Global Permissions.
  6. In the Permission Type list, select or clear the permissions based on your needs.

Use the AnalyticDB for MySQL console to edit permissions

Note You can use the AnalyticDB for MySQL console to edit permissions only on Data Lakehouse Edition (V3.0) clusters.
Prerequisites
  • An Alibaba Cloud account is used.
  • A RAM user that has the AliyunADBFullAccess permission is used. A database account that is associated with the RAM user and has the GRANT OPTION permission is used.
Procedure
  1. Log on to the AnalyticDB for MySQL console.
  2. In the upper-left corner of the page, select the region where the cluster resides.
  3. In the left-side navigation pane, click Clusters.
  4. On the Data Lakehouse Edition (V3.0) tab, find the cluster and click its Cluster ID.
  5. In the left-side navigation pane, click Accounts.
  6. On the page that appears, find the database account that you want to manage and click Permissions in the Actions column, and then click Edit Permissions.
  7. Configure the Authorization Level and Permission Configuration parameters.
  8. Click the icon 1 and then click OK.