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
;
As colunas C1 e C2 são colunas de origem que a exibição acessa diretamente, que são registradas na coluna
base_objects_accessedda exibição ACCESS_HISTORY.A coluna C3 é usada para filtrar as linhas que a exibição inclui, o que é registrado na coluna
base_objects_accessedda 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 colunadirect_objects_accessedda 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;
Duas tabelas diferentes são necessárias para criar a exibição:
bt(tabela base) ejt(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_accessedda 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 colunadirect_objects_accessedda 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
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;
Neste exemplo, o Snowflake retorna:
view_2na colunadirect_objects_accessed, porque a consulta especificaview_2.base_tablena colunabase_objects_accessedporque essa é a fonte original dos dados emview_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_tableCriação de uma tabela a partir da fonte de dados (ou seja, CTAS):
create table table_1 as select * from base_table;
Neste exemplo, o Snowflake retorna:
base_tablenas colunasbase_objects_accessededirect_objects_accessed, porque a tabela foi acessada diretamente e é a fonte dos dados.table_1na colunaobjects_modifiedcom 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)
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 dev1.
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 ;
Facilitação de auditorias de conformidade¶
Os exemplos a seguir ajudam a facilitar auditorias de conformidade:
Adicione o valor
object_idpara 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()) ;
Usando o valor
object_idde32998411400350, 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()) ;
Usando o valor
object_idde32998411400350, 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' ;
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);
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" }
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 }
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;
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 } ] }
A coluna objects_modified especifica o estágio no qual os dados acessados foram gravados:
{ "objectId": 1, "objectName": "mystage1", "objectDomain": STAGE, "stageKind": "External Named" }
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;
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" }
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" }
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/;
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" }
A coluna objects_modified especifica o diretório no qual os dados acessados foram gravados:
{ "location": "file:///tmp/data/" }
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;Onde:
T1,T2…T7especificam os nomes das tabelas.
S1eS2especificam 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_accessedeobjects_modifiedsão mostrados na tabela a seguir.
direct_objects_accessed
base_objects_accessed
objects_modifiedObserve 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;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»]
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 campodirectSourcese a coluna de destino.// 2: obtenha o mapeamento entre o campobaseSourcese 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
;
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
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 ;
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 ;
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')
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;
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_productem uma tabela chamadamydb.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 $$ ;
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)" } ]
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;
A exibição ACCESS_HISTORY registra a função get_product na:
coluna
direct_objects_accessedporque a função é explicitamente nomeada na instrução SQL, ena coluna
objects_modifiedna matrizdirectSourcesporque 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
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';
Valor da coluna:
{ "objectDomain": "TAG", "objectName": "governance.tags.pii", "objectId": "1", "operationType": "CREATE", "properties": { "allowedValues": { "sensitive": { "subOperationType": "ADD" }, "public": { "subOperationType": "ADD" } } } }
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');
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 } } } } } } }
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;
Valor da coluna:
{ "objectDomain": "TAG", "objectName": "governance.tags.pii", "objectId": "1", "operationType": "ALTER", "properties": { "maskingPolicies": { "governance.policies.email_mask": { "subOperationType": "ADD", "objectId": { "value": 2 } } } } }
Troca de uma tabela¶
Troque a tabela chamada t2 pela tabela chamada t3:
alter table governance.tables.t2 swap with governance.tables.t3;
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" } } }
Registro 2:
{ "objectDomain": "Table", "objectId": 0, "objectName": "GOVERNANCE.TABLES.T3", "operationType": "ALTER", "properties": { "swapTargetDomain": { "value": "Table" }, "swapTargetId": { "value": 0 }, "swapTargetName": { "value": "GOVERNANCE.TABLES.T2" } } }
Descarte de uma política de mascaramento¶
Descarte uma política de mascaramento
drop masking policy governance.policies.email_mask;
Valor da coluna:
{ "objectDomain" : "MASKING_POLICY", "objectName": "governance.policies.email_mask", "objectId" : "1", "operationType": "DROP", "properties" : {} }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';
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';
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;
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); $$ ;
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" } ]
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();
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;+----------+-----------------+---------------+-----------------------------------+ | 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_parentconforme mostrado na colunadirect_objects_accessed.As colunas
parent_query_ideroot_query_idretornam NULL porque você chamou esse procedimento armazenado diretamente.A segunda linha corresponde à consulta que chama o primeiro procedimento denominado
myproc_childconforme mostrado emdirect_objects_accessed column.As colunas
parent_query_ideroot_query_idretornam o mesmo ID de consulta porque a consulta que chamamyproc_childfoi iniciada pela consulta que chamamyproc_parent, que você chamou diretamente.A terceira linha corresponde à consulta que acessou a tabela denominada
mytableno procedimentomyproc_childconforme mostrado na colunadirect_objects_accessed.A coluna
parent_query_idretorna o ID de consulta da consulta que acessoumytable, que corresponde à chamada demyproc_child. Esse procedimento armazenado foi iniciado pela consulta que chamamyproc_parent, mostrada na colunaroot_query_id.