CREATE JOIN POLICY¶

Creates a new join policy in the current/specified schema or replaces an existing join policy.

After creating a join policy, assign the policy to a table using an ALTER TABLE command or a view using an ALTER VIEW command. Alternatively, you can assign a join policy to a table when you create it.

See also:: Join policy DDL reference

语法

CREATE [ OR REPLACE ] JOIN POLICY [ IF NOT EXISTS ] <name>
  AS () RETURNS JOIN_CONSTRAINT -> <body>
  [ COMMENT = '<string_literal>' ]

参数

name: 联接策略的标识符；对于架构必须是唯一的。

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.
AS () RETURNS JOIN_CONSTRAINT: 策略的签名和返回类型。签名不接受任何实参，返回类型为 JOIN_CONSTRAINT，这是一种内部数据类型。所有联接策略都具有相同的签名和返回类型。

body

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> }
  )
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.

COMMENT = 'string_literal': 添加注释或覆盖联接策略的现有注释。

访问控制要求

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

权限	对象	备注
CREATE JOIN POLICY	架构

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent 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 Managing join policies.

使用说明

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 or GET_DDL function.

The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.
CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

关于元数据：

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.

示例

创建要求查询包含联接的联接策略（当策略应用于这些查询中显示的表和视图时）：

CREATE JOIN POLICY jp1 AS ()
  RETURNS JOIN_CONSTRAINT -> JOIN_CONSTRAINT(JOIN_REQUIRED => TRUE);

创建一个联接策略，该策略允许具有 ACCOUNTADMIN 角色的用户来运行没有联接的查询；其他用户必须运行联接查询：

CREATE JOIN POLICY jp2 AS ()
  RETURNS JOIN_CONSTRAINT ->
    CASE
      WHEN CURRENT_ROLE() = 'ACCOUNTADMIN'
        THEN JOIN_CONSTRAINT(JOIN_REQUIRED => FALSE)
      ELSE JOIN_CONSTRAINT(JOIN_REQUIRED => TRUE)
    END;