How to Fix “Access Denied” in ClickHouse

Galaxy Glossary

How do I fix “Access denied” errors in ClickHouse?

Resolve ClickHouse "Access denied" errors by granting the correct privileges, roles, or user settings.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why does ClickHouse return “Access denied”?

ClickHouse raises this error when the connected user lacks the privilege needed for the requested action. The server validates privileges defined in users.xml or SQL-based users and roles. Grant the missing privilege, switch to a role that has it, or create a user with broader rights to clear the error.

How to inspect current privileges?

Run SHOW GRANTS FOR current_user. The result lists all direct grants and inherited roles, showing whether SELECT, INSERT, or other rights are missing on ecommerce objects like Customers or Orders. Use this output to craft an exact GRANT statement instead of guessing.

What is the GRANT syntax in ClickHouse?

GRANT privilege [, …] ON [database.]table TO user [WITH GRANT OPTION]; or GRANT ROLE role TO user. Privileges include SELECT, INSERT, ALTER, CREATE, and more. Separate multiple privileges with commas. Use REVOKE to remove them and SHOW GRANTS to confirm.

How to grant SELECT on one table?

Example: GRANT SELECT ON ecommerce.Customers TO analytics_user; lets analytics_user read Customers while keeping other tables protected. Narrow, table-level privileges reduce blast radius and comply with the principle of least privilege.

How to solve “Access denied” for INSERT into Orders?

Grant both INSERT and SELECT when the statement reads from another table: GRANT INSERT, SELECT ON ecommerce.Orders TO writer_user; Without SELECT, INSERT … SELECT fails even if INSERT alone is granted.

Best practices for privilege management

Create roles—read_only, writer, admin—and grant privileges to roles, not users. Assign default roles minimally and use WITH GRANT OPTION only for administrators. Document every GRANT to ease audits.

Common mistakes

Granting on * unknowingly covers future tables; prefer specific tables. Forgetting SYSTEM RELOAD CONFIG after editing users.xml leaves changes inactive; always reload or restart ClickHouse.

Frequently asked questions

Can I grant privileges on all future tables?

Yes. GRANT SELECT ON ecommerce.* TO reporting_role; covers existing and upcoming tables. Use cautiously.

How do I revoke an accidental privilege?

Run REVOKE SELECT ON ecommerce.Customers FROM analytics_user; and verify with SHOW GRANTS FOR analytics_user.

Why do users.xml changes not apply immediately?

ClickHouse caches configs. Execute SYSTEM RELOAD CONFIG or restart the server to apply new user or quota settings.

Why How to Fix “Access Denied” in ClickHouse is important

How to Fix “Access Denied” in ClickHouse Example Usage


-- Fix “Access denied” when loading daily orders
GRANT INSERT, SELECT ON ecommerce.Orders TO loader_bot;

-- Verify the grant
SHOW GRANTS FOR loader_bot;

How to Fix “Access Denied” in ClickHouse Syntax


GRANT { privilege [, ...] | ROLE role [, ...] } 
ON { [db.]table | db.* | *.* } 
TO { user | role } 
[WITH GRANT OPTION];

-- Examples in an ecommerce context
-- 1. Give read access on a single table
GRANT SELECT ON ecommerce.Customers TO analyst;

-- 2. Allow order writers to insert and read their own data
GRANT INSERT, SELECT ON ecommerce.Orders TO order_writer;

-- 3. Assign a pre-defined role
GRANT ROLE read_only TO alice;

Common Mistakes

Granting privileges on *.* without realizing it includes future tables. Instead, specify database.table (e.g., ecommerce.Customers) or use db.* only when intentional.
Editing users.xml and forgetting to reload configuration. Always run SYSTEM RELOAD CONFIG or restart the server to apply privilege changes.