ClickHouse GRANT and REVOKE: Privileges, Roles, and WITH GRANT OPTION

GRANT in ClickHouse gives a user or role the permission to perform specific operations. The full privilege space is hierarchical and granular: you can grant SELECT on a single column of a single table, or ALL on *.*. The companion REVOKE statement undoes a grant. Both statements support WITH GRANT OPTION (for privilege delegation) and WITH ADMIN OPTION (for role delegation). This guide covers the syntax, the privilege hierarchy, the most common patterns, and the gotchas you only hit in production.

The Quick Answer

GRANT SELECT ON analytics.* TO alice;
GRANT SELECT, INSERT ON analytics.events TO app_writer;
GRANT analyst TO alice WITH ADMIN OPTION;

The first grants read on every table in analytics to a user. The second grants reads and writes on one table to a role. The third grants a role to a user and lets them re-grant that role to others.

Full GRANT Syntax

-- Privilege grant
GRANT [ON CLUSTER cluster_name]
  privilege [(column [, column ...])] [, privilege ...]
  ON { db.table | db.* | *.* }
  TO { user | role | CURRENT_USER } [, ...]
  [WITH GRANT OPTION]
  [WITH REPLACE OPTION];

-- Role grant
GRANT [ON CLUSTER cluster_name]
  role [, role ...]
  TO { user | role | CURRENT_USER } [, ...]
  [WITH ADMIN OPTION]
  [WITH REPLACE OPTION];

WITH REPLACE OPTION is the safer alternative to running REVOKE ALL before granting; the new grant replaces the existing one atomically.

The Privilege Hierarchy

Privileges in ClickHouse compose hierarchically. Granting a parent privilege implies the children. Some of the important top-level privileges:

The docs put it succinctly: "The ALTER privilege includes all other ALTER* privileges." The same applies to SELECT, INSERT, CREATE, DROP, and most other categories. Granting SELECT ON db.table covers the column-scoped sub-privileges.

For the full list and the children of each, see the official privileges reference.

Column-Level Grants

GRANT SELECT(user_id, event_time) ON analytics.events TO alice;
GRANT INSERT(event_time, payload)  ON analytics.events TO app_writer;

When a column-level grant is in place, queries that touch columns outside the grant get no data back. The docs are explicit: "Processing this query, ClickHouse does not return any data" when the user tries to read a column they were not granted.

Pair this with row policies for full row- and column-level security on multi-tenant tables.

Granting Roles

-- Make Alice a member of the analyst role
GRANT analyst TO alice;

-- Make a role inherit from another role
GRANT base_reader TO analyst;

-- Allow Eve to manage who has the analyst role
GRANT analyst TO eve WITH ADMIN OPTION;

Note that being granted a role is not the same as the role being active. See the create role guide for default roles vs activated roles.

WITH GRANT OPTION

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

WITH GRANT OPTION allows team_lead to re-grant the privilege (or a narrower subset of it) to other users. The docs note: "Users can grant privileges of the same scope they have and less." So a user with SELECT ON analytics.* and grant option can grant SELECT ON analytics.events to someone else, but not SELECT ON sensitive.*.

Use sparingly. WITH GRANT OPTION is delegation; once you give it, that user can fan out access without you seeing the grants. Combine with GRANTEES on the user definition to constrain who they can re-grant to.

WITH ADMIN OPTION

GRANT analyst TO eve WITH ADMIN OPTION;

WITH ADMIN OPTION is the role-equivalent of WITH GRANT OPTION. Eve can grant and revoke the analyst role to other users. The docs go further: with admin option you can "assign and revoke any roles including those which are not assigned to the user with the admin option," which makes admin option a powerful capability. Treat it like superuser.

REVOKE

REVOKE SELECT ON analytics.* FROM alice;
REVOKE SELECT(user_id) ON analytics.events FROM alice;
REVOKE analyst FROM alice;
REVOKE ALL ON *.* FROM alice;

REVOKE ALL revokes every direct privilege the user has on the target. It does not revoke privileges that come via a role; remove those by revoking the role. There is no "partial revoke" that overrides a still-granted parent privilege.

For roles, the ADMIN OPTION FOR clause lets you revoke just the delegation right while leaving the role membership in place: REVOKE ADMIN OPTION FOR <role> FROM <user>.

Common Patterns

Least-privilege analyst

CREATE ROLE analyst SETTINGS readonly = 1;
GRANT SHOW TABLES, SELECT ON analytics.* TO analyst;
GRANT SHOW TABLES, SELECT ON dim.* TO analyst;

