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
- ClickHouse server
- A user account with access control and user management permissions
- Optional – Cell Tower dataset loaded in Clickhouse (https://clickhouse.com/docs/en/getting-started/example-datasets/cell-towers). Users may use any table of their choice in their ClickHouse server.
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