2022_08 Bundle¶

Edit SAML2 Security Integrations: Enforcing the Validity Dates of X.509 Certificates¶

When defining a SAML2 security integration to enable single sign-on, the security administrator specifies a X.509 certificate using the SAML2_X509_CERT parameter.

Snowflake now enforces the validity dates of these X.509 certificates so that expired certificates result in failed authentication. Certificates with a NotBefore date that has not yet occurred also fails authentication. The enforcement of validity dates cannot be disabled.

Previously:

Snowflake did not check the validity date of an X.509 certificate to verify whether it was expired or if the NotBefore date had not yet occurred.

Currently:

Snowflake enforces the validity dates of an X.509 certificate. If the current date does not fall within the validity dates of the certificate, authentication fails.

Database & Schemas: Dropping or Replacing Not Allowed if Results in Dangling References for Password Policies and Session Policies¶

The behavior of the DROP SCHEMA, DROP DATABASE, CREATE OR REPLACE DATABASE, and CREATE OR REPLACE SCHEMA operations with respect to a password policy and session policy has changed as follows:

Previously:

Snowflake allowed the DROP and REPLACE operations on the schema/database that contained the policy when the policy was set on the Snowflake account containing the policy or when the policy was set on a user in the same account.

Currently:

Snowflake allows the DROP and REPLACE operations on the schema/database that contains the policy when the policy is set on the Snowflake account containing the policy or when the policy is set on a user in the same account.

As a result, the behavior of the same four commands has changed:

• DROP DATABASE

• DROP SCHEMA

• CREATE OR REPLACE DATABASE

• CREATE OR REPLACE SCHEMA

Depending on the operation, Snowflake returns one of the following error messages:

Policy set on a user:

DROP DATABASE and CREATE OR REPLACE DATABASE:

Cannot drop database because policy 'MYDB.MYSCHEMA.POLICY1' is set on user 'JSMITH'. Unset the policy 'MYDB.MYSCHEMA.POLICY1' and then try the drop operation again.

DROP SCHEMA and CREATE OR REPLACE SCHEMA:

Cannot drop schema because policy 'MYDB.MYSCHEMA.POLICY1' is set on user 'JSMITH'. Unset the policy 'MYDB.MYSCHEMA.POLICY1' and then try the drop operation again.

Policy set on the account:

DROP DATABASE and CREATE OR REPLACE DATABASE:

Cannot drop database because policy 'MYDB.MYSCHEMA.POLICY1' is set on account 'MYACCOUNT'. Unset the policy 'MYDB.MYSCHEMA.POLICY1' and then try the drop operation again.

DROP SCHEMA and CREATE OR REPLACE SCHEMA:

Cannot drop schema because policy 'MYDB.MYSCHEMA.POLICY1' is set on account 'MYACC

SHOW FUNCTIONS Command: New Column in Output¶

The following column has been added to the output of the SHOW FUNCTIONS command:

To help minimize the impact of this addition, the column has been added as the last column in the output.

SHOW VIEWS Command: New Column in Output¶

The SHOW VIEWS output now includes a new CHANGE_TRACKING column. The column indicates whether change tracking is enabled on the view.

The column displays either of the following values for individual views:

• ON: Change tracking is enabled. You can query this change tracking data using streams or the CHANGES clause for SELECT statements.

• OFF: Change tracking is currently disabled, but could be enabled.

To help limit the impact of this change, the column has been added as the last column in the output.

GET_DDL Function: Support Added for Tags & Policies¶

The GET_DDL function now supports tags and policies as follows:

• When a tag is set on a table, view, or materialized view, the output of calling the GET_DDL function includes the tag assignments in the CREATE statement.

• When one or more tags are set on a schema or database, the output of calling the GET_DDL function includes the following statements to assign the tags:

  • An ALTER DATABASE statement when the tag is set on the database.

  • An ALTER SCHEMA statement when the tag is set on the schema.

  • An ALTER DATABASE statement and an ALTER SCHEMA statement when the tag is set on both the database and schema.

