How to Configure ClickHouse Grant Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up clickhouse grant permissions by turning on SQL-driven access control first. Then you assign specific table or column privileges to database users and roles. Most teams start by creating one admin account with full management rights. From there, they build roles for analysts, engineers, and apps. When teams hurry this step, they often hand out broad database privileges. Overly broad roles let users read sensitive columns or change production data by mistake. How clickhouse grant perm

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 set up clickhouse grant permissions by turning on SQL-driven access control first. Then you assign specific table or column privileges to database users and roles. Most teams start by creating one admin account with full management rights. From there, they build roles for analysts, engineers, and apps. When teams hurry this step, they often hand out broad database privileges. Overly broad roles let users read sensitive columns or change production data by mistake.

How clickhouse grant permissions work in practice

ClickHouse treats users, roles, row policies, settings profiles, and quotas as access entities. You can create and manage all of these entities using standard SQL statements. Before you start, you must enable SQL-driven access control on the server. This setup needs a storage directory path set in the server parameters. You also need at least one initial account with access management turned on. That account also needs named collection control set to one.

Once your admin account is ready, you can create new database accounts. Creating an account requires the specific privilege to create users. During creation, you define how the person logs in and which hosts can connect. You can also assign default roles and custom settings to that user right away. ClickHouse enforces a strict rule when people share privileges with others. A user can only grant the privileges that they hold themselves.

Scoping read rules down to specific columns and tables

Good access control means giving people only the data they need for work. ClickHouse lets you grant read privileges on specific columns inside a table. Suppose you grant a user read access to just two columns. That user can run queries that name only those two allowed columns. If the query asks for a third column, the database blocks it. Running a select-all query with an asterisk also fails and returns no data. The select-all query works only when the user has rights to every column by default.

You can also group permissions across multiple tables using name prefixes. Starting in version 24.10, a trailing asterisk grants rights by table name prefix. This helps when your team creates many tables that share a naming pattern. Instead of updating grants for every new table, one prefix rule covers them. Combining roles with column grants keeps your permission setup clean and easy to read.

ClickHouse Access Management + AI Agent Permissions: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native grants solve and what gaps remain

Native database grants do a great job of deciding who can log in. They also define which tables and columns a static role may touch. Still, static role rules stay active all day for everyone holding that role. Native grants cannot require a peer review before a risky query runs. They also struggle when an engineer needs to read a table containing mixed data. If a column holds emails, phone numbers, card numbers, or national IDs, grants are binary. You either block the whole column or expose every raw value in the result. Static database roles do not rewrite matched fields in query results on the fly. They also leave teams managing separate logins across every database cluster.

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

Why does a select-all query fail when I have column permissions?

ClickHouse blocks select-all queries if you only have grants on specific columns. You must name the allowed columns in your query, unless every column is granted.

Can a database user grant permissions they do not own?

No, ClickHouse prevents users from handing out rights they do not hold. A user can only grant the exact privileges that they possess themselves.

How do I enable SQL-driven user and role management in ClickHouse?

You must set the access control storage directory path on the server first. Then you enable access management and named collection control on an initial user account.

The query runs. The emails stay hidden.