How do I create, delete, modify, and query the database accounts in an ApsaraDB for ClickHouse cluster? - ApsaraDB for ClickHouse

This topic describes how to create and delete database accounts in an ApsaraDB for ClickHouse cluster that runs Community-compatible Edition. It also explains how to modify permissions and change passwords for database accounts.

Notes

Only ApsaraDB for ClickHouse Community-compatible Edition clusters support viewing the configuration method of database accounts.
You can go to the Community Edition Instance List, click the target cluster, and click Account Management in the navigation pane on the left. On the Account Management page, you can view the Configuration Method column of the target account.
You can use XML configuration files or SQL statements to configure database accounts. However, you can use only one method to configure database accounts in a cluster.
- XML-supported clusters:
  - Community-compatible Edition clusters of version 20.8 or later that were created before December 27, 2022.
  - Clusters whose engine version is 20.3 or earlier.
- SQL-supported clusters:
  Version 20.8 or later Community-compatible Edition clusters created after December 27, 2022.

Account configuration methods

Configuration Method	Supported Clusters	Account Type	Description
XML	Version 20.8 or later Community-compatible Edition clusters that were created before December 27, 2022. Clusters whose engine version is 20.3 or earlier.	Standard account	You can create and manage standard accounts in the ApsaraDB for ClickHouse console or by calling API operations. You can create up to 500 standard accounts in a cluster. You can grant standard accounts the DML and DDL permissions and specify resources that can be accessed by the accounts.
SQL	Clusters that run Community-compatible Edition version 20.8 or later and were created after December 27, 2022.	Privileged account	You can create and manage privileged accounts in the ApsaraDB for ClickHouse console or by calling API operations. You can create only one privileged account in a cluster and use the privileged account to manage all standard accounts and databases in the cluster. A privileged account lets you manage more permissions in a fine-grained manner as needed. For example, you can grant each standard account the permissions to query specific tables.
SQL		Standard account	You can create and manage standard accounts in the ApsaraDB for ClickHouse console, by calling API operations, or using SQL statements. For more information about how to use a privileged account to create standard accounts using SQL statements, see CREATE USER. You can create up to 500 standard accounts in a cluster. By default, you can use a standard account to only log on to databases. For more information about how to use a privileged account to grant other permissions to standard accounts, see GRANT. You cannot use a standard account to create or manage other accounts.

Create a database account

Log on to the ApsaraDB for ClickHouse console.
In the upper-left corner of the page, select the region where the cluster is deployed.
On the Clusters page, select Clusters of Community-compatible Edition and click the ID of the target cluster.
In the navigation pane on the left, click Account Management.
In the upper-right corner of the Account Management page, click Create Account.

In the Create Account panel, configure the following parameters as prompted.

Version 20.8 or later

Parameter	Description
Database Account	The name of the database account. The name must meet the following requirements: The name must be unique. The name can contain lowercase letters, digits, or underscores (_). The name must start with a lowercase letter and end with a lowercase letter or a digit. The name must be 2 to 64 characters in length.
Account Type	The type of the database account. Valid values: Privileged Account Standard Account Note By default, you can use a standard account to only log on to databases. You can use a privilege account to grant permissions to standard accounts using SQL statements. For more information, see GRANT.
Password	The password of the database account. The password must meet the following requirements: The password must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters. The following special characters are supported: !@#$%^&*()_+-= The password must be 8 to 32 characters in length.
Confirm Password	The same password that you entered in the Password field.
Description	The description of the database account. The description must meet the following requirements: The value can be up to 256 characters in length. The description cannot start with http:// or https://.

Version 20.3

Parameter	Description
Database Account	The name of the database account. The name must meet the following requirements: The name must be unique. The name can contain lowercase letters, digits, or underscores (_). The name must start with a lowercase letter and end with a lowercase letter or a digit. The name must be 2 to 64 characters in length.
Authorized Access Scope	The resources that can be accessed by the database account. Valid values: All Databases and Dictionaries. Partial Databases and Dictionaries. You can select the databases or dictionaries based on your requirements, and then click the button or the button to grant or revoke permission.
DML Permission	Specifies whether to grant write permissions. Valid values: Read, Write, and Set Permissions: You can perform read, write, and set operations on the authorized databases and dictionaries. Read and Set Permissions: You can perform only read and set operations on the authorized databases and dictionaries. You cannot write data to the authorized databases and dictionaries.
DDL Permission	Specifies whether to grant DDL permissions. Valid values: Enable DDL. Disable DDL.
Password	The password of the database account. The password must meet the following requirements: The password must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters. The following special characters are supported: !@#$%^&*()_+-= The password must be 8 to 32 characters in length.
Confirm Password	The same password that you entered in the Password field.
Description	The description of the database account. The description must meet the following requirements: The value can be up to 256 characters in length. The description cannot start with http:// or https://.

Click OK.

Modify permissions

Using SQL to modify permissions

Note

This operation is applicable only to the clusters whose database accounts are configured using SQL statements. This applies to version 20.8 or later Community-compatible Edition clusters that were created after December 27, 2022.
For the following clusters, you must use the ApsaraDB for ClickHouse console to modify permissions. Even if you execute the GRANT operation, the authorization will not take effect. In addition, the SHOW GRANTS command may display administrator permissions, but the actual permissions are subject to the console.
- Community-compatible Edition clusters whose engine version is 20.8 or later and that were created before December 27, 2022.
- Clusters whose engine version is 20.3 or earlier.

Use a privileged account to log on to the database that you want to manage. For more information, see Connect to a database.
Execute an SQL statement to grant the required permissions to a specific standard account.
By default, you can use a standard account to only log on to databases. For more information about how to use a privileged account to grant other permissions to standard accounts, see GRANT.

Modifying permissions in the console

Note

This operation is applicable only to the clusters whose database accounts are configured using XML configuration files. The clusters refer to the following types of clusters:
- Community-compatible Edition clusters of version 20.8 or later that were created before December 27, 2022.
- Clusters whose engine version is 20.3 or earlier.
For Community-compatible Edition clusters that run version 20.8 or later and were created after December 27, 2022, you must use SQL statements to modify permissions.

Log on to the ApsaraDB for ClickHouse console.
In the upper-left corner of the page, select the region where the cluster is deployed.
On the Clusters page, select Clusters of Community-compatible Edition and click the ID of the target cluster.
In the navigation pane on the left, click Account Management.
In the Actions column of the target database account, click Modify Permissions.
In the Modify Permissions panel, modify the Authorized Access Scope, DML Permission, and DDL Permission of the database account based on your requirements.
Click OK.

Change a password

Log on to the ApsaraDB for ClickHouse console.
In the upper-left corner of the page, select the region where the cluster is deployed.
On the Clusters page, select Clusters of Community-compatible Edition and click the ID of the target cluster.
In the navigation pane on the left, click Account Management.
In the Actions column of the target database account, click Change Password.
In the Change Password panel, enter the new password twice.
Click OK.

Delete a database account

Log on to the ApsaraDB for ClickHouse console.
In the upper-left corner of the page, select the region where the cluster is deployed.
On the Clusters page, select Clusters of Community-compatible Edition and click the ID of the target cluster.
In the navigation pane on the left, click Account Management.
In the Actions column of the target database account, click Delete.
In the Delete Account dialog box, click OK.
Warning
Exercise caution when you delete an account. You cannot restore an account after it is deleted.