How to Configure ClickHouse Read Only User
You set up a clickhouse read only user by turning on SQL access control. Then you create the account and grant read privileges on your target tables. Many engineering teams start with shared admin accounts for analytics work and fixing. That shortcut gives every person and reporting tool the power to drop tables or alter data. Moving to a strict read model stops accidental writes and protects your data warehouse. How to set up a clickhouse read only user with SQL ClickHouse manages users, rol
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 a clickhouse read only user by turning on SQL access control. Then you create the account and grant read privileges on your target tables. Many engineering teams start with shared admin accounts for analytics work and fixing. That shortcut gives every person and reporting tool the power to drop tables or alter data. Moving to a strict read model stops accidental writes and protects your data warehouse.
How to set up a clickhouse read only user with SQL
ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You can create and manage all of these entities with SQL statements. Before you start, your server needs a storage folder for these access rules. You also need at least one admin account that has access management rights turned on. An admin can only grant privileges that they hold themselves.
Creating a new user requires the privilege to create users on the server. During setup, you define how the person logs in and which hosts they can use. You can also assign default roles and apply custom settings profiles to the account. To make the account read only, you grant select privileges on specific databases or tables. Since version 24.10, a trailing asterisk lets you grant rights by table name prefix.
You can also lock down read rights to individual columns inside a table. When you grant select on specific columns, the user can only query those named fields. Queries that ask for other columns will fail right away. A wildcard select query will also fail and return no data at all. It only works if the table holds just the columns granted to that user.
Why native read roles need finer controls
Native roles and column grants are great for separating duties across your team. They stop analysts and external apps from deleting rows or changing table schemas. Yet database roles only check who logs in and which objects they can touch. They do not inspect what individual queries return while a session is active.
In a fast data warehouse, a permitted column often holds sensitive customer details. A support engineer might need to query a customer table to debug an order issue. If that table holds emails, phone numbers, or card numbers, a read grant exposes them. Maintaining separate column grants for every table and team quickly becomes hard to manage. Schema changes can also break queries when users rely on strict column lists.
Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Static database accounts also create credential management work for your platform team. You have to rotate passwords, track host rules, and clean up old accounts by hand. When five engineers share one read role, your audit logs only show the role name. You lose clear visibility into which person ran a heavy query during an incident.
Managing read workflows across teams
To fix this, teams separate user identity from the underlying database privileges. Instead of handing out static database passwords, they tie database sessions to central identity providers. When a user leaves the company, their database permissions stop working right away. Teams also add runtime controls that rewrite matched fields in query results. This approach masks emails, phone numbers, and national IDs before results reach the client. Engineers can still run wildcard queries on tables without getting blocked by column grants.
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 with column grants runs a wildcard select?
ClickHouse refuses the wildcard query and returns no data at all. The query only succeeds if the table contains only the exact columns granted to that user.
What server setup is required before creating SQL users in ClickHouse?
You must configure the access control storage folder on the ClickHouse server. You also need at least one user account with access management privileges enabled.
Can a ClickHouse user grant read rights to another teammate?
A user can only grant the privileges that they already hold themselves. They also need the proper access management rights to create users or assign grants.
Related guides
In this post