Join policies¶

Creating a join policy¶

The simplest form of join policy requires users to always join a table or view to some other table or view. In other words, queries against a single table, without a join specification, are disallowed. For example, create a policy named jp1:

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

For the complete syntax of this command and its JOIN_CONSTRAINT function, see CREATE JOIN POLICY.

Applying a join policy to a table or view¶

Having created a join policy, you implement it by assigning it to a specific table or view:

• Use an ALTER TABLE or ALTER VIEW command if the table or view already exists.

• Use a CREATE TABLE or CREATE VIEW command for a new table or view.

In either case, specify the JOIN POLICY parameter to identify the join policy itself. For example, the following command assigns the policy jp to the table join_table:

CREATE OR REPLACE TABLE join_table (
  col1 INT,
  col2 VARCHAR,
  col3 NUMBER )
  JOIN POLICY jp1;

Optionally, you can also specify the ALLOWED JOIN KEYS parameter if you want to restrict joins to use specific joining columns. See Restricting joins on specific columns.

A table or view can have only one join policy assigned to it at any given time. See Replacing a join policy.

Testing the join policy by running some queries¶

The following queries demonstrate the expected behavior when the jp1 policy is in effect for the table join_table. This table does not need to be loaded; an empty table is sufficient to demonstrate the expected behavior.

A query without a join returns an expected error:

SELECT * FROM join_table;

506037 (23001): SQL compilation error: Join Policy violation, please contact the policy admin for details

A query with an explicit inner join on col1 returns results:

SELECT * FROM join_table jt1 INNER JOIN join_table_2 jt2 ON jt1.col1=jt2.col1;

+------+------+------+------+------+------+
| COL1 | COL2 | COL3 | COL1 | COL2 | COL3 |
|------+------+------+------+------+------|
+------+------+------+------+------+------+

A query with an explicit inner join on col2 also returns results:

SELECT * FROM join_table jt1 INNER JOIN join_table_2 jt2 ON jt1.col2=jt2.col2;

+------+------+------+------+------+------+
| COL1 | COL2 | COL3 | COL1 | COL2 | COL3 |
|------+------+------+------+------+------|
+------+------+------+------+------+------+

Restricting joins on specific columns¶

To further test join policy behavior, detach (unset) the policy from the table, drop and recreate the join policy, then recreate join_table with DDL that includes the ALLOWED JOIN KEYS parameter.

ALTER TABLE join_table UNSET JOIN POLICY;

DROP JOIN POLICY jp1;

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

CREATE OR REPLACE TABLE join_table (
  col1 INT,
  col2 VARCHAR,
  col3 NUMBER )
  JOIN POLICY jp1 ALLOWED JOIN KEYS (col1);

Now try one of the previous queries again, with col2 as the joining column. The query fails because col2 is not an allowed join key.

SELECT * FROM join_table jt1 INNER JOIN join_table_2 jt2 ON jt1.col2=jt2.col2;

506038 (23001): SQL compilation error: Join Policy violation, invalid join condition with reason: Disallowed join key used.

The same query with jt1.col1=jt2.col1 as the join condition would succeed. A natural join of these two tables would fail because it would attempt to join over col1 and col2.

Showing and describing join policies¶

You can use SHOW JOIN POLICIES and DESCRIBE JOIN POLICY commands to get basic information about existing join policies in your account. To return more detailed information about join policies, see Monitoring join policies.

SHOW JOIN POLICIES;

+-------------------------------+------+---------------+----------------+-------------+--------------+---------+-----------------+---------+
| created_on                    | name | database_name | schema_name    | kind        | owner        | comment | owner_role_type | options |
|-------------------------------+------+---------------+----------------+-------------+--------------+---------+-----------------+---------|
| 2024-12-04 15:15:49.591 -0800 | JP1  | POLICY1_DB    | POLICY1_SCHEMA | JOIN_POLICY | POLICY1_ROLE |         | ROLE            |         |
+-------------------------------+------+---------------+----------------+-------------+--------------+---------+-----------------+---------+

