Best Practices for ClickHouse's Role Based Access Control

Role-based access control (RBAC) is a method of restricting access to a resource based on the roles of the users within an organization. RBAC can ensure that the users are allowed to access the resource and the information within the scope of their job and nothing more or less. RBAC is based on the roles and privileges to the roles. RBAC roles are directly correlated with the employee roles and privileges are granted to access the resources that are permitted for the particular role.

Some of the benefits of RBAC approach are

Transparency – The permissions are based on roles. So the employees with the same role would have the same set of permissions.
Flexibility – When the employee’s role within an organization changes, he can be easily re-assigned the new role and revoke the old one.
Security – Every role is granted permissions per the least privilege principle.
Efficient – It is easier to track the permissions and reduces the efforts to maintain the users and roles.

Table of Contents

Principle of Least Privilege

The principle of least privilege is an information security concept that states that every entity must be able to access only the information and resources that are necessary for completing the required task.

ClickHouse RBAC

In ClickHouse, Access control management is supported via RBAC approach. The entities that are part of ClickHouse RBAC are

User Account – Hepls to identify individual end users or the application which connects and accesses the data in ClickHouse
Role – Roles hold the information about privileges, settings, and constraints for the users
Settings profile – Group of settings that will be applied to the user or a role
Quotas – Limit the resource usage for a role or user over a period of time

RBAC in ClickHouse

The above illustration shows the access entities and the possible assignment of access entities among themselves. In ClickHouse, it is possible to

Grant roles, settings profile, and any specific privilege to users
Grant settings profile and any specific privilege to any role

While this approach can help in implementing RBAC, the strict enforcement of the Principle of Least Privilege is entirely in the hands of the database admin. This is because, let’s say, we have two users A and B, with the same role. They can end up having different sets of privileges and settings because it is possible to grant settings profiles and any specific privileges to individual users. We can enforce the upcoming RBAC approach to ensure consistency and strict implementation of the Principle of Least Privilege.

ChistaDATA RBAC for ClickHouse

Best Practices

We shall grant only the roles to users
We shall grant privileges and settings profile to roles

The mentioned approach is much simpler and more effective. This will ensure that we have a consistent set of privileges and enforce the Principle of Least Privilege effectively. ChistaDATA will follow the above approach in our DBAAS solution. But before we start granting permissions, we have to follow the following best practices

Identify the data and the required levels of permission for the data in the database server
Identify the roles, the level of access to those roles and the accessible data for those roles
The roles should be aligned with the organizational roles of the employees and there shouldn’t be too many roles
Identify the user on-boarding and termination within the scope of RBAC
Educate the employees on the roles and privileges assigned to them and that are available
Audit the roles and privileges periodically and add/remove the obsolete privileges and new set of privileges