Role Based Access Control

Role Based Access Control

The ClickHouse security model is based on Role Based Access Control (RBAC). This is a common model for controlling access to data, and is used in both applications and databases including ClickHouse.

It involves giving users one or more roles (such as manager, call handler, CEO or salesperson), and then granting privileges to those roles such as the ability to see or modify certain tables or rows. A user can have many roles, and a role can have many users.

In this lesson we will learn more about this model and demonstrate how to administer it in ClickHouse.

XML vs SQL configuration

Note that there are two models for administering RBAC in ClickHouse.

The legacy route was by changing permissions statically in XML files. The newer, reccomended route is to use SQL commands to administer them via the client.

We will use the latter option in this lesson.

Enabling User Creation Via SQL

There is however one situation where we need to the xml file when bootstrapping our new instance.

Having start ClickHouse, let's try to create a new user with a static password:

create user if not exists benjaminwootton identified with plaintext_password by 'password321';

If you try to execute this command, you will find that the default user is not able to administer users on a new install of ClickHouse using the SQL route. It has to be explicitly enabled.

Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have grant CREATE USER ON *.*. (ACCESS_DENIED)

The way to enable this is using the ClickHouse users.xml file. Once this has been turned on in the XML file, we can grant the permission to create users to other users in the system via SQL. This is purely a security feature when bootstrapping a new ClickHouse server.

We can edit the users.xml file in the following location:

nano /etc/clickhouse-server/users.xml

And uncomment the line regarding access_management:

<users>
    <default>
            <access_management>1</access_management>

Uncommenting and setting this flag to 1 effectively says that the default user has permission to create new users and grant permissions to them.

We can then restart the ClickHouse server, being careful to reference the main configuration file, which in turn will reference the users.xml file we have just edited:

clickhouse-server -C /etc/clickhouse-server/config.xml

If we then attempt to recreate our user from the ClickHouse client:

create user if not exists benjaminwootton identified with plaintext_password by 'password321';

We should see that it has been succesfully created:

CREATE USER IF NOT EXISTS benjaminwootton IDENTIFIED WITH plaintext_password BY 'password321'

Query id: b19e48ab-f7bd-48bf-a313-e0eec6b22a0c

Ok.

0 rows in set. Elapsed: 0.004 sec.

The new user, in this case benjaminwootton will then be able to login at the client with the credentials specified.

Administering Roles

As discussed above, the ClickHouse access management model is based on roles.

For example, say we have two roles SALESPERSON and SALESMANAGER. These may have a different set of read and write permissions.

SALESPERSON

• Can read customers
• Can write to sales

SALESMANAGER

• Can write to customers
• Can write to sales
• Can read employees

Roles are created through SQL:

create role if not exists salesperson;

create role if not exists salesmanager;

We can then assign permissions to our newly created roles:

grant select on db.customers TO salesperson;
grant insert, select on db.sales TO salesperson;

grant insert on db.customers TO salesmanager;
grant insert on db.sales TO salesmanager;
grant select on db.employees to salesmanager;

Assigning Roles To Users

The next step is to assign the newly created roles to our user:

grant salesmanager to benjaminwootton;

Roles can also be assigned to users at creation time like so:

create user if not exists martinjones 
identified with plaintext_password by 'password321' 
default role salesperson 

Privileges

The final component of the RBAC model is privileges, describing a permission to execute certain types of queries over certain database objects.

For instance, in the following query, we are stating that any users with the sales manager role, should be able to select the order_id and the pizza_type columns from the pizza_orders table.

grant select(order_id,pizza_type) ON db.pizza_orders 
to salesmanager with grant option

In the example above, we granted permissions to roles. This is best practice, and the most maintinable option. In similar deployments, we may prefer to grant privileges directly to users, which is also supported by ClickHouse:

grant select(order_id,pizza_type) ON db.pizza_orders 
to benjaminwootton with grant option

WITH GRANT OPTION

The WITH GRANT OPTION clauses means that the user we grant the permission to, in turn has access to grant the same permission to other users. In the instance above, we may be comfortable with letting our salesmanager devolve permissions to read data to their employees, but this may not be something we would be comfortable with salespeople being able to do. The specific configuration will depend on your organisational security model.

Row Policies

As well as limiting data acess by columns, it is also possible to limit the data which users can see in a more granular way by rows. This can be done with ROW POLICY objects, which accept a SQL query stating which rows given users or roles can access:

create row policy low_value_orders_policy on
mydb.pizza_orders USING pizza_value < 1000 TO salesperson