How to Configure ClickHouse Row Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up clickhouse row level security by creating SQL row policies for users. These rules filter table rows during read queries based on user roles. Many teams start by giving analysts broad read rights on whole tables. That approach exposes sensitive customer rows when one person runs a wide query. Adding row rules limits which records each role can read from a table. How clickhouse row level security works in practice ClickHouse treats users, roles, row policies, settings profiles, and q

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 row level security by creating SQL row policies for users. These rules filter table rows during read queries based on user roles. Many teams start by giving analysts broad read rights on whole tables. That approach exposes sensitive customer rows when one person runs a wide query. Adding row rules limits which records each role can read from a table.

How clickhouse row level security works in practice

ClickHouse treats users, roles, row policies, settings profiles, and quotas as access entities. You manage all of these items with SQL statements. First, you must turn on SQL-driven access control on the server. This step needs a storage folder defined in the server settings. You also need at least one initial account with access management turned on. That admin account can then create new users and roles.

When you create a user, you must hold the user creation privilege. The creation statement sets how the person logs in and which hosts can connect. It also sets default roles and personal settings for that account. A user can only grant privileges that they already hold themselves. Once roles exist, you attach row policies to filter data for those roles. You can also combine row policies with column grants for tighter control. For instance, you can grant read rights on only two specific columns. The user can then query those two columns, but nothing else. If they run a query for all columns, the database refuses it. It returns no data unless the table holds only those granted columns. Newer versions also let you grant rights across table prefixes using a wildcard.

Where native row policies fall short for teams

Native row rules work well when every person has a fixed database role. Still, managing hundreds of SQL policies across many clusters gets hard fast. Every time a team changes, an admin must update roles on each server. Static row rules also cannot inspect what happens inside an active session.

Row policies filter rows, but they do not mask values inside allowed rows. If a support engineer needs a row for debugging, they get every allowed column. Native column grants block whole columns rather than rewriting sensitive fields in results. A query on allowed rows will still return raw emails or phone numbers. Card numbers and national IDs in those rows also return in plain text. Finally, native setup decides who logs in, not what individual queries do in flight. It cannot pause a risky statement for peer approval before it runs.

Row-Level Security + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Combining column grants and row rules safely

To get the best results from native controls, start with role groups. Map each job function to a dedicated role instead of individual user grants. Keep your admin accounts separate from daily read accounts. Only give access management rights to the main setup account. Grant specific column rights first so broad select queries fail by default. Then layer row policies on top to limit which records appear. Review your storage folder backups often so you do not lose access rules.

Add runtime guardrails and masking beside ClickHouse

Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev sits inline in the native TCP protocol beside the server as a sidecar for your ClickHouse. For every production query an agent or human sends, hoop.dev decides whether to pass, mask, block, or escalate it for review.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run or hold risky calls for approval, and data masking rewrites matched string columns in results in memory. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

How do you turn on SQL access control for row policies?

You must set the access control storage folder in the server settings. You also need one user account with access management and named collection control enabled.

What happens if a user runs a wildcard select with column grants?

The database refuses the query and returns no data at all. A wildcard query only works if the user has grants for every column by default.

Who can grant row access or privileges to other ClickHouse users?

An account can only grant privileges that it already holds itself. Creating new users also requires holding the dedicated user creation privilege.

The query runs. The emails stay hidden.