Discover the power of Snowflake‘s Dynamic Data Masking, a robust column-level security feature designed to redact or tokenize sensitive data in the face of unauthorized access using entitlement tables. This innovative solution allows organizations to selectively grant access to non-redacted data based on user roles, ensuring enhanced security and control.
Key Benefits of Snowflake’s Dynamic Data Masking:
- Real-time Redaction: Dynamic Data Masking censors sensitive values in real time, tailoring data visibility according to user roles and access policies.
- Efficient Data Security: Database administrators can mitigate the risk of exposure to sensitive data without physically storing tokenized or encrypted data.
- Zero Query Performance Impact: Enjoy enhanced security benefits without compromising query performance, keeping end-users satisfied.
Implementing Dynamic Data Masking Policies
Dynamic Data Masking Policies are defined using SQL like other powerful Snowflake features. For instance:
Here we have created a masking policy “ddm_email”, which we can later apply to any columns containing email addresses. All columns that we will apply this policy to will be string columns, and the redacted value will also be of string type. The case statement defines the policy conditions. Here we have dictated that:
- When the user querying the data is using the Human Resources role ‘HR_TEAM’, return the actual value (’val’)
- Otherwise, return the string ‘REDACTED’
To apply this policy to a given column, we need to execute the following:
This modifies the column customer_email of the table customers and applied the ddm_email policy. Now, anybody assuming the HR_TEAM role will be able to query the true values of customers.customer_email, whereas all other users will be returned an ‘REDACTED’ string instead.
Use Case: Governing Data Masking with an Entitlements Table
An entitlements table becomes invaluable in complex scenarios where access requirements vary over time. This table, such as ddm_entitlements, manages user access based on policies and start and end dates, providing a dynamic solution for intricate access patterns.
Leveraging the Entitlements Table
Create a function, ddm_is_allowed, which checks user authorization against the entitlements table. Incorporate this function into the Dynamic Data Masking policy for even greater control over data visibility.
Here is an example of the entitlements table ddm_entitlements we used in the attached example:
| ddm_policy | access_type | access_name | start_date | end_date |
|---|---|---|---|---|
| ddm_email | ROLE | SYSADMIN | 2023-02-13 | 9999-01-01 |
| ddm_email | USER | JOHNDOE | 2023-02-13 | 2023-02-20 |
As you can see, there are two entitlement records to grant visibiltiy over data masked by policy ddm_email:
- Any user assuming the SYSADMIN role, between 2023-02-13 and 9999-01-01
- Any user named JOHNDOE, between 2023-02-13 and 2023-02-20
To implement this function in the Dynamic Data Masking policy, we can include it in our case statement conditions. Now, each time a user attempts to query data from the masked column, the dynamic data masking policy will run this function to check that the time-sensitive privilege is allowed by our entitlements table.
Conclusion
Snowflake’s Dynamic Data Masking policy ensures real-time evaluation against the entitlements table, granting authorized users access to unmasked data values. As a result, administrators can efficiently manage access requests from a centralized point, enhancing overall data security.
Interested in optimizing your Snowflake workloads? As an Elite Services Partner, we help organizations maximize the ROI and performance of their Snowflake deployments and enable your team to derive business-impacting insights quickly. Contact our team using for form below for your tailored solution.
