Role-based access control in ClickHouse

Why role-based access control in ClickHouse decides who can read, write and administer data

On a self-managed cluster that started as a proof of concept, the default user often still holds every permission, and analysts, ingestion pipelines and administrators connect with the same credentials or with accounts created ad hoc. ClickHouse access control is the mechanism that replaces this with deliberate boundaries. It covers users, roles, row policies, settings profiles and quotas, and together they decide which tables a person can query, which statements a pipeline can run, which rows are visible, how much resource a session can consume and who can change any of it. The ClickHouse access control documentation recommends managing these objects through SQL rather than hand-edited configuration, and recommends restricting the default user, which starts with all permissions.

Access design belongs before go-live, not after the first incident. Once dashboards, Kafka consumers and scheduled inserts are wired to shared accounts, changing credentials or splitting privileges means coordinating with every owner at once. Before you open the cluster to users, confirm which identities you need (human analysts, service accounts for ingestion, a small set of administrators), which of them must be able to write, and whether any data is restricted by tenant or region. Also confirm how your team will run access reviews, since the SQL-driven workflow makes grants inspectable but someone still has to schedule and perform the review.

Access control in ClickHouse is an authorization layer, not a substitute for authentication design. Decide first how identities are verified, whether by password, certificates or one of the external authenticators the ClickHouse access control documentation describes, and then build roles and grants on top. Any compliance outcome from this work depends on your policies, your evidence and an independent assessment, so treat the grant model as an input to those reviews rather than a conclusion.

Which role pattern fits each ClickHouse workload

This table compares five common role patterns in self-managed ClickHouse: analyst, dashboard service, ingestion, schema change and administrator. Read each row for the typical grants, the risk if the role is too broad, and the question to ask in a review. Treat the grant lists as starting points, since exact names and scopes vary by release.

Role pattern	Typical grants	Main risk if too broad	Review question
Read-only analyst	SELECT on the specific databases or tables analysts need, with row policies applied where tenants or regions are separated	Access to raw event or personal data that the analyst's job does not require, and unbounded queries against large tables	Which databases and columns does this person query, and is a quota or settings profile limiting their resource use?
Dashboard service	SELECT on the reporting tables or views only, granted to a dedicated service account with its own password or certificate	Shared credentials spread across dashboard tools, and a compromised tool exposing every table it can read	Does each dashboard tool have its own account, and can each account read only the objects its dashboards use?
Ingestion	INSERT on the target tables, and only the minimal extra rights needed for the pipeline, such as access to the source it reads from	Ability to write into reporting tables or to delete and alter existing data, which can corrupt history	Can the ingestion account write only to landing tables, and can it be prevented from running ALTER or DROP?
Schema change	Scoped CREATE, ALTER and DROP rights on named databases, typically used through a change process rather than by everyday users	Accidental or malicious drops and alterations of production tables, and loss of data that replication will faithfully copy	Who can run schema changes, is each change reviewed and logged, and does the role exclude production data reads?
Administrator	Broad rights for user, role and grant management, access to system tables, and cluster-level operations, held by a small named group	Full control over access, audit settings and data, with no separation between the people who grant access and those who use data	Is the admin account named and individual, is the default user restricted, and is admin use logged and reviewed?

Setting up roles and grants in SQL, step by step

This sequence turns an open ClickHouse server that starts with broad default permissions into named users whose access follows the workloads they serve. Each step ends with a check you can repeat after every change.