DESCRIBE JOIN POLICY jp1;

+------+-----------+-----------------+----------------------------------------+
| name | signature | return_type     | body                                   |
|------+-----------+-----------------+----------------------------------------|
| JP1  | ()        | JOIN_CONSTRAINT | JOIN_CONSTRAINT(JOIN_REQUIRED => TRUE) |
+------+-----------+-----------------+----------------------------------------+

Creating and applying conditional join policies¶

Policy administrators can define the SQL expression of a join policy so different queries have different restrictions based on factors such as the role of the user executing the query. This strategy can allow one user to query a table without restriction while requiring others to use joins.

For example, the following join policy gives users with the roles ACCOUNTADMIN, FINANCE_ROLE, or HR_ROLE unrestricted access to a table while requiring all other users to specify a join.

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

Copy

Tip

You can use the following strategies when using context functions such as CURRENT_ROLE in a conditional policy:

• Context functions return strings, so comparisons using them are case-sensitive. You can use LOWER to convert strings to all lowercase if you’d like to do a case-insensitive comparison.

• The POLICY_CONTEXT function helps you evaluate whether a policy body is returning the correct value when a context function returns a certain value. The POLICY_CONTEXT function simulates query results based upon a specified value of one or more context functions.

Queries without joins fail¶

When a join policy is assigned to join_table, simple queries without joins fail. The following query returns an error.

SELECT col1, col2 FROM join_table;

WHERE clause joins are prohibited¶

If join_table (alias jt1) is a join-constrained table, the following WHERE clause join returns an error:

SELECT *
  FROM join_table jt1, join_table_2 jt2
  WHERE jt1.col1=jt2.col1;

Left and right outer joins depend on the order of tables¶

The following examples show the expected behavior with outer joins, where join_table (alias jt1) is the join-constrained table. The first query returns an error.

SELECT * FROM join_table jt1
  LEFT OUTER JOIN join_table_2 jt2 ON jt1.col1=jt2.col1;

The second query returns results.

SELECT * FROM join_table jt1
  RIGHT OUTER JOIN join_table_2 jt2 ON jt1.col1=jt2.col1;

Joining two join-constrained tables is not supported¶

If join_table and join_table_2 both have a join policy assigned, the following join query returns an error:

SELECT * FROM join_table jt1 JOIN join_table_2 jt2 ON jt1.col1=jt2.col1;

UNION set operations are disallowed, but INTERSECT operations are allowed¶

In these examples, join_table has a join policy but join_table_3 does not. The UNION query fails, but a similar INTERSECT query succeeds.

SELECT * FROM JOIN_TABLE
UNION
SELECT * FROM JOIN_TABLE_3;

SELECT * FROM JOIN_TABLE
INTERSECT
SELECT * FROM JOIN_TABLE_3;

EXCEPT behavior depends on the order of query blocks¶

The EXCEPT behavior depends on the order of query blocks. Note that the first query selects from the join-constrained table first and returns an error.

SELECT * FROM JOIN_TABLE
EXCEPT
SELECT * FROM JOIN_TABLE_3;

The second query succeeds.

SELECT * FROM JOIN_TABLE_3
EXCEPT
SELECT * FROM JOIN_TABLE;

A view on a join-constrained table is also protected¶

Assume that join_table has been assigned join policy jp1. Create a view on the table:

CREATE VIEW join_table_view AS
  SELECT * FROM join_table;

Now query the view without specifying a join:

SELECT * FROM join_table_view;

The query fails because it violates the policy on join_table. The query on the view must contain a join. For more information about join policy behavior with views, see Views and materialized views.

Modifying a join policy¶

You can use the ALTER JOIN POLICY command to modify join policy rules. You can also rename a policy or change its comment.

Before modifying a join policy, run the DESCRIBE JOIN POLICY command or GET_DDL function to review the policy’s current SQL expression.

For example, run the following command to update the SQL expression of the join policy jp3 so that joins are not required:

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

Replacing a join policy¶

