ClickHouse DB::Exception: Dictionary access denied

The "DB::Exception: Dictionary access denied" error in ClickHouse appears when a user tries to query or interact with a dictionary without having the necessary permissions. The DICTIONARY_ACCESS_DENIED error code signals that the server recognized the dictionary but denied the request due to insufficient privileges.

Impact

Any query that references the restricted dictionary will fail. This includes calls to dictGet, dictHas, and other dictionary functions, as well as queries against tables backed by the Dictionary engine. Applications that rely on dictionary lookups for enrichment or data transformation will produce errors until the permission issue is resolved.

Common Causes

1. The user has not been granted the dictGet privilege on the specific dictionary
2. A role providing dictionary access was revoked or not assigned to the user
3. The dictionary was recently created and permissions were not configured for existing users
4. Using SELECT from a Dictionary engine table without having the dictGet privilege
5. Confusion between database-level and dictionary-level permissions

Troubleshooting and Resolution Steps

1. Verify the user's current privileges:

   SHOW GRANTS FOR my_user;
   

   Look for dictGet or related dictionary privileges on the target dictionary.

2. Confirm the dictionary exists:

   SHOW DICTIONARIES;
   

   Ensure the dictionary name and database are correct.

3. Grant dictionary access:

   • For a specific dictionary:

     GRANT dictGet ON my_database.my_dictionary TO my_user;
     

   • For all dictionaries in a database:

     GRANT dictGet ON my_database.* TO my_user;
     

4. Check role-based access:

   • If the user depends on a role for dictionary permissions, verify the role assignment:

     SHOW GRANTS FOR my_user;
     SHOW GRANTS FOR my_role;
     

5. Test access with a simple query:

   SELECT dictGet('my_database.my_dictionary', 'value_column', toUInt64(1));
   

6. Review XML-based dictionary configurations:

   • If dictionaries are defined in XML files, check that no <allow_databases> or profile restriction is blocking access to the database containing the dictionary.

Best Practices

• Grant dictionary privileges through roles rather than directly to users for easier management.
• When creating new dictionaries, include the privilege grants as part of the deployment process.
• Use the principle of least privilege: grant dictGet rather than broader permissions when users only need to read dictionary values.
• Document which dictionaries each application role requires access to.
• Periodically review dictionary access grants, especially after organizational changes.

Frequently Asked Questions

Q: What privilege do I need to read from a dictionary?
A: You need the dictGet privilege on the specific dictionary. This can be granted with GRANT dictGet ON database.dictionary TO user.

Q: Is dictionary access controlled separately from table access?
A: Yes. Dictionary privileges are distinct from table-level SELECT privileges. Even if a user can query tables in a database, they need explicit dictGet grants to access dictionaries.

Q: Can I grant access to all dictionaries at once?
A: You can grant dictGet ON *.* to allow access to all dictionaries across all databases, or dictGet ON my_database.* for all dictionaries in a specific database.

Q: Why do I get this error when querying a table that uses the Dictionary engine?
A: Querying a Dictionary engine table internally requires dictGet privileges on the underlying dictionary. You need both SELECT on the table and dictGet on the dictionary.