Inventory workloads, owners and data sets
Before writing any SQL, list every process and person that touches the cluster: dashboards, ingestion jobs, scheduled reports, administrators and on-call engineers. For each one, record the owner, the databases and tables it reads or writes, and whether it needs to create or alter objects. Review the list with the data owners and the platform team. The step is done when every entry has a named owner and a clear read, write or admin need, and nothing is left as a shared login.
Create roles before users
Create one role per access pattern, such as a read-only analyst role for a set of databases, an ingestion role for a specific landing table, and an admin role for schema changes. Keeping grants on roles rather than on individual users means a new analyst inherits the right access in one assignment. Confirm the exact CREATE ROLE syntax and the privilege names against the ClickHouse release you run, because privilege granularity has changed across versions. Check the result with SHOW ROLES and confirm each role exists with no grants yet.
Grant the minimum privileges to each role
Grant only the privileges each workload needs, scoped to the narrowest database or table that works. An analyst role typically needs SELECT on specific databases, while an ingestion role needs INSERT on its landing table and nothing else. Avoid wildcard grants on the whole server unless the role is an administrator role. Confirm the privilege names for your release in the access control documentation. Verify each role with SHOW GRANTS FOR the role name and compare the output line by line against the inventory from the first step.
Create named users and assign roles
Create one user per person or service account, with its own credentials, and assign the matching roles. Service accounts for ingestion should not share a login with an analyst or an administrator. Use the authentication method your organization requires, and if you plan to delegate authentication to LDAP, Kerberos, JWT or another external authenticator, confirm how user definitions and role mappings work with that method before creating accounts. Check the result with SHOW GRANTS FOR on each user and confirm that inherited privileges match the role assignment.
Set default roles for each user
Set the default roles so that each session starts with only the privileges the user needs for routine work. Administrators should keep their elevated role out of the default set and activate it deliberately when a change is required. Confirm the default role syntax and behavior for your release, including how sessions that connect through a client with multiple roles are handled. The step is done when a fresh session for each test user reports only the expected roles, which you can confirm with SHOW CURRENT ROLES or an equivalent query.
Disable or restrict the default user
The default user starts with all permissions, so leaving it open with no password or with broad network access undermines the named accounts you just created. Set a strong password or disable the account, and limit the network addresses it can connect from. Coordinate this with the team that uses the default account for bootstrapping, and move that work to a named administrator. The check is that a connection attempt as the default user is refused or limited as intended, and that every named administrator can still manage access.
Test each role by connecting as the user
Connect as a test account for each role and run the statements that role should allow, then run statements it should not, such as an INSERT from the analyst account or a DROP TABLE from the ingestion account. Each denied statement should fail with an access error, and each allowed statement should succeed. Repeat the test after any change to grants or roles. The step is done when every role has a recorded pass for allowed work and a recorded denial for forbidden work, and the results are kept with the access review record.
Record the baseline for later access reviews
Save the output of SHOW ACCESS and the grants for each role and user as the baseline for the first access review. Store it in version control or your change management system with the date and the name of the approver. Later reviews compare the live output against this baseline to catch grants added outside the process. Ask your team to confirm the exact output format for the release in use, since the query output and available fields can differ between versions.

Grant design mistakes that weaken ClickHouse access control

Most weak ClickHouse access models are not missing features. They are defaults left in place, accounts shared across jobs, and grants that were convenient on day one. The cards below cover six mistakes we see most often, what each one allows, and the fix to apply during design or review.

Leaving the default user open

The default user starts with all permissions, so anyone who can reach the server on a network path without a password or with a known credential gets full control. Check whether the default user can log in from your client networks and whether it still holds administrative grants. The fix is to set a strong password or disable the account, restrict its network access, and create a named administrator through SQL. Confirm the exact behavior of your version before relying on it.

Sharing one ingestion account

When every pipeline writes with the same account, a broken or compromised job can modify tables that belong to other teams, and audit logs cannot show which pipeline ran a given insert. Create one ingestion role per pipeline, granting INSERT only on the tables that pipeline feeds. Use separate credentials for each job so that rotating one does not stop the others, and review query_log by user to confirm that each account touches only its own tables.

Granting ALL on the server

A convenience role built with GRANT ALL ON *.* is effectively an administrator, even when it was created for a reporting team. Any analyst assigned that role can drop databases, alter tables or change other users' grants. Start from the smallest set of SELECT grants on named databases and tables, and add write or DDL privileges only when a documented task needs them. Treat any wildcard grant in a review as a finding that needs an owner and a reason.

Mixing admin and analyst duties

An account that runs dashboards and also manages users or changes settings means a single phishing or session theft exposes the whole server. Keep administrative roles on named accounts that analysts never use for daily queries, and require a separate login for maintenance work. Review which roles include access management privileges, since those roles can grant more access to themselves or others. Document who may hold each administrative role.

Trusting an unreviewed directory group

If LDAP, Kerberos or another external authenticator maps a directory group to ClickHouse roles, anyone added to that group in the directory gains database access without a ClickHouse change. Nobody on the database team may see the membership change. List every group-to-role mapping, confirm the owner of each directory group, and include those groups in the same quarterly access review as local accounts. Ask your identity team how membership changes are approved and logged.

Keeping unreviewed users.xml accounts

Accounts defined in users.xml sit in configuration files, so they often escape the SQL review that covers accounts created with CREATE USER. They may also persist across restarts and show up on nodes that were never updated, which leaves hidden accounts on some replicas. Inventory every account in configuration and in SQL on every node, record an owner for each one, and move the ones you keep into SQL-managed definitions. Remove accounts that no one can explain.

Migrating from users.xml to SQL-driven access control

Teams move users defined in users.xml and users.d toward SQL-managed users and roles because SQL changes can be written, reviewed and applied the same way at every step, and the resulting access state can be inspected at any time with SHOW ACCESS, SHOW GRANTS and the access-related system tables. XML changes need a file edit and a server reload on each node, which makes drift between replicas more likely. The migration is not a single switch. Keep the XML definitions in place until every application has been verified against its new SQL account, so a mistake never locks out a working client.

Work in this order. First, inventory every XML user, its password or authentication method, its network restrictions, its profile and quota, and the databases and tables it reads or writes. Then recreate each one as an SQL user with the same or narrower rights, using roles for shared grants so that privileges are defined once. Give the new accounts their own credentials and test them on a non-production cluster first. Move each application to its new account, confirm it connects and runs its real queries, and only then remove the matching entry from users.xml or users.d. Note that default and any admin account need the most care, because removing the wrong entry can end administrative access. Make this a documented step with a named owner for each application.

Access-managed entities, meaning SQL users, roles, row policies, quotas and profiles, must be stored somewhere that survives restarts, and the configured access storage location decides whether they persist and where they are replicated. Confirm with the ClickHouse documentation for the release you run how the access storage is configured, how entities defined in XML interact with SQL-defined entities of the same name, and whether changes made on one node reach the others. Verify this on a staging cluster by restarting nodes and checking that the grants still exist before you depend on the new model in production.

Running SHOW ACCESS reviews by audience

Security reviewers check whether the live access model matches the approved design: who can authenticate, who holds administrative rights and whether any principal can read data it should not. They start with SHOW ACCESS to list users, roles, row policies, quotas and settings profiles in one output, then drill into individual principals with SHOW GRANTS FOR and SHOW CREATE USER. The comparison only works if the baseline is the signed-off design document. The access control documentation describes each statement.

Administrative rights: Confirm that only named administrators hold grants that can change users, roles or grants, and that the default user is restricted or disabled.
Broad grants: Search for GRANT ALL and database-wide grants that do not map to a documented role.
Row policy coverage: Use SHOW ROW POLICIES to confirm every table holding tenant or regional data has the policies the design requires, and that no unexpected principal is exempt.
Authentication paths: Check each account's authentication method against the approved list, including any external authenticators in use.

Record each finding with a ticket, an owner from your register and a decision marked as accepted or remediated.

Platform operators keep the cluster running and need to know that access changes took effect everywhere they were applied. Their review confirms that statements ran on the intended nodes, that configuration-file users still match what the team expects and that limits are attached to the right principals. Confirm with your own deployment how access entities are stored and propagated in your cluster version, because the answer determines where a SHOW statement must be run to see the truth.

Cluster consistency: Run the review against more than one node, or verify replication of access changes in your setup, and recheck after restarts and node replacements.
Configuration-file users: List users defined in users.xml or users.d separately, since they must be reviewed alongside SQL-defined accounts until migration is complete.
Quotas and settings profiles: Use SHOW QUOTAS and SHOW SETTINGS PROFILES to confirm limits are attached to the principals the design names.
Change history: Keep a record of who ran each GRANT, REVOKE or CREATE statement, using the audit trail your change process already produces.
Cadence: Run a short check after each release or access change, and a full review on the schedule your security owner agrees.

Flag any principal that exists on one node but not another before the next review closes.

Application owners are responsible for the datasets and service accounts their teams use. The review asks whether each account holds only the grants its pipeline or dashboard needs. Owners do not usually run the commands themselves. Security or platform staff produce the output, and the owner signs off on whether each grant still has a business reason.

Service accounts: Confirm each ingestion or dashboard account holds the write or read rights its target tables require, and no DDL or drop rights, using SHOW GRANTS FOR on the account name.
Read paths: Check that analyst roles reach only the databases, tables and views they were approved to use.
Row policy filters: Confirm the filter that restricts a tenant or region still matches the business rule after schema or pipeline changes.
Unused grants: Identify grants with no current use, so they can be removed in the next change window.
Cadence: Review when a pipeline changes, when staff change roles, and at the periodic review your security owner agrees.

Return a written confirmation to the security reviewer that each account's grants match its pipeline, with any exceptions listed by name.

How role-based access control works with row policies and quotas

Roles decide which objects and actions a user may use, row policies decide which rows of a table are visible to that user, and settings profiles and quotas decide how much work the user may consume. These are separate controls, and a secure ClickHouse design needs all three to answer a different question. A grant answers whether a user can run SELECT on a table at all. A row policy answers which rows that SELECT returns. A quota answers how many queries, rows or seconds the user may spend in a time window. Treat each one as its own decision, and write it down that way before you create any accounts.

The main trade-off is review effort. A single broad role combined with a row policy is quick to create, but the effective access then depends on the policy as well as the grant, so an auditor has to read two places to know what a user sees. Several narrow roles, each granting one well-defined read or write pattern, are easier to review with SHOW ACCESS and SHOW GRANTS, but they take more design work up front. Quotas do not replace grants: a user with a generous quota can still read or change anything they are granted, and a user with no quota can still be blocked by a missing grant. Confirm which settings profile constraints apply to each role, since they also shape what a session can do.

The detailed mechanics of row policies and quotas each have their own sibling pages, so this section only sets the model they fit into. Before go-live, create a few test accounts that mirror real roles, run representative queries as each one, and check the returned rows, the denied statements and the quota counters against what you intended. Use the ClickHouse access control documentation as the reference for the statements you will rely on, and confirm the behavior on your own ClickHouse version in a pre-production environment.

Limits to confirm before you rely on these roles

The role design only holds if the ClickHouse version, cluster topology and identity provider behave the way the design assumes. The cards below list what to confirm before production, who confirms each item, and where the documentation is the natural starting point.

Privilege support per release

Grant-level granularity differs between releases, and privilege names can change when a version is upgraded. Confirm that every privilege your analyst, ingestion and admin roles depend on exists in the exact release you will run, and test each role against a staging cluster on that release. The database engineer owns this check, and the answer should be recorded alongside the role definitions. Re-run it before each upgrade, since the security policy only covers a fixed set of supported versions.

Storage and backup of access entities

Users, roles, row policies and quotas are stored as access-managed entities, and their storage location decides how they replicate and how they are backed up. Confirm where entities live in your cluster, whether SQL-created entities reach every replica, and whether your backup procedure captures them. A restore that recovers tables but not grants leaves analysts locked out or, worse, with the wrong access. The platform team should confirm this with a restore test on a spare cluster.

Roles across replicas and shards

A role created on one node does not automatically mean every query path enforces it. Confirm how access entities propagate across the replicas and shards behind your load balancer, and whether Distributed tables evaluate grants on the initiating node or on each shard. Test a read-only role against each node type with a query that should fail. The cluster operator should own this test, and it should run after every topology change.

External authenticator group mapping

When LDAP, Kerberos, HTTP or JWT authentication supplies group membership, the mapping from external groups to ClickHouse roles determines real access. Confirm how group names are matched, what happens when a user belongs to several groups, and whether removing someone from a directory group removes their ClickHouse access on their next session. The identity engineer should verify this with a test account in each group, using the external authenticators documentation as the reference.

Default user and XML user interaction

The default user starts with all permissions, and users defined in XML files can coexist with SQL-created users during migration. Confirm which accounts can still log in on each port, which take precedence when names collide, and whether any XML user retains privileges that the SQL roles were meant to remove. Run a privilege review with SHOW ACCESS after migration and disable or restrict the default user only once named admins are confirmed. The security engineer signs off on the final state.

Identity provider group alignment

Your identity provider is the source of truth for who holds analyst or admin access, so its group names must match the roles you design. Confirm which groups are issued to each application, who approves membership changes, and how leavers are removed on a schedule that fits your access review cycle. Mismatched or stale groups are the most common reason a role grants more than intended. The IAM lead owns the group catalog, and the ClickHouse owner confirms the mapping before go-live.

How much work is role-based access control in ClickHouse, and what does it need from us?

How long does a design and rollout take for a small set of workloads?

A small set of workloads, such as one analyst group and one ingestion pipeline on a single cluster, is the simplest case because the role model stays short and the number of grants to test is limited. Timing depends less on the SQL itself than on how quickly your team confirms who needs what, approves the role model, and agrees a change window for the rollout. No duration is stated here because it follows from a written scope. Ask for a plan that lists the roles, the workloads each one covers, the test accounts, and the review step before any production change.

What makes role-based access control in ClickHouse take longer?

Effort rises with the number of workloads, the number of teams that own them, and how many distinct access levels they need. Four drivers matter most:

