This behavior change is in the 2022_06 bundle. The bundle was first introduced disabled by default in the 6.29 release.
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 CREATE DATABASE and CREATE SCHEMA commands has changed as follows:
Previously:
Snowflake allowed the CREATE OR REPLACE DATABASE and CREATE OR REPLACE SCHEMA commands to execute on the database or schema containing a masking policy or row access policy that protect an object in a different database or schema. For example:
- A masking policy named db1.s1.p1 protects a column named db2.s1.t1.c1.
- A row access policy named db1.s1.p2 protects a table named db2.s1.t1.
The result was a dangling reference which caused all queries on the column or object to fail.
Note that this behavior also applied to CLONE statements such as CREATE OR REPLACE SCHEMA S1 CLONE S2;.
Currently:
The CREATE OR REPLACE DATABASE or CREATE OR REPLACE SCHEMA command fails if the result is a dangling reference on a policy-protected object. Snowflake returns either of the following error messages:
- For CREATE OR REPLACE DATABASE: Cannot drop database because: Policy '<db.schema.policy>' used by schema '<db.schema>' in another database
- For CREATE OR REPLACE SCHEMA: Cannot drop schema because: Policy '<db.schema.policy>' used by another schema '<db.schema>'
If either of the two error messages occur, query the Account Usage POLICY_REFERENCES view, use a role to unset the masking or row access policy, and then retry the CREATE OR REPLACE statement.
For example:
1. Query the view:
Cross-schema policy references that need to be removed prior to replacement:
select * from snowflake.account_usage.policy_references
where policy_db=<policy_db> and
policy_schema=<policy_schema_to_replace> and ref_schema_name != <policy_schema>;
Cross-database policy references that need to be removed prior to replacement:
select * from snowflake.account_usage.policy_references
where policy_db=<policy_db_to_replace>’ and ref_database_name != <policy_db>;
2. Unset the policies. For example:
For masking policies:
alter table <table_name> modify column <col_name> unset masking policy;
For row access policies:
alter table <table_name> drop all row access policies;
3. Retry the CREATE OR REPLACE command.
Note that with CLONE operations, store the policy objects in a separate database or schema prior to running the CLONE statements.
Ref: 809
