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

Procedure

1. 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'

   

   • 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.

2. You can use either of the following methods to create a role with the read-only permission for a specified database:

   Method 1

   1. Create a role that has the read-only permission on a specified database.

      Log in to FusionInsight Manager and create a role with the read-only permission for a specified database, for example, ck_role. For details, see ClickHouse User and Permission Management.

   2. Granting the read-only permission role to a common user

      GRANT ck_role to user_01 on cluster default_cluster;

   3. 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:

      Log in to FusionInsight Manager and create a user, for example, user_01. For details, see ClickHouse User and Permission Management.

   2. Granting the query permission on a specified database to the created user:

      grant select on default.* to user_01 on cluster default_cluster;

   3. Querying user permissions:

      select * from system.grants where user_name = 'user_01';