ClickHouse DB::Exception: Invalid grant

The "DB::Exception: Invalid grant" error in ClickHouse is raised when a GRANT statement is structurally invalid or violates ClickHouse's access control rules. The INVALID_GRANT error code typically appears when a user tries to grant privileges they do not hold themselves, or when the grant references an invalid combination of privilege and target object.

Impact

The privilege assignment fails, leaving the target user or role without the intended permissions. This can block deployments, prevent team members from accessing the resources they need, and stall provisioning workflows that depend on successful grant execution.

Common Causes

1. Attempting to grant a privilege that the granting user does not possess (missing WITH GRANT OPTION)
2. Granting a privilege on an object type where it does not apply (e.g., INSERT on a view that does not support inserts)
3. Using an invalid syntax in the GRANT statement
4. Trying to grant privileges on a nonexistent database or table
5. Granting column-level privileges with an incorrect column specification
6. Circular grant dependencies or conflicting privilege definitions

Troubleshooting and Resolution Steps

1. Check the granting user's own privileges:

   SHOW GRANTS FOR current_user;
   

   The granting user must hold the privilege with GRANT OPTION to pass it on.

2. Add GRANT OPTION to the granting user if needed:

   GRANT SELECT ON my_database.* TO admin_user WITH GRANT OPTION;
   

3. Verify the target object exists:

   SHOW TABLES FROM my_database;
   

   Ensure the database and table referenced in the GRANT statement actually exist.

4. Review the privilege-object combination:

   • Not all privileges apply to all object types. For example, INSERT cannot be granted on a dictionary.
   • Verify that the privilege makes sense for the target object.

5. Check statement syntax:

   • A valid GRANT statement follows this pattern:

     GRANT SELECT, INSERT ON my_database.my_table TO my_user;
     

   • For column-level grants:

     GRANT SELECT(col1, col2) ON my_database.my_table TO my_user;
     

6. Use an admin account:

   • If you are unsure about privilege chains, connect as a user with full administrative rights to execute the GRANT.

Best Practices

• Grant WITH GRANT OPTION only to administrators who genuinely need to delegate privileges.
• Structure your privilege hierarchy so that a small number of admin accounts manage grants, reducing the chance of invalid grant chains.
• Validate GRANT statements in a test environment before running them in production.
• Use roles as intermediaries: grant privileges to roles, then assign roles to users, which simplifies the privilege model.
• Document the intended privilege structure to make it easier to troubleshoot when grant errors occur.

Frequently Asked Questions

Q: What does "WITH GRANT OPTION" mean?
A: It allows the recipient to grant the same privilege to other users or roles. Without it, a user can use the privilege but cannot pass it on.

Q: Can I grant privileges on a database that does not exist yet?
A: No, ClickHouse validates that the target object exists at the time of the GRANT. Create the database or table first, then grant privileges on it.

Q: Why do I get INVALID_GRANT when I am an admin?
A: Even admin users must hold the specific privilege with GRANT OPTION, or be connected as the default user with full access. Check that your admin role includes WITH GRANT OPTION for the privileges in question.

Q: Can I grant all privileges at once?
A: Yes, you can use GRANT ALL ON my_database.* TO my_user to grant all applicable privileges on a database. The granting user must hold all those privileges with GRANT OPTION.