The number of workloads and tables, since each new database multiplies the grants to design and test.
Integration with an external directory such as LDAP, Kerberos or an identity provider, which adds mapping between groups and roles.
Migration from users.xml to SQL-driven management, which requires inventorying existing accounts and profiles.
Review requirements from security or compliance, which add approval records and evidence of testing.

Each driver can be sized from your inventory before any work starts.

Can our internal team design and roll out ClickHouse access control alone?

Yes, if your team has ClickHouse administration skills and can test changes outside production. The access-control model is SQL-driven, so the required knowledge is mostly about privileges, roles, row policies and quotas, plus the operational discipline to review grants. Teams usually find the hard part is the workload inventory and the ownership decisions, not the statements themselves. A partner adds value when the scope is wide, when an external directory is involved, or when an independent evidence trail is required. Decide this by comparing your available hours with the scope you write down.

What does the work need from our team before it starts?

The most important input is a workload inventory that lists each database, table, ingestion path, dashboard and scheduled job, with a named owner for each. The work also needs:

Test accounts or a spare cluster where role changes can be validated without touching production.
Access to current users.xml files, settings profiles and any external authenticator configuration.
Change windows agreed with the teams that depend on the cluster.
A named approver for the final role model.

Without owners, the design stalls because nobody can say which grants are still needed.

What is one-time work and what recurs after the rollout?

The design, the initial role model, the migration of accounts and the first testing are one-time work. Recurring work is the access review: checking SHOW ACCESS output and role membership on a schedule, removing grants for people who have left or changed teams, and onboarding new workloads as they appear. Ask any provider to state which of these are included in the fixed scope and which would be covered by an ongoing retainer. Recurring review effort grows with the number of teams and identities, so agree how often reviews happen and who signs them off.

What should a quote for ClickHouse access control include?

A written scope should list the workloads covered, the roles to be designed, whether the default user will be restricted, whether external authentication is in scope, whether users.xml migration is included, and what testing evidence will be delivered. It should also name what the client provides and what is excluded, such as changes to network exposure or encryption, which are separate controls. Ask how the scope changes if the workload inventory turns out larger than expected. DeployOpen does not publish prices, so a quote follows from this written scope.

Which parts of ClickHouse access control need external review or evidence?

Access control often feeds compliance assessments, but DeployOpen prepares systems, controls and evidence for those assessments and does not issue audit reports or certifications. Your independent auditor decides what evidence is sufficient. Plan for exported grant listings, a documented role model, test results for read-only and ingestion roles, and records of periodic reviews. Confirm the specific evidence format with your auditor before the design is finalized, because the same grants can be documented in different ways.

How DeployOpen scopes and delivers ClickHouse access control

DeployOpen treats access control in self-managed ClickHouse as a designed deliverable, not a configuration afterthought. We scope the user, role and grant model against your analyst, ingestion and administrator populations, then build and test it in your own environment and hand over the SQL, the review process and the runbook.

How we scope

Inventory every existing user, service account and shared login, including the default user and any accounts defined in users.xml.
Map analysts, ingestion pipelines and administrators to named roles, and agree which privileges each role may hold.
Record the written scope before work starts, including the fixed deliverables, environments in scope and the migration path from users.xml.
Confirm which external authenticators, such as LDAP or JWT, will supply identities, since that changes who creates and disables users.

How we build

Create roles and grants with SQL statements in a non-production cluster first, then promote the same statements through version control.
Test each role against real queries, confirming that analyst roles cannot write and ingestion roles cannot read beyond their target tables.
Separate administrative grants from data access so that schema changes and user management sit with a small named group.
Check that the default user is restricted or disabled, and that no role inherits broader privileges than its documented purpose.

How we hand over

Deliver the role catalog, a grant matrix and the SQL scripts that recreate the full access model from an empty cluster.
Document the SHOW ACCESS review procedure, including who runs it, how often, and how findings are recorded and closed.
Provide a migration record for users.xml entries, showing each old account, its replacement and the date it was retired.
Walk your operators through the runbook for adding a user, changing a role and revoking access during an incident.

How we support

Ongoing support covering access reviews, new ingestion sources and role changes is available as a retainer after handover.
Re-run the access tests after each ClickHouse upgrade, since release notes should be read before upgrading to confirm grant behavior.
Ask any partner for the written scope, the acceptance criteria for access tests, and the retainer terms before signing.
Confirm with your security and compliance leads which review evidence your assessors expect, since DeployOpen prepares evidence but does not issue attestations.