How to Use RBAC in ClickHouse

Galaxy Glossary

How do I set up RBAC in ClickHouse?

RBAC in ClickHouse lets you manage fine-grained privileges with roles and users, improving security and auditability.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problems does ClickHouse RBAC solve?

RBAC (Role-Based Access Control) centralizes privilege management. Instead of granting rights to every user, you grant them to roles, then assign roles to users. This reduces admin work and audit complexity.

How do I create a role?

Run CREATE ROLE role_name. Roles are metadata objects that hold privileges but cannot log in. Example: CREATE ROLE analytics_reader;

How do I grant table privileges to a role?

Use GRANT privilege ON db.table TO role. You can grant multiple privileges at once: GRANT SELECT ON ecommerce.Customers TO analytics_reader;

How do I create a user and assign roles?

Users authenticate to ClickHouse. Create with CREATE USER, then GRANT role TO user. Example: CREATE USER alice IDENTIFIED BY 's3cr3t'; GRANT analytics_reader TO alice;

How do I see existing roles and privileges?

Query system tables: SELECT * FROM system.roles; for roles, and SHOW GRANTS FOR role analytics_reader; for role privileges.

Best practices for ClickHouse RBAC

Create roles around job functions (reader, writer). Grant the minimum required privileges. Use role inheritance for tiered access. Review system.grants regularly.

What are common RBAC mistakes?

Granting privileges directly to users bypasses RBAC’s benefit. Forgetting to set SET DEFAULT ROLE can block user access after login.

Why How to Use RBAC in ClickHouse is important

How to Use RBAC in ClickHouse Example Usage


-- Alice can read customer data but not modify it
SET ROLE analytics_reader;
SELECT id, name, email FROM ecommerce.Customers ORDER BY created_at DESC LIMIT 10;

How to Use RBAC in ClickHouse Syntax


-- Create roles
CREATE ROLE analytics_reader;
CREATE ROLE analytics_writer;

-- Grant privileges to roles
GRANT SELECT ON ecommerce.Customers TO analytics_reader;
GRANT SELECT, INSERT ON ecommerce.Orders TO analytics_writer;
GRANT SELECT ON ecommerce.Products TO analytics_reader;

-- Create users
CREATE USER alice IDENTIFIED BY 's3cr3t';
CREATE USER bob   IDENTIFIED BY 's3cr3t';

-- Assign roles to users
GRANT analytics_reader TO alice;
GRANT analytics_reader, analytics_writer TO bob;

-- Make role active by default
SET DEFAULT ROLE analytics_reader TO alice;

Common Mistakes

Granting table privileges directly to users. This creates scattered permissions that are hard to audit. Fix by creating a role, granting the privilege to the role, then assigning the role to users.
Creating a role but not making it the user’s default. Users log in with no active role and get permission errors. Use SET DEFAULT ROLE role_name TO user;