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. The topic also describes how to modify the permissions and change the passwords of the database accounts.

Usage notes

You can view the configuration methods of database accounts only in ApsaraDB for ClickHouse clusters that run Community-compatible Edition.
On the Clusters of Community-compatible Edition tab, click the cluster that you want to manage. In the left-side navigation pane of the page that appears, click Account Management. On the User Account tab, view the information in the Configuration Method column of the 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 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
- SQL-supported clusters:
  Community-compatible Edition clusters whose engine version is 20.8 or later and that were created after December 27, 2022

Account configuration methods

Configuration method	Supported cluster	Account type	Description
XML	Community-compatible Edition clusters whose engine version is 20.8 or later and were created before December 27, 2022 Clusters whose engine version is 20.3 or earlier	Standard account	You can create and manage the 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	Community-compatible Edition clusters whose engine version is 20.8 or later and that were created after December 27, 2022	Privileged account	You can create and manage the 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 allows you to manage more permissions in a fine-grained manner based on your business requirements. 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 by using SQL statements. For more information about how to use a privileged account to create a standard account by executing an SQL statement, 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 a standard account, see GRANT. You cannot use a standard account to create or manage other accounts.

Creates a database account

Log on to the ApsaraDB for ClickHouse console.
In the top navigation bar, select the region in which the cluster that you want to manage resides.
On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the cluster that you want to manage.
In the left-side navigation pane, click Account Management.
In the upper-right corner of the page that appears, click Create Account.

In the Create Account panel, configure the required parameters. The following tables describe the parameters required for creating a database account in clusters of different engine versions.

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, and underscores (_). The name must start with a lowercase letter and end with a lowercase letter or 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 by 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 description must be up to 256 characters in length or an empty string. 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, and underscores (_). The name must start with a lowercase letter and end with a lowercase letter or 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 After you select the required databases or dictionaries, click the or icon to add or remove authorized databases and dictionaries.
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 description must be up to 256 characters in length or an empty string. The description cannot start with http:// or https://.

Click OK.

Modify the permissions of a database account in the ApsaraDB for ClickHouse console or by using SQL statements

SQL statements

Note

This operation is applicable only to the clusters whose database accounts are configured by using SQL statements. The clusters refer to Community-compatible Edition clusters whose engine version is 20.8 or later and that were created after December 27, 2022.

Use a privileged account to log on to the database that you want to manage. For more information, see Database connectivity.
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 a standard account, see GRANT.

ApsaraDB for ClickHouse console

Note

This operation is applicable only to the clusters whose database accounts are configured by using XML configuration files. The clusters refer to the following types of clusters:

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

Log on to the ApsaraDB for ClickHouse console.
In the top navigation bar, select the region in which the cluster that you want to manage resides.
On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the cluster that you want to manage.
In the left-side navigation pane, click Account Management.
Find the database account that you want to manage and click Modify Permissions in the Actions column.
In the Modify Permissions panel, configure the Authorized Access Scope, DML Permissions, and DDL Permissions parameters of the database account based on your business requirements.
Click OK.

Change the password of a database account

Log on to the ApsaraDB for ClickHouse console.
In the top navigation bar, select the region in which the cluster that you want to manage resides.
On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the cluster that you want to manage.
In the left-side navigation pane, click Account Management.
Find the database account whose password you want to change and click Change Password in the Actions column.
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 top navigation bar, select the region in which the cluster that you want to manage resides.
On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the cluster that you want to manage.
In the left-side navigation pane, click Account Management.
Find the database account that you want to delete and click Delete in the Actions column.
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.