How to Audit Access in ClickHouse

Galaxy Glossary

How do I audit table access in ClickHouse?

Audit access in ClickHouse by querying built-in system logs to see who read, wrote, or altered data.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is the fastest way to see who queried a table?

Query system.query_log. The log stores one row per finished statement, including user, client address, and the SQL text.

SELECT event_time, initial_user, initial_address, query FROM system.query_log WHERE event_type = 'QueryFinish' AND query_kind = 'Select' AND position(query, 'Orders') > 0 ORDER BY event_time DESC;

How do I enable detailed audit logging?

Audit logs are on by default, but you can tighten retention and verbosity in config.xml.Add or edit:

<query_log> <database>system</database> <table>query_log</table> <partition_by>toYYYYMM(event_date)</partition_by> <flush_interval_milliseconds>7500</flush_interval_milliseconds> </query_log>

Restart ClickHouse for changes to apply.

How can I list failed log-ins?

Read system.text_log and filter for the authentication failure phrase.

SELECT event_time, message FROM system.text_log WHERE message ILIKE '%Authentication failed%';

How do I audit privilege changes?

Use system.query_log again, filtering DDL statements that touch GRANT or REVOKE.

SELECT event_time, initial_user, query FROM system.query_log WHERE query_kind = 'Other' AND (query ILIKE 'GRANT %' OR query ILIKE 'REVOKE %');

What best practices keep audits usable?

• Retain logs in a separate disk-based table with TTL.
• Grant SELECT on system logs only to auditors.
• Create materialized views that roll up audit data hourly to cut query cost.
• Stream logs to S3 or Kafka for tamper-proof archiving.

Why How to Audit Access in ClickHouse is important

How to Audit Access in ClickHouse Example Usage


-- Who modified stock levels in the last week?
SELECT
    event_time,
    initial_user,
    query
FROM system.query_log
WHERE query_kind = 'Insert'
  AND position(query, 'Products') > 0
  AND event_time >= today() - 7
ORDER BY event_time DESC;

How to Audit Access in ClickHouse Syntax


-- Basic audit query syntax
SELECT
    event_time,
    initial_user AS actor,
    initial_address AS ip,
    query_duration_ms,
    query
FROM system.query_log
WHERE event_type = 'QueryFinish'            -- Only completed statements
  AND query_kind IN ('Select','Insert','Other')
  AND event_time &gt;= now() - INTERVAL 1 DAY   -- Time window
  AND position(lower(query), lower('Orders')) &gt; 0;

Common Mistakes

Relying solely on <code>system.text_log</code>; it captures messages, not full SQL. Always use <code>system.query_log</code> for statement-level detail.
Leaving default TTL at 30 days; high-traffic clusters can drop needed evidence. Extend TTL or export logs to object storage.