How to Configure ClickHouse Column Level Security
You set up clickhouse column level security by granting read rights on specific table columns. This restricts users from reading full tables when they only need a few fields. Many teams skip this step at first and grant broad table roles to save time. Broad roles expose sensitive fields like emails, phone numbers, card numbers, and national IDs. When every analyst or app uses a wide role, a single mistake exposes private data. How clickhouse column level security works ClickHouse manages user
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 column level security by granting read rights on specific table columns. This restricts users from reading full tables when they only need a few fields. Many teams skip this step at first and grant broad table roles to save time. Broad roles expose sensitive fields like emails, phone numbers, card numbers, and national IDs. When every analyst or app uses a wide role, a single mistake exposes private data.
How clickhouse column level security works
ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL objects. Before you create these rules, you must turn on SQL-driven control on the server. This setup needs a storage folder path set in the server files. You also need at least one admin account with permission to manage other users.
Once you turn this on, an admin can create new users and roles with SQL. Creating a user requires the right to create users. A user can only grant permissions that they already hold themselves. During setup, the admin sets how the user logs in and which hosts can connect. The admin also assigns default roles and user settings.
To lock down columns, the admin grants read rights on named columns for a table. For example, a rule can let a user read only two columns from an orders table. That user can then run queries that ask for one or both of those columns. If the user asks for a third restricted column, ClickHouse blocks the query.
This rule also changes how wildcard queries work for that user. A query that asks for all columns at once fails and returns no data. It only succeeds if the user has rights to every single column in that table. Since version 24.10, admins can also use a trailing star to grant rights by table prefix.
Why static column grants leave operational gaps
Native column grants work well when your tables and team roles rarely change. They help you separate public columns from sensitive columns for basic reporting jobs. Still, managing hundreds of column rules across fast-changing tables quickly becomes hard to track.
First, blocking wildcard queries often breaks business intelligence tools and ad hoc fixing scripts. Engineers and analysts often run broad queries to inspect table shapes during an incident. When the database drops the whole query, teams often ask for full table roles instead.
Column-Level Encryption + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Second, static grants decide who can read a column, not how the data returns. If a support engineer needs to check a user record, they either get the raw field or get blocked. Native grants do not rewrite matched fields like emails or card numbers in query results.
Third, database roles apply the same static rules to every session from that user. They cannot demand a live peer review before a user runs an unusual query. They also do not tie individual SQL statements back to single sign-on identities when teams share an account.
Managing column access across teams and tools
To keep data safe without breaking tools, teams need controls that inspect queries in flight. Instead of maintaining dozens of fragile column roles per table, you can keep database roles simple. You can then apply field rules and approvals at the query level based on who is asking. This keeps wildcard queries working while masking sensitive values like phone numbers or national IDs in the results.
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
What happens when a user runs a select all query with restricted columns?
ClickHouse refuses the query and returns no data at all. The query only works if the user holds read rights on every column in that table by default.
Who is allowed to grant column permissions to another user in ClickHouse?
An account must have user management rights turned on to manage access rules. Even then, a user can only grant permissions that they already hold themselves.
Can ClickHouse grant access to multiple tables at the same time?
Yes, starting in version 24.10, admins can put a trailing star at the end of a table name. This grants read rights across all tables that share that name prefix.
Related guides
In this post