Manage ClickHouse® users

This page explains how to create and manage users in your Managed ClickHouse® cluster. A user or user account is a special account that allows you to interact with databases in the cluster. You can grant users different permissions through roles and thus efficiently control access to the data.

Users in ClickHouse® are different from DoubleCloud users. Users in ClickHouse® only exist in a specific cluster and can perform specific actions with the cluster resources. Sometimes they can also be referred to as database users or service users.

You can create and manage users both in the DoubleCloud console and via SQL. However, some parameters and settings may only be available via SQL.

View users and their roles

Console

SQL

In the console, select the cluster where you want to view users.
Select the Access control tab and then the Users tab. This page shows users in the ClickHouse® cluster and their roles.

Connect to the cluster with the admin user.
Get a list of users:
```
SHOW USERS;
```
View the user's roles and privileges:
```
SHOW GRANTS FOR <username>
```

Create a user

Console

SQL

To create a user, take the following steps:

In the console, select the cluster where you want to create a user.
Select the Access control tab and then the Users tab.
Click Create user.
Enter a username, select the auth type, and enter a password.
Username and password requirements
- A username must be between 2 and 128 characters long, can contain uppercase and lowercase letters (a-Z), numbers, and dashes, and can't start with a number.
- A password must be between 8 and 128 characters long, must contain uppercase and lowercase letters, numbers, and special characters (-!@#$%^&*_=+:;'\"\\|/?,.`~§±()[]{}<>"), and can only begin with a letter.
When you create a user in the console, you can choose from the following auth types: the SHA256-encoded password, double SHA-1-encoded password, or bcrypt-encoded password. If you need to use a different auth type, create a user via SQL.
(Optional) In Roles, select the predefined roles you want to assign to the new user.

If there are no roles in the cluster, you can skip this step now, create a role after creating the user, and then assign it to the user.
(Optional) If you want to configure permissions for this user individually, enable Privileges and specify grants or revokes.

Tip

It's recommended to grant privileges and apply settings to ClickHouse® users through roles rather than individually. With roles, you can grant a set of privileges to one or several users at the same time. It makes it easier to manage permissions and control access.

Only grant privileges individually if you need to do so.
Privilege reference
- Action: Grant permissions with this privilege or revoke them.
  
  Tip
  
  Revokes allow you to withdraw privileges the user already has through a role or directly. You can also revoke a part of an existing privilege or grant a certain privilege and revoke its part.
- Access type: Access type that's granted or revoked.
- Databases: Apply the privilege to all databases or one specific database.
- Tables: Apply the privilege to all tables or one specific table.
- Columns Apply the privilege to all columns or specific columns.
- Grant option: Give the permission to execute the GRANT query and grant privileges of the same or lower scope.
(Optional) In Settings, configure additional settings for the user.
Click Submit.

Connect to the cluster with the admin user.

Create a user:

CREATE USER <username> IDENTIFIED WITH sha256_password BY '<user_password>';

Change a password

Console

In the console, select the cluster where you want to change a user password.
Select the Access control tab and then the Users tab.
Find the user and click → Edit.
Change the password and, if needed, the auth type.
Click Submit.

Edit a user

Console

SQL

To change user settings:

In the console, select the cluster where you want to edit a user.
Select the Access control tab and then the Users tab.
Find the user and click → Edit.
Modify the settings and privileges.

Warning

If the user was created via SQL, some properties may not be available in the console. To change them, edit the user via SQL.
Click Save.

Connect to the cluster with the admin user.
Modify the settings and privileges:
- Edit the user. Use the ALTER USER statement, for example:
```
ALTER USER <username> RENAME TO <new_username>
```
- Edit the user's settings. Use the ALTER USER statement, for example:
```
ALTER USER <username> SETTINGS <list_of_ClickHouse_settings>;
```
- Assign or remove roles. Use the GRANT or REVOKE statements, for example:
```
GRANT <role> TO <username>
// or
REVOKE <role>
```
  By default, the GRANT statement appends roles. If you want to replace them, add the WITH REPLACE OPTION clause.
- Grant or revoke privileges. Use the GRANT or REVOKE statements, for example:
```
GRANT SELECT ON <database_name>.* TO <username>;
// or
REVOKE SELECT(<column_name>) ON <database_name>.<table_name> FROM <username>;
```
  By default, the GRANT statement appends privileges. If you want to replace them, add the WITH REPLACE OPTION clause.
  
  If you want to allow the user to grant privileges of the same or lower scope to other users, use WITH GRANT OPTION.
  
  Tip
  
  Roles and privileges are related, but they serve different purposes. When you give privileges to a specific user, you grant them certain permissions individually. With roles, you can grant a set of privileges to one or several users at the same time.
  
  Granting permissions through roles makes it easier to manage them than through assigning them individually.

Delete a user

Console

SQL

In the console, select the cluster where you want to delete a user.
Find the user, click → Delete, and confirm deletion.

Connect to the cluster with the admin user.
Delete the user:
```
DROP USER <username>;
```

Manage ClickHouse® users

View users and their roles

Create a user

Change a password

Edit a user

Delete a user

See also