How to Configure ClickHouse User Roles
You set up clickhouse user roles by turning on SQL-driven access control first. Then you create role entities, grant read or write rights, and assign them to users. Most teams start by sharing one broad admin account across apps and engineers. That approach speeds up early testing for a small team. Still, broad permissions soon expose every table and setting to accidental changes. A single bad query or script can alter production data or read private columns. Splitting access into scoped roles f
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 user roles by turning on SQL-driven access control first. Then you create role entities, grant read or write rights, and assign them to users. Most teams start by sharing one broad admin account across apps and engineers. That approach speeds up early testing for a small team. Still, broad permissions soon expose every table and setting to accidental changes. A single bad query or script can alter production data or read private columns. Splitting access into scoped roles fixes this problem early on.
How clickhouse user roles work in SQL
ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You can create and change all of these items with SQL statements. First, you must turn on SQL-driven access control in the server setup. This step needs a storage folder defined in the access control path parameter. You also need at least one user account with access management turned on. That user also needs named collection control set to one.
Once that initial account exists, you can create new accounts and roles. Creating a new user requires the create user privilege on your own account. ClickHouse enforces a strict rule when you share permissions with others. A user can only grant the privileges that they already hold themselves. During user creation, you can define how the person or app logs in. You can also restrict which hosts a user may connect from. Finally, you can assign default roles and specific settings to that user.
Scoping table and column grants
Roles work best when you pair them with narrow read or write grants. Instead of granting full table reads, you can grant read rights on specific columns. The user can then run queries that request only those allowed columns. If the user asks for another column, the database blocks the query. A wildcard query that asks for all columns is also refused right away. It returns no data unless the table contains only the granted columns.
You can also group tables when you hand out read rights to roles. Since version 24.10, a trailing asterisk grants rights by table name prefix. This lets a team grant access to a whole group of matching tables. When a new table matches that prefix, the role gets access right away.
User Provisioning (SCIM) + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what keeps the session active
Native roles give you strong control over who logs into the database. They also let you separate read users from admin accounts with ease. By grouping privileges into roles, you avoid editing dozens of individual user accounts. When a team member switches jobs, you just swap their assigned role.
Still, static database roles only check permissions at the table or column level. Once a user has read rights on a column, every row comes back raw by default. If an allowed column holds emails, phone numbers, or card numbers, queries return them. Blocking the column breaks wildcard queries and stops analysts from joining on those fields. Native roles also remain active all day once you assign them to someone. They do not require a peer review before a user runs an ad-hoc command. Teams often need runtime controls that inspect queries and mask fields in 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
How do you turn on SQL-driven access control in ClickHouse?
You must set a storage directory using the access control path server parameter. You also need one user with access management and named collection control set to one.
What happens when a user with column grants runs a wildcard query?
ClickHouse refuses the wildcard query and returns no data at all. It only works if the table holds just the exact columns granted to that user.
Can a ClickHouse user grant any permission to a role?
No, a user can only grant the privileges that they already have themselves. They also need the create user privilege to make new user accounts.
Related guides
In this post