Histórico de acesso¶

Este tópico fornece conceitos sobre o histórico de acesso do usuário no Snowflake.

Neste tópico:

Visão geral¶

O Histórico de Acesso no Snowflake refere-se a quando a consulta do usuário lê dados e quando a instrução SQL realiza uma operação de gravação de dados, como INSERT, UPDATE e DELETE junto com variações do comando COPY, desde o objeto de dados de origem até o objeto de dados de destino. O histórico de acesso do usuário pode ser encontrado consultando-se a exibição ACCESS_HISTORY de Account Usage. Os registros nesta exibição facilitam a auditoria de conformidade regulatória e fornecem insights sobre tabelas e colunas populares e frequentemente acessadas, uma vez que existe uma ligação direta entre o usuário (ou seja, o operador da consulta), a consulta, a tabela ou exibição, a coluna e os dados.

Cada linha na exibição ACCESS_HISTORY contém um único registro por instrução SQL. O registro contém os seguintes tipos de informações:

As colunas de origem que a consulta acessou direta e indiretamente, como as tabelas subjacentes das quais provêm os dados para a consulta.
As colunas projetadas que o usuário vê no resultado da consulta, como as colunas especificadas em uma instrução SELECT.
As colunas usadas para determinar o resultado da consulta, mas não projetadas, como colunas em uma cláusula WHERE para filtrar o resultado.

Por exemplo:

CREATE OR REPLACE VIEW v1 (vc1, vc2) AS
SELECT c1 as vc1,
       c2 as vc2
FROM t
WHERE t.c3 > 0
;

Copy

As colunas C1 e C2 são colunas de origem que a exibição acessa diretamente, que são registradas na coluna base_objects_accessed da exibição ACCESS_HISTORY.
A coluna C3 é usada para filtrar as linhas que a exibição inclui, o que é registrado na coluna base_objects_accessed da exibição ACCESS_HISTORY.
As colunas VC1 e VC2 são colunas projetadas que o usuário vê ao consultar a exibição, SELECT * FROM v1;, que são registradas na coluna direct_objects_accessed da exibição ACCESS_HISTORY.

O mesmo comportamento se aplica a uma coluna-chave em uma cláusula WHERE. Por exemplo:

CREATE OR REPLACE VIEW join_v (vc1, vc2, c1) AS
  SELECT
      bt.c1 AS vc1,
      bt.c2 AS vc2,
      jt.c1
  FROM bt, jt
  WHERE bt.c3 = jt.c1;

Copy

Duas tabelas diferentes são necessárias para criar a exibição: bt (tabela base) e jt (tabela de junção).
As colunas C1, C2 e C3 da tabela base e a coluna C1 da tabela de junção são todas registradas na coluna base_objects_accessed da exibição ACCESS_HISTORY.
As colunas VC1, VC2 e C1 são colunas projetadas que o usuário vê ao consultar a exibição, SELECT * FROM join_v;, que são registradas na coluna direct_objects_accessed da exibição ACCESS_HISTORY.

Nota

Registros na exibição QUERY_HISTORY do Account Usage nem sempre são gravados na exibição ACCESS_HISTORY. A estrutura da instrução SQL determina se o Snowflake registra uma entrada na exibição ACCESS_HISTORY.

Para obter mais detalhes sobre as operações de leitura e escrita que o Snowflake suporta na exibição ACCESS_HISTORY, consulte a exibição Notas de uso.

Rastreamento de operações de leitura e gravação¶

A exibição ACCESS_HISTORY contém estas colunas:

query_id | query_start_time | user_name | direct_objects_accessed | base_objects_accessed | objects_modified | object_modified_by_ddl | policies_referenced | parent_query_id | root_query_id

Copy

As operações de leitura são rastreadas através das primeiras cinco colunas, enquanto a última coluna, objects_modified, especifica as informações de gravação de dados que envolveram colunas, tabelas e estágios do Snowflake.

A consulta no Snowflake e como os objetos de banco de dados foram criados determinam as informações que o Snowflake retorna nas colunas direct_objects_accessed, base_objects_accessed e objects_modified.

Da mesma forma, se a consulta fizer referência a um objeto protegido por uma política de acesso a linhas ou a uma coluna protegida por uma política de mascaramento, o Snowflake registra as informações da política na coluna policies_referenced.

A coluna object_modified_by_ddl registra a operação DDL em um banco de dados, esquema, tabela, exibição e coluna. Essas operações também incluem instruções que especificam uma política de acesso a linhas em uma tabela ou exibição, uma política de mascaramento em uma coluna e atualizações de tags (por exemplo, definir uma tag, alterar um valor de tag) no objeto ou coluna.