• When a tag is created in a schema and this same schema is specified when calling the GET_DDL function, the output includes a CREATE OR REPLACE statement to generate the tag.

  The same is true for a tag created in a database: specifying the database when calling the GET_DDL function includes the CREATE OR REPLACE statement to generate the tag.

• If a row access policy is set on a table or view or a masking policy is set on column, calling the GET_DDL function on the table or view adds the WITH keyword to indicate that the policy is set on the table, view, or column in the CREATE statement.

  Note that if you were to manually create a table, specifying the WITH keyword is optional when setting a row access policy on the table or view or a masking policy on a column.

Data Sharing Usage Views: Changes to Column in Views¶

All of the views in the DATA_SHARING_USAGE schema (in the SNOWFLAKE shared database) have changed as follows:

Previously:

Data displayed in the SNOWFLAKE_REGION column in the views was displayed as <cloud> <region> in all lowercase (e.g. aws us_west_2). This was inconsistent with the values displayed for Snowflake region in the SHOW REGIONS output.

Impacted views:

• LISTING_ACCESS_HISTORY

• LISTING_CONSUMPTION_DAILY

• LISTING_EVENTS_DAILY

• LISTING_TELEMETRY_DAILY

Currently:

In the views listed above, values in the SNOWFLAKE_REGION column are displayed now as <cloud>_<region> in all uppercase. This is consistent with the SHOW REGIONS output.

For example, the us_west_2 region in AWS is now displayed as AWS_US_WEST_2.

Data Sharing Usage Views: New Column in Views¶

This announcement was added on February 13, 2023.

The REGION_GROUP column has been added to the following views in the DATA_SHARING_USAGE schema:

• LISTING_ACCESS_HISTORY

• LISTING_CONSUMPTION_DAILY

• LISTING_EVENTS_DAILY

• LISTING_TELEMETRY_DAILY

To help minimize the impact of this addition, the REGION_GROUP column has been added as the last column in the output.

ACCESS_HISTORY View (Account Usage): New Columns in the View¶

The following columns have been added to the ACCESS_HISTORY view (in the ACCOUNT_USAGE schema):

• object_modified_by_ddl

• policies_referenced

To help minimize the impact of this addition, these columns have been added as the last columns in the output.

Note that these columns are placeholders and will be populated in an upcoming release.

FUNCTIONS View (Information Schema): New Column in Output¶

The following column has been added to the Information Schema FUNCTIONS view (in the INFORMATION_SCHEMA schema):

To help minimize the impact of this addition, the column has been added as the last column in the view.

TABLE_CONSTRAINTS View (Account Usage): New Column in View¶

The following new column has been added to the TABLE_CONSTRAINTS view (in the ACCOUNT_USAGE schema):

USAGE_IN_CURRENCY_DAILY View (Organization Usage): New Column in View¶

The USAGE_IN_CURRENCY_DAILY view (in the ORGANIZATION_USAGE schema) now includes a new BALANCE_SOURCE column.

To help limit the impact of this change, the column has been added as the last column in the view.

USERS View (Account Usage): New Column in View¶

The USERS view (in the ACCOUNT_USAGE schema) has been updated to include a new USER_ID column.

Consumers: SHOW Commands Return Error If Account Has Been Revoked from Share¶

The behavior of the SHOW <objects> commands in a consumer account for which a share has been revoked has changed as follows:

Previously:

If a consumer account has mounted two or more shares containing the same provider objects and later the provider revokes the account from only one of the shares, data was returned when SHOW commands were executed in the consumer account on the mounted database or schema in the remaining share(s).

For example, share1 and share2 containing the same provider objects were mounted in the xy12345 consumer account and then the provider removed the account from share2:

alter share share2 remove accounts = xy12345;

Copy

When any of the following commands were executed in the consumer account, valid data was returned:

SHOW <objects> IN DATABASE <revoked_mounted_db>;

SHOW <objects> IN SCHEMA <revoked_mounted_db>.<schema>;

Copy

Currently:

If the same scenario occurs, an error similar to the following is returned when executing the SHOW commands described above:

SQL compilation error: <details of the specific error>.

Network Policies: Dangling References for Policies with Replication & Failover/Failback¶

The behavior of replication and network policies along with failover/failback and network policies has changed as follows:

Previously:

With network policies and their references (i.e. assignments to the primary account and users in the primary account):

• Specify USERS and NETWORK POLICIES in the replication/failover group when there are user-level network policies.

• Specify NETWORK POLICIES in the replication/failover group when there are only account-level network policies.

• Replication and failover/failback occurred even if the result was a dangling reference in the target account.

A dangling reference means that an object in the secondary account references an object that does not exist in the same account. For example:

• A user/username in the secondary account references a network policy that is not in the secondary account. This scenario occurs when a network policy is assigned to a user in the primary account and the replication/failover group specifies USERS, but not NETWORK POLICIES.

• A network policy is attached to the primary account and the replication/failover group does not include NETWORK POLICIES.

Currently:

The current behavior has changed as follows:

• Specify ACCOUNT PARAMETERS, USERS, and NETWORK POLICIES in the replication/failover group when there are user-level network policies.

• Specify ACCOUNT PARAMETERS and NETWORK POLICIES in the replication/failover group when there are only account-level network policies.

• Replication and failover/failback fail in the secondary account if the result is a dangling reference.

For example, if the primary account has an account-level network policy set and a user-level network policy set on a user and dangling references would be created in the target account for both the account-level parameter and the user:

Dangling references in the snapshot. Correct the errors before refreshing again. The following references are missing (referred entity <- [referring entities]):

ACCOUNT PARAMETERS <- [NETWORK POLICIES]. Add ACCOUNT PARAMETER into the replication group to fix it.

NETWORK_POLICY 'MYACCOUNT.P2' <- [USER 'MYACCOUNT.USERNAME']

Copy

Otherwise, the error message specifies either the account parameter statement or the user statement depending on how the replication group is configured and what the result would be in the target account.

Replication Groups: Limit Account Objects to Membership in a Single Group¶

Account replication enables the replication of account objects in a replication or failover group. Account objects can include account parameters, roles, security objects, and users. For a full list of supported objects, refer to Replicated objects.

Currently, if an account does not have a failover group that includes account object types, different account object types can be included in more than one replication group. This behavior has changed as follows:

Previously:

Account object types could be included in more than one replication group.

Currently:

Account object types are limited to one replication group.

Replication Groups: Read-only Account Parameters in Target Accounts¶

When an account object type is included in a replication or failover group, the object type is read-only in the target account. For example, if roles are replicated to a target account, roles cannot be created or modified in the target account.

Account parameters can be included in a replication or failover group to be replicated to a target account. Some parameters (e.g. STATEMENT_TIMEOUT_IN_SECONDS) can be set on multiple levels (e.g. at the account level and per user). Account parameter replication only replicates the account-level setting for a parameter.

The behavior for account parameter replication has changed as follows:

Previously:

If ACCOUNT PARAMETERS were included in the OBJECT_TYPES list for a replication or failover group, secondary account-level parameters in target accounts could be modified.

Currently:

If ACCOUNT PARAMETERS are included in the OBJECT_TYPES list for a replication or failover group, secondary account-level parameters in target accounts are read-only.

SHOW REPLICATION GROUPS Command: Changes to Output¶

The SHOW REPLICATION GROUPS command includes a next_scheduled_refresh column in its output that displays the date and time of the next scheduled refresh for a secondary replication or failover group with a replication schedule. This column is NULL for secondary replication or failover groups with a replication schedule if it is not in the current region.

This behavior has changed as follows:

Previously:

The next_scheduled_refresh column was NULL for secondary replication or failover groups with a replication schedule that was not in the current region.

Currently:

The next_scheduled_refresh column includes the date and time for the next scheduled refresh for all secondary replication and failover groups with a replication schedule.