Behavior Change Management

This document explains how to check whether a particular behavior change bundle is enabled in your account and how to enable or disable it.

In this Topic:

Overview

Snowflake implements behavior changes monthly in bundles included in regularly-scheduled releases. During the testing period and opt-out period for each behavior change bundle, you can enable or disable the bundle in your account. This document explains how to check whether a particular bundle is enabled in your account and how to enable or disable it.

In this document, bundle_name is the name of the behavior change bundle in the form YYYY_NN, found in the Behavior Change Log.

참고

Behavior changes in bundles cannot be enabled/disabled individually. To enable/disable a behavior change, you must enable/disable the bundle containing the change.

Checking the Status of a Behavior Change Bundle in Your Account

To check whether a specific behavior change bundle is enabled in your account, use the SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS function with the bundle_name as the argument:

SELECT SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS('2021_02');

+-------------------------------------------------+
| SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS('2021_02') |
|-------------------------------------------------|
| DISABLED                                        |
+-------------------------------------------------+
Copy

Enabling a Behavior Change Bundle in Your Account

To enable a particular behavior change in your account, use the SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE function and specify the bundle_name that contains the change:

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2021_02');

+-------------------------------------------------+
| SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2021_02') |
|-------------------------------------------------|
| ENABLED                                         |
+-------------------------------------------------+
Copy

Disabling a Behavior Change Bundle in Your Account

To disable a particular behavior change in your account, use the SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE function and specify the bundle_name that contains the change:

SELECT SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE('2021_02');

+-------------------------------------------------+
| SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE('2021_02')|
|-------------------------------------------------|
| DISABLED                                        |
+-------------------------------------------------+
Copy

Mitigating Masking Policy Return Value Updates

This section describes updates to NULL return values and return values for length, precision and scale in masking policy conditions (collectively: 《return value updates》), and how to use the BCR_2022_07_DDM_ROLLOUT temporary view (in the SNOWFLAKE.BCR_ROLLOUT schema) to mitigate these return value updates.

During the testing period of the 2022_08 behavior change bundle, Snowflake fails queries when the following are true:

  • The behavior change bundle is enabled, and

  • Querying a column with masking policy when:

    • The masking policy conditions return NULL for a column field that is NOT NULL.

      For example, the masking policy returns NULL for a field that should be an integer because the column data type is INTEGER.

    • The masking policy conditions return a value whose length, scale, or precision is greater than the length, scale, or precision of the column to which the masking policy is assigned. For example:

      Create a policy:

      create masking policy mp as (s string)
      returns string -> 'abcdef';
      
      Copy

      Assign the policy:

      create table t(col1 string(2));
      
      alter table t modify column col1 set masking policy mp;
      
      Copy

      Query the column (fails):

      select * from t;
      
      Copy

To determine the impact of this change and provide enough time to update the masking policy conditions to protect data, Snowflake provides a special schema and view within the shared SNOWFLAKE database to map the table column, masking policy, and a possible reason for why a query on a masking policy-protected column fails.

Note that the return value updates will be enabled by default when the 2023_01 behavior change bundle (i.e. the opt-out period for the 2022_08 behavior change bundle) is published. During the opt-out period, the option to disable the behavior change bundle is available to further delay the effects of the return value updates.

The BCR_2022_07_DDM_ROLLOUT view is temporary. Snowflake will remove the view when the return value updates are generally enabled when the 2023_02 behavior change bundle is published. At this point, it will no longer be possible to query the view to determine affected columns and policies, or prevent column query or masking policy assignment operation failures due to return value updates.

For details on the behavior change process, see Behavior Change Policy.

Identify Masking Policy & Column Associations

To query the view and mitigate the upcoming return value changes, do the following:

  1. Query the SNOWFLAKE.BCR_ROLLOUT.BCR_2022_07_DDM_ROLLOUT view. For example:

    use role accountadmin;
    
    select * from snowflake.bcr_rollout.bcr_2022_07_ddm_rollout;
    
    Copy
  2. Evaluate the REASON column in the BCR_2022_07_DDM_ROLLOUT View Reference section to determine what update needs to be made to the masking policy conditions.

  3. Update the masking policy conditions with an ALTER MASKING POLICY statement to ensure the column data remains protected and that policy assignment operations or protected column queries do not fail.

  4. Test the new policy conditions by querying the table columns to which the masking policies are assigned.

BCR_2022_07_DDM_ROLLOUT View Reference

The BCR_2022_07_DDM_ROLLOUT view (in the SNOWFLAKE.BCR_ROLLOUT schema) records information starting on July 15, 2022 and contains the following columns:

Column

Data type

Description

POLICY_NAME

TEXT

The name of the policy.

POLICY_ID

NUMBER

Internal/system-generated identifier for the policy.

POLICY_SCHEMA

TEXT

The parent schema of the policy.

POLICY_DATABASE

TEXT

The parent database of the policy.

POLICY_BODY

TEXT

The conditions of the policy to mask or unmask the column data.

COLUMN_NAME

TEXT

The name of the column that has the policy.

TABLE_NAME

TEXT

The name of the table.

TABLE_ID

NUMBER

Internal/system-generated identifier for the table.

TABLE_SCHEMA

TEXT

The parent schema of the table.

TABLE_DATABASE

TEXT

The parent database of the table.

REASON

TEXT

Possible reason for the mismatch.

The REASON column can have one of the following values as specified by the REASON_MESSAGE column in the following table. The additional columns in the table below are to help administrators decide how to interpret the REASON column and update the masking policy conditions:

Reason Message

Cause

Action

POLICY_ASSOCIATION_ON_NOT_NULL_COLUMN

A masking policy is assigned to a NOT NULL column.

Verify the masking policy conditions do not return a NULL value anywhere in the policy conditions.

LENGTH_MISMATCH

A masking policy assigned to a column whose data type is VARCHAR or BINARY returns data with a larger length than the length of the column.

Modify the masking policy conditions to ensure the length of the return value has a length less than or equal to the length of the column.

PRECISION_MISMATCH

A masking policy assigned to a column whose data type is NUMBER returns a larger precision data than the precision of the column.

Modify the policy conditions to ensure the precision of the return value is less than or equal to the precision of the column.

SCALE_MISMATCH

A policy assigned to a column whose data type is NUMBER or TIMESTAMP returns data with a larger scale than the scale of the column.

Modify the policy conditions to ensure the scale of the return value is less than or equal to the scale of the column.

Unknown mismatch. Manual investigation recommended.

The masking policy returns a NULL value or the masking policy conditions contain a subquery.

Modify the policy so that the return value is less than or equal to the length, precision, or scale of the column.