As colunas parent_query_id e root_query_id ` registram os IDs da consulta que correspondem a:

Uma consulta que executa uma operação de leitura ou gravação em outro objeto.
Uma consulta que executa uma operação de leitura ou gravação em um objeto que chama um procedimento armazenado, incluindo chamadas de procedimentos armazenados aninhados. Para obter detalhes, consulte consultas antigas (neste tópico).

Para obter detalhes das colunas, consulte a seção Colunas na exibição ACCESS_HISTORY.

Leitura¶

Considere o seguinte cenário para entender uma consulta de leitura e como a exibição ACCESS_HISTORY registra esta informação:

Uma série de objetos: base_table » view_1 » view_2 » view_3.
Uma consulta de leitura em view_2, como:
```
select * from view_2;
```
Copy

Neste exemplo, o Snowflake retorna:

view_2 na coluna direct_objects_accessed, porque a consulta especifica view_2.
base_table na coluna base_objects_accessed porque essa é a fonte original dos dados em view_2.

Observe que view_1 e view_3 não estão incluídas nas colunas direct_objects_accessed e base_objects_accessed porque nenhuma dessas exibições foi incluída na consulta e não são o objeto de base que serve de origem para os dados em view_2.

Gravação¶

Considere o seguinte cenário para entender uma operação de gravação e como a exibição ACCESS_HISTORY registra esta informação:

Uma fonte de dados: base_table
Criação de uma tabela a partir da fonte de dados (ou seja, CTAS):
```
create table table_1 as select * from base_table;
```
Copy

Neste exemplo, o Snowflake retorna:

base_table nas colunas base_objects_accessed e direct_objects_accessed, porque a tabela foi acessada diretamente e é a fonte dos dados.
table_1 na coluna objects_modified com as colunas que foram gravadas ao criar a tabela.

Linhagem de colunas¶

A linhagem de colunas (isto é, histórico de acesso para colunas) estende a exibição ACCESS_HISTORY do Account Usage para especificar como os dados fluem da coluna de origem para a coluna de destino em uma operação de escrita. Snowflake rastreia os dados das colunas de origem através de todos os objetos da tabela subsequente que referenciam os dados das colunas de origem (por exemplo, INSERT, MERGE, CTAS), desde que os objetos da cadeia de linhagem não sejam descartados. Snowflake torna a linhagem de colunas acessível, melhorando a coluna objects_modified na exibição ACCESS_HISTORY.

Para detalhes adicionais, consulte:

Benefícios (neste tópico)
Exemplo: linhagem de colunas (neste tópico)
Notas sobre o uso da Linhagem de colunas

Referências de política de mascaramento e de acesso a linhas¶

A coluna POLICY_REFERENCED especifica o objeto que tem uma política de acesso a linhas definida em uma tabela ou uma política de mascaramento definida em uma coluna, incluindo quaisquer objetos intermediários que são protegidos por uma política de acesso a linhas ou uma política de mascaramento. Snowflake registra a política que é aplicada na tabela ou coluna.

Considere estes objetos:

t1 » v1 » v2

Onde:

t1 é uma tabela de base.
v1 é uma exibição construída a partir da tabela de base.
v2 é uma exibição construída a partir de v1.

Se o usuário consultar v2, a coluna policies_referenced registrará a política de acesso a linhas que protege v2, cada política de mascaramento que protege as colunas em v2, ou ambos os tipos de política, conforme aplicável. Além disso, esta coluna registra quaisquer políticas de mascaramento ou de acesso a linhas que protegem t1 e v1.

Estes registros podem ajudar os administradores de dados a entender como seus objetos protegidos por políticas são acessados.

A coluna policies_referenced fornece benefícios adicionais para a exibição ACCESS_HISTORY:

Identificar os objetos protegidos por políticas que um usuário acessa em uma determinada consulta.
Simplificar o processo de auditoria de políticas.

A consulta da exibição ACCESS_HISTORY elimina a necessidade de junções complexas em outras exibições do Account Usage (por exemplo, POLICY_REFERENCES e QUERY_HISTORY), para obter informações sobre os objetos protegidos e as colunas protegidas que o usuário acessa.

Operações com suporte¶

Para uma descrição completa das operações de leitura e gravação com suporte da exibição ACCESS_HISTORY, consulte a seção de notas de uso em Exibição ACCESS_HISTORY.

Benefícios¶

O histórico de acesso do Snowflake oferece os seguintes benefícios pertencentes às operações de leitura e escrita:

Descoberta de dados

Descubra dados não utilizados para determinar se devem ser arquivados ou excluídos.

Rastreamento de dados confidenciais

Rastreie a movimentação de dados de um local externo de armazenamento em nuvem (por exemplo, bucket do Amazon S3) até a tabela de destino do Snowflake e vice-versa.

Rastreie a movimentação interna de dados de uma tabela do Snowflake para outra.

Após rastrear a movimentação de dados confidenciais, aplique políticas (mascaramento e acesso de linha) para proteger dados, atualize configurações de controle de acesso para regular ainda mais o acesso ao estágio e à tabela e defina tags para garantir que estágios, tabelas e colunas com dados confidenciais possam ser rastreados para atender às exigências de conformidade.

Validação de dados

A precisão e integridade dos relatórios, painéis e produtos de visualização de dados, tais como gráficos, são validados uma vez que os dados podem ser rastreados até sua origem.

Os administradores de dados também podem notificar os usuários antes de remover ou alterar uma determinada tabela ou exibição.

Auditoria de conformidade

Identifique o usuário do Snowflake que realizou uma operação de gravação em uma tabela ou estágio e quando a operação de gravação ocorreu para atender a regulamentações de conformidade, tais como GDPR e CCPA.

Aprimoramento da governança de dados geral

A exibição ACCESS_HISTORY fornece uma imagem unificada de quais dados foram acessados, quando o acesso aos dados ocorreu e como os dados acessados passaram do objeto de origem para o objeto de destino de dados.

A linhagem de colunas oferece estes benefícios adicionais:

Proteção de objetos futuros: Os administradores de dados podem facilmente marcar colunas de fontes sensíveis sem trabalho adicional depois de criar objetos derivados (por exemplo, CTAS). Posteriormente, o administrador de dados pode proteger tabelas contendo colunas sensíveis com uma política de acesso a linhas ou proteger as próprias colunas sensíveis com uma política de mascaramento ou uma política de mascaramento baseada em tags.
Frequência de cópia da coluna sensível: Os responsáveis pela privacidade de dados podem determinar rapidamente a contagem de objetos (por exemplo, 1 tabela, 2 exibições) de uma coluna contendo dados sensíveis. Ao saber quantas vezes uma coluna com dados sensíveis aparece em um objeto de tabela, os responsáveis pela privacidade de dados podem provar como eles satisfazem as normas de conformidade regulamentar (por exemplo, para atender às normas do Regulamento Geral de Proteção de Dados (GDPR) na União Europeia).
Análise da causa raiz: A linhagem de colunas fornece um mecanismo para rastrear os dados até sua fonte, o que pode ajudar a identificar pontos de falha resultantes da má qualidade dos dados e reduzir o número de colunas a serem analisadas durante o processo de solução de problemas.

Consulta da exibição ACCESS_HISTORY¶

As seções a seguir fornecem exemplos de consultas para a exibição ACCESS_HISTORY.

Observe que algumas das consultas de exemplo filtram a coluna query_start_time para aumentar o desempenho da consulta. Outra opção para aumentar o desempenho é consultar em intervalos de tempo mais estreitos.

Exemplos: consultas de leitura¶

As subseções abaixo detalham como consultar a exibição ACCESS_HISTORY para operações de leitura para os seguintes casos de uso:

Obter o histórico de acesso para um usuário específico.
Facilitar auditorias de conformidade para acesso a dados confidenciais nos últimos 30 dias, com base em object_id (por exemplo, uma identificação de tabela), para responder as seguintes perguntas:
- Quem acessou os dados?
- Quando os dados foram acessados?
- Quais colunas foram acessadas?

Apresentação do histórico de acesso do usuário¶

Apresente o histórico de acesso do usuário, ordenado por usuário e hora de início da consulta, a partir do acesso mais recente.

SELECT user_name
       , query_id
       , query_start_time
       , direct_objects_accessed
       , base_objects_accessed
FROM access_history
ORDER BY 1, 3 desc
;

Copy

Facilitação de auditorias de conformidade¶

Os exemplos a seguir ajudam a facilitar auditorias de conformidade:

Adicione o valor object_id para determinar quem acessou uma tabela confidencial nos últimos 30 dias:

SELECT distinct user_name
FROM access_history
     , lateral flatten(base_objects_accessed) f1
WHERE f1.value:"objectId"::int=<fill_in_object_id>
AND f1.value:"objectDomain"::string='Table'
AND query_start_time >= dateadd('day', -30, current_timestamp())
;

Copy

Usando o valor object_id de 32998411400350, determine quando o acesso ocorreu nos últimos 30 dias:

SELECT query_id
       , query_start_time
FROM access_history
     , lateral flatten(base_objects_accessed) f1
WHERE f1.value:"objectId"::int=32998411400350
AND f1.value:"objectDomain"::string='Table'
AND query_start_time >= dateadd('day', -30, current_timestamp())
;

Copy

Usando o valor object_id de 32998411400350, determine quais colunas foram acessadas nos últimos 30 dias:

SELECT distinct f4.value AS column_name
FROM access_history
     , lateral flatten(base_objects_accessed) f1
     , lateral flatten(f1.value) f2
     , lateral flatten(f2.value) f3
     , lateral flatten(f3.value) f4
WHERE f1.value:"objectId"::int=32998411400350
AND f1.value:"objectDomain"::string='Table'
AND f4.key='columnName'
;

Copy

Exemplos: operações de gravação¶

As subseções abaixo detalham como consultar a exibição ACCESS_HISTORY para operações de gravação para os seguintes casos de uso:

Carregar dados de um estágio para uma tabela.
Descarregar dados de uma tabela para um estágio.
Usar o comando PUT para carregar um arquivo local em um estágio.
Usar o comando GET para recuperar arquivos de dados de um estágio para um diretório local.
Rastrear a movimentação de dados em estágios confidenciais.

Carregamento de dados de um estágio para uma tabela¶

Carregue um conjunto de valores de um arquivo de dados de um armazenamento em nuvem externo para colunas de uma tabela de destino.

copy into table1(col1, col2)
from (select t.$1, t.$2 from @mystage1/data1.csv.gz);

Copy

As colunas direct_objects_accessed e base_objects_accessed especificam que um estágio externo nomeado foi acessado:

{
  "objectDomain": STAGE
  "objectName": "mystage1",
  "objectId": 1,
  "stageKind": "External Named"
}

Copy

A coluna objects_modified especifica que foram gravados dados em duas colunas da tabela:

{
  "columns": [
     {
       "columnName": "col1",
       "columnId": 1
     },
     {
       "columnName": "col2",
       "columnId": 2
     }
  ],
  "objectId": 1,
  "objectName": "TEST_DB.TEST_SCHEMA.TABLE1",
  "objectDomain": TABLE
}

Copy

Descarregamento de dados de uma tabela para um estágio¶

Descarregue um conjunto de valores de uma tabela do Snowflake para o armazenamento em nuvem.

copy into @mystage1/data1.csv
from table1;

Copy

As colunas direct_objects_accessed e base_objects_accessed especificam as colunas da tabela que foram acessadas:

{
  "objectDomain": TABLE
  "objectName": "TEST_DB.TEST_SCHEMA.TABLE1",
  "objectId": 123,
  "columns": [
     {
       "columnName": "col1",
       "columnId": 1
     },
     {
       "columnName": "col2",
       "columnId": 2
     }
  ]
}

Copy

A coluna objects_modified especifica o estágio no qual os dados acessados foram gravados:

{
  "objectId": 1,
  "objectName": "mystage1",
  "objectDomain": STAGE,
  "stageKind": "External Named"
}

Copy

Uso do comando PUT para carregar um arquivo local em um estágio¶

Copie um arquivo de dados para um estágio interno (ou seja, Snowflake).

put file:///tmp/data/mydata.csv @my_int_stage;

Copy

As colunas direct_objects_accessed e base_objects_accessed especificam o caminho local para o arquivo que foi acessado:

{
  "location": "file:///tmp/data/mydata.csv"
}

Copy

A coluna objects_modified especifica o estágio em que os dados acessados foram gravados:

{
  "objectId": 1,
  "objectName": "my_int_stage",
  "objectDomain": STAGE,
  "stageKind": "Internal Named"
}

Copy

Uso do comando GET para recuperar arquivos de dados de um estágio para um diretório local¶

Recupere um arquivo de dados de um estágio interno para um diretório na máquina local.

get @%mytable file:///tmp/data/;

Copy

As colunas direct_objects_accessed e base_objects_accessed especificam o estágio e o diretório local que foram acessados:

{
  "objectDomain": Stage
  "objectName": "mytable",
  "objectId": 1,
  "stageKind": "Table"
}

Copy

A coluna objects_modified especifica o diretório no qual os dados acessados foram gravados:

{
  "location": "file:///tmp/data/"
}

Copy

Rastreamento da movimentação de dados em estágios confidenciais¶

Rastreie dados de estágios confidenciais à medida que passam por uma série de consultas executadas em ordem cronológica.

Execute as seguintes consultas. Observe que cinco das instruções acessam dados de estágio. Portanto, quando você consulta a exibição ACCESS_HISTORY para acesso ao estágio, o conjunto de resultados deve incluir cinco linhas.

use test_db.test_schema;
create or replace table T1(content variant);
insert into T1(content) select parse_json('{"name": "A", "id":1}');

-- T1 -> T6
insert into T6 select * from T1;

-- S1 -> T1
copy into T1 from @S1;

-- T1 -> T2
create table T2 as select content:"name" as name, content:"id" as id from T1;

-- T1 -> S2
copy into @S2 from T1;

-- S1 -> T3
create or replace table T3(customer_info variant);
copy into T3 from @S1;

-- T1 -> T4
create or replace table T4(name string, id string, address string);
insert into T4(name, id) select content:"name", content:"id" from T1;

-- T6 -> T7
create table T7 as select * from T6;

Copy

Onde:

T1, T2 … T7 especificam os nomes das tabelas.
S1 e S2 especificam os nomes dos estágios.

Consulte o histórico de acesso para determinar o acesso ao estágio S1.

Os dados para as colunas direct_objects_accessed, base_objects_accessed e objects_modified são mostrados na tabela a seguir.

`direct_objects_accessed`	`base_objects_accessed`	`objects_modified`
[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy	[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy	[ { "columns": [ { "columnId": 68611, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66566, "objectName": "TEST_DB.TEST_SCHEMA.T6" } ] Copy
[ { "objectDomain": "Stage", "objectId": 117, "objectName": "TEST_DB.TEST_SCHEMA.S1", "stageKind": "External Named" } ] Copy	[ { "objectDomain": "Stage", "objectId": 117, "objectName": "TEST_DB.TEST_SCHEMA.S1", "stageKind": "External Named" } ] Copy	[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy
[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy	[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy	[ { "columns": [ { "columnId": 68613, "columnName": "ID" }, { "columnId": 68612, "columnName": "NAME" } ], "objectDomain": "Table", "objectId": 66568, "objectName": "TEST_DB.TEST_SCHEMA.T2" } ] Copy
[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy	[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy	[ { "objectDomain": "Stage", "objectId": 118, "objectName": "TEST_DB.TEST_SCHEMA.S2", "stageKind": "External Named" } ] Copy
[ { "objectDomain": "Stage", "objectId": 117, "objectName": "TEST_DB.TEST_SCHEMA.S1", "stageKind": "External Named" } ] Copy	[ { "objectDomain": "Stage", "objectId": 117, "objectName": "TEST_DB.TEST_SCHEMA.S1", "stageKind": "External Named" } ] Copy	[ { "columns": [ { "columnId": 68614, "columnName": "CUSTOMER_INFO" } ], "objectDomain": "Table", "objectId": 66570, "objectName": "TEST_DB.TEST_SCHEMA.T3" } ] Copy
[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy	[ { "columns": [ { "columnId": 68610, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66564, "objectName": "TEST_DB.TEST_SCHEMA.T1" } ] Copy	[ { "columns": [ { "columnId": 68615, "columnName": "NAME" }, { "columnId": 68616, "columnName": "ID" } ], "objectDomain": "Table", "objectId": 66572, "objectName": "TEST_DB.TEST_SCHEMA.T4" } ] Copy
[ { "columns": [ { "columnId": 68611, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66566, "objectName": "TEST_DB.TEST_SCHEMA.T6" } ] Copy	[ { "columns": [ { "columnId": 68611, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66566, "objectName": "TEST_DB.TEST_SCHEMA.T6" } ] Copy	[ { "columns": [ { "columnId": 68618, "columnName": "CONTENT" } ], "objectDomain": "Table", "objectId": 66574, "objectName": "TEST_DB.TEST_SCHEMA.T7" } ] Copy

Observe o seguinte sobre o exemplo de consulta:

Usa uma expressão de tabela comum recursiva.

Utiliza um constructo JOIN em vez de uma cláusula USING.

with access_history_flatten as (
    select
        r.value:"objectId" as source_id,
        r.value:"objectName" as source_name,
        r.value:"objectDomain" as source_domain,
        w.value:"objectId" as target_id,
        w.value:"objectName" as target_name,
        w.value:"objectDomain" as target_domain,
        c.value:"columnName" as target_column,
        t.query_start_time as query_start_time
    from
        (select * from TEST_DB.ACCOUNT_USAGE.ACCESS_HISTORY) t,
        lateral flatten(input => t.BASE_OBJECTS_ACCESSED) r,
        lateral flatten(input => t.OBJECTS_MODIFIED) w,
        lateral flatten(input => w.value:"columns", outer => true) c
        ),
    sensitive_data_movements(path, target_id, target_name, target_domain, target_column, query_start_time)
    as
      -- Common Table Expression
      (
        -- Anchor Clause: Get the objects that access S1 directly
        select
            f.source_name || '-->' || f.target_name as path,
            f.target_id,
            f.target_name,
            f.target_domain,
            f.target_column,
            f.query_start_time
        from
            access_history_flatten f
        where
        f.source_domain = 'Stage'
        and f.source_name = 'TEST_DB.TEST_SCHEMA.S1'
        and f.query_start_time >= dateadd(day, -30, date_trunc(day, current_date))
        union all
        -- Recursive Clause: Recursively get all the objects that access S1 indirectly
        select sensitive_data_movements.path || '-->' || f.target_name as path, f.target_id, f.target_name, f.target_domain, f.target_column, f.query_start_time
          from
             access_history_flatten f
            join sensitive_data_movements
            on f.source_id = sensitive_data_movements.target_id
                and f.source_domain = sensitive_data_movements.target_domain
                and f.query_start_time >= sensitive_data_movements.query_start_time
      )
select path, target_name, target_id, target_domain, array_agg(distinct target_column) as target_columns
from sensitive_data_movements
group by path, target_id, target_name, target_domain;

Copy

A consulta produz o seguinte conjunto de resultados relacionado ao estágio de movimentação de dados S1:

PATH

TARGET_NAME

TARGET_ID

TARGET_DOMAIN

TARGET_COLUMNS

TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T1

TEST_DB.TEST_SCHEMA.T1

66564

Tabela

[«CONTENT»]

TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T1–>TEST_DB.TEST_SCHEMA.S2

TEST_DB.TEST_SCHEMA.S2

118

Estágio

[]

TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T1–>TEST_DB.TEST_SCHEMA.T2

TEST_DB.TEST_SCHEMA.T2

66568

Tabela

[«NAME»,»ID»]

TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T1–>TEST_DB.TEST_SCHEMA.T4

TEST_DB.TEST_SCHEMA.T4

66572

Tabela

[«ID»,»NAME»]

TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T3

TEST_DB.TEST_SCHEMA.T3

66570

Tabela

[«CUSTOMER_INFO»]

PATH	TARGET_NAME	TARGET_ID	TARGET_DOMAIN	TARGET_COLUMNS
TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T1	TEST_DB.TEST_SCHEMA.T1	66564	Tabela	[«CONTENT»]
TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T1–>TEST_DB.TEST_SCHEMA.S2	TEST_DB.TEST_SCHEMA.S2	118	Estágio	[]
TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T1–>TEST_DB.TEST_SCHEMA.T2	TEST_DB.TEST_SCHEMA.T2	66568	Tabela	[«NAME»,»ID»]
TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T1–>TEST_DB.TEST_SCHEMA.T4	TEST_DB.TEST_SCHEMA.T4	66572	Tabela	[«ID»,»NAME»]
TEST_DB.TEST_SCHEMA.S1–>TEST_DB.TEST_SCHEMA.T3	TEST_DB.TEST_SCHEMA.T3	66570	Tabela	[«CUSTOMER_INFO»]

Exemplo: linhagem de colunas¶

O exemplo seguinte consulta a exibição ACCESS_HISTORY e usa a função FLATTEN para nivelar a coluna objects_modified.

Como um exemplo representativo, execute a seguinte consulta SQL em sua conta Snowflake para produzir a tabela abaixo, onde os comentários numerados indicam o seguinte:

// 1: obtenha o mapeamento entre o campo directSources e a coluna de destino.
// 2: obtenha o mapeamento entre o campo baseSources e a coluna de destino.

// 1

select
  directSources.value: "objectId" as source_object_id,
  directSources.value: "objectName" as source_object_name,
  directSources.value: "columnName" as source_column_name,
  'DIRECT' as source_column_type,
  om.value: "objectName" as target_object_name,
  columns_modified.value: "columnName" as target_column_name
from
  (
    select
      *
    from
      snowflake.account_usage.access_history
  ) t,
  lateral flatten(input => t.OBJECTS_MODIFIED) om,
  lateral flatten(input => om.value: "columns", outer => true) columns_modified,
  lateral flatten(
    input => columns_modified.value: "directSources",
    outer => true
  ) directSources

union

// 2

select
  baseSources.value: "objectId" as source_object_id,
  baseSources.value: "objectName" as source_object_name,
  baseSources.value: "columnName" as source_column_name,
  'BASE' as source_column_type,
  om.value: "objectName" as target_object_name,
  columns_modified.value: "columnName" as target_column_name
from
  (
    select
      *
    from
      snowflake.account_usage.access_history
  ) t,
  lateral flatten(input => t.OBJECTS_MODIFIED) om,
  lateral flatten(input => om.value: "columns", outer => true) columns_modified,
  lateral flatten(
    input => columns_modified.value: "baseSources",
    outer => true
  ) baseSources
;

Copy

Retorna:

SOURCE_OBJECT_ID

SOURCE_OBJECT_NAME

SOURCE_COLUMN_NAME

SOURCE_COLUMN_TYPE

TARGET_OBJECT_NAME

TARGET_COLUMN_NAME

1

D.S.T0

NAME

BASE

D.S.T1

NAME

2

D.S.V1

NAME

DIRECT

D.S.T1

NAME

SOURCE_OBJECT_ID	SOURCE_OBJECT_NAME	SOURCE_COLUMN_NAME	SOURCE_COLUMN_TYPE	TARGET_OBJECT_NAME	TARGET_COLUMN_NAME
1	D.S.T0	NAME	BASE	D.S.T1	NAME
2	D.S.V1	NAME	DIRECT	D.S.T1	NAME

Exemplo: rastreamento das referências da política de acesso a linhas¶

Retornar uma linha para cada instância quando uma política de acesso a linhas for definida em uma tabela, exibição ou exibição materializada sem duplicatas:

use role accountadmin;
select distinct
    obj_policy.value:"policyName"::VARCHAR as policy_name
from snowflake.account_usage.access_history as ah
    , lateral flatten(ah.policies_referenced) as obj
    , lateral flatten(obj.value:"policies") as obj_policy
;

Copy

Exemplo: rastreamento de referências da política de mascaramento¶

Retornar uma linha para cada instância quando uma política de mascaramento proteger uma coluna sem duplicatas. Observe que é necessário um nivelamento adicional porque a coluna policies_referenced especifica a política de mascaramento em uma coluna em um nível mais profundo do que a política de acesso a linhas em uma tabela:

use role accountadmin;
select distinct
    policies.value:"policyName"::VARCHAR as policy_name
from snowflake.account_usage.access_history as ah
    , lateral flatten(ah.policies_referenced) as obj
    , lateral flatten(obj.value:"columns") as columns
    , lateral flatten(columns.value:"policies") as policies
;

Copy

Exemplo: rastreamento da política aplicada em uma consulta¶

Retorne a hora em que a política foi atualizada (POLICY_CHANGED_TIME) e as condições da política (POLICY_BODY) para uma determinada consulta em um determinado período.

Antes de usar esta consulta, atualize os valores de entrada da cláusula WHERE:

where query_start_time > '2023-07-07' and
   query_start_time < '2023-07-08' and
   query_id = '01ad7987-0606-6e2c-0001-dd20f12a9777')

Copy

Onde:

query_start_time > '2023-07-07': Especifica o carimbo de data/hora inicial.
query_start_time < '2023-07-08': Especifica o carimbo de data/hora final.
query_id = '01ad7987-0606-6e2c-0001-dd20f12a9777': Especifica o identificador da consulta na exibição ACCESS_HISTORY do Account Usage.

Executar a consulta:

SELECT *
from(
  select j1.*,j2.QUERY_START_TIME as POLICY_CHANGED_TIME, POLICY_BODY
from
(
  select distinct t1.*,
      t4.value:"policyId"::number as PID
  from (select *
      from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
      where query_start_time > '2023-07-07' and
         query_start_time < '2023-07-08' and
         query_id = '01ad7987-0606-6e2c-0001-dd20f12a9777') as t1, //
  lateral flatten (input => t1.POLICIES_REFERENCED,OUTER => TRUE) t2,
  lateral flatten (input => t2.value:"columns", OUTER => TRUE) t3,
  lateral flatten (input => t3.value:"policies",OUTER => TRUE) t4
) as j1
left join
(
  select OBJECT_MODIFIED_BY_DDL:"objectId"::number as PID,
      QUERY_START_TIME,
      OBJECT_MODIFIED_BY_DDL:"properties"."policyBody"."value" as POLICY_BODY
      from SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
      where OBJECT_MODIFIED_BY_DDL is not null and
      (OBJECT_MODIFIED_BY_DDL:"objectDomain" ilike '%masking%' or OBJECT_MODIFIED_BY_DDL:"objectDomain" ilike '%row%')
) as j2
On j1.POLICIES_REFERENCED is not null and j1.pid = j2.pid and j1.QUERY_START_TIME>j2.QUERY_START_TIME) as j3
QUALIFY ROW_NUMBER() OVER (PARTITION BY query_id,pid ORDER BY policy_changed_time DESC) = 1;

Copy

Exemplos: UDFs¶

Estes exemplos de UDF mostram como a exibição ACCESS_HISTORY do Account Usage faz os registros:

Chamando uma UDF chamada get_product.
Inserindo o produto de chamar a função get_product em uma tabela chamada mydb.tables.t1.
<label-access_history_example_udf_shared> UDFs compartilhadas.

Como chamar uma UDF¶

Considere a seguinte UDF SQL que calcula o produto de dois números e considera que esteja armazenado no esquema chamado mydb.udfs:

CREATE FUNCTION MYDB.UDFS.GET_PRODUCT(num1 number, num2 number)
RETURNS number
AS
$$
    NUM1 * NUM2
$$
;

Copy

A chamada get_product resulta diretamente no registro dos detalhes da UDF na coluna direct_objects_accessed:

[
  {
    "objectDomain": "FUNCTION",
    "objectName": "MYDB.UDFS.GET_PRODUCT",
    "objectId": "2",
    "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)",
    "dataType": "NUMBER(38,0)"
  }
]

Copy

Este exemplo é análogo a chamar um procedimento armazenado (neste tópico).

UDF com INSERT DML¶

Considere a seguinte instrução INSERT para atualizar as colunas chamadas 1 e 2 na tabela chamada mydb.tables.t1:

insert into t1(product)
select get_product(c1, c2) from mydb.tables.t1;

Copy

A exibição ACCESS_HISTORY registra a função get_product na:

coluna direct_objects_accessed porque a função é explicitamente nomeada na instrução SQL, e
na coluna objects_modified na matriz directSources porque a função é a fonte dos valores que são inseridos nas colunas.

Da mesma forma, a tabela t1 é registrada nestas mesmas colunas:

`direct_objects_accessed`	`objects_modified`
[ { "objectDomain": "FUNCTION", "objectName": "MYDB.UDFS.GET_PRODUCT", "objectId": "2", "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)", "dataType": "NUMBER(38,0)" }, { "objectDomain": "TABLE", "objectName": "MYDB.TABLES.T1", "objectId": 1, "columns": [ { "columnName": "c1", "columnId": 1 }, { "columnName": "c2", "columnId": 2 } ] } ] Copy	[ { "objectDomain": "TABLE", "objectName": "MYDB.TABLES.T1", "objectId": 2, "columns": [ { "columnId": "product", "columnName": "201", "directSourceColumns": [ { "objectDomain": "Table", "objectName": "MYDB.TABLES.T1", "objectId": "1", "columnName": "c1" }, { "objectDomain": "Table", "objectName": "MYDB.TABLES.T1", "objectId": "1", "columnName": "c2" }, { "objectDomain": "FUNCTION", "objectName": "MYDB.UDFS.GET_PRODUCT", "objectId": "2", "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)", "dataType": "NUMBER(38,0)" } ], "baseSourceColumns":[] } ] } ] Copy

direct_objects_accessed

objects_modified

[
  {
    "objectDomain": "FUNCTION",
    "objectName": "MYDB.UDFS.GET_PRODUCT",
    "objectId": "2",
    "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)",
    "dataType": "NUMBER(38,0)"
  },
  {
    "objectDomain": "TABLE",
    "objectName": "MYDB.TABLES.T1",
    "objectId": 1,
    "columns":
    [
      {
        "columnName": "c1",
        "columnId": 1
      },
      {
        "columnName": "c2",
        "columnId": 2
      }
    ]
  }
]

Copy

 [
   {
     "objectDomain": "TABLE",
     "objectName": "MYDB.TABLES.T1",
     "objectId": 2,
     "columns":
     [
       {
         "columnId": "product",
         "columnName": "201",
         "directSourceColumns":
         [
           {
             "objectDomain": "Table",
             "objectName": "MYDB.TABLES.T1",
             "objectId": "1",
             "columnName": "c1"
           },
           {
             "objectDomain": "Table",
             "objectName": "MYDB.TABLES.T1",
             "objectId": "1",
             "columnName": "c2"
           },
           {
             "objectDomain": "FUNCTION",
             "objectName": "MYDB.UDFS.GET_PRODUCT",
             "objectId": "2",
             "argumentSignature": "(NUM1 NUMBER, NUM2 NUMBER)",
             "dataType": "NUMBER(38,0)"
           }
         ],
         "baseSourceColumns":[]
       }
     ]
   }
]

Copy

UDFs compartilhadas¶

UDFs compartilhadas podem ser referenciadas direta ou indiretamente:

Uma referência direta é a mesma que chamar uma UDF explicitamente (neste tópico), mas resulta no registro da UDF nas colunas base_objects_accessed e direct_objects_accessed.
Um exemplo de referência indireta é chamar a UDF para criar uma exibição:
create view v as select get_product(c1, c2) as vc from t;
Copy
A coluna base_objects_accessed registra a UDF e a tabela.

A coluna direct_objects_accessed registra a exibição.

Exemplos: rastreamento de objetos modificados por uma operação DDL¶

Criação de uma tag com ALLOWED_VALUES¶

Crie a tag:

create tag governance.tags.pii allowed_values 'sensitive','public';

Copy

Valor da coluna:

{
  "objectDomain": "TAG",
  "objectName": "governance.tags.pii",
  "objectId": "1",
  "operationType": "CREATE",
  "properties": {
    "allowedValues": {
      "sensitive": {
        "subOperationType": "ADD"
      },
      "public": {
        "subOperationType": "ADD"
      }
    }
  }
}

Copy

Nota

Se você não especificar valores permitidos ao criar a tag, o campo properties será uma matriz vazia (ou seja, {}).

Criação de uma tabela com uma tag e política de mascaramento¶

Crie a tabela com uma política de mascaramento na coluna, uma tag na coluna e uma tag na tabela:

create or replace table hr.data.user_info(
  email string
    with masking policy governance.policies.email_mask
    with tag (governance.tags.pii = 'sensitive')
  )
with tag (governance.tags.pii = 'sensitive');

Copy

Valor da coluna:

{
  "objectDomain": "TABLE",
  "objectName": "hr.data.user_info",
  "objectId": "1",
  "operationType": "CREATE",
  "properties": {
    "tags": {
      "governance.tags.pii": {
        "subOperationType": "ADD",
        "objectId": {
          "value": "1"
        },
        "tagValue": {
          "value": "sensitive"
        }
      }
    },
    "columns": {
      "email": {
        objectId: {
          "value": 1
        },
        "subOperationType": "ADD",
        "tags": {
          "governance.tags.pii": {
            "subOperationType": "ADD",
            "objectId": {
              "value": "1"
            },
            "tagValue": {
              "value": "sensitive"
            }
          }
        },
        "maskingPolicies": {
          "governance.policies.email_mask": {
            "subOperationType": "ADD",
            "objectId": {
              "value": 2
            }
          }
        }
      }
    }
  }
}

Copy

Definição de uma política de mascaramento em uma tag¶

Defina uma política de mascaramento na tag (ou seja, mascaramento baseado em tags):

alter tag governance.tags.pii set masking policy governance.policies.email_mask;

Copy

Valor da coluna:

{
  "objectDomain": "TAG",
  "objectName": "governance.tags.pii",
  "objectId": "1",
  "operationType": "ALTER",
  "properties": {
    "maskingPolicies": {
      "governance.policies.email_mask": {
        "subOperationType": "ADD",
        "objectId": {
          "value": 2
        }
      }
    }
  }
}

Copy

Troca de uma tabela¶

Troque a tabela chamada t2 pela tabela chamada t3:

alter table governance.tables.t2 swap with governance.tables.t3;

Copy

Observe os dois registros diferentes na exibição.

Registro 1:

{
  "objectDomain": "Table",
  "objectId": 0,
  "objectName": "GOVERNANCE.TABLES.T2",
  "operationType": "ALTER",
  "properties": {
    "swapTargetDomain": {
      "value": "Table"
    },
    "swapTargetId": {
      "value": 0
    },
    "swapTargetName": {
      "value": "GOVERNANCE.TABLES.T3"
    }
  }
}

Copy

Registro 2:

{
  "objectDomain": "Table",
  "objectId": 0,
  "objectName": "GOVERNANCE.TABLES.T3",
  "operationType": "ALTER",
  "properties": {
    "swapTargetDomain": {
      "value": "Table"
    },
    "swapTargetId": {
      "value": 0
    },
    "swapTargetName": {
      "value": "GOVERNANCE.TABLES.T2"
    }
  }
}

Copy

Descarte de uma política de mascaramento¶

Descarte uma política de mascaramento

drop masking policy governance.policies.email_mask;

Copy

Valor da coluna:

{
  "objectDomain" : "MASKING_POLICY",
  "objectName": "governance.policies.email_mask",
  "objectId" : "1",
  "operationType": "DROP",
  "properties" : {}
}
Copy
Nota

O valor da coluna é representativo e se aplica a uma operação DROP em uma tag e política de acesso a linhas.

O campo properties é uma matriz vazia e não fornece nenhuma informação sobre a política antes da operação DROP.

Rastreamento de referências de tags em uma coluna¶

Consulte a coluna object_modified_by_ddl para monitorar como uma tag é definida em uma coluna.

Como o administrador da tabela, defina uma tag em uma coluna, desmarque a tag e atualize a tag com um valor de cadeia de caracteres diferente:

alter table hr.tables.empl_info
  alter column email set tag governance.tags.test_tag = 'test';

alter table hr.tables.empl_info
  alter column email unset tag governance.tags.test_tag;

alter table hr.tables.empl_info
  alter column email set tag governance.tags.data_category = 'sensitive';

Copy

Como engenheiro de dados, altere o valor da tag:

alter table hr.tables.empl_info
  alter column email set tag governance.tags.data_category = 'public';

Copy

Consulte a exibição ACCESS_HISTORY para monitorar as mudanças:

select
  query_start_time,
  user_name,
  object_modified_by_ddl:"objectName"::string as table_name,
  'EMAIL' as column_name,
  tag_history.value:"subOperationType"::string as operation,
  tag_history.key as tag_name,
  nvl((tag_history.value:"tagValue"."value")::string, '') as value
from
  TEST_DB.ACCOUNT_USAGE.access_history ah,
  lateral flatten(input => ah.OBJECT_MODIFIED_BY_DDL:"properties"."columns"."EMAIL"."tags") tag_history
where true
  and object_modified_by_ddl:"objectDomain" = 'Table'
  and object_modified_by_ddl:"objectName" = 'TEST_DB.TEST_SH.T'
order by query_start_time asc;

Copy

Retorna:

+-----------------------------------+---------------+---------------------+-------------+-----------+-------------------------------+-----------+
| QUERY_START_TIME                  | USER_NAME     | TABLE_NAME          | COLUMN_NAME | OPERATION | TAG_NAME                      | VALUE     |
+-----------------------------------+---------------+---------------------+-------------+-----------+-------------------------------+-----------+
| Mon, Feb. 14, 2023 12:01:01 -0600 | TABLE_ADMIN   | HR.TABLES.EMPL_INFO | EMAIL       | ADD       | GOVERNANCE.TAGS.TEST_TAG      | test      |
| Mon, Feb. 14, 2023 12:02:01 -0600 | TABLE_ADMIN   | HR.TABLES.EMPL_INFO | EMAIL       | DROP      | GOVERNANCE.TAGS.TEST_TAG      |           |
| Mon, Feb. 14, 2023 12:03:01 -0600 | TABLE_ADMIN   | HR.TABLES.EMPL_INFO | EMAIL       | ADD       | GOVERNANCE.TAGS.DATA_CATEGORY | sensitive |
| Mon, Feb. 14, 2023 12:04:01 -0600 | DATA_ENGINEER | HR.TABLES.EMPL_INFO | EMAIL       | ADD       | GOVERNANCE.TAGS.DATA_CATEGORY | public    |
+-----------------------------------+---------------+---------------------+-------------+-----------+-------------------------------+-----------+

Exemplo: como chamar um procedimento armazenado¶

Considere o seguinte procedimento armazenado e considere que está armazenado no esquema chamado mydb.procedures:

create or replace procedure get_id_value(name string)
returns string not null
language javascript
as
$$
  var my_sql_command = "select id from A where name = '" + NAME + "'";
  var statement = snowflake.createStatement( {sqlText: my_sql_command} );
  var result = statement.execute();
  result.next();
  return result.getColumnValue(1);
$$
;

Copy

Chamar my_procedure resulta diretamente no registro dos detalhes do procedimento nas colunas direct_objects_accessed e base_objects_accessed como se segue:

[
  {
    "objectDomain": "PROCEDURE",
    "objectName": "MYDB.PROCEDURES.GET_ID_VALUE",
    "argumentSignature": "(NAME STRING)",
    "dataType": "STRING"
  }
]

Copy

Este exemplo é análogo a chamar uma UDF (neste tópico).

Exemplo: consultas antigas com procedimentos armazenados¶

Você pode usar as colunas parent_query_id e root_query_id para entender como as chamadas de procedimento armazenado se relacionam entre si.

Suponha que você tenha três instruções de procedimento armazenado diferentes e as execute na seguinte ordem:

CREATE OR REPLACE PROCEDURE myproc_child()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  BEGIN
  SELECT * FROM mydb.mysch.mytable;
  RETURN 1;
  END
$$;

CREATE OR REPLACE PROCEDURE myproc_parent()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  BEGIN
  CALL myproc_child();
  RETURN 1;
  END
$$;

CALL myproc_parent();

Copy

Uma consulta exibição ACCESS_HISTORY registra as informações da seguinte forma:

SELECT
  query_id,
  parent_query_id,
  root_query_id,
  direct_objects_accessed
FROM
  SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY;

Copy

+----------+-----------------+---------------+-----------------------------------+
| QUERY_ID | PARENT_QUERY_ID | ROOT_QUERY_ID | DIRECT_OBJECTS_ACCESSED           |
+----------+-----------------+---------------+-----------------------------------+
|  1       | NULL            | NULL          | [{"objectName": "myproc_parent"}] |
|  2       | 1               | 1             | [{"objectName": "myproc_child"}]  |
|  3       | 2               | 1             | [{"objectName": "mytable"}]       |
+----------+-----------------+---------------+-----------------------------------+

A primeira linha corresponde à chamada do segundo procedimento denominado myproc_parent conforme mostrado na coluna direct_objects_accessed.

As colunas parent_query_id e root_query_id retornam NULL porque você chamou esse procedimento armazenado diretamente.
A segunda linha corresponde à consulta que chama o primeiro procedimento denominado myproc_child conforme mostrado em direct_objects_accessed column.

As colunas parent_query_id e root_query_id retornam o mesmo ID de consulta porque a consulta que chama myproc_child foi iniciada pela consulta que chama myproc_parent, que você chamou diretamente.
A terceira linha corresponde à consulta que acessou a tabela denominada mytable no procedimento myproc_child conforme mostrado na coluna direct_objects_accessed.

A coluna parent_query_id retorna o ID de consulta da consulta que acessou mytable, que corresponde à chamada de myproc_child. Esse procedimento armazenado foi iniciado pela consulta que chama myproc_parent, mostrada em root_query_id column.