ALTER JOIN POLICY¶

Syntax¶

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

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

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

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

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

ALTER JOIN POLICY [ IF EXISTS ] <name> UNSET COMMENT

Parameters¶

name

Specifies the identifier for the join 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 join policy; must be unique for your schema. The new identifier cannot be used if the identifier is already in place for a different join policy.

For more information, 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 join policy:

BODY -> expression

SQL expression that determines the restrictions of a join policy.

To define the body of the join policy, call the JOIN_CONSTRAINT function, which returns TRUE or FALSE. When the function returns TRUE, queries are required to use a join to return results.

The syntax of the JOIN_CONSTRAINT function is:

JOIN_CONSTRAINT (
  { JOIN_REQUIRED => <boolean_expression> }
  )

Copy

Where:

JOIN_REQUIRED => boolean_expression

Specifies whether a join is required in queries when data is selected from tables or views that have the join policy assigned to them.

The body of a policy cannot reference user-defined functions, tables, or views.

Allowed join columns are specified in the CREATE or ALTER statement for the table or view to which the policy is applied, not in the CREATE JOIN POLICY statement.

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 join policy.

Default: No value

UNSET ...

Specifies one or more properties and/or parameters to unset, by resetting them to their defaults, for the join 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:

The USAGE privilege on the parent database and schema are required to perform operations on any object in a 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 more information about join policy DDL and privileges, see Privileges and commands.

Usage notes¶

• If you want to update an existing join policy and need to see the current body of the policy, run the DESCRIBE JOIN POLICY command. You can also use the GET_DDL function to obtain the full definition of the join policy, including its body.

• Moving a join policy to a managed access schema (using the ALTER JOIN POLICY … RENAME TO syntax) is prohibited unless the join policy owner (that is, the role that has the OWNERSHIP privilege on the join 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¶

Modify the SQL expression for a join policy:

ALTER JOIN POLICY jp3 SET BODY -> JOIN_CONSTRAINT(JOIN_REQUIRED => FALSE);

Rename a join policy:

ALTER JOIN POLICY my_join_policy RENAME TO my_join_policy_2;