Assigning Multiple Roles to a Single User in ClickHouse

ClickHouse has a feature-rich SQL driven access control and account management based on Role-Based Access control approach. The pre-defined roles determine the permissions and the level of access to the database resources. The database users are assigned one or more roles based on the business requirements. In this article, we will look at creating ClickHouse users and assign them one or more roles with different levels of access.

Pre-requisites

• ClickHouse server
• User account with access control and user management permissions

Approach

• Create two different tables and insert some test data
• Create two different user roles (role_1 and role_2)
• Provide read access to the first table for role_1
• Provide read access to the second table for role_2
• Create a new user 
• Flush the default role assigned to the user
• Try reading the data via new user
• Grant role_1 and try reading the data from table 1
• Grant role_2 and  try reading the data from table 2

Table Creation

Let’s create a database called RBAC and create two tables with just one column and using Log table engine and insert a row of data in to them. Run the below commands as admin user with necessary privileges unless specified. 

CREATE DATABASE RBAC; 

CREATE TABLE RBAC.t1 
(col1 String)
Engine = Log;

CREATE TABLE RBAC.t2
(col1 String)
Engine = Log;

INSERT INTO RBAC.t1 VALUES ('a'); 
INSERT INTO RBAC.t2 VALUES ('b');

User Role Creation and granting access

The below SQL statements will create two different roles and first role is provided read access to the first table and second role to the second table.

CREATE ROLE  rbac_select_1; 
GRANT SELECT  ON RBAC.t1 TO rbac_select_1;

CREATE ROLE  rbac_select_2;
GRANT SELECT  ON RBAC.t2 TO rbac_select_2;

User creation

Let’s create a user to whom the roles will be assigned.

CREATE USER clickhouse_rbac IDENTIFIED WITH PLAINTEXT_PASSWORD BY '123456';

This user will have the default role inherited. To remove all the roles that are assigned to this user, we have to execute the following command.

SET DEFAULT ROLE NONE TO clickhouse_rbac;

Testing the role based access

Login to the ClickHouse client with the newly created user credentials.

Try reading from one of the tables and the following exception is expected.

Grant new role to the user and assign it as the default role

GRANT rbac_select_1 TO clickhouse_rbac;

SET DEFAULT ROLE rbac_select_1 TO clickhouse_rbac;

Run the above command as the admin user with necessary privileges. After the successful execution, switch back to the newly created user and repeat the select query.

Note: In case there is an exception stating insufficient privileges , exit the clickhouse-client and login back again as the new user and execute the query.

Set multiple roles

To set multiple roles simultaneously to the same user, grant the second role to the user and set both the roles as default role for the user.

GRANT rbac_select_2 TO clickhouse_rbac;

SET DEFAULT ROLE rbac_select_1, rbac_select_2 TO clickhouse_rbac;

Now try reading the data from the tables, for which we didn’t have access earlier.

Conclusion

• ClickHouse assigns default user role unless another role is specified while creating the user
• Multiple user roles can be assigned to a single user
• Multiple settings profile can be inherited based on the user roles that are assigned (will be covered in another article)