Managing RBAC Roles and Grants

Granting privileges directly to individual ClickHouse users looks harmless on day one and becomes unauditable by month three: the ETL account accumulates a DROP here and an ALTER there, nobody remembers why, and revoking access for a departing engineer means grepping a dozen scattered GRANT statements. Role-based access control fixes this by making the role the unit of privilege — you grant privileges to roles once, grant roles to users, and reason about access as a small graph instead of a flat pile of grants. This guide covers creating roles with CREATE ROLE, granting table and column-level privileges, composing roles into a hierarchy with GRANT role TO role, pinning what a user gets at login with SET DEFAULT ROLE, delegating with GRANT OPTION, and auditing the whole graph through system.grants, system.role_grants, and SHOW GRANTS.

Prerequisites

• ClickHouse 22.3+ with SQL-driven access control enabled for the administering user (access_management = 1 in its settings profile)
• The target databases and tables already created, so privileges can name real objects
• An administering user that itself holds the privileges it intends to grant, ideally WITH GRANT OPTION
• A naming convention for roles (for example role_reader, role_etl_writer, role_analyst) agreed with the platform team
• clickhouse-client access to run SET ROLE and SHOW GRANTS for verification
• For clusters, ON CLUSTER privileges so role definitions replicate to every node

Why a role graph beats direct grants

ClickHouse RBAC has three kinds of edges: a privilege granted to a role, a role granted to another role (which builds a hierarchy), and a role granted to a user. A user’s effective privileges are the transitive closure of the roles active in the session. Because roles can be granted to roles, you compose a base of narrow capabilities — role_reader reads one schema, role_etl_writer inserts into staging — into broader composite roles without ever re-listing the underlying privileges. Revocation is symmetric: revoke a role from a user and every privilege that role carried, however deeply nested, disappears in one statement.

Step 1 — Create the base capability roles

Start with the narrowest useful roles. A base role should represent one capability against one schema, so it can be reused across many composites.

CREATE ROLE IF NOT EXISTS role_reader;
CREATE ROLE IF NOT EXISTS role_etl_writer;

GRANT SELECT ON analytics.* TO role_reader;
GRANT INSERT ON staging.*   TO role_etl_writer;

GRANT SELECT ON analytics.* covers every current and future table in the schema; scope to a single table (analytics.events) when a role should not automatically pick up new tables. Privileges are hierarchical — ALL implies every privilege on the object, SELECT is a leaf — so grant the leaf that matches the job, never ALL to a workload role.

Step 2 — Grant column-level privileges for masked reads

SELECT can be scoped to a column list, which is how you expose a table while hiding sensitive columns without building a separate view. A role granted SELECT(a, b) can read only those columns; selecting any other column, or SELECT *, is rejected.

CREATE ROLE IF NOT EXISTS role_analyst;

-- Analyst may read event shape but not raw payload or PII.
GRANT SELECT(tenant_id, event_ts, event_type) ON analytics.events TO role_analyst;

Column grants are enforced at parse time, so a query touching a forbidden column fails with ACCESS_DENIED before it reads a single granule. This pairs naturally with the row-level filters in chaining row policies for multi-tenant isolation: column grants control which columns a role sees, row policies control which rows.

Step 3 — Compose roles into a hierarchy

Grant roles to roles to build composites. The composite inherits every privilege of the roles granted to it, transitively.

-- role_analyst inherits everything role_reader can do, plus its own column grant.
GRANT role_reader TO role_analyst;

-- role_pipeline is a superset: it can read analytics and write staging.
CREATE ROLE IF NOT EXISTS role_pipeline;
GRANT role_reader, role_etl_writer TO role_pipeline;

Now editing role_reader — say, extending it to a second schema — instantly flows to role_analyst and role_pipeline. This is the payoff of the graph: capability changes happen in one place.

Step 4 — Grant roles to users and set the default

A role does nothing until it is both granted to a user and activated in the session. The default role is what activates automatically at login; without a default, a granted role stays dormant until the user runs SET ROLE.

