ALTER PROJECTION POLICY¶

Replaces the existing projection policy rules with new rules or a new comment and allows the renaming of a projection policy.

Any changes made to the policy rules go into effect when the next SQL query that uses the projection policy runs.

See also:

Projection policy DDL reference

Syntax¶

ALTER PROJECTION POLICY [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER PROJECTION POLICY [ IF EXISTS ] <name> SET BODY -> <expression>

ALTER PROJECTION POLICY <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER PROJECTION POLICY <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

ALTER PROJECTION POLICY [ IF EXISTS ] <name> SET COMMENT = '<string_literal>'

ALTER PROJECTION POLICY [ IF EXISTS ] <name> UNSET COMMENT
Copy

Parameters¶

name

Specifies the identifier for the projection policy to alter.

If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

RENAME TO new_name

Specifies the new identifier for the projection policy; must be unique for your schema. The new identifier cannot be used if the identifier is already in place for a different projection policy.

For more details, see Identifier requirements.

You can move the object to a different database and/or schema while optionally renaming the object. To do so, specify a qualified new_name value that includes the new database and/or schema name in the form db_name.schema_name.object_name or schema_name.object_name, respectively.

Note

  • The destination database and/or schema must already exist. In addition, an object with the same name cannot already exist in the new location; otherwise, the statement returns an error.

  • Moving an object to a managed access schema is prohibited unless the object owner (that is, the role that has the OWNERSHIP privilege on the object) also owns the target schema.

SET ...

Specifies one (or more) properties to set for the projection policy:

BODY -> expression

SQL expression that determines whether to project the column.

The expression calls the internal PROJECTION_CONSTRAINT function to determine whether to project a column:

  • PROJECTION_CONSTRAINT(ALLOW => TRUE) — Allows the column to which the projection policy is attached to be projected.

  • PROJECTION_CONSTRAINT(ALLOW => FALSE) — Does not allow the column to which the projection policy is attached to be projected.

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

COMMENT = 'string_literal'

Adds a comment or overwrites the existing comment for the projection policy.

Default: No value

UNSET ...

Specifies one or more properties and/or parameters to unset, by resetting them to their defaults, for the projection policy:

  • TAG tag_name [ , tag_name ... ]

  • COMMENT

When resetting a property/parameter, specify only the name; specifying a value for the property will return an error.

Access control requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege

Object

Notes

OWNERSHIP

Projection policy

OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

For additional details on projection policy DDL and privileges, see Privileges and commands.

Usage notes¶

  • If you want to update an existing projection policy and need to see the current definition of the policy, run the DESCRIBE PROJECTION POLICY command or GET_DDL function.

  • Moving a projection policy to a managed access schema (using the ALTER PROJECTION POLICY … RENAME TO syntax) is prohibited unless the projection policy owner (i.e. the role that has the OWNERSHIP privilege on the projection policy) also owns the target schema.

  • Regarding metadata:

    Attention

    Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

Examples¶

Rename a projection policy:

ALTER PROJECTION POLICY mypolicy RENAME TO proj_policy_acctnumber;
Copy
Language: English