Skip to content

Dynamic Column Masking

Dynamic Column Masking allows administrators to control the visibility of sensitive data at the column level. Instead of completely denying access, data can be partially or fully masked based on user roles, groups, or other contextual conditions. So when the same table is queried by different users, the results may differ based on the column masking.

Overview

  • Applies only to SELECT operations.
  • Enforced at the column level.
  • Supports static, deterministic, random, and custom masking types.
  • Dynamic expressions can be used to control when masking is applied.

Dynamic Column Masking is not supported for Object Storage (e.g., AWS S3).

Objects in S3, ADLS, GCS, or MinIO are accessed using Apache Spark (EMR, EKS, or Kubernetes), Jupyter Notebooks, CLI, or File Explorer tools, the entire object is read on the client side and there is no way to filter rows before the data is read. Privacera does not support dynamic column masking for these services.

Pre-requisites Reading

Please read the following before creating Dynamic Column Masking policies:

Supported Services

Service Support Level
Snowflake 🟢 Yes
Databricks UC 🟢 Yes
Redshift 🟢 Yes
PostgreSQL 🟢 (via Secure Views)
AWS S3 🔴 No

Components of a Masking Policy

Field Description
Resource database → table → column
Masking Type Masking strategy: nullify, custom, random, constant, etc.
Policy Scope Users, groups, roles
Conditions Optional macros (e.g., USER.clearance != 'high')
Audit Enabled Recommended to track how and when masking is applied

Creating a Masking Policy via Privacera Portal

  1. Go to Access Management > [Service Type].
  2. Switch to the Masking Policies tab.
  3. Click + Add Policy.
  4. Select the target resource (database, table, column).
  5. Choose the Masking Type.
  6. Add user, group, or role-based policy items.
  7. (Optional) Add masking conditions using macros.
  8. Save the policy.

Example: Mask SSN Column for Public Group

Use Case: Mask ssn column for all users unless their clearance is high.

Component Value
Service Snowflake
Database customer_db
Table profiles
Column ssn
Masking Type MASKED
Group public

The public group in Apache Ranger and Privacera is a special built-in group that includes all users by default.

Conditional Column Masking

Conditional column masking lets you mask a column's value based on conditions evaluated on other columns within the same table or across different tables. This enables dynamic masking, where Column B is masked only when specific conditions on Column A (or other columns) are met.

Supported Services

Service Support Level
Dremio 🟢 Yes
Databricks Unity Catalog 🟢 Yes
Redshift 🟢 Yes
Snowflake 🟢 Yes
Vertica 🟢 Yes

Configuration Steps

To configure conditional column masking:

  1. Create a Policy: Follow the standard policy creation workflow refer Creating a Masking Policy via Privacera Portal.
  2. Define Target Resources: Specify the Database, Schema, Table/View, and Column to be masked.
  3. Select Custom Masking: In the Masking Conditions section, choose Custom as the masking option.
  4. Define Conditional Logic: Enter your conditional expression using SQL CASE statements in the provided text field.

Important Considerations

  • String values enclosed in single quotes are case-sensitive in most databases.

Conditional Masking Examples

The following examples illustrate various approaches to implementing conditional column masking:

Example 1: Single Column Condition

Scenario: Mask phone numbers for users when the customer's country is UK.

Component Value
Target Column us_phone
Condition Column country
Policy Users emily
Condition country = 'UK'

Conditional Expression:

SQL
CASE WHEN (obj.COUNTRY='UK') THEN MD5({col}) ELSE {col} END

Result: The us_phone column will be masked with MD5 hash only when the country column value is 'UK'.

Example 2: Multiple Column Conditions

Scenario: Mask phone numbers based on both country and person name criteria.

Component Value
Target Column us_phone
Condition Columns country, person_name
Policy Users emily
Conditions country = 'UK' AND person_name = 'Adam'

Conditional Expression:

SQL
CASE WHEN (obj.COUNTRY='UK' AND obj.PERSON_NAME='Adam') THEN MD5({col}) ELSE {col} END

Result: The us_phone column will be masked only when both conditions are met simultaneously.

Example 3: Cross-Table Lookup (Subquery)

Scenario: Mask sales amounts based on customer data from a separate lookup table.

Component Value
Target Column sales_amount
Lookup Table sales_db.customer_schema.customer_data
Policy Users emily
Condition Customer's country exists in lookup table where name='Daniel'

Conditional Expression:

SQL
CASE WHEN (obj.country IN (SELECT country FROM sales_db.customer_schema.customer_data WHERE name='Daniel')) THEN MD5({col}) ELSE {col} END

Result: The sales_amount column will be masked when the country matches entries in the lookup table.

Subquery and Cross-Table Limitations

General Limitations:

  • Conditional masking with subqueries will fail if the referenced lookup table has its own masking or row-level security (RLS) policies applied.

Service-Specific Limitations:

  • Snowflake: Subqueries are not supported in masking expressions.
    • Error: SQL compilation error: Unsupported subquery type cannot be evaluated
  • Vertica: Only simple expressions are allowed in access policies.
    • Error: SQL Error [6368][22023]: [Vertica]VJDBC ROLLBACK: Only simple "expression" is allowed in access policy

Limitations

  • Masking applies only during SELECT access.
  • Native masking may not be available in all services; Privacera will use Secure Views as fallback (e.g., PostgreSQL).
  • Column Masking is generally implemented using UDFs or native functions and this can impact performance.
  • Complex expressions should be validated in native query engine for correctness.
  • Cannot be combined with Row Level Filters on the same column in some engines.
  • Column Masking is not supported for Object Storage (e.g., AWS S3).

Column Masking is implemented using UDFs or native functions and this can impact performance.

Tips

  • Choose nullify or custom for simple use cases; use random for data obfuscation.
  • Test masking logic using user impersonation or Preview.
  • Avoid hardcoding sensitive logic in multiple policies—reuse macros or tags.

Comments