How to Configure ClickHouse Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a clickhouse audit log by turning on SQL-driven access control and system logging. This approach records which user accounts log in and what queries they run. Most data teams start by creating shared roles for analysts and apps. Still, broad permissions make it hard to trace individual actions during an incident. When several people share one login, the log only shows the group account name. How a clickhouse audit log works with access control A useful audit trail begins with disti

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up a clickhouse audit log by turning on SQL-driven access control and system logging. This approach records which user accounts log in and what queries they run. Most data teams start by creating shared roles for analysts and apps. Still, broad permissions make it hard to trace individual actions during an incident. When several people share one login, the log only shows the group account name.

How a clickhouse audit log works with access control

A useful audit trail begins with distinct user identities and clear database roles. ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL-driven access control, you must set a storage directory on the server. You also need at least one initial account with access management permissions turned on. Once active, admins can create new users and assign roles with standard SQL statements.

A user can only grant the privileges that they already hold themselves. Creating a new account requires the dedicated user creation privilege inside the database. That creation step sets how the person logs in and which hosts they can use. It also sets default roles and specific settings for that user account. When each engineer has a personal account, system logs tie every query to a name.

Teams also pair audit logging with strict column grants to limit data exposure. You can grant read rights on specific columns instead of the whole table. If a user only has rights to two columns, queries on other columns fail. Running a wildcard select on all columns also fails and returns no data at all. That query only works if the table holds just the allowed columns. Since version 24.10, a trailing asterisk lets admins grant rights by table name prefix.

What native logging solves and what it leaves open

Native access rules and system logs solve basic account tracking inside a single cluster. They prove that a specific database user ran a statement at a certain time. They also record when someone changes roles, row policies, or user quotas. For a small team with one database, these built-in records cover basic review needs.

Still, native setup decides who logs in, not what individual statements do in flight. First, keeping personal accounts in sync with company identity systems takes constant manual work. Because of this friction, teams often fall back to shared admin or read accounts. Once two engineers share a login, the audit trail loses personal accountability right away.

Audit Log Integrity + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Second, column grants are an all-or-nothing rule during daily fixing. If an engineer needs to read a table for a bug, they see raw values. Native grants do not rewrite sensitive fields like emails or phone numbers in query results. They also cannot mask card numbers or national IDs when a broad select runs.

Finally, database logs live inside the database server itself. If an attacker or mistake drops a log table, you lose the history. Teams running many clusters also have to collect and join logs from every single node. A complete audit strategy needs to tie identity, approval, and query results together outside the database.

Best practices for reviewing database activity

Start by removing shared accounts for human users across all production servers. Give every person a distinct identity so logs always show who ran a command. Limit who holds the privilege to create users or grant new roles. Review your role grants often to make sure prefix rules do not expose new tables. Store your audit records in a safe place where database admins cannot alter them.

Add runtime guardrails and masking beside ClickHouse

Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev sits inline in the native TCP protocol beside the server as a sidecar for your ClickHouse. For every production query an agent or human sends, hoop.dev decides whether to pass, mask, block, or escalate it for review.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run or hold risky calls for approval, and data masking rewrites matched string columns in results in memory. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

How does ClickHouse track who changes user permissions?

ClickHouse manages users, roles, and policies through SQL statements when SQL-driven access control is on. System logs record the user account that ran those SQL statements and when it happened.

What happens in the log when a user runs a blocked wildcard query?

If a user only has grants on specific columns, a wildcard select is refused. The database returns no data and records the failed query attempt in the system log.

Why do shared database roles weaken an audit trail?

Native logs only know the database user name used to open the session. When five engineers share one login, the log cannot show which person ran a query.

The query runs. The emails stay hidden.