Políticas de junção¶

Criação de uma política de junção¶

A forma mais simples de política de junção exige que os usuários sempre unam uma tabela ou exibição a alguma outra tabela ou exibição. Em outras palavras, as consultas em uma única tabela, sem uma especificação de junção, não são permitidas. Por exemplo, crie uma política nomeada jp1:

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

Para obter a sintaxe completa desse comando e sua função JOIN_CONSTRAINT, consulte CREATE JOIN POLICY.

Aplicação de uma política de junção a uma tabela ou exibição¶

Após criar uma política de junção, você a implementa atribuindo-a a uma tabela ou exibição específica:

• Use um comando ALTER TABLE ou ALTER VIEW se a tabela ou exibição já existir.

• Use um comando CREATE TABLE ou CREATE VIEW para uma nova tabela ou exibição.

Em ambos os casos, especifique o parâmetro JOIN POLICY para identificar a própria política de junção. Por exemplo, o comando a seguir atribui a política jp à tabela join_table:

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

Opcionalmente, você também pode especificar o parâmetro ALLOWED JOIN KEYS se quiser restringir as junções para que usem colunas de junção específicas. Consulte Restrição de junções em colunas específicas.

Uma tabela ou exibição pode ter apenas uma política de junção atribuída a ela em um determinado momento. Consulte Substituição de uma política de junção.

Testes da política de junção executando algumas consultas¶

As consultas a seguir demonstram o comportamento esperado quando a política jp1 está em vigor para a tabela join_table. Essa tabela não precisa ser carregada; uma tabela vazia é suficiente para demonstrar o comportamento esperado.

Uma consulta sem uma junção retorna um erro esperado:

SELECT * FROM join_table;

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

Uma consulta com uma junção interna explícita em col1 retorna resultados:

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

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

Uma consulta com uma junção interna explícita em col2 também retorna resultados:

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

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

Restrição de junções em colunas específicas¶

Para testar melhor o comportamento da política de junção, desanexe (remova) a política da tabela, remova e recrie a política de junção e, em seguida, recrie join_table com a DDL que inclui o parâmetro ALLOWED JOIN KEYS.

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);

Agora, tente novamente uma das consultas anteriores, com col2 como a coluna de junção. A consulta falha porque col2 não é uma chave de junção permitida.

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.

A mesma consulta com jt1.col1=jt2.col1 como condição de junção seria bem-sucedida. Uma junção natural dessas duas tabelas falharia porque tentaria unir col1 e col2.

Exibição e descrição de políticas de junção¶

É possível usar os comandos SHOW JOIN POLICIES e DESCRIBE JOIN POLICY para obter informações básicas sobre as políticas de junção existentes em sua conta. Para obter informações mais detalhadas sobre as políticas de junção, consulte Monitoramento de políticas conjuntas.

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) |
+------+-----------+-----------------+----------------------------------------+

Criação e aplicação de políticas de junção condicional¶

Os administradores de políticas podem definir a expressão SQL de uma política de junção para que diferentes consultas tenham restrições diferentes com base em fatores como a função do usuário que executa a consulta. Essa estratégia pode permitir que um usuário consulte uma tabela sem restrições e exigir que outros usem junções.

Por exemplo, a política de junção a seguir oferece aos usuários com as funções ACCOUNTADMIN, FINANCE_ROLE ou HR_ROLE acesso irrestrito a uma tabela e exige que todos os outros usuários especifiquem uma junção.

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

Dica

É possível usar as seguintes estratégias ao usar funções de contexto, como CURRENT_ROLE, em uma política condicional:

• Funções de contexto retornam cadeias de caracteres, portanto, comparações que as utilizam diferenciam maiúsculas de minúsculas. Você pode usar LOWER para converter todas as letras das cadeias de caracteres para minúsculas se quiser fazer uma comparação sem diferenciar entre maiúsculas e minúsculas.

