How to GRANT Permissions in ClickHouse

Galaxy Glossary

How do I grant table, database, or column permissions in ClickHouse?

GRANT assigns privileges on databases, tables, or columns to users or roles in ClickHouse.

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

Description

What problems does GRANT solve?

GRANT lets administrators give fine-grained privileges—like SELECT, INSERT, or ALTER—so users access only the data they need. It supports column-level, database-level, and role-based permissions.

How does basic GRANT syntax work?

Use GRANT privilege_list ON scope TO user_or_role. Separate multiple privileges with commas. “ALL” grants every privilege allowed for the object.

How do I grant SELECT on a single table?

Run:
GRANT SELECT ON ecommerce.Orders TO alice;
Alice can now read every row and column in Orders.

Can I limit access to specific columns?

Yes.Column-level security keeps sensitive data hidden.
GRANT SELECT(name, price) ON ecommerce.Products TO analyst_role;

How to grant multiple privileges at once?

GRANT SELECT, INSERT, UPDATE ON ecommerce.OrderItems TO warehouse_app;
This one line replaces three separate GRANT calls.

How do roles simplify management?

Create a role, grant privileges to the role, then assign users to it.
CREATE ROLE reporting_role; GRANT SELECT ON ecommerce.* TO reporting_role; GRANT reporting_role TO bob;

How to revoke or replace permissions?

Use REVOKE to remove privileges.
REVOKE INSERT ON ecommerce.Orders FROM alice;
To replace a privilege set, use:
GRANT ...WITH REPLACE OPTION

Best practices for GRANT

• Follow least-privilege—grant only what’s required.
• Use roles for teams, not individuals.
• Keep an audit log of GRANT and REVOKE statements via system.query_log.

What’s the difference from PostgreSQL GRANT?

ClickHouse lacks schemas and uses database.table notation, supports column-level GRANT out-of-the-box, but does not yet support row-level security through GRANT.

.

Why How to GRANT Permissions in ClickHouse is important

How to GRANT Permissions in ClickHouse Example Usage


-- Allow the analytics app to query revenue reports
GRANT SELECT, SHOW ON ecommerce.Orders, ecommerce.OrderItems
TO analytics_app;

-- Grant reporting_role read-only access to all ecommerce data
CREATE ROLE reporting_role;
GRANT SELECT ON ecommerce.* TO reporting_role;
GRANT reporting_role TO charlie;

How to GRANT Permissions in ClickHouse Syntax


GRANT [ON CLUSTER <cluster>] <privilege_list>
      ON [<database>.] <table | *>
      [ ( <column_list> ) ]
      TO <user_name | role_name> [,...]
      [WITH GRANT OPTION]

<privilege_list> ::= ALL | SELECT | INSERT | ALTER | OPTIMIZE | 
                     CREATE | DROP | TRUNCATE | SHOW | SYSTEM

Example scopes
• ON *.* – every table in every database
• ON ecommerce.* – every table in the ecommerce database
• ON ecommerce.Orders – single table
• ON ecommerce.Products (name, price) – specific columns

Common Mistakes

Granting ALL ON *.* to individual users. This overexposes data and violates least-privilege. Instead, grant specific privileges on required objects or use a tightly scoped role.
Forgetting WITH GRANT OPTION when a user must delegate permissions. Without it, downstream GRANT attempts fail. Add WITH GRANT OPTION only for trusted admin roles.

Frequently Asked Questions (FAQs)

Can I grant privileges on multiple databases at once?

Yes. Use ON *.* or list databases explicitly, e.g., GRANT SELECT ON sales.*, marketing.* TO analyst_role;

Does GRANT create users?

No. First run CREATE USER or CREATE ROLE, then GRANT privileges to them.

How do I view current grants?

Query system.grants or run SHOW GRANTS FOR user_name;

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