AnalyticDB for MySQL uses a hierarchical permission model that controls what each database account can do at the cluster, database, table, or column level. This topic explains the permission levels, maps SQL operations to the permissions they require, and shows how to view and edit permissions.
Permission levels
Permissions are granted at four levels.
| Level | Scope |
|---|---|
| GLOBAL | Entire cluster |
| DB | A single database |
| TABLE | A single table |
| COLUMN | One or more columns in a table |
Example: To allow an account to read only one column, grant the SELECT permission at the COLUMN level:
GRANT SELECT (customer_id) ON customer TO 'test321'Operations and required permissions
The following table maps each SQL operation to the permissions it requires and the levels at which those permissions can be granted.
| Operation | Required permissions | Supported levels |
|---|---|---|
| SELECT | SELECT | GLOBAL, DB, TABLE, COLUMN |
| INSERT | INSERT | GLOBAL, DB, TABLE, COLUMN |
| INSERT...SELECT...FROM... | INSERT, SELECT | GLOBAL, DB, TABLE, COLUMN |
| UPDATE | UPDATE | GLOBAL, DB, TABLE, COLUMN |
| DELETE | DELETE | GLOBAL, DB, TABLE |
| TRUNCATE TABLE | DROP | GLOBAL, DB, TABLE |
| ALTER TABLE | ALTER, INSERT, CREATE | GLOBAL, DB, TABLE |
| CREATE DATABASE | CREATE | GLOBAL |
| CREATE TABLE | CREATE | GLOBAL, DB, TABLE |
| SHOW CREATE TABLE | SELECT | GLOBAL, DB, TABLE |
| DROP DATABASE | DROP | GLOBAL, DB |
| DROP TABLE | DROP | GLOBAL, DB, TABLE |
| CREATE VIEW | CREATE VIEW, SELECT | GLOBAL, DB, TABLE |
| DROP VIEW | DROP | GLOBAL, DB, TABLE |
| SHOW CREATE VIEW | SHOW VIEW, SELECT | GLOBAL, DB, TABLE |
| CREATE USER / DROP USER / RENAME USER | CREATE_USER | GLOBAL |
| SET PASSWORD | SUPER | GLOBAL |
| GRANT / REVOKE | GRANT | GLOBAL |
To run CREATE VIEW REPLACE, the account must also have the DROP permission.View permissions
SQL statements and Data Management (DMS) work on both Data Warehouse Edition (V3.0) and Data Lakehouse Edition (V3.0) clusters. The AnalyticDB for MySQL console is available for Data Lakehouse Edition (V3.0) clusters only.
Use SQL statements
Run the SHOW GRANTS statement to view the permissions of a specific account.
Use DMS
Prerequisites
Before you begin, ensure that you have:
An Alibaba Cloud account, or a Resource Access Management (RAM) user with the AliyunADBReadOnlyAccess permission. For instructions, see Grant permissions to the RAM user.
Steps
In the left-side navigation pane, click Instances Connected.
Right-click the cluster and select Account Management.
On the Account Management page, view the permissions.
Use the AnalyticDB for MySQL console
This method is available for Data Lakehouse Edition (V3.0) clusters only.
Prerequisites
Before you begin, ensure that you have:
An Alibaba Cloud account, or a RAM user with the AliyunADBReadOnlyAccess permission. For instructions, see Grant permissions to the RAM user.
Steps
Log on to the AnalyticDB for MySQL console.
In the upper-left corner, select the region where the cluster resides.
In the left-side navigation pane, click Clusters.
On the Data Lakehouse Edition (V3.0) tab, find the cluster and click its Cluster ID.
In the left-side navigation pane, click Accounts.
Find the account, click Permissions in the Actions column, and then click View Permissions.
Select the permission level to view the account's permissions.
Edit permissions
SQL statements and DMS support both Data Warehouse Edition (V3.0) and Data Lakehouse Edition (V3.0) clusters. The console supports Data Lakehouse Edition (V3.0) clusters only.
Use SQL statements
Prerequisites
Before you begin, ensure that you have:
A database account with the GRANT OPTION permission.
Steps
Run the GRANT statement to grant permissions.
Use DMS
Prerequisites
Before you begin, ensure that you have:
An Alibaba Cloud account, or a RAM user with the AliyunADBFullAccess and AliyunServiceRoleForDMS permissions. For instructions, see Grant permissions to the RAM user.
Steps
In the left-side navigation pane, click Instances Connected.
Right-click the cluster and select Account Management.
On the Account Management page, find the account and click Edit in the Operation column.
In the Edit User dialog box, click Global Permissions.
In the Permission Type list, select or clear permissions as needed.
Use the AnalyticDB for MySQL console
This method is available for Data Lakehouse Edition (V3.0) clusters only.
Prerequisites
Before you begin, ensure that you have:
An Alibaba Cloud account, or a RAM user with the AliyunADBFullAccess permission.
A database account associated with the RAM user that has the GRANT OPTION permission.
Steps
Log on to the AnalyticDB for MySQL console.
In the upper-left corner, select the region where the cluster resides.
In the left-side navigation pane, click Clusters.
On the Data Lakehouse Edition (V3.0) tab, find the cluster and click its Cluster ID.
In the left-side navigation pane, click Accounts.
Find the account, click Permissions in the Actions column, and then click Edit Permissions.
Configure the Authorization Level and Permission Configuration parameters.
Click the icon
and then click OK.