How to REVOKE Permissions in ClickHouse

Galaxy Glossary

How do I revoke user permissions in ClickHouse?

REVOKE removes previously granted privileges, roles, or quotas from users or roles in ClickHouse.

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

What does REVOKE do in ClickHouse?

REVOKE strips a user or role of previously granted privileges, roles, or quotas. Use it to tighten security, clean up unused access, or correct accidental GRANTs.

How do I write the REVOKE syntax?

Start with the privilege list, optionally add ON object, then use FROM followed by users or roles. Append GRANTED BY when removing rights granted by another account.

How to remove SELECT on a table?

Run:
REVOKE SELECT ON ecommerce.Customers FROM analyst;
This prevents analyst from reading customer data while leaving other rights intact.

How to drop all privileges from a user role?

Use
REVOKE ALL PRIVILEGES ON *.* FROM sales_role;
to wipe every permission the sales_role holds across all databases and tables.

Can I REVOKE privileges from multiple users at once?

Yes. Separate principals with commas:
REVOKE INSERT, UPDATE ON ecommerce.Orders FROM alice, bob;

Best practices for managing privileges

Grant privileges to roles, not individuals, then assign roles to users. Review privilege sets periodically and automate revocations when employees change teams.

Common errors and how to fix them

Error: “Privilege not found” occurs when you omit the ON clause. Always specify the object unless revoking global rights.

Error: Revoking from a user who inherited rights from a role does nothing. Revoke from the role instead or detach the role from the user.

Why How to REVOKE Permissions in ClickHouse is important

How to REVOKE Permissions in ClickHouse Example Usage


-- Remove DELETE on Orders from a support user
REVOKE DELETE ON ecommerce.Orders FROM support_agent;

-- Revoke SELECT on Products and OrderItems from a temporary contractor
REVOKE SELECT ON ecommerce.Products, ecommerce.OrderItems FROM temp_contractor;

How to REVOKE Permissions in ClickHouse Syntax


REVOKE [ON CLUSTER cluster_name]
    privilege [, privilege2 …] | ALL PRIVILEGES
    ON [database_name.]table_name | *.*
    FROM user_name | role_name [, …]
    [GRANTED BY grantor]

-- Common privileges
-- SELECT, INSERT, UPDATE, DELETE, ALTER, SHOW, TRUNCATE, ALL

-- Example objects
-- ecommerce.Customers, ecommerce.Orders, ecommerce.Products, ecommerce.OrderItems

Common Mistakes

Omitting the ON clause: <code>REVOKE SELECT FROM analyst;</code> fails because ClickHouse doesn't know which object. Fix by adding the object: <code>REVOKE SELECT ON ecommerce.Customers FROM analyst;</code>
Revoking from the user instead of the role: If a user has SELECT via <code>sales_role</code>, <code>REVOKE SELECT ON Orders FROM alice;</code> changes nothing. Revoke from the role or detach the role from the user.