How to Configure ClickHouse Create User

Published
Length
3 min read

Luis Felipe Dias

All posts

You run a clickhouse create user statement through SQL-driven access control. This step sets how an account logs in and what roles it gets. Many teams set up a few shared accounts for their devs and apps. Broad roles make setup fast, but they expose sensitive data to every query. A single mistake can leak private fields or overload the server. How the clickhouse create user workflow works ClickHouse treats users, roles, row rules, settings profiles, and quotas as access objects. You manage th

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 run a clickhouse create user statement through SQL-driven access control. This step sets how an account logs in and what roles it gets. Many teams set up a few shared accounts for their devs and apps. Broad roles make setup fast, but they expose sensitive data to every query. A single mistake can leak private fields or overload the server.

How the clickhouse create user workflow works

ClickHouse treats users, roles, row rules, settings profiles, and quotas as access objects. You manage these objects with SQL statements instead of static files. Before you start, you must turn on SQL-driven access control on the server. This setup needs a storage folder defined in the access control path setting. You also need an initial account with access management and named collection control set to one.

Once you turn on SQL control, an admin can add new accounts. Running the user creation statement requires the create user privilege. Also, an admin can only grant privileges that they hold themselves. During creation, you define the login method for the new account. You can also limit which hosts the person or app can log in from. The statement lets you assign default roles and custom per-user settings right away.

Scoping read access with roles and column grants

After you make an account, you must give it specific read or write permissions. Most teams group these permissions into roles and assign them to accounts. You do not have to grant read rights to an entire table. ClickHouse lets you grant select rights on specific columns in a table. If a user only has rights to two columns, they can query only those two. Any query that asks for a third column will fail.

Column grants also change how wildcard queries work for that person. A select all query gets refused and returns no data at all. It only works if the table holds just the granted columns. Starting in version 24.10, admins can also use a trailing asterisk in grants. This rule grants access to tables that share a specific name prefix. It saves time when your team manages many tables with similar names.

User Provisioning (SCIM) + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native user roles solve and what they miss

Native database accounts and roles solve the basic problem of who can log in. They verify the person or app at the start of a session. They also set static boundaries around which tables and columns a role can read. This setup works well when data stays clean and roles match team duties.

Still, static grants cannot inspect what individual queries do in flight. If an analyst needs a table for work, they see every row in that column. Native grants do not mask emails, phone numbers, card numbers, or national IDs in results. Sharing role passwords across a team also hides who ran a bad query. When an AI tool or script uses a shared account, the audit log shows one generic name. You need runtime controls to check queries and rewrite sensitive fields before data leaves.

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 server settings do I need before making users with SQL?

You must configure the access control path directory on the server first. You also need at least one account with access management and named collection control set to one.

Why does a wildcard query fail for a user with column grants?

ClickHouse blocks wildcard select queries when a role only has grants for specific columns. The query only succeeds if the table contains nothing but those exact granted columns.

Can a team lead grant any permission to a new ClickHouse account?

No, an account needs the create user privilege to make a new user. Also, a person can only grant privileges that they already hold on their own account.

The query runs. The emails stay hidden.