• A função POLICY_CONTEXT ajuda você a avaliar se um corpo de política está retornando o valor correto quando uma função de contexto retorna um determinado valor. A função POLICY_CONTEXT simula resultados de consulta com base no valor especificado de uma ou mais funções de contexto.

Falhas nas consultas sem junções¶

Quando uma política de junção é atribuída a join_table, as consultas simples sem junções falham. A consulta a seguir retorna um erro.

SELECT col1, col2 FROM join_table;

Proibições das junções de cláusulas WHERE¶

Se join_table (alias jt1) for uma tabela com restrição de junção, a seguinte junção de cláusulas WHERE retornará um erro:

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

As junções externas à esquerda e à direita dependem da ordem das tabelas¶

Os exemplos a seguir mostram o comportamento esperado com junções externas, em que join_table (alias jt1) é a tabela com restrição de junção. A primeira consulta retorna um erro.

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

A segunda consulta retorna resultados.

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

Não há suporte para a junção de duas tabelas com restrições de junção¶

Se join_table e join_table_2 tiverem uma política de junção atribuída, a seguinte consulta de junção retornará um erro:

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

As operações de conjunto UNION não são permitidas, mas as operações INTERSECT são permitidas¶

Nesses exemplos, join_table tem uma política de junção, mas join_table_3 não tem. A consulta UNION falha, mas uma consulta semelhante INTERSECT é bem-sucedida.

SELECT * FROM JOIN_TABLE
UNION
SELECT * FROM JOIN_TABLE_3;

SELECT * FROM JOIN_TABLE
INTERSECT
SELECT * FROM JOIN_TABLE_3;

O comportamento de EXCEPT depende da ordem dos blocos de consulta¶

O comportamento de EXCEPT depende da ordem dos blocos de consulta. Observe que a primeira consulta seleciona primeiro a tabela com restrição de junção e retorna um erro.

SELECT * FROM JOIN_TABLE
EXCEPT
SELECT * FROM JOIN_TABLE_3;

A segunda consulta é bem-sucedida.

SELECT * FROM JOIN_TABLE_3
EXCEPT
SELECT * FROM JOIN_TABLE;

Uma exibição em uma tabela com restrição de junção também é protegida¶

Suponha que join_table tenha recebido a política de junção jp1. Crie uma exibição na tabela:

CREATE VIEW join_table_view AS
  SELECT * FROM join_table;

Agora, consulte a exibição sem especificar uma junção:

SELECT * FROM join_table_view;

A consulta falha porque viola a política em join_table. A consulta na exibição deve conter uma junção. Para obter mais informações sobre o comportamento da política de junção com exibições, consulte Exibições e exibições materializadas.

Modificação de uma política de junção¶

É possível usar o comando ALTER JOIN POLICY para modificar as regras da política de junção. Você também pode renomear uma política ou alterar seu comentário.

Antes de modificar uma política de junção, execute o comando DESCRIBE JOIN POLICY ou a função GET_DDL para analisar a expressão SQL atual da política.

Por exemplo, execute o seguinte comando para atualizar a expressão SQL da política de junção jp3 para que as junções não sejam necessárias:

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

Substituição de uma política de junção¶

O método recomendado para substituir uma política de junção é usar o parâmetro FORCE em uma instrução ALTER TABLE, que desvincula a política existente e atribui uma nova política em um único comando. Essa abordagem permite que você substitua atomicamente a política antiga, sem deixar nenhuma lacuna na proteção.

Por exemplo, para atribuir uma nova política de junção a uma tabela que já tenha restrições de junção:

ALTER TABLE join_table SET JOIN POLICY jp2 FORCE;

Você também pode desvincular a política de uma tabela ou exibição em uma instrução (usando UNSET JOIN POLICY) e, em seguida, definir uma nova política em uma instrução diferente (usando SET JOIN POLICY). Se você escolher esse método, a tabela não será protegida por uma política de junção no intervalo entre as duas operações. Uma consulta poderia potencialmente acessar dados confidenciais durante esse período.

