ClickHouse® has a powerful RBAC system, but managing it through raw SQL is error-prone and hard to audit. Here is how to set it up properly.
The basics: users, roles, and grants
ClickHouse® RBAC has three building blocks:
Users authenticate and connect. Each user has a password (or certificate), a list of allowed networks, and a default database.
Roles group permissions together. A role like analytics_readonly might have SELECT on specific tables but no INSERT or ALTER.
Grants assign permissions to roles or users. Grants cascade: granting SELECT on a database grants it on all current and future tables in that database.
Designing a role hierarchy
Start with the principle of least privilege. A practical hierarchy:
viewer- SELECT on reporting tables onlyanalyst- SELECT on all tables, no DDLdeveloper- SELECT and INSERT, limited ALTERdba- Full access including DDL and SYSTEM commands
Assign roles to users, never grant permissions directly to users. This makes it easy to onboard new team members and audit who has access to what.
Row-level security
ClickHouse® supports row-level security through row policies. A policy can restrict which rows a user sees based on a condition. For example, a multi-tenant application can ensure each tenant only sees their own data by filtering on tenant_id.
Network restrictions
Limit which IP addresses can connect to ClickHouse®. Use ALLOW lists in user definitions to restrict access to your VPN, office network, or specific application servers. This is a simple but effective layer of defense.
How CHOps makes RBAC manageable
Managing RBAC through SQL means running SHOW GRANTS, GRANT, REVOKE, and CREATE ROLE commands manually. Mistakes are easy and hard to spot.
CHOps provides visual grant trees that show the complete permission structure at a glance. You can see which roles have which permissions on which tables, and modify them with clicks instead of SQL. The interactive tree makes it obvious when a role has more access than intended.
CHOps Pro adds SSO support so team members use their company credentials, and an audit log that records every significant action with timestamps and user identity.
Regular audits
Schedule quarterly access reviews. For each user, verify they still need their current access level. Remove inactive users. Check for overly permissive grants. CHOps Pro can export the audit log as a verified PDF for compliance documentation.
