How to Configure ClickHouse Revoke Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You run clickhouse revoke access by removing privileges from a user or role. An admin with access rights runs a SQL command to drop permissions. Teams often grant broad database roles during a fast project launch. Later, those wide permissions stay active long after the task ends. Overly broad roles let users read private tables or change data by mistake. How clickhouse revoke access works in SQL ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities.

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You run clickhouse revoke access by removing privileges from a user or role. An admin with access rights runs a SQL command to drop permissions. Teams often grant broad database roles during a fast project launch. Later, those wide permissions stay active long after the task ends. Overly broad roles let users read private tables or change data by mistake.

How clickhouse revoke access works in SQL

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You create and change these entities with standard SQL commands. Before you start, you must turn on SQL-driven access control. This setup needs a storage directory set in the server settings. It also needs at least one user account with access management turned on.

In this model, a user can only grant privileges they already hold. The same rule applies when you take rights away from an account. Admins can remove read or write rights at the database or table level. You can also manage read access down to single columns on a table. Since version 24.10, a trailing asterisk grants rights by table name prefix. When you remove a role from a user, their session loses those rights.

When you grant read rights on specific columns, the user can query only those fields. If the user runs a full table query with an asterisk, ClickHouse refuses it. That query returns no data unless the table holds only the permitted columns. Taking away a column right stops the user from reading that field again.

Why role management is slow to maintain

Managing column grants by hand creates extra work for database admins. Every time a table schema changes, someone must update the role rules. If a user loses access to one column, their saved reports may break. A simple query that grabs all columns will fail and return nothing. Analysts then file tickets to get their broad table access back.

Admins also struggle to track who holds which rights across many clusters. Direct grants on user accounts mix with inherited roles and prefix grants. Cleaning up old permissions takes careful review so you do not break apps. Because of this friction, teams often leave wide read roles in place.

ClickHouse Access Management + Customer Support Access to Production: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native access control solves and what it leaves open

Native ClickHouse roles and grants decide who can log in to the server. They also set which tables and columns an account can touch. You can lock an account to specific client hosts and set default roles. This built-in system handles static rules for known apps and permanent team roles.

Still, static grants do not check what single queries do in flight. Once a user has read rights on a column, they get raw values. Native roles cannot rewrite emails, phone numbers, card numbers, or national IDs in results. They also cannot pause a risky command for human approval before it runs. Standing permissions keeps the session active twenty-four hours a day until an admin revokes them. Teams still need a way to control live queries and mask sensitive fields.

Check ClickHouse queries and results before execution

Access control in ClickHouse is essential setup. Setup alone does not check single queries for risk or rewrite sensitive strings in returned rows. By design, hoop.dev sits inline in the protocol beside the server as a sidecar for your ClickHouse. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human approval.

First, hoop.dev guardrails block disallowed deletes, drops, and alter mutations or hold them for review. Then hoop.dev data masking rewrites sensitive string columns in responses in memory and logs each decision per resource. Read the docs and source on GitHub.

Frequently asked questions

Who has the right to revoke privileges in ClickHouse?

An account needs SQL-driven access management turned on to manage user rights. Also, a user can only manage the privileges that they hold themselves.

What happens to queries when you limit column read rights?

The user can query only the allowed columns by name. Queries that select all columns with an asterisk fail and return no data.

Can ClickHouse grants hide sensitive text inside an allowed column?

No, native column grants are all or nothing for that specific field. To rewrite card numbers or emails in query results, you need data masking.

The query runs. The emails stay hidden.