Dynamic Data Masking in Snowflake: Entitlement Tables for Data Security and Accessibility

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:

-- Create the masking policy for column "customer_email" that returns 
-- the value for allowed users and roles, otherwise a hash

create masking policy ddm_email as (val string) returns string ->
  case
    when ddm_is_allowed('ddm_email') then val
    else sha1(val)
  end;
  

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:

alter table customers
modify column customer_email
set masking policy ddm_email

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:

As you can see, there are two entitlement records to grant visibiltiy over data masked by policy ddm_email:

1. Any user assuming the SYSADMIN role, between 2023-02-13 and 9999-01-01
2. Any user named JOHNDOE, between 2023-02-13 and 2023-02-20

-- Create UDF that tests the current_role() and current_user() against the
-- constraints in the entitlements table

create function ddm_is_allowed(policy_name varchar)
  returns boolean as 
$$ 
  select 
      count(access_name)::boolean
  from dev.security_schema.ddm_entitlements 
  where 
      ddm_policy = policy_name
      and current_timestamp() between start_date and end_date
      and (
          	(access_type = 'ROLE' and current_role() = access_name)
          	or (access_type = 'USER' and current_user() = access_name)
          )

$$
;   

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.

-- Create the masking policy for column "customer_email" that returns
-- the value for allowed users and roles, otherwise a hash
 
create masking policy ddm_email as (val string) returns string ->
  case
    when ddm_is_allowed('ddm_email') then val
    else sha1(val)
  end

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.