GRANT role_analyst  TO analyst;
GRANT role_pipeline TO etl_svc;

-- Activate at login so the user does not have to SET ROLE manually.
SET DEFAULT ROLE role_analyst  TO analyst;
SET DEFAULT ROLE role_pipeline TO etl_svc;

Use SET DEFAULT ROLE ALL TO user to activate every granted role at login, or SET DEFAULT ROLE NONE TO user to force explicit SET ROLE per session — the latter is the safer posture for a break-glass admin account whose elevated role should never be active by default.

Step 5 — Delegate with GRANT OPTION

To let a team lead manage grants for their own domain without making them a superuser, append WITH GRANT OPTION. The grantee can then re-grant exactly the privileges they hold, and no more.

GRANT SELECT ON analytics.* TO role_data_lead WITH GRANT OPTION;

A user holding role_data_lead can now GRANT SELECT ON analytics.events TO some_role, but cannot grant INSERT (they do not hold it) and cannot grant SELECT on a schema they were not given. GRANT OPTION is bounded delegation, not escalation.

Verification

Inspect a user’s full effective grant set — including everything inherited through roles — with SHOW GRANTS, which resolves the transitive closure for you:

SHOW GRANTS FOR analyst;

GRANT SELECT(tenant_id, event_ts, event_type) ON analytics.events TO analyst
GRANT role_analyst TO analyst

For programmatic auditing, query the system tables directly. system.grants lists privilege edges; system.role_grants lists role-to-role and role-to-user edges:

-- Every privilege edge, whether attached to a user or a role.
SELECT user_name, role_name, access_type, database, table, column
FROM system.grants
WHERE database = 'analytics'
ORDER BY role_name, access_type;

-- The role hierarchy: who has been granted which role.
SELECT user_name, role_name, granted_role_name, with_admin_option
FROM system.role_grants
ORDER BY granted_role_name;

Finally, confirm what is actually active in a session versus merely granted. SET ROLE changes the active set, and system.enabled_roles reflects it:

SET ROLE role_reader;               -- narrow the session to one role
SELECT role_name FROM system.enabled_roles;
-- Only role_reader (and roles it inherits) should be listed now.

A privilege that appears in system.grants but never in system.enabled_roles for any expected session is a dead grant — a candidate for revocation.

Gotchas & edge cases

• A granted role is inert until activated. Granting role_pipeline to etl_svc does not give the service account any privilege on its next connection unless role_pipeline is its default role or the session runs SET ROLE. Connections that suddenly lose access after a role change are almost always missing a SET DEFAULT ROLE.
• GRANT OPTION is per-privilege, not global. Holding SELECT … WITH GRANT OPTION lets a user re-grant only that SELECT, on only the objects they hold it for. It does not confer the ability to create roles or grant unrelated privileges — that requires ACCESS MANAGEMENT privileges, which should stay with the platform team.
• Column grants and SELECT * interact surprisingly. A role with a column-scoped SELECT(a, b) cannot run SELECT *, because the wildcard expands to columns it lacks. Application code that relies on SELECT * will fail with ACCESS_DENIED under a masked role; pin explicit column lists in queries destined for restricted roles.
• Revoking a base role cascades everywhere it was composed. REVOKE role_reader FROM role_analyst silently narrows every user who held role_analyst. Before revoking a widely-composed base role, query system.role_grants for granted_role_name = 'role_reader' to see the full blast radius, and prefer creating a new narrower role over mutating one that many composites depend on.

Related

• Chaining Row Policies for Multi-Tenant Isolation — row-level filters that attach to the roles created here.
• Enforcing TLS and Encrypted Connections — protecting the credentials that assume these roles on the wire.
• Security & Access Control Boundaries — the full privilege, quota, and perimeter model.
• ClickHouse Core Architecture & Analytics Fundamentals — why parse-time privilege checks make least-privilege cheap to enforce.

Up: Security & Access Control Boundaries