CHouseUI
Configure

ClickHouse privileges

What a connection's ClickHouse user needs for every feature, how Test and Save check it, and a ready-made GRANT script — generated from the server.

Source
Generated from PRIVILEGE_FEATURES in packages/server/src/services/connectionPrivileges.ts, so it always matches the code.

What a connection's ClickHouse user must be able to do for every CHouse UI feature to work. Test connection checks the user against every feature below, and a connection is only saved once every required feature passes: Create and Update run the same check on the server first. Whatever is missing comes back as GRANT statements you can copy and run as a ClickHouse admin.

What each feature needs

FeatureRequired to saveCoversRead (SELECT)Privileges
CoreYesConnect, explorer, SQL editorsystem.databases, system.tables, system.columns, system.functions, system.keywords—
Monitoring and fleetYesMetrics, live queries, dashboardssystem.query_log, system.processes, system.metrics, system.events, system.asynchronous_metrics, system.metric_log, system.asynchronous_metric_log, system.parts, system.parts_columns, system.part_log, system.merges, system.mutations, system.replicas, system.replication_queue, system.disks, system.errors, system.clusters, system.macros, system.distributed_ddl_queue, system.distribution_queue, system.crash_log—
Data health and observabilityYesCollectors, pipelines, Doctor, upgradessystem.tables, system.columns, system.databases, system.dictionaries, system.parts, system.part_log, system.query_log, system.query_views_log, system.processes, system.merges, system.mutations, system.replicas, system.disks, system.errors, system.settings_profile_elements, system.kafka_consumers, system.s3queue, system.s3queue_log, system.azure_queue, system.azure_queue_log, system.view_refreshes, system.distribution_queue, system.asynchronous_insert_log, system.asynchronous_inserts, system.asynchronous_metrics, system.data_skipping_indices, system.server_settings, system.merge_tree_settings—
Query controlYesKill running queries—KILL QUERY on *.*
Data editingYesCreate, import, alter and drop tables—CREATE TABLE, INSERT, ALTER TABLE, DROP TABLE on the default database
ClickHouse access adminNo — warnsClickHouse users and roles pagessystem.users, system.roles, system.grants, system.role_grantsCREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, ROLE ADMIN on *.*

ClickHouse access admin is optional: its privileges let the connection user create other users and hand out grants, which many teams keep away from a UI's service account. Without them the connection still saves, with a warning, and every other feature works; only the ClickHouse users & roles pages fail until you grant them.

Data editing is checked on the connection's default database (default when none is set). Grant the same on any other database people should be able to change. Test connection also lists, for information only, what the user can do in each other database it can see — query all or only some tables, and whether it can change tables — with the GRANT statements for full access; it never blocks the save.

How the check works

  • System tables are read with LIMIT 0, so a grant that comes through a role, or a revoke on one table, counts exactly as ClickHouse applies it. A table the server doesn't have, such as system.crash_log on a server that never crashed, or system.distributed_ddl_queue without Keeper, is shown as not available and doesn't count — except system.query_log, which every server creates on its first flush, so its grant is checked even before the table exists.
  • Other privileges are checked against the user's own SHOW GRANTS, with roles expanded (SHOW GRANTS FINAL where the server supports it, each enabled role on older servers) and ClickHouse's privilege tree from system.privileges, so ALL, ACCESS MANAGEMENT or ALTER cover the privileges under them.
  • The check needs no privilege of its own and changes nothing on the server.

Existing connections are never blocked: the check runs when you create one, change its host, port, user, password, SSL or default database, or press Test. Test on a row of the connection list warns when that connection's user is missing something. A check that fails for another reason, such as a timeout, blocks the save only for a required feature.

A user with everything

Change the password and database names, then run as a ClickHouse admin. The admin needs these privileges itself (WITH GRANT OPTION for the access-management ones); GRANT CURRENT GRANTS is a shortcut when it has more.

sql
CREATE USER chouse_ui IDENTIFIED BY 'change-me';
-- What people query: repeat for each database
GRANT SELECT ON analytics.* TO chouse_ui;
GRANT SELECT ON system.databases TO chouse_ui;
GRANT SELECT ON system.tables TO chouse_ui;
GRANT SELECT ON system.columns TO chouse_ui;
GRANT SELECT ON system.functions TO chouse_ui;
GRANT SELECT ON system.keywords TO chouse_ui;
GRANT SELECT ON system.query_log TO chouse_ui;
GRANT SELECT ON system.processes TO chouse_ui;
GRANT SELECT ON system.metrics TO chouse_ui;
GRANT SELECT ON system.events TO chouse_ui;
GRANT SELECT ON system.asynchronous_metrics TO chouse_ui;
GRANT SELECT ON system.metric_log TO chouse_ui;
GRANT SELECT ON system.asynchronous_metric_log TO chouse_ui;
GRANT SELECT ON system.parts TO chouse_ui;
GRANT SELECT ON system.parts_columns TO chouse_ui;
GRANT SELECT ON system.part_log TO chouse_ui;
GRANT SELECT ON system.merges TO chouse_ui;
GRANT SELECT ON system.mutations TO chouse_ui;
GRANT SELECT ON system.replicas TO chouse_ui;
GRANT SELECT ON system.replication_queue TO chouse_ui;
GRANT SELECT ON system.disks TO chouse_ui;
GRANT SELECT ON system.errors TO chouse_ui;
GRANT SELECT ON system.clusters TO chouse_ui;
GRANT SELECT ON system.macros TO chouse_ui;
GRANT SELECT ON system.distributed_ddl_queue TO chouse_ui;
GRANT SELECT ON system.distribution_queue TO chouse_ui;
GRANT SELECT ON system.crash_log TO chouse_ui;
GRANT SELECT ON system.dictionaries TO chouse_ui;
GRANT SELECT ON system.query_views_log TO chouse_ui;
GRANT SELECT ON system.settings_profile_elements TO chouse_ui;
GRANT SELECT ON system.kafka_consumers TO chouse_ui;
GRANT SELECT ON system.s3queue TO chouse_ui;
GRANT SELECT ON system.s3queue_log TO chouse_ui;
GRANT SELECT ON system.azure_queue TO chouse_ui;
GRANT SELECT ON system.azure_queue_log TO chouse_ui;
GRANT SELECT ON system.view_refreshes TO chouse_ui;
GRANT SELECT ON system.asynchronous_insert_log TO chouse_ui;
GRANT SELECT ON system.asynchronous_inserts TO chouse_ui;
GRANT SELECT ON system.data_skipping_indices TO chouse_ui;
GRANT SELECT ON system.server_settings TO chouse_ui;
GRANT SELECT ON system.merge_tree_settings TO chouse_ui;
GRANT KILL QUERY ON *.* TO chouse_ui;
-- The connection's default database
GRANT CREATE TABLE, INSERT, ALTER TABLE, DROP TABLE ON analytics.* TO chouse_ui;
-- Optional: ClickHouse access admin
GRANT SELECT ON system.users TO chouse_ui;
GRANT SELECT ON system.roles TO chouse_ui;
GRANT SELECT ON system.grants TO chouse_ui;
GRANT SELECT ON system.role_grants TO chouse_ui;
GRANT CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, ROLE ADMIN ON *.* TO chouse_ui;

To let ClickHouse users & roles hand out privileges to others, the connection user also needs those privileges WITH GRANT OPTION.