Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 35 ยท Integrations and Operations

Users, Roles, Quotas and Security

Secure ClickHouse with SQL-driven access control: users, roles, GRANT, row policies, settings profiles, quotas, network rules, TLS and safe defaults for production.

A database holding every event your business ever produced needs real access control. ClickHouse offers a complete model driven by SQL: users, roles, grants, row policies, settings profiles and quotas. This lesson walks through them in the order you would use them, and finishes with the network and encryption basics.

The server cannot run on this site, so every ClickHouse statement is a read-only snippet. A SQL stand-in models grants and row filters with views. Passwords below are placeholders only.

SQL-driven access control

Older setups define users in users.xml. Prefer SQL-driven access control, enabled per user with access_management = 1 in the config for an admin account. Users and grants created with SQL are stored in the server's access storage (and replicated across nodes when you configure Keeper-backed storage or use ON CLUSTER).

SQL
CREATE USER analyst
    IDENTIFIED WITH sha256_password BY 'change-me-please'
    HOST IP '10.0.0.0/8'
    DEFAULT DATABASE analytics;
  • Authentication methods include sha256_password, bcrypt_password, ldap, kerberos and SSL certificates. Avoid plaintext_password.
  • HOST restricts where the user may connect from (IP, host name or regexp).
  • The built-in default user often ships with no password: restrict or replace it before exposing the server.

Roles and grants

Grant privileges to roles, then roles to users. This keeps permissions manageable.

SQL
CREATE ROLE reporting;
GRANT SELECT ON analytics.* TO reporting;
GRANT SELECT ON system.query_log TO reporting;

CREATE ROLE loader;
GRANT INSERT ON analytics.events TO loader;
GRANT CREATE TEMPORARY TABLE ON *.* TO loader;

GRANT reporting TO analyst;
GRANT loader TO etl_service;

Privileges cover SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE, SHOW, dictGet, SYSTEM, REMOTE, S3 and many more. Column-level grants are possible: GRANT SELECT(ts, site) ON analytics.events TO analyst. Inspect with SHOW GRANTS FOR analyst, SHOW CREATE USER analyst and the tables system.users, system.roles and system.grants.

Principle of least privilege: loaders only INSERT, dashboards only SELECT, no application account with ALL ON *.*.

Row policies

A row policy adds a filter to every query a user or role makes on a table, useful for multi-tenant data:

SQL
CREATE ROW POLICY tenant_a_only ON analytics.events
    FOR SELECT USING tenant_id = 42
    TO tenant_a_role;

Users of tenant_a_role see only rows with tenant_id = 42, whatever their query says. Combine with column grants to hide sensitive columns. Policies are applied as an extra PREWHERE/WHERE; design the sorting key so the filter prunes well (for multi-tenant tables put tenant_id first in ORDER BY).

Settings profiles and constraints

A settings profile bundles settings and optional constraints for users, so one heavy query cannot take the cluster down:

SQL
CREATE SETTINGS PROFILE dashboard_limits
    SETTINGS max_memory_usage = 8000000000,
             max_execution_time = 30,
             max_rows_to_read = 5000000000,
             readonly = 1
    TO reporting;

readonly = 1 forbids changes to data and settings. Constraints (CONST, MIN, MAX, CHANGEABLE_IN_READONLY) stop users from raising their own limits. Other protective settings: max_threads, max_result_rows, max_concurrent_queries_for_user and max_bytes_before_external_group_by.

Quotas

A quota limits consumption over a time interval, per user or role:

SQL
CREATE QUOTA analyst_quota
    FOR INTERVAL 1 hour MAX queries = 600, read_rows = 20000000000, execution_time = 1800
    TO analyst;

Quotas count queries, errors, result rows, read rows, read bytes and execution time. Intervals can be sliding windows of an hour, a day or a month. SELECT * FROM system.quotas_usage shows current usage. The difference to a profile: profiles limit a single query, quotas limit totals over time.

flowchart LR
    U["User"] --> R["Roles: privileges"]
    U --> P["Settings profile: per-query limits"]
    U --> Q["Quota: totals per interval"]
    R --> RP["Row policies: row filters"]

Four independent mechanisms, all attached to users or roles.

Network and encryption

  • Bind to the right interfaces (listen_host), keep the native port 9000 and Keeper ports on a private network and put a firewall in front of everything.
  • Enable TLS for HTTPS (8443) and the secure native port (9440), with openSSL config for certificates. Use TLS for inter-node traffic when nodes talk across untrusted networks.
  • For Distributed queries across nodes, set a shared <secret> in the cluster definition so nodes authenticate each other as the initial user instead of a stored password.
  • Block risky functions for web-facing accounts (REMOTE, FILE, URL, S3 source grants are privileges you can simply not grant).
  • Use encrypted disks or volume encryption at rest, and store backups encrypted.
  • Audit with system.query_log (user, query, address) and system.session_log.

Stand-in: grants and row policy with views

SQLite has no users, so this models the idea with a view: the "tenant" role reads only its own rows through a filtered view, and an aggregate-only view hides row-level detail.

SQL Stand-in: a row policy as a filtered view

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: a quota counting usage

Press Run (or Ctrl + Enter) to see the result here.

Common mistakes

  • Leaving the default user open to the network without a password.
  • Granting ALL to application users instead of narrow roles.
  • Relying only on application-level filters in multi-tenant systems instead of row policies.
  • Putting secrets in queries, which are logged in system.query_log.
  • Exposing ports 9000, 8123 or Keeper to the internet.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the recommended way to give permissions to many users?

    Roles keep privilege sets reusable and reviewable; users receive roles.
  2. 2. What does a row policy do?

    A row policy applies an additional condition to queries from the users or roles it targets.
  3. 3. How does a quota differ from a settings profile limit such as max_memory_usage?

    Profiles bound one query; quotas bound cumulative usage per interval.
  4. 4. Which is a sound network practice?

    Restrict listeners, use firewalls and TLS, and use strong password hashing or external authentication.

Summary

  • Manage users, roles and grants with SQL; follow least privilege.
  • Row policies and column grants isolate tenants and sensitive columns.
  • Settings profiles bound single queries; quotas bound usage over time.
  • Lock down the default user, ports and traffic; enable TLS and audit with the query log.

Next: Monitoring, Backups and Upgrades