ClickHouse DB::Exception: Database access denied

The "DB::Exception: Database access denied" error in ClickHouse indicates that the current user does not have the required privileges to access a specific database. ClickHouse raises this DATABASE_ACCESS_DENIED error when a query targets a database that the user has not been granted permission to use.

Impact

The affected query is rejected entirely, preventing the user from reading, writing, or performing administrative operations on the database in question. If an application relies on access to that database, it will experience failures that can disrupt data pipelines, dashboards, and reporting systems until the appropriate permissions are granted.

Common Causes

1. The user has not been granted any privileges on the target database
2. A role that previously provided access was revoked or reassigned
3. The user is connecting to the wrong database by mistake
4. Row-level or database-level restrictions were recently tightened by an administrator
5. The default_database setting points to a database the user cannot access
6. Using the default user with restricted access after security hardening

Troubleshooting and Resolution Steps

1. Check the user's current privileges:

   SHOW GRANTS FOR my_user;
   

   Look for grants that include the target database.

2. Verify the database exists:

   SHOW DATABASES;
   

   Ensure the database name is spelled correctly and exists on the server.

3. Grant the necessary privileges:

   • For read access:

     GRANT SELECT ON my_database.* TO my_user;
     

   • For full access:

     GRANT ALL ON my_database.* TO my_user;
     

4. Check role assignments:

   • If the user relies on a role for access, verify the role is granted:

     SHOW GRANTS FOR my_user;
     

   • Confirm the role itself has the needed database privileges:

     SHOW GRANTS FOR my_role;
     

5. Review the allow_databases setting:

   • In users.xml, check whether the user's profile restricts database access:

     <allow_databases>
       <database>allowed_db</database>
     </allow_databases>
     

   • If the target database is not listed, add it or remove the restriction.

6. Test with a minimal query:

   SELECT 1 FROM my_database.my_table LIMIT 1;
   

   This helps confirm whether the issue is specifically a database-level permission problem.

Best Practices

• Follow the principle of least privilege: grant only the specific permissions each user or role requires.
• Use roles to manage database access rather than granting privileges directly to individual users.
• Maintain a clear naming convention for roles that indicates which databases they provide access to (e.g., analytics_reader, staging_writer).
• Audit grants periodically using SHOW GRANTS to detect privilege drift.
• Avoid relying on the default user for application access; create dedicated service accounts instead.

Frequently Asked Questions

Q: How can I see which databases a user has access to?
A: Run SHOW GRANTS FOR username to see all granted privileges. You can also query the system.grants table for a programmatic view of all access rights.

Q: Can I grant access to all databases at once?
A: Yes, you can use GRANT SELECT ON *.* TO my_user to grant read access across all databases. Be cautious with broad grants in production environments.

Q: What is the difference between DATABASE_ACCESS_DENIED and ACCESS_DENIED?
A: DATABASE_ACCESS_DENIED is specifically about lacking permission to access a particular database, while a general access denied error may relate to table-level, column-level, or other privilege checks.

Q: Does creating a database automatically grant the creator access to it?
A: The user who creates a database typically has access to it if they have the CREATE DATABASE privilege. However, explicit grants are still recommended to ensure consistent access control.