1. Home
  2. Knowledge Base
  3. ClickHouse DBA
  4. Multiple settings profiles for a ClickHouse user

Multiple settings profiles for a ClickHouse user

In ClickHouse, a group or collection of settings and constraints can be stored as a settings profile, which can then be applied to a user role or an individual user. We have already seen how to assign multiple user roles to a single user in ClickHouse (https://chistadata.com/knowledge-base/assigning-multiple-roles-to-a-single-user-in-clickhouse/). Similarly, it is possible to assign multiple settings profiles to a ClickHouse user.

Every ClickHouse role will have a default settings profile assigned to it. We can also specify the settings profile of our choice while creating the role. So to assign multiple settings profiles to a user, we can use the following approach.

 

  • Create two different settings profiles without any overlapping setting
  • Create two different user roles and assign one settings profile per user role
  • Grant read permission to the table
  • Create a new user and assign both the newly created roles as default

Pre-requisites

 

Steps

  • Create the settings profile using the below SQL statements. The first profile will limit the number of rows in the result set and the second profile will limit the number of columns that will be read from a table using any query.
CREATE SETTINGS PROFILE max_res_profiles SETTINGS max_result_rows = 10;
CREATE SETTINGS PROFILE max_cols_profiles SETTINGS max_columns_to_read = 10;

 

  • Create user roles inheriting the newly created settings profiles
CREATE ROLE multiple_settings_profile_rows SETTINGS PROFILE max_res_profiles;
CREATE ROLE multiple_settings_profile_cols SETTINGS PROFILE max_cols_profiles;

 

  • Grant read access to any of the database in ClickHouse server
GRANT SELECT ON datasets.* TO multiple_settings_profile_rows;
GRANT SELECT ON datasets.* TO multiple_settings_profile_cols;

 

  • Create a user inheriting these newly created user roles as default
CREATE USER test_multiple_settings IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456'
DEFAULT ROLE multiple_settings_profile_rows, multiple_settings_profile_cols;

 

  • Login to the clickhouse-client as the newly created user
clickhouse-client  -u test_multiple_settings --password '123456'

 

  • Run the following query and an exception is expected since we limited the number of columns to process in one of our settings
SELECT *
FROM datasets.cell_towers
LIMIT 10

Query id: 68d66bd7-80ca-4d77-aeac-53155b33508b


0 rows in set. Elapsed: 0.004 sec. 

Received exception from server (version 22.6.3):
Code: 161. DB::Exception: Received from localhost:9000. DB::Exception: Limit for number of columns to read exceeded. Requested: 14, maximum: 10. (TOO_MANY_COLUMNS)

 

  • Run the following query and an exception is expected since we limited the number of rows in the result set based on one of our settings
SELECT
    radio,
    cell
FROM datasets.cell_towers

Query id: 0fc1ed7b-da6e-472e-9e58-dbf554bb01ca


0 rows in set. Elapsed: 0.009 sec. 

Received exception from server (version 22.6.3):
Code: 396. DB::Exception: Received from localhost:9000. DB::Exception: Limit for result exceeded, max rows: 10.00, current rows: 65.50 thousand. (TOO_MANY_ROWS_OR_BYTES)

 

  • Run the following query and the results should be displayed properly since they are valid based on both the settings assigned to this user role
SELECT
    radio,
    cell
FROM datasets.cell_towers
LIMIT 10

Query id: 7996f0c3-4d8b-4a56-9749-710110961d65

┌─radio─┬──cell─┐
│ CDMA  │ 25455 │
│ CDMA  │ 25452 │
│ CDMA  │ 25451 │
│ CDMA  │ 25456 │
│ CDMA  │ 19471 │
│ CDMA  │ 19811 │
│ CDMA  │ 19863 │
│ CDMA  │ 19472 │
│ CDMA  │  1953 │
│ CDMA  │     0 │
└───────┴───────┘

10 rows in set. Elapsed: 0.003 sec.

 

 

References:

https://clickhouse.com/docs/en/operations/access-rights/#settings-profiles-management

Was this article helpful?

Related Articles

CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES

In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.