Desvinculação de uma política de junção¶

Use a cláusula UNSET JOIN POLICY de um comando ALTER TABLE ou ALTER VIEW para separar uma política de junção de uma tabela ou exibição. O nome da política não é necessário porque um objeto não pode ter mais de uma política. Por exemplo:

ALTER VIEW join_view UNSET JOIN POLICY;

Monitoramento de políticas conjuntas¶

É possível monitorar o uso da política de junção das seguintes maneiras:

• Consulte a exibição JOIN_POLICIES no esquema Account Usage do banco de dados compartilhado SNOWFLAKE. Essa exibição é um catálogo para todas as políticas de junção em sua conta Snowflake.

• Consulte a função de tabela Information Schema POLICY_REFERENCES para identificar referências de políticas de junção e descobrir quais tabelas e exibições têm políticas aplicadas a elas no momento.

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                                              |                               |
+-------------+----------------------------------------------------------+-------------------------------+

Práticas recomendadas para a administração de políticas¶

A criação de uma política de junção e a atribuição da política a uma tabela requerem o mesmo procedimento geral de criação e atribuição de outras políticas, como políticas de mascaramento, projeção e agregação:

1. Se você estiver usando uma abordagem de gerenciamento centralizado, crie uma função personalizada (como join_policy_admin) para gerenciar a política. Como alternativa, você pode usar uma função existente.

2. Conceda a essa função os privilégios para criar e atribuir uma política de junção.

3. Crie a política de junção.

4. Crie ou altere uma tabela para atribuir a política à tabela e permitir colunas de junção (ALLOWED JOIN KEYS).

5. Teste algumas consultas com e sem junção na tabela.

As consultas bem-sucedidas na tabela devem unir seus dados a outra tabela ou exibição e devem unir as colunas permitidas.

Tarefas do administrador de controle de acesso

1. Crie uma função personalizada para gerenciar a política de junção. Você também pode reutilizar uma função existente.

   USE ROLE USERADMIN;
   
   CREATE ROLE join_policy_admin;
   

   Copy

2. Conceda à função personalizada join_policy_admin os privilégios para criar uma política de junção em um esquema e atribuir a política a uma tabela ou exibição na conta Snowflake.

   Esta etapa pressupõe que a política de junção será armazenada em um banco de dados e esquema denominado privacy.join_policies e que esse banco de dados e esquema já existem:

   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

   A função join_policy_admin agora pode ser atribuída a um ou mais usuários.

   Para obter informações sobre os privilégios necessários para trabalhar com as políticas de junção, consulte Gerenciamento das políticas de junção (neste tópico).

Tarefas do administrador de políticas de junção

• Crie uma política de junção:

  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

Outras políticas¶

Esta seção descreve como uma política de junção interage com outras políticas, incluindo políticas de mascaramento, políticas de acesso a linhas, políticas de agregação e políticas de projeção.

É possível anexar outras políticas a uma tabela com restrição de junção. Uma consulta bem-sucedida na tabela deve atender aos requisitos de todas as políticas.

Se uma política de acesso a linhas for atribuída a uma tabela com restrição de junção, uma linha excluída dos resultados de consulta com base na política de acesso a linhas não será inclusa no cálculo dos resultados da junção.

O corpo de uma política de mascaramento, política de acesso a linhas, política de agregação ou política de projeção não pode fazer referência a uma tabela com restrição de junção, incluindo suas colunas.

Exibições e exibições materializadas¶

É possível atribuir uma política de junção a exibições e exibições materializadas. Quando uma política de junção é aplicada a uma exibição, a tabela subjacente não se torna limitada pela junção. Esta tabela base ainda pode ser consultada sem restrições.

Se você pode ou não criar uma exibição a partir de uma tabela com restrições de junção depende do tipo de exibição:

