How to create, retrieve, update, and delete accounts in ClickHouse - ApsaraDB for ClickHouse

This topic describes how to create, modify permissions, change passwords, and delete database accounts in a Community-compatible Edition cluster.

Usage notes

You can only view the database account configuration method on ApsaraDB for ClickHouse Community-compatible Edition clusters.

On the Community-compatible Edition instance list page, click the ID of the target cluster. On the Cluster Information page, click Account Management in the left-side navigation pane. On the Account management page, find the target account and view its Configuration Method.
The account configuration method is either XML or SQL. A cluster supports only one of these methods.
- The following clusters support XML-based configuration:
  - Community-compatible Edition clusters (version 20.8 or later) created before December 27, 2022.
  - Clusters of version 20.3 or earlier.
- The following clusters support SQL-based configuration:
  
  Community-compatible Edition clusters (version 20.8 or later) created after December 27, 2022.

Configuration methods

Configuration method	Applicable clusters	Account type	Description
XML	Community-compatible Edition clusters (version 20.8 or later) created before December 27, 2022. Clusters of version 20.3 or earlier.	Standard account	You can create and manage accounts only in the console or by using the API. You can create a maximum of 500 standard accounts in a cluster. You can grant DML and DDL permissions and specify the authorized access scope for accounts.
SQL	Community-compatible Edition clusters (version 20.8 or later) created after December 27, 2022.	Privileged account	You can create and manage the account only in the console or by using the API. You can create only one privileged account in a cluster. The privileged account can manage all standard accounts and databases. This account provides more options for customized, fine-grained permission management, such as assigning query permissions on different tables to different accounts.
SQL		Standard account	You can create and manage accounts in the console, by using the API, or by running SQL statements. For information about how to use a privileged account to create a standard account by running an SQL statement, see CREATE USER. You can create a maximum of 500 standard accounts in a cluster. By default, a standard account only has permission to log on to the database. To grant other permissions using a privileged account, see GRANT. A standard account cannot be used 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 your cluster is deployed.
On the Clusters page, go to the Clusters of Community-compatible Edition tab and click the ID of the target cluster.
In the left-side navigation pane, 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.

Version 20.8 or later

Parameter	Description
Database account	The database account name must meet the following requirements: The name must be unique. It must consist of lowercase letters, digits, and underscores (_). It must start with a lowercase letter and end with a lowercase letter or a digit. It 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, a standard account only has permission to log on to the database. You can use a privileged account to grant other permissions to a standard account by running an SQL statement. For more information, see GRANT.
Password	The password for the database account must meet the following requirements: It must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters. The following special characters are supported: !@#$%^&*()_+-=. It must be 8 to 32 characters in length.
Confirm password	Confirm the password.
Description	The database account description must meet the following requirements: The length can be 0 to 256 characters. It cannot start with http:// or https://.

Version 20.3

Parameter	Description
Database account	The database account name must meet the following requirements: The name must be unique. It must consist of lowercase letters, digits, and underscores (_). It must start with a lowercase letter and end with a lowercase letter or a digit. It must be 2 to 64 characters in length.
Authorized access scope	The access scope for the database account. All Databases and Dictionaries Partial Databases and Dictionaries Select the required databases or dictionaries, and then click the button or button to add or remove authorization.
DML permission	Specifies whether to grant write permissions. Read, Write, and Set Permissions: The account can read, write, and configure the authorized databases and dictionaries. Read and Set Permissions: The account can only read and configure the authorized databases and dictionaries, but cannot write data.
DDL permission	Specifies whether to grant DDL permissions. Enable DDL. Disable DDL.
Password	The password for the database account must meet the following requirements: It must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters. The following special characters are supported: !@#$%^&*()_+-=. It must be 8 to 32 characters in length.
Confirm password	Confirm the password.
Description	The database account description must meet the following requirements: The length can be 0 to 256 characters. It cannot start with http:// or https://.

Click OK.

Modify permissions

SQL

Note

This operation applies only to clusters that use the SQL configuration method. That is, Community-compatible Edition clusters (version 20.8 or later) created after December 27, 2022.
For the following cluster versions, modify permissions in the console. Running the GRANT statement has no effect. Although the SHOW GRANTS command might display administrator permissions, the actual permissions are those configured in the console.
- Community-compatible Edition clusters (version 20.8 or later) created before December 27, 2022.
- Clusters of version 20.3 or earlier.

Use a privileged account to log on to the database. For more information, see Connect to a database.
Run a grant statement.

By default, a standard account only has permission to log on to the database. For information about how a privileged account grants other permissions to a standard account, see GRANT.

Console

Note

This operation applies only to the following clusters whose account configuration method is XML:
- Community-compatible Edition clusters (version 20.8 or later) created before December 27, 2022.
- Clusters of version 20.3 or earlier.
For Community-compatible Edition clusters (version 20.8 or later) created after December 27, 2022, you must use SQL to modify permissions.

Log on to the ApsaraDB for ClickHouse console.
In the upper-left corner of the page, select the region where your cluster is deployed.
On the Clusters page, go to the Clusters of Community-compatible Edition tab and click the ID of the target cluster.
In the left-side navigation pane, click Account Management.
In the Actions column of the target database account, click Modify Permission.
In the Modify Permission panel, modify the Authorized Access Scope, DML Permission, and DDL Permission of the database account as required.
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 your cluster is deployed.
On the Clusters page, go to the Clusters of Community-compatible Edition tab and click the ID of the target cluster.
In the left-side navigation pane, 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 your cluster is deployed.
On the Clusters page, go to the Clusters of Community-compatible Edition tab and click the ID of the target cluster.
In the left-side navigation pane, click Account Management.
In the Actions column of the target database account, click Delete.
In the Delete Account dialog box, click OK.

Warning
Deleted accounts cannot be restored.