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.
• The ON CLUSTER statement has been used to create a database or table in the cluster 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.
   1. Run the following command to go to the client installation directory:

      cd /opt/Client installation directory

   2. Run the following command to configure environment variables:

      source bigdata_env

   3. Run the following commands to connect to ClickHouse. The commands require the permission to create ClickHouse tables. For details about how to obtain the permission, see Adding the ClickHouse Role.

      Cluster with Kerberos authentication enabled:

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

      Enter the password.

      Cluster with Kerberos authentication disabled:

      clickhouse client --host ClickHouse instance IP address --userUsername --multiline --port ClickHouse port number

      

      • The default user is automatically used in the command for clusters that do not require Kerberos authentication. To specify other users, you can add a management user by using the function provided in the ClickHouse open source community, or use FusionInsight Manager to add a user with the ClickHouse permission.
      • For the default user, run the following command:

        clickhouse client --host ClickHouse instance IP address --user default --password --port ClickHouse port number

        Enter the password.

      • To obtain the IP address of the ClickHouse instance, log in to FusionInsight Manager, choose Cluster > Services > ClickHouse, and click the Instance tab.

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.