ClickHouse User and Permission Management

Updated on 2024-11-29 GMT+08:00

View PDF

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.

**Table 1** Permission management objects supported by ClickHouse
Resource	Integration	Remarks
Database	Yes (level 1)	-
Table	Yes (level 2)	-
View	Yes (level 2)	Same as tables

**Table 2** Resource permission list
Resource	Available Permission	Remarks
Database	CREATE	CREATE DATABASE/TABLE/VIEW/DICTIONARY
Database	DELETE	DROP/TRUNCATE DATABASE/TABLE/VIEW/DICTIONARY
Database	ADMIN	CREATE/SHOW/SELECT/INSERT/ALTER/DROP/TRUNCATE/OPTIMIZE/SYSTEM/dictGet
Table/View/Dictionary	READ	SELECT
Table/View/Dictionary	WRITE	INSERT
Table/View/Dictionary	DELETE	ALTER
Table/View/Dictionary	DELETE	DROP/TRUNCATE

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.

NOTE:

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

Adding the ClickHouse Role

Log in to Manager and choose System > Permission > Role. On the Role page, click Create Role.
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.
NOTE:
- 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.
Select SUPER_USER_GROUP and click OK.
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.
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

Log in to Manager and choose System > Permission > User and click Create.
Select Human-Machine for User Type and set Password and Confirm Password to the password of the user.
NOTE:
- Username: The username cannot contain hyphens (-). Otherwise, the authentication will fail.
- Password: The password cannot contain special characters $, ., and #. Otherwise, the authentication will fail.
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.
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
  NOTE:
  - 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.

NOTE:

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.

Log in to the node where the ClickHouseServer instance is located as user root.
Run the following command to obtain the path of the clickhouse.keytab file:

ls ${BIGDATA_HOME}/FusionInsight_ClickHouse_*/install/FusionInsight-ClickHouse-*/clickhouse/keytab/clickhouse.keytab
Log in to the node where the client is installed as the client installation user.
Run the following command to go to the client installation directory:

cd /opt/client
Run the following command to configure environment variables:

source bigdata_env

For an MRS 3.1.0 cluster with Kerberos authentication enabled, additionally run the following command:

export CLICKHOUSE_SECURITY_ENABLED=true
Run the following command to connect to the ClickHouseServer instance:

If Kerberos authentication is enabled for the current cluster, run the following command:

clickhouse client --host IP address of the node where the ClickHouseServer instance is located --user clickhouse/hadoop.<System domain name> --password clickhouse.keytab path obtained in 2 --port ClickHouse port number --secure

If Kerberos authentication is disabled for the current cluster, run the following command:

clickhouse client --host IP address of the node where the ClickHouseServer instance is located --user clickhouse --port ClickHouse port number
Run the following statement to grant permissions to a database:

In the syntax for granting permissions, DATABASE indicates the name of the target database, and role indicates the target role.

GRANT [ON CLUSTER cluster_name] privilege ON {DATABASE|TABLE} TO {user | role]

For example, grant user testuser the CREATE permission on database t2:

GRANT CREATE ON m2 to testuser;
Run the following commands to grant permissions on the table or view. In the following command, TABLE indicates the name of the table or view to be operated, and user indicates the role to be operated.

Run the following command to grant the query permission on tables in a database:

GRANT SELECT ON TABLE TO user;

Run the following command to grant the write permission on tables in a database:

GRANT INSERT ON TABLE TO user;
Run the following command to exit the client:

quit;