How to Manage Users in ClickHouse

Galaxy Glossary

How do I manage users and permissions in ClickHouse?

CREATE USER, ALTER USER, GRANT, and DROP USER let you add, modify, privilege, and remove accounts in ClickHouse.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why create separate ClickHouse users?

Separate users isolate query history, resource quotas, and data-access rights, simplifying audit and security reviews.

What is the quickest way to add a user?

Run CREATE USER john IDENTIFIED BY 'S3cur3!'. The account is created instantly and can be granted permissions next.

How do I grant read-only access to Orders?

First create the role, then grant it: GRANT SELECT ON ecommerce.Orders TO john. The user can now query order data but not modify it.

Can I batch-grant rights to multiple tables?

Yes.Use wildcards or schemas: GRANT SELECT ON ecommerce.* TO analyst_role and then GRANT analyst_role TO john.

How do I change an existing password?

Execute ALTER USER john IDENTIFIED BY 'N3wP@ss'. The new credential is active immediately; no service restart required.

What happens when a user leaves?

Remove access fast with DROP USER IF EXISTS john. All active sessions terminate, and privileges disappear.

Best practice: use roles not direct grants

Create roles like readonly_orders and grant them to users.This centralizes privilege management and prevents drift.

Best practice: keep passwords external

Store secrets in users.xml with environment variable substitution or use LDAP/OAuth plugins instead of plaintext SQL.

.

Why How to Manage Users in ClickHouse is important

How to Manage Users in ClickHouse Example Usage


-- Create read-only analyst and limit to order data
CREATE USER analyst IDENTIFIED BY 'a9xY!k';
GRANT SELECT ON ecommerce.Orders TO analyst;
-- Verify
SHOW GRANTS FOR analyst;

How to Manage Users in ClickHouse Syntax


CREATE USER [IF NOT EXISTS] name [ON CLUSTER cluster] [IDENTIFIED [{WITH|BY} auth_type] 'password'] [HOST IP | HOST REGEXP 'regex'] [DEFAULT ROLE role];
ALTER USER [IF EXISTS] name [ON CLUSTER cluster] [RENAME TO new_name] [IDENTIFIED BY 'new_password'] [DEFAULT ROLE role];
DROP USER [IF EXISTS] name [ON CLUSTER cluster];
GRANT privilege_list ON [db.]table TO {user|role} [WITH GRANT OPTION];
REVOKE privilege_list ON [db.]table FROM {user|role};
-- Ecommerce example
CREATE USER readonly IDENTIFIED BY 'ro_pass';
GRANT SELECT ON ecommerce.Orders, ecommerce.OrderItems TO readonly;

Common Mistakes

Granting privileges directly to every user. This becomes unmanageable. Fix by creating roles (e.g., readonly_orders) and granting roles to users.
Leaving default passwords or using plaintext files checked into Git. Always change defaults immediately and externalize secrets via env vars or secret managers.

Frequently Asked Questions (FAQs)

Can I force a user to change their password?

No built-in flag exists. Instead, set a temporary strong password with ALTER USER and communicate it securely.

How do quotas interact with users?

Attach a QUOTA to the user or role to limit query time, read rows, or errors per interval.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie