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

This behavior change is in the 2022_08 bundle. In the 6.35 release, the bundle is disabled by default.

For the most up-to-date details about the version and date in which it will be enabled, as well as other release-related details, see the Behavior Change Log.

---

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.

Four commands were affected:

• DROP DATABASE
• DROP SCHEMA
• CREATE OR REPLACE DATABASE
• CREATE OR REPLACE SCHEMA

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.

Four commands are currently affected:

• DROP DATABASE
• DROP SCHEMA
• CREATE OR REPLACE DATABASE
• CREATE OR REPLACE SCHEMA

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

1. For a policy set on a user:

DROP DATABASE & 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 & 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.

2. For a policy set on the account:

DROP DATABASE & 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 & CREATE OR REPLACE SCHEMA:

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

 

 

---

Ref: 857