CREATE OR REPLACE: Grants on the replaced object are copied by default (Pending)¶

When this behavior change bundle is enabled, COPY GRANTS becomes the default behavior for a CREATE OR REPLACE statement that replaces an object that already exists: the privilege grants on the replaced object are copied to the replacement automatically, as though COPY GRANTS had been specified. The change applies to tables, views, materialized views, streams, semantic views, functions, procedures, and Cortex Agents.

Before the change:

A CREATE OR REPLACE statement that didn’t specify COPY GRANTS dropped the privileges that had been granted on the replaced object, and the replacement object inherited any future grants defined for that object type in the schema or database instead. Retaining the existing grants required specifying COPY GRANTS explicitly.

After the change:

All privileges except OWNERSHIP are copied from the replaced object to the replacement object, and the replacement object no longer inherits future grants. The role that executes the statement still owns the replacement object. To drop the grants instead, specify the new IGNORE GRANTS clause, which requires OR REPLACE and can’t be combined with COPY GRANTS:

Copy code

CREATE OR REPLACE TABLE t1 (c1 INT) IGNORE GRANTS;

Grants are copied only when the statement replaces an object that already exists. A CREATE OR REPLACE ... LIKE or CREATE OR REPLACE ... CLONE statement that creates a new object still copies grants from the LIKE or CLONE source only when COPY GRANTS is specified.

Because a grant to a share is now copied along with other grants, replacing a secure view or secure function with a non-secure one fails if the object is granted to a share whose SECURE_OBJECTS_ONLY property is TRUE:

Copy code

090838 (0A000): Non-secure object can only be granted to shares with "secure_objects_only" property set to false.

How to update your code¶

Review CREATE OR REPLACE statements in pipelines and deployment scripts that relied on the previous behavior: grants being dropped on replace, or future grants applying to the replacement object instead. Dropping grants on CREATE OR REPLACE is a common source of unintended loss of access: a routine rebuild of a table, view, or function silently revokes the privileges that consumers depend on, and the failure surfaces later as an insufficient-privileges error in a downstream workload.

To keep the previous behavior for a specific statement, add IGNORE GRANTS:

CREATE OR REPLACE TABLE t1 (c1 INT) IGNORE GRANTS;

Before replacing a secure view or secure function with a non-secure version, check whether the object is granted to a share whose SECURE_OBJECTS_ONLY property is TRUE. If it is, the replacement fails with error 090838 until you update the share setting or the grant.

Ref: 2433