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 | |
Databricks UC | |
Redshift | |
PostgreSQL | |
AWS S3 |
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¶
- Go to Access Management > [Service Type].
- Switch to the Masking Policies tab.
- Click + Add Policy.
- Select the target resource (database, table, column).
- Choose the Masking Type.
- Add user, group, or role-based policy items.
- (Optional) Add masking conditions using macros.
- 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 | |
Databricks Unity Catalog | |
Redshift | |
Snowflake | |
Vertica |
Configuration Steps¶
To configure conditional column masking:
- Create a Policy: Follow the standard policy creation workflow refer Creating a Masking Policy via Privacera Portal.
- Define Target Resources: Specify the Database, Schema, Table/View, and Column to be masked.
- Select Custom Masking: In the Masking Conditions section, choose Custom as the masking option.
- 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:
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:
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:
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
- Error:
- 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
- Error:
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
orcustom
for simple use cases; userandom
for data obfuscation. - Test masking logic using user impersonation or Preview.
- Avoid hardcoding sensitive logic in multiple policies—reuse macros or tags.
Related Topics¶
- Prev topic: Dynamic Row-Level Filtering
- Next topic: Resource Definitions