The recommended method to replace a join policy is to use the FORCE parameter in an ALTER TABLE statement, which detaches the existing policy and assigns a new one in a single command. This approach allows you to atomically replace the old policy, leaving no gap in protection.

For example, to assign a new join policy to a table that is already join-constrained:

ALTER TABLE join_table SET JOIN POLICY jp2 FORCE;

You can also detach the policy from a table or view in one statement (using UNSET JOIN POLICY), then set a new policy in a different statement (using SET JOIN POLICY). If you choose this method, the table is not protected by a join policy in the interim between the two operations. A query could potentially access sensitive data during this time.

Detaching a join policy¶

Use the UNSET JOIN POLICY clause of an ALTER TABLE or ALTER VIEW command to detach a join policy from a table or view. The name of the policy is not required because an object can’t have more than one policy. For example:

ALTER VIEW join_view UNSET JOIN POLICY;

Monitoring join policies¶

You can monitor join policy usage in the following ways:

• Query the JOIN_POLICIES view in the Account Usage schema of the shared SNOWFLAKE database. This view is a catalog for all join policies in your Snowflake account.

• Query the POLICY_REFERENCES Information Schema table function to identify join policy references and find out which tables and views currently have policies applied to them.

SELECT policy_name, policy_body, created
  FROM SNOWFLAKE.ACCOUNT_USAGE.JOIN_POLICIES
  WHERE policy_name='JP2' AND created LIKE '2024-11-26%';

+-------------+----------------------------------------------------------+-------------------------------+
| POLICY_NAME | POLICY_BODY                                              | CREATED                       |
|-------------+----------------------------------------------------------+-------------------------------|
| JP2         | CASE                                                     | 2024-11-26 11:22:54.848 -0800 |
|             |           WHEN CURRENT_ROLE() = 'ACCOUNTADMIN'           |                               |
|             |             THEN JOIN_CONSTRAINT(JOIN_REQUIRED => FALSE) |                               |
|             |           ELSE JOIN_CONSTRAINT(JOIN_REQUIRED => TRUE)    |                               |
|             |         END                                              |                               |
+-------------+----------------------------------------------------------+-------------------------------+

Best practices for policy administration¶

Creating a join policy and assigning the policy to a table requires the same general procedure as creating and assigning other policies, such as masking, projection, and aggregation policies:

1. If you are using a centralized management approach, create a custom role (such as join_policy_admin) to manage the policy. Alternatively, you can use an existing role.

2. Grant this role the privileges to create and assign a join policy.

3. Create the join policy.

4. Create or alter a table to assign the policy to the table and to allow joining columns (ALLOWED JOIN KEYS).

5. Test some join and non-join queries on the table.

Successful queries against the table must join its data to another table or view and must join on the allowed columns.

Access control administrator tasks

1. Create a custom role to manage the join policy. You could also re-use an existing role.

   USE ROLE USERADMIN;
   
   CREATE ROLE join_policy_admin;
   

   Copy

2. Grant the join_policy_admin custom role the privileges to create a join policy in a schema and assign the policy to a table or view in the Snowflake account.

   This step assumes the join policy will be stored in a database and schema named privacy.join_policies and that this database and schema already exist:

   GRANT USAGE ON DATABASE privacy TO ROLE join_policy_admin;
   GRANT USAGE ON SCHEMA privacy.join_policies TO ROLE join_policy_admin;
   
   GRANT CREATE JOIN POLICY
     ON SCHEMA privacy.join_policies TO ROLE join_policy_admin;
   
   GRANT APPLY JOIN POLICY ON ACCOUNT TO ROLE join_policy_admin;
   

   Copy

   The join_policy_admin role can now be assigned to one or more users.

   For information about the privileges needed to work with join policies, refer to Managing join policies (in this topic).

Join policy administrator tasks

• Create a join policy:

  USE ROLE join_policy_admin;
  USE SCHEMA privacy.join_policies;
  
  CREATE OR REPLACE JOIN POLICY jp1
    AS () RETURNS JOIN_CONSTRAINT -> JOIN_CONSTRAINT(JOIN_REQUIRED => TRUE);
  

  Copy

