How to Audit Access in ClickHouse

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.

.