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

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. Retaining those privileges required specifying COPY GRANTS explicitly.

Separately, whether the replacement object inherited any future grants defined for that object type in the schema or database depended on the object type:

• For tables, views, and materialized views, the replacement object inherited future grants only when COPY GRANTS was not specified.
• For streams, semantic views, functions, procedures, and Cortex Agents, the replacement object always inherited future grants, regardless of whether COPY GRANTS was specified.

By default, the role that executed the statement owned the replacement object; if the replacement object inherited a future grant of the OWNERSHIP privilege, as described above, the role named in that future grant owned it instead.

After the change:

All privileges except OWNERSHIP are copied from the replaced object to the replacement object. Whether future grants also apply depends on the object type:

• For tables, views, and materialized views, future grants defined for the object type in the schema apply to the replacement object as follows:
  • A future grant of the OWNERSHIP privilege is always applied: the role named in that future grant owns the replacement object, and the copied privileges are retained.
  • A future grant of any other privilege is applied only if the replaced object had no explicit access privileges (other than OWNERSHIP) to copy; in that case, all future grants defined for the object type in the schema are applied, as if COPY GRANTS weren’t used.
• For streams, semantic views, functions, procedures, and Cortex Agents, future grants defined for the object type in the schema always apply to the replacement object, in addition to the copied privileges.

By default, the role that executes the statement owns the replacement object, unless a future grant of the OWNERSHIP privilege applies, in which case the role named in that future grant owns it instead. 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;

Because IGNORE GRANTS drops the privileges on the replaced object instead of copying them, the replacement object inherits all future grants defined for the object type in the schema, regardless of object type or whether the replaced object had any privileges to copy.

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