Manage ClickHouse® roles

A role is a collection of permissions that can be assigned to one or several users in a ClickHouse® cluster. Roles allow you to manage privileges and access more efficiently.

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

View roles

Console

SQL

Select the cluster where you want to view roles.
Select the Access control tab and then the Roles tab. This page shows all the roles that exist in the ClickHouse® cluster.

Connect to the cluster with the admin user.
View roles in the cluster:
```
SHOW ROLES
```

Create a role

Console

SQL

To create a role, take the following steps:

Select the cluster where you want to create a user.
Select the Access control tab and then the Roles tab.
Click Create role.
Enter a role name.
(Optional) If you want to assign privileges to the role, enable Privileges and specify grants or revokes.
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.
In Settings, configure additional settings for the role.
Click Submit.

Connect to the cluster with the admin user.

Create a role:

CREATE ROLE <role_name>
// or
CREATE ROLE <role_name> SETTINGS <setting> = <setting_value>

(Optional) Grant privileges to the role:

GRANT SELECT ON <database_name>.* TO <role_name>;

Assign a role to a user

Console

SQL

Select the cluster where you want to assign a role to a user.
Select the Access control tab and then the Users tab.
Find the user you want to assign a role to and click → Edit.
Under Roles, select the roles you want to assign to the user.
Click Save

Connect to the cluster with the admin user.
Assign a role to a user:
```
GRANT <role> TO <username>
```

Edit a role

Console

SQL

Select the cluster where you want to edit a role.
Find the role you want to edit and click on it.
Modify the settings and privileges.
Click Save.

Connect to the cluster with the admin user.
Modify the settings and privileges:
- Edit the role. Use the ALTER ROLE statement, for example:
```
ALTER ROLE <role_name> RENAME TO <new_role_name>
```
- Edit the role settings. Use the ALTER ROLE statement, for example:
```
ALTER ROLE <role_name> SETTINGS <setting> = <new_setting_value>
```
- Grant or revoke privileges. Use the GRANT or REVOKE statements, for example:
```
GRANT SELECT ON <database_name>.* TO <role_name>;
// or
REVOKE SELECT(<column_name>) ON <database_name>.<table_name> FROM <role_name>;
```
  By default, the GRANT statement appends privileges. If you want to replace them, add the WITH REPLACE OPTION clause.
  
  If you want to allow users with this role to grant privileges of the same or lower scope to other users, use WITH GRANT OPTION.

Delete a role

Console

SQL

Select the cluster where you want to delete a role.
Find the role you want to delete, click next to it, and confirm deletion.

This also revokes the role from all the users it was assigned to.

Connect to the cluster with the admin user.
Delete the role:
```
DROP ROLE <role_name>
```
This also revokes the role from all the users it was assigned to.

Manage ClickHouse® roles

View roles

Create a role

Assign a role to a user

Edit a role

Delete a role

See also