How Do I Grant the Select Permission at the Database Level to ClickHouse Users?

Updated on 2024-05-28 GMT+08:00

View PDF

Procedure

Log in to the node where the ClickHouse client is installed in the MRS cluster and run the following commands:

su - omm

source {Client installation directory}/bigdata_env

kinit Component user (You do not need to run the kinit command for normal clusters.)

clickhouse client --host IP address of the ClickHouse node --port 9000 -m --user clickhouse –password 'Password of the ClickHouse user'
NOTE:
- View the password of the ClickHouse user.
  Log in to FusionInsight Manager and choose Cluster > Services > ClickHouse. Click Instance and click any ClickHouseServer role name. Go to the Dashboard tab page of ClickHouseServer, click the users.xml file in Configuration File area, and view the password of the ClickHouse user.
- There can be security risks if a command contains the authentication password. You are advised to disable the command recording function (history) before running the command.
You can use either of the following methods to create a role with the read-only permission for a specified database:

Method 1
1. Creating a role with the read-only permission for a specified database (the default database is used as an example)
  create role ck_role on cluster default_cluster;
  
  GRANT SELECT ON default.* TO ck_role on cluster default_cluster;
2. Creating a common user
  CREATE USER user_01 on cluster default_cluster IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'password';
3. Granting the read-only permission role to a common user
  GRANT ck_role to user_01 on cluster default_cluster;
4. Viewing user permissions
  show grants for user_01;
  
  select * from system.grants where role_name = 'ck_role';
Method 2

Creating a user with the read-only permission for a specified database
1. Creating a user:
  CREATE USER user_01 on cluster default_cluster IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'password';
1. Granting the query permission on a specified database to the created user:
  grant select on default.* to user_01 on cluster default_cluster;
2. Querying user permissions:
  select * from system.grants where user_name = 'user_01';

Parent topic: ClickHouse FAQ

Previous topic: An Error Is Reported in Logs When the Auxiliary ZooKeeper or Replica Data Is Used to Synchronize Table Data

Next topic: Using DBService

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot