Skip to content

You are viewing documentation for Immuta version 2023.4.

For the latest version, view our documentation for Immuta SaaS or the latest self-hosted version.

Snowflake Integration without Snowflake Governance Features

In this legacy integration, all enforcement is done by creating views that contain all policy logic. Each view has a 1-to-1 relationship with the original table. All policy-enforced views are accessible through the PUBLIC role and access controls are applied in the view, allowing customers to leverage Immuta's powerful set of attribute-based policies. Additionally, users can continue using roles to enforce compute-based policies through "warehouse" roles, without needing to grant each of those roles access to the underlying data or create multiple views of the data for each specific business unit.

Architecture

This integration leverages webhooks to keep Snowflake views up-to-date with the corresponding Immuta data sources. Whenever a data source or policy is created, updated, or disabled, a webhook will be called that will create, modify, or delete the Snowflake view with Immuta policies.

The SQL that makes up all views includes a join to the secure view: immuta_system.user_profile. This view is a select from the immuta_system.profile table (which contains all Immuta users and their current groups, attributes, projects, and a list of valid tables they have access to) with a constraint immuta__userid = current_user() to ensure it only contains the profile row for the current user. This secure view is readable by all users and will only display the data that corresponds to the user executing the query.

Note: The immuta_system.profile table is updated through webhooks whenever a user's groups or attributes change, they switch projects, they acknowledge a purpose, or when their data source access is approved or revoked. The profile table can only be read and updated by the Immuta system account.

By default, all views are created within the immuta database, which is accessible by the PUBLIC role, so users acting under any Snowflake role can connect. All views within the database have the SELECT permission granted to the PUBLIC role as well, and access is enforced by the access_check function built into the individual views. Consequently, there is no need for users to manage any role-based access to any of the database objects managed by Immuta.

Secure and non-secure views

When creating a Snowflake data source, users have the option to use a regular view (traditional database view) or a secure view; however, according to Snowflake's documentation , "the Snowflake query optimizer, when evaluating secure views, bypasses certain optimizations used for regular views. This may result in some impact on query performance for secure views." To use the data source with both Snowflake and Snowflake workspaces, secure views are necessary. Note: If HIPAA compliance is required, secure views must be used.

Non-secure view policy implications

When using a non-secure view, certain policies may leak sensitive information. In addition to the concerns outlined here, there is also a risk of someone exploiting the query optimizer to discover that a row exists in a table that has been excluded by row-level policies. This attack is mentioned here in the Snowflake documentation.

Policies that will not leak sensitive information

  • Masking by making NULL, using a constant, or by rounding (date/numeric)
  • Minimization row-level policies
  • Date-based row-level policies
  • K-anonymization masking policies

Policies that could leak sensitive information

  • Masking using a regex will show the regex being applied. In general this should be safe, but if you have a regex policy that removes a specific selector to redact (e.g., a regex of /123-45-6789/g to specifically remove a single SSN from a column), then someone would be able to identify columns with that value.
  • In conditional masking and custom WHERE clauses including “Right To Be Forgotten,” the custom SQL will be visible, so for a policy like "only show rows where COUNTRY NOT IN(‘UK’, ‘AUS’)," users will know that it’s possible there is data in that table containing those values.

Policies that will leak potentially sensitive information

These policies leak information sensitive to Immuta, but in most cases would require an attacker to reverse the algorithm. In general these policies should be used with secure views:

  • Masking using hashing will include the salt used.
  • Numeric and categorical randomized response will include the salt used.
  • Reversible masking will include both a key and an IV.
  • Format preserving masking will include a tweak, key, an alphabet range, prefix, pad to length, and checksum id if used.

Policy enforcement

The data sources themselves have all the Data policies included in the SQL through a series of CASE statements that determine which view of the data a user will see. Row-level policies are applied as top-level WHERE clauses, and usage policies (purpose-based or subscription-level) are applied as WHERE clauses against the user_profile JOIN. The access_check function allows Immuta to throw custom errors when a user lacks access to a data source because they are not subscribed to the data source, they are operating under the wrong project, or they cannot view any data because of policies enforced on the data source.

Integration migration

Migration troubleshooting
  • If multiple Snowflake integrations are enabled, they will all migrate together. If one fails, they will all revert to the Snowflake Standard integration.
  • If an error occurs during migration and the integration cannot be reverted, the integration must be disabled and re-enabled.

You can migrate from a Snowflake integration without governance features to a Snowflake integration with governance features on the app settings page. Once prompted, Immuta will migrate the integration, allowing users to seamlessly transition workloads from the legacy Immuta views to the direct Snowflake tables.

After the migration is complete, Immuta views will still exist for pre-existing Snowflake data sources to support existing workflows. However, disabling the Immuta data source will drop the Immuta view, and, if the data source is re-enabled, the view will not be recreated.

Limitations

  • Certain interpolation functions can also block the creation of a view, specifically @interpolatedComparison() and @iam.

  • When configuring one Snowflake instance with multiple Immuta instances, the user or system account that enables the integration on the app settings page must be unique for each Immuta instance.