Producer/consumer split for an event pipeline

CREATE ROLE events_producer;
GRANT INSERT ON analytics.events TO events_producer;

CREATE ROLE events_consumer;
GRANT SELECT ON analytics.events TO events_consumer;

A "data steward" who can manage one schema

CREATE ROLE steward_finance;
GRANT SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE, OPTIMIZE ON finance.* TO steward_finance;
GRANT steward_finance TO frances WITH ADMIN OPTION;

Migrating from per-user grants to roles

-- 1. Build the role
CREATE ROLE analyst;
GRANT SELECT ON analytics.* TO analyst;

-- 2. Grant the role to everyone who had the same privileges
GRANT analyst TO alice, bob, carol;
ALTER USER alice DEFAULT ROLE analyst;
ALTER USER bob   DEFAULT ROLE analyst;
ALTER USER carol DEFAULT ROLE analyst;

-- 3. Once verified, revoke the direct grants
REVOKE SELECT ON analytics.* FROM alice, bob, carol;

The intermediate step (grant the role, then revoke the direct grants) lets you verify nothing breaks before tightening up. SHOW GRANTS FOR alice is the audit command.

Inspecting Grants

SHOW GRANTS;                    -- current user
SHOW GRANTS FOR alice;          -- specific user
SHOW GRANTS FOR analyst;        -- specific role

-- Everything in one place
SELECT * FROM system.grants WHERE user_name = 'alice' OR role_name = 'alice';

-- Role memberships
SELECT * FROM system.role_grants WHERE user_name = 'alice';

Common Errors

• Not enough privileges: the operator does not have the privilege they are trying to grant. They need it themselves with WITH GRANT OPTION. See invalid-grant.
• Cannot set non-granted role: trying to activate a role the user does not have. See set-non-granted-role.
• Unknown access type: the privilege name is misspelled or refers to a feature your build does not support. See unknown-access-type.
• Access denied: required privilege X on Y: the user is missing a grant. Inspect with SHOW GRANTS and grant the missing piece.

How Pulse Helps With Grant Hygiene

Grant sprawl is one of the slowest-moving but highest-cost forms of cluster drift. Users keep accumulating privileges, roles get added on top of direct grants, WITH GRANT OPTION gets handed out and forgotten, and after a year nobody can tell you who actually has access to what. Pulse continuously analyzes the grant graph on ClickHouse clusters and surfaces over-privileged users, users with both a role and a direct grant for the same scope (indicating an incomplete migration), grant-option holders, dormant grants on tables that no longer exist, and grants on system databases that nobody intended. Connect your ClickHouse cluster to Pulse and let it audit grants so you don't have to.

Frequently Asked Questions

Q: What is the difference between WITH GRANT OPTION and WITH ADMIN OPTION?

WITH GRANT OPTION applies to privilege grants (GRANT SELECT ON ... WITH GRANT OPTION) and lets the user re-grant that specific privilege. WITH ADMIN OPTION applies to role grants (GRANT analyst TO eve WITH ADMIN OPTION) and lets the user grant or revoke the role to anyone. Admin option is broader: a user with admin option on a role can delegate roles they themselves have not been granted.

Q: Why does my user get no data back on a column they should be able to see?

Most likely a column-level grant. If SELECT(x, y) was granted but the query asks for z or *, the docs note ClickHouse "does not return any data." Inspect with SHOW GRANTS FOR <user>.

Q: Does GRANT ALL ON *.* work in ClickHouse Cloud?

No. The docs explicitly state ALL is "not supported in ClickHouse Cloud." Grant the specific privileges you need.

Q: How do I temporarily reduce my own privileges for a query?

SET ROLE NONE deactivates all roles for the current session, leaving only direct grants. To re-enable, SET ROLE DEFAULT or SET ROLE <specific>. Useful when running a privileged user but wanting to validate that a query works under a lesser role.

Q: Can I grant privileges on a Distributed table?

Yes, but the privileges on the Distributed table itself only let the user query it; the underlying tables on each shard need their own grants (typically through the same role granted via ON CLUSTER).

Q: Where do grants live?

In the access entity backend, just like users and roles. system.grants is the single source of truth for direct grants; system.role_grants for role memberships. system.grants returns one row per privilege, scoped to either a user or a role.

Q: How do I revoke a privilege that was granted via a role?

You cannot revoke a single privilege that comes from a role. Either modify the role (REVOKE SELECT(...) FROM analyst) which affects every user with that role, or revoke the role from the specific user and replace it with a narrower role.