How to Mask Data in ClickHouse

How do I mask sensitive columns in ClickHouse without copying data?

Data masking in ClickHouse hides sensitive column values for specific users or roles by applying masking policies at query time.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a ClickHouse masking policy?

Masking policies are schema-level rules that transform column values at read time. They let you reveal only the necessary slices of data (e.g., first two email characters) without duplicating or encrypting the source table.

When should I use data masking?

Use masking when analysts need production tables but should not see PII like full emails, phone numbers, or credit-card digits. Masking reduces compliance risk while keeping queries simple.

How do I create a masking policy?

Create a policy with CREATE MASKING POLICY, supply a masking expression, and attach it to one or more columns. The policy runs automatically for any role granted the policy.

Step-by-step example

1. Create the policy.
2. Attach to columns.
3. Grant it to a role.
4. Test with the role’s session.

What masking functions are common?

substr, concat, replaceRegexpAll, and math functions like intDiv are popular. Any deterministic SQL expression is valid inside USING.

Best practices for masking

Store full data; mask only on read. Use consistent masks to keep joins working (e.g., keep customer_id intact). Document each policy’s purpose in the database description.

How to test your masking policy?

Open a new session, SET ROLE to the granted role, and run representative SELECTs. Confirm that administrators without the role still see full data.

Can I edit or drop a policy safely?

Yes. Use ALTER MASKING POLICY … USING to change the expression. Dropping a policy immediately restores full visibility, so revoke user access first.

Why How to Mask Data in ClickHouse is important

How to Mask Data in ClickHouse Example Usage


-- Mask customers.email for support role
SET ROLE support_team;
SELECT id, name, email
FROM Customers
ORDER BY id
LIMIT 5;

-- Result sample
-- id | name     | email         
-- 1  | Alice    | al***@shop.com
-- 2  | Bob      | bo***@shop.com

How to Mask Data in ClickHouse Syntax


-- Create masking policy
CREATE MASKING POLICY hide_email
USING concat(substr(email, 1, 2), '***', substr(email, position(email, '@')));

-- Attach to columns
ALTER TABLE Customers
  MODIFY COLUMN email VARCHAR(255) MASKING POLICY hide_email;

-- Grant policy to a role
GRANT SELECT ON Customers TO ROLE support_team
  USING MASKING POLICY hide_email;

-- Update or drop
ALTER MASKING POLICY hide_email USING '*** redacted ***';
DROP MASKING POLICY IF EXISTS hide_email;

Common Mistakes

Applying the policy but forgetting to grant it to a role. Without the GRANT ... USING clause, no user benefits from the mask. Always grant the policy right after creation.
Using a reversible hash or simple ROT13 as a mask. Attackers can decode it. Prefer irreversible truncation or fixed substitution to prevent reconstruction.