ClickHouse User and Permission Management

User Permission Model

ClickHouse user permission management enables unified management of users, roles, and permissions on each ClickHouse instance in the cluster. You can use the permission management module of the Manager UI to create users, create roles, and bind the ClickHouse access permissions. User permissions are controlled by binding roles to users.

Resource management: Table 1 lists the resources supported by ClickHouse permission management.

Resource permissions: Table 2 lists the resource permissions supported by ClickHouse.

Prerequisites

• The ClickHouse and Zookeeper services are running properly.
• When creating a database or table in the cluster, the ON CLUSTER statement is used to ensure that the metadata of the database and table on each ClickHouse node is the same.

After the permission is granted, it takes about 1 minute for the permission to take effect.

Adding the ClickHouse Role

1. Log in to Manager and choose System > Permission > Role. On the Role page, click Create Role.

2. On the Create Role page, specify Role Name. In the Configure Resource Permission area, click the cluster name. On the service list page that is displayed, click the ClickHouse service.

   Determine whether to create a role with the ClickHouse administrator permissions based on service requirements.

   

   • The ClickHouse administrator has all the database operation permissions except the permissions to create, delete, and modify users and roles.
   • Only the built-in user clickhouse of ClickHouse has the permission to manage users and roles.
   • If yes, go to 3.
   • If no, go to 4.

3. Select SUPER_USER_GROUP and click OK.
4. Click ClickHouse Scope. The ClickHouse database resource list is displayed. If you select create, the role has the create permission on the database.

   Determine whether to grant the permission based on the service requirements.

   • If yes, click OK.
   • If no, go to 5.

5. Click the resource name and select the Database resource name to be operated. On the displayed page, select READ (SELECT permission) or WRITE (INSERT permission) based on service requirements, and click OK.

Adding a User and Binding the ClickHouse Role to the User

1. Log in to Manager and choose System > Permission > User and click Create.
2. Select Human-Machine for User Type and set Password and Confirm Password to the password of the user.
   

   • Username: The username cannot contain hyphens (-). Otherwise, the authentication will fail.
   • Password: The password cannot contain special characters $, ., and #. Otherwise, the authentication will fail.

3. In the Role area, click Add. In the displayed dialog box, select a role with the ClickHouse permission and click OK to add the role. Then, click OK.

4. Log in to the node where the ClickHouse client is installed and use the new username and password to connect to the ClickHouse service.
   • Run the following command to go to the client installation directory:

     cd /opt/Client installation directory

   • Run the following command to configure environment variables:

     source bigdata_env

   • If Kerberos authentication is enabled for the current cluster, run the following command to authenticate the current user. The user must have the permission to create ClickHouse tables. Therefore, you need to bind the corresponding role to the user. For details, see Adding the ClickHouse Role. If Kerberos authentication is disabled for the current cluster, skip this step.

     For an MRS 3.1.0 cluster, run the export CLICKHOUSE_SECURITY_ENABLED=true command first.

     kinit User added in 1

   • Log in to the system as the new user.

     Cluster with Kerberos authentication disabled:

     clickhouse client --host IP address of the ClickHouse instance --multiline --port ClickHouse port number --secure

     Cluster with Kerberos authentication enabled:

     clickhouse client --host IP address of the ClickHouse instance--user Username --password --port 9440 --secure

     Enter the user password.

     

     The user in normal mode is the default user, or you can create an administrator using the open source capability provided by the ClickHouse community. You cannot use the users created on FusionInsight Manager.

Granting Permissions Using the Client in Abnormal Scenarios

By default, the table metadata on each node of the ClickHouse cluster is the same. Therefore, the table information on a random ClickHouse node is collected on the permission management page of Manager. If the ON CLUSTER statement is not used when databases or tables are created on some nodes, the resource may fail to be displayed during permission management, and permissions may not be granted to the resource. To grant permissions on the local table on a single ClickHouse node, perform the following steps on the background client.

The following operations are performed based on the obtained roles, database or table names, and IP addresses of the node where the corresponding ClickHouseServer instance is located.

• You can log in to FusionInsight Manager and choose Cluster > Services > ClickHouse > Instance to obtain the service IP address of the ClickHouseServer instance.
• The default system domain name is hadoop.com. Log in to FusionInsight Manager and choose System > Permission > Domain and Mutual Trust. The value of Local Domain is the system domain name. Change the letters to lowercase letters when running a command.