• É possível criar uma exibição regular a partir de uma ou mais tabelas com restrições de junção; no entanto, as consultas a essa exibição devem unir os dados de forma a atender às restrições dessas tabelas base. Você não pode contornar uma política de junção em uma tabela protegida criando uma exibição na tabela. A política da tabela é respeitada e aplicada para consultas na exibição. Para obter um exemplo, consulte Uma exibição em uma tabela com restrição de junção também é protegida.

• Você não pode criar uma exibição materializada com base em uma tabela ou exibição com restrição de junção, nem pode atribuir uma política de junção a uma tabela ou exibição na qual uma exibição materializada se baseia.

Objetos clonados¶

A abordagem a seguir ajuda a proteger dados de usuários com o privilégio SELECT em uma tabela ou exibição clonada armazenada no banco de dados ou esquema clonado:

• Não há suporte para a clonagem de um objeto de política de junção individual.

• A clonagem de um banco de dados resulta na clonagem de todas as políticas de junção dentro do banco de dados.

• A clonagem de um esquema resulta na clonagem de todas as políticas de junção dentro do esquema.

• Uma tabela clonada mapeia para as mesmas políticas de junção que a tabela de origem.

  • Quando uma tabela é clonada no contexto de seu esquema pai sendo clonado, se a tabela de origem tiver uma referência a uma política de junção no mesmo esquema pai (ou seja, uma referência local), a tabela clonada terá uma referência à política de junção clonada.

  • Se a tabela de origem se referir a uma política de junção em um esquema diferente (ou seja, uma referência estrangeira), a tabela clonada manterá a referência estrangeira.

Para obter mais informações, consulte CREATE <objeto> … CLONE.

Replicação¶

As políticas de ingresso e suas atribuições podem ser replicadas usando a replicação de banco de dados e os grupos de replicação.

Para replicação de banco de dados, a operação de replicação falha se uma das seguintes condições for verdadeira:

• O banco de dados primário está em uma conta Enterprise (ou superior) e contém uma política, mas uma ou mais das contas aprovadas para replicação estão em edições anteriores.

• Uma tabela ou exibição contida no banco de dados primário tem uma referência pendente para uma política em outro banco de dados.

O comportamento de referência pendente para a replicação de banco de dados pode ser evitado ao replicar múltiplos bancos de dados em um grupo de replicação.

Privilégios da política de junção¶

O Snowflake oferece suporte aos seguintes privilégios no objeto de política de junção.

O privilégio USAGE no banco de dados e no esquema pai é necessário para executar operações em qualquer objeto de um esquema.

Para obter mais informações, consulte Resumo de comandos DDL, operações e privilégios.

Referência de DDL da política de junção¶

O Snowflake oferece suporte aos seguintes comandos de DDL para criar e gerenciar políticas de junção.

• CREATE JOIN POLICY

• ALTER JOIN POLICY

• DESCRIBE JOIN POLICY

• DROP JOIN POLICY

• SHOW JOIN POLICIES

Resumo de comandos DDL, operações e privilégios¶

A tabela a seguir resume a relação entre os privilégios da política de junção e as operações de DDL.

O privilégio USAGE no banco de dados e no esquema pai é necessário para executar operações em qualquer objeto de um esquema.

O Snowflake oferece suporte a diferentes permissões para criar e definir uma política de junção em um objeto.

1. Para uma abordagem de gerenciamento centralizado de políticas em que a função personalizada join_policy_admin cria e define políticas de junção em todas as tabelas, são necessárias as seguintes permissões:

   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. Em uma abordagem de gerenciamento híbrido, uma única função tem o privilégio CREATE JOIN POLICY para garantir que as políticas de ingresso sejam nomeadas de forma consistente e equipes ou funções individuais têm o privilégio APPLY para uma política de junção específica.

   Por exemplo, a função personalizada finance_role pode receber a permissão para definir a política de junção cost_center em tabelas e exibições das quais a função é proprietária (ou seja, a função tem o privilégio OWNERSHIP na tabela ou exibição):

   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