Other policies¶

This section describes how a join policy interacts with other policies, including masking policies, row access policies, aggregation policies, and projection policies.

You can attach other policies to a join-constrained table. A successful query against the table must meet the requirements of all policies.

If a row access policy is assigned to a join-constrained table, a row excluded from the query results based on the row access policy is not included when calculating the results of the join.

The body of a masking policy, row access policy, aggregation policy, or projection policy cannot reference a join-constrained table, including its columns.

Views and materialized views¶

You can assign a join policy to both views and materialized views. When a join policy is applied to a view, the underlying table does not become join-constrained. This base table can still be queried without restriction.

Whether you can create a view from a join-constrained table depends on the type of view:

• You can create a regular view from one or more join-constrained tables; however, queries against that view must join data in a way that meets the restrictions of those base tables. You cannot circumvent a join policy on a protected table by creating a view on the table. The policy for the table is respected and enforced for queries against the view. For an example, see A view on a join-constrained table is also protected.

• You cannot create a materialized view based on a join-constrained table or view, nor can you assign a join policy to a table or view upon which a materialized view is based.

Cloned objects¶

The following approach helps to safeguard data from users with the SELECT privilege on a cloned table or view that is stored in the cloned database or schema:

• Cloning an individual join policy object is not supported.

• Cloning a database results in the cloning of all join policies within the database.

• Cloning a schema results in the cloning of all join policies within the schema.

• A cloned table maps to the same join policies as the source table.

  • When a table is cloned in the context of its parent schema being cloned, if the source table has a reference to a join policy in the same parent schema (that is, a local reference), the cloned table will have a reference to the cloned join policy.

  • If the source table refers to a join policy in a different schema (that is, a foreign reference), the cloned table retains the foreign reference.

For more information, see CREATE <object> … CLONE.

Replication¶

Join policies and their assignments can be replicated using database replication and replication groups.

For database replication, the replication operation fails if either of the following conditions is true:

• The primary database is in an Enterprise (or higher) account and contains a policy but one or more of the accounts approved for replication are on lower editions.

• A table or view contained in the primary database has a dangling reference to a policy in another database.

The dangling reference behavior for database replication can be avoided when replicating multiple databases in a replication group.

Join policy privileges¶

Snowflake supports the following privileges on the join policy object.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For information, see Summary of DDL commands, operations, and privileges.

Join policy DDL reference¶

Snowflake supports the following DDL commands to create and manage join policies.

• CREATE JOIN POLICY

• ALTER JOIN POLICY

• DESCRIBE JOIN POLICY

• DROP JOIN POLICY

• SHOW JOIN POLICIES

Summary of DDL commands, operations, and privileges¶

The following table summarizes the relationship between join policy privileges and DDL operations.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

Snowflake supports different permissions to create and set a join policy on an object.

1. For a centralized policy management approach in which the join_policy_admin custom role creates and sets join policies on all tables, the following permissions are necessary:

   USE ROLE securityadmin;
   GRANT USAGE ON DATABASE mydb TO ROLE join_policy_admin;
   GRANT USAGE ON SCHEMA mydb.schema TO ROLE join_policy_admin;
   GRANT CREATE JOIN POLICY ON SCHEMA mydb.schema TO ROLE join_policy_admin;
   GRANT APPLY ON JOIN POLICY ON ACCOUNT TO ROLE join_policy_admin;
   

   Copy

2. In a hybrid management approach, a single role has the CREATE JOIN POLICY privilege to ensure join policies are named consistently and individual teams or roles have the APPLY privilege for a specific join policy.

   For example, the custom role finance_role can be granted the permission to set the join policy cost_center on tables and views the role owns (that is, the role has the OWNERSHIP privilege on the table or view):

   USE ROLE securityadmin;
   GRANT CREATE JOIN POLICY ON SCHEMA mydb.schema TO ROLE join_policy_admin;
   GRANT APPLY ON JOIN POLICY cost_center TO ROLE finance_role;
   

   Copy