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 | +-------------------------------------------------+
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 | +-------------------------------------------------+
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 | +-------------------------------------------------+
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';
Assign the policy:
create table t(col1 string(2)); alter table t modify column col1 set masking policy mp;
Query the column (fails):
select * from t;
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:
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;
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.
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.
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. |