Historique des accès

Cette rubrique fournit des concepts sur l’historique des accès utilisateur dans Snowflake.

Dans ce chapitre :

Vue d’ensemble

L’historique d’accès dans Snowflake fait référence au moment où la requête d’un utilisateur lit les données et où l’instruction SQL effectue une opération d’écriture de données, telle que INSERT, UPDATE et DELETE ainsi que des variantes de la commande COPY, de l’objet de données source à l’objet de données cible. L’historique des accès utilisateur est accessible en interrogeant la vue Account Usage ACCESS_HISTORY. Les enregistrements de cette vue facilitent l’audit de conformité réglementaire et fournissent des informations sur les tables et les colonnes les plus fréquemment utilisées, car il existe un lien direct entre l’utilisateur (c’est-à-dire l’opérateur de la requête), la requête, la table ou la vue, la colonne et les données.

Chaque ligne de la vue ACCESS_HISTORY contient un seul enregistrement par instruction SQL. L’enregistrement contient les types d’informations suivants :

  • L’enregistrement décrit les colonnes sources auxquelles la requête a accédé directement et indirectement, comme des tables sous-jacentes d’où proviennent les données de la requête.

  • Les colonnes projetées que l’utilisateur voit dans le résultat de la requête, comme des colonnes spécifiées dans une instruction SELECT.

  • Les colonnes qui sont utilisées pour déterminer le résultat de la requête, mais qui ne sont pas projetées, comme des colonnes d’une clause WHERE pour filtrer le résultat.

Par exemple :

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

  • Les colonnes C1 et C2 sont des colonnes sources auxquelles la vue accède directement et qui sont enregistrées dans la colonne base_objects_accessed de la vue ACCESS_HISTORY.

  • La colonne C3 est utilisée pour filtrer les lignes que la vue inclut, ce qui est enregistré dans la colonne base_objects_accessed de la vue ACCESS_HISTORY.

  • Les colonnes VC1 et VC2 sont des colonnes projetées que l’utilisateur voit lors de l’interrogation de la vue, SELECT * FROM v1;, qui sont enregistrées dans la colonne direct_objects_accessed de la vue ACCESS_HISTORY.

Le même comportement s’applique à une colonne clé dans une clause WHERE. Par exemple :

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

  • Deux tables différentes sont nécessaires pour créer la vue : bt (table de base) et jt (table de jointure).

  • Les colonnes C1, C2 et C3 de la table de base et la colonne C1 de la table de jointure sont toutes enregistrées dans la colonne base_objects_accessed de la vue ACCESS_HISTORY.

  • Les colonnes VC1, VC2 et C1 sont des colonnes projetées que l’utilisateur voit lors de l’interrogation de la vue, SELECT * FROM join_v;, qui sont enregistrées dans la colonne direct_objects_accessed de la vue ACCESS_HISTORY.

Note

Les enregistrements de la vue QUERY_HISTORY Account Usage ne sont pas toujours enregistrés dans la vue ACCESS_HISTORY. La structure de l’instruction SQL détermine si Snowflake enregistre une entrée dans la vue ACCESS_HISTORY.

Pour plus de détails sur les opérations de lecture et d’écriture prises en charge par Snowflake dans la vue ACCESS_HISTORY, reportez-vous à : Notes sur l’utilisation de la vue.

Suivi des opérations de lecture et d’écriture

La vue ACCESS_HISTORY contient les colonnes suivantes :

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

Les opérations de lecture sont suivies à travers les cinq premières colonnes, tandis que la dernière colonne, objects_modified, spécifie les informations d’écriture de données qui ont impliqué les colonnes, les tables et les zones de préparation de Snowflake.

La requête dans Snowflake et la façon dont les objets de la base de données ont été créés déterminent les informations que Snowflake renvoie dans les colonnes direct_objects_accessed, base_objects_accessed et objects_modified.

De même, si la requête fait référence à un objet protégé par une politique d’accès aux lignes ou à une colonne protégée par une politique de masquage, Snowflake enregistre les informations relatives à la politique dans la colonne policies_referenced.

La colonne object_modified_by_ddl enregistre l’opération DDL sur une base de données, un schéma, une table, une vue et une colonne. Ces opérations comprennent également des instructions qui spécifient une politique d’accès aux lignes sur une table ou une vue, une politique de masquage sur une colonne et des mises à jour de balises (par exemple, définition d’une balise, modification d’une valeur de balise) sur l’objet ou la colonne.

Les colonnes parent_query_id et root_query_id enregistrent les IDs de requêtes qui correspondent à :

  • Une requête qui effectue une opération de lecture ou d’écriture sur un autre objet.

  • Une requête qui effectue une opération de lecture ou d’écriture sur un objet qui appelle une procédure stockée, incluant des appels de procédures stockées imbriquées. Pour plus de détails, voir requêtes ancêtres (dans cette rubrique).

Pour plus de détails sur les colonnes, voir la section Colonnes de la vue ACCESS_HISTORY.

Lire

Regardez le scénario suivant pour comprendre une requête de lecture et comment la vue ACCESS_HISTORY enregistre ces informations :

  • Une série d’objets : base_table » view_1 » view_2 » view_3.

  • Une requête de lecture sur view_2, telle que :

    select * from view_2;
    Copy

Dans cet exemple, Snowflake renvoie :

  • view_2 dans la colonne direct_objects_accessed, car la requête spécifie view_2.

  • base_table dans la colonne base_objects_accessed, car c’est la source d’origine des données dans view_2.

Dans cet exemple, view_1 et view_3 ne sont pas incluses dans les colonnes direct_objects_accessed et base_objects_accessed, car aucune de ces vues n’a été incluse dans la requête et elles ne sont pas l’objet de base qui sert de source aux données dans view_2.

Écriture

Considérez le scénario suivant pour comprendre une opération d’écriture et comment la vue ACCESS_HISTORY enregistre ces informations :

  • Une source de données : base_table

  • Créez une table à partir de la source de données (c’est-à-dire CTAS) :

    create table table_1 as select * from base_table;
    Copy

Dans cet exemple, Snowflake renvoie :

  • base_table dans les colonnes base_objects_accessed et direct_objects_accessed car il y a eu un accès direct à la table et celle-ci est la source des données.

  • table_1 dans la colonne objects_modified avec les colonnes qui ont été écrites lors de la création de la table.

Lignée de colonnes

La lignée de colonnes (c’est-à-dire l’historique des accès aux colonnes) étend la vue ACCESS_HISTORY de Account Usage pour spécifier comment les données circulent de la colonne source à la colonne cible dans une opération d’écriture. Snowflake suit les données des colonnes sources à travers tous les objets de table suivants qui font référence aux données des colonnes sources (par exemple, INSERT, MERGE, CTAS), à condition que les objets de la chaîne de lignée ne soient pas supprimés. Snowflake rend accessible la lignée de colonnes en mettant en valeur la colonne objects_modified dans la vue ACCESS_HISTORY.

Pour plus de détails, voir :

Masquage et références à la politique d’accès aux lignes

La colonne POLICY_REFERENCED indique l’objet pour lequel une politique d’accès aux lignes est définie pour une table ou une politique de masquage pour une colonne, y compris les objets intermédiaires qui sont protégés par une politique d’accès aux lignes ou une politique de masquage. Snowflake enregistre la politique qui est appliquée sur la table ou la colonne.

Considérez les objets suivants :

t1 » v1 » v2

Où :

  • t1 est une table de base.

  • v1 est une vue conçue à partir de la table de base.

  • v2 est une vue conçue à partir de v1.

Si l’utilisateur interroge v2, la colonne policies_referenced enregistre soit la politique d’accès aux lignes qui protège v2, soit chaque politique de masquage qui protège les colonnes de v2, soit les deux types de politique, selon le cas. En outre, cette colonne enregistre toute politique de masquage ou d’accès aux lignes qui protège t1 et v1.

Ces enregistrements peuvent aider les gouverneurs de données à comprendre comment on accède à leurs objets protégés par des politiques.

La colonne policies_referenced offre des avantages supplémentaires à la vue ACCESS_HISTORY :

  • Identifier les objets protégés par une politique auxquels un utilisateur accède dans une requête donnée.

  • Simplifier le processus d’audit des politiques.

    L’interrogation de la vue ACCESS_HISTORY élimine la nécessité d’effectuer des jointures complexes sur d’autres vues Account Usage (par exemple POLICY_REFERENCES et QUERY_HISTORY), pour obtenir des informations sur les objets et les colonnes protégés auxquels un utilisateur accède.

Opérations prises en charge

Pour une description complète des opérations de lecture et d’écriture prises en charge par la vue ACCESS_HISTORY, voir les sections Notes sur l’utilisation de Vue ACCESS_HISTORY.

Avantages

L’historique des accès dans Snowflake offre les avantages suivants en matière d’opérations de lecture et d’écriture :

Découverte de données:

Découvrez des données inutilisées pour déterminer s’il faut archiver ou supprimer les données.

Suivre les mouvements des données sensibles:

Suivez le mouvement des données d’un emplacement de stockage externe dans le Cloud (compartiment Amazon S3, par exemple) vers la table Snowflake cible, et vice versa.

Suivez le mouvement interne des données d’une table Snowflake vers une autre table Snowflake.

Après avoir retracé le mouvement des données sensibles, appliquez des politiques (masquage et accès aux lignes) pour protéger les données, mettez à jour les paramètres de contrôle d’accès pour réglementer davantage l’accès à la zone de préparation et à la table, et définissez des balises pour garantir que les zones de préparation, les tables et les colonnes contenant des données sensibles peuvent faire l’objet d’un suivi pour les exigences de conformité.

Validation des données:

L’exactitude et l’intégrité des rapports, des tableaux de bord et des produits de visualisation des données tels que les diagrammes et les graphiques sont validées puisque les données peuvent être retracées jusqu’à leur source d’origine.

Les gestionnaires de données peuvent également informer les utilisateurs avant de supprimer ou de modifier une table ou une vue donnée.

Audit de conformité:

Identifiez l’utilisateur Snowflake qui a effectué une opération d’écriture sur une table ou une zone de préparation et le moment où l’opération d’écriture s’est produite pour répondre aux règles de conformité, telles que GDPR et CCPA.

Améliorer la gouvernance globale des données:

La vue ACCESS_HISTORY fournit une image unifiée des données auxquelles on a accédé. Elle indique également le moment où l’accès aux données a eu lieu et la manière dont les données auxquelles on a accédé sont passées de l’objet source de données à l’objet cible de données.

La lignée de colonnes offre ces avantages supplémentaires :

Protection des objets dérivés:

Les responsables de la gestion des données peuvent facilement baliser des colonnes sources sensibles sans avoir à effectuer de travail supplémentaire après la création d’objets dérivés (par exemple, des CTAS). Par la suite, le responsable de la gestion des données peut protéger les tables contenant des colonnes sensibles à l’aide d’une politique d’accès aux lignes ou protéger les colonnes sensibles elles-mêmes à l’aide d’une politique de masquage ou d’une politique de masquage basée sur des balises.

Fréquence de copie des colonnes sensibles:

Les responsables de la confidentialité des données peuvent rapidement déterminer le nombre d’objets (par exemple, 1 table, 2 vues) d’une colonne contenant des données sensibles. En sachant combien de fois une colonne contenant des données sensibles apparaît dans un objet de table, les responsables de la confidentialité des données peuvent prouver comment ils satisfont aux normes de conformité réglementaire (par exemple, pour répondre aux normes du Règlement général sur la protection des données (GDPR) dans l’Union européenne).

Analyse des causes profondes:

La lignée de colonnes fournit un mécanisme permettant de retracer les données jusqu’à leur source, ce qui peut aider à localiser les points de défaillance résultant d’une mauvaise qualité des données et réduire le nombre de colonnes à analyser pendant le processus de dépannage.

Interrogation de la vue ACCESS_HISTORY Vue

Les sections suivantes fournissent des exemples de requêtes pour la vue ACCESS_HISTORY.

Notez que certains des exemples de requêtes appliquent un filtre sur la colonne query_start_time afin d’augmenter les performances de la requête. Une autre option pour améliorer les performances consiste à effectuer des requêtes sur des périodes plus restreintes.

Exemples : requêtes de lecture

Les sous-sections ci-dessous détaillent comment interroger la vue ACCESS_HISTORY pour les opérations de lecture dans les cas d’utilisation suivants :

  • Obtenez l’historique des accès pour un utilisateur spécifique.

  • Facilitez les audits de conformité pour l’accès aux données sensibles au cours des 30 derniers jours, en fonction de object_id (par exemple, un identifiant de table), pour répondre aux questions suivantes :

    • Qui a accédé aux données ?

    • Quand a-t-on accédé à ces données ?

    • Quelles sont les colonnes qui ont fait l’objet d’un accès ?

Revenir à l’historique des accès utilisateur

Renvoie l’historique des accès utilisateur, classé par utilisateur et par heure de début de la requête, en commençant par l’accès le plus récent.

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

Faciliter les audits de conformité

Les exemples suivants permettent de faciliter les audits de conformité :

  • Ajoutez la valeur object_id pour déterminer qui a accédé à une table sensible au cours des 30 derniers jours :

    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

  • En utilisant la valeur object_id de 32998411400350, déterminez quand l’accès a eu lieu au cours des 30 derniers jours :

    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

  • En utilisant la valeur object_id de 32998411400350, déterminez quelles colonnes ont été consultées au cours des 30 derniers jours :

    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

Exemples : opérations d’écriture

Les sous-sections ci-dessous expliquent en détail comment interroger la vue ACCESS_HISTORY pour les opérations d’écriture dans les cas d’utilisation suivants :

  • Copier des données d’une zone de préparation vers une table.

  • Décharger les données d’une table vers une zone de préparation.

  • Utiliser la commande PUT pour charger un fichier local vers une zone de préparation.

  • Utiliser la commande GET pour récupérer des fichiers de données d’une zone de préparation dans un répertoire local.

  • Suivre le mouvement des données sensibles de la zone de préparation.

Copier des données d’une zone de préparation vers une table

Chargez un ensemble de valeurs d’un fichier de données dans un stockage Cloud externe dans les colonnes d’une table cible.

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

Les colonnes direct_objects_accessed et base_objects_accessed indiquent qu’un accès à une zone de préparation externe nommée a eu lieu :

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

La colonne objects_modified indique que les données ont été écrites dans deux colonnes de la table :

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

Décharger les données d’une table vers une zone de préparation

Déchargez un ensemble de valeurs d’une table Snowflake dans un stockage dans le Cloud.

copy into @mystage1/data1.csv
from table1;
Copy

Les colonnes direct_objects_accessed et base_objects_accessed spécifient les colonnes de la table auxquelles on a accédé :

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

La colonne objects_modified spécifie la zone de préparation dans laquelle les données auxquelles on a accédé ont été écrites :

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

Utiliser la commande PUT pour charger un fichier local vers une zone de préparation

Copiez un fichier de données vers une zone de préparation interne (c’est-à-dire Snowflake).

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

Les colonnes direct_objects_accessed et base_objects_accessed spécifient le chemin d’accès local au fichier auquel on a accédé :

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

La colonne objects_modified spécifie la zone de préparation dans laquelle les données auxquelles on a accédé ont été écrites :

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

Utiliser la commande GET pour récupérer des fichiers de données d’une zone de préparation vers un répertoire local

Récupérez un fichier de données d’une zone de préparation interne vers un répertoire de la machine locale.

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

Les colonnes direct_objects_accessed et base_objects_accessed spécifient la zone de préparation et le répertoire local auxquels on a accédé :

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

La colonne objects_modified spécifie le répertoire dans lequel les données auxquelles on a accédé ont été écrites :

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

Suivi du mouvement des données sensibles de la zone de préparation

Suivez les données sensibles de la zone de préparation au fur et à mesure qu’elles passent par une série de requêtes exécutées dans l’ordre chronologique.

Exécutez les requêtes suivantes. Notez que cinq des instructions accèdent à des données de la zone de préparation. Par conséquent, lorsque vous interrogez la vue ACCESS_HISTORY pour l’accès à la zone de préparation, le jeu de résultats doit comprendre cinq lignes.

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

Où :

  • T1, T2 … T7 spécifient les noms des tables.

  • S1 et S2 spécifient les noms des zones de préparation.

Interrogez l’historique d’accès pour déterminer l’accès à la zone de préparation S1.

Les données des colonnes direct_objects_accessed, base_objects_accessed et objects_modified sont présentées dans le tableau suivant.

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

Notez ce qui suit à propos de l’exemple de requête :

  • Utilise une expression de table commune récursive.

  • Utilise une construction JOIN plutôt qu’une clause 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

La requête produit le jeu de résultats suivant relatif au mouvement de données de la zone de préparation 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

Table

[« CONTENT »]

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

TEST_DB.TEST_SCHEMA.S2

118

Zone de préparation

[]

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

TEST_DB.TEST_SCHEMA.T2

66568

Table

[« NAME », »ID »]

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

TEST_DB.TEST_SCHEMA.T4

66572

Table

[« ID », »NAME »]

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

TEST_DB.TEST_SCHEMA.T3

66570

Table

[« CUSTOMER_INFO »]

Exemple : lignée de colonnes

L’exemple suivant interroge la vue ACCESS_HISTORY et utilise la fonction FLATTEN pour aplatir la colonne objects_modified.

À titre d’exemple représentatif, exécutez la requête SQL suivante dans votre compte Snowflake pour produire la table ci-dessous, où les commentaires numérotés indiquent ce qui suit :

  • // 1 : Obtenir le mappage entre le champ directSources et la colonne cible.

  • // 2 : Obtenir le mappage entre le champ baseSources et la colonne cible.

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

Renvoie :

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

Exemple : suivi des références de la politique d’accès aux lignes

Renvoie une ligne pour chaque instance lorsqu’une politique d’accès aux lignes est définie sur une table, une vue ou une vue matérialisée sans doublons :

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

Exemple : suivi des références de la politique de masquage

Renvoie une ligne pour chaque instance lorsqu’une politique de masquage protège une colonne sans doublons. Notez qu’un aplatissement supplémentaire est nécessaire, parce que la colonne policies_referenced spécifie la politique de masquage sur une colonne à un niveau plus profond que la politique d’accès aux lignes sur une table :

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

Exemple : suivre la politique appliquée dans une requête

Renvoyez l’heure à laquelle la politique a été mise à jour (POLICY_CHANGED_TIME) et les conditions de la politique (POLICY_BODY) pour une requête donnée dans un laps de temps donné.

Avant d’utiliser cette requête, mettez à jour les valeurs d’entrée de la clause WHERE :

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

Où :

query_start_time > '2023-07-07'

Spécifie l’horodatage de début.

query_start_time < '2023-07-08'

Spécifie l’horodatage de fin.

query_id = '01ad7987-0606-6e2c-0001-dd20f12a9777'

Spécifie l’identificateur de la requête dans la vue Account Usage ACCESS_HISTORY.

Exécutez la requête :

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

Exemples : UDFs

Ces exemples d’UDF montrent comment la vue ACCESS_HISTORY Account Usage enregistre :

Appeler une UDF

Considérons l’UDF SQL suivante qui calcule le produit de deux nombres et supposons qu’elle est stockée dans le schéma nommé mydb.udfs :

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

L’appel direct de get_product entraîne l’enregistrement des détails de l’UDF dans la colonne direct_objects_accessed :

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

Cet exemple est analogue à l’appel d’une procédure stockée (dans cette rubrique).

UDF avec INSERT DML

Considérons l’instruction INSERT suivante pour mettre à jour les colonnes nommées 1 et 2 dans la table nommée mydb.tables.t1 :

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

La vue ACCESS_HISTORY enregistre la fonction get_product dans :

  • La colonne direct_objects_accessed car la fonction est explicitement nommée dans l’instruction SQL, et

  • La colonne objects_modified dans le tableau directSources car la fonction est la source des valeurs insérées dans les colonnes.

De même, la table t1 est enregistrée dans ces mêmes colonnes :

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 partagées

Les UDFs partagées peuvent être référencées directement ou indirectement :

  • Une référence directe est identique à l’appel explicite de l’UDF (dans cette rubrique), mais l’UDF est enregistrée à la fois dans les colonnes base_objects_accessed et direct_objects_accessed.

  • Un exemple de référence indirecte est l’appel de l’UDF pour créer une vue :

    create view v as
select get_product(c1, c2) as vc from t;
    Copy

    La colonne base_objects_accessed enregistre l’UDF et la table.

    La colonne direct_objects_accessed enregistre la vue.

Exemples : suivi d’objets modifiés par une opération DDL

Créer une balise avec ALLOWED_VALUES

Créer la balise :

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

Valeur de colonne :

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

Note

Si vous ne spécifiez pas de valeurs autorisées lors de la création de la balise, le champ properties est un tableau vide (c’est-à-dire {}).

Créer une table avec une balise et une politique de masquage

Créez la table avec une politique de masquage sur la colonne, une balise sur la colonne et une balise sur la table :

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

Valeur de colonne :

{
  "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

Définir une politique de masquage sur une balise

Définir une politique de masquage sur la balise (par exemple, masquage basé sur la balise) :

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

Valeur de colonne :

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

Échanger une table

Remplacer la table nommée t2 par la table nommée t3 :

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

Notez les deux enregistrements différents dans la vue.

Enregistrement 1 :

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

Enregistrement 2 :

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

Supprimer une politique de masquage

Supprimer la politique de masquage :

drop masking policy governance.policies.email_mask;
Copy

Valeur de colonne :

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

Note

La valeur de la colonne est représentative et s’applique à une opération DROP sur une balise et une politique d’accès aux lignes.

Le champ properties est un tableau vide et ne fournit aucune information sur la politique avant l’opération DROP.

Suivre les références d’une balise sur une colonne

La requête sur la colonne object_modified_by_ddl permet de contrôler la manière dont une balise est définie sur une colonne.

En tant qu’administrateur de table, définissez une balise sur une colonne, désactivez la définition de la balise et mettez à jour la balise avec une valeur de chaîne différente :

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

En tant qu’ingénieur des données, modifiez la valeur de la balise :

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

Effectuez une requête dans la vue ACCESS_HISTORY pour suivre les changements :

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

Renvoie :

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

Exemple : appeler une procédure stockée.

Considérons la procédure stockée suivante et supposons qu’elle est stockée dans le schéma nommé 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

L’appel direct de my_procedure entraîne l’enregistrement des détails de la procédure dans les colonnes direct_objects_accessed et base_objects_accessed comme suit :

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

Cet exemple est analogue à l’appel d’une UDF (dans cette rubrique).

Exemple : requêtes ancêtres à l’aide de procédures stockées

Vous pouvez utiliser les colonnes parent_query_id et root_query_id pour comprendre comment les appels de procédures stockées sont liés les uns aux autres.

Supposons que vous disposiez de trois instructions de procédures stockées différentes et que vous les exécutiez dans l’ordre suivant :

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

Une requête sur la vue ACCESS_HISTORY enregistre les informations comme suit :

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"}]       |
+----------+-----------------+---------------+-----------------------------------+

  • La première ligne correspond à l’appel de la deuxième procédure nommée myproc_parent comme indiqué dans la colonne direct_objects_accessed.

    Les colonnes parent_query_id et root_query_id renvoient NULL parce que vous avez appelé cette procédure stockée directement.

  • La deuxième ligne correspond à la requête qui appelle la première procédure nommée myproc_child comme indiqué dans la colonne direct_objects_accessed column.

    Les colonnes parent_query_id et root_query_id renvoient la même requête ID car la requête appelant myproc_child a été initiée par la requête appelant myproc_parent, que vous avez appelée directement.

  • La troisième ligne correspond à la requête qui a accédé à la table nommée mytable dans la procédure myproc_child, comme indiqué dans la colonne direct_objects_accessed.

    La colonne parent_query_id renvoie la requête ID de la requête qui a accédé à mytable, ce qui correspond à l’appel de myproc_child. Cette procédure stockée a été lancée par la requête appelant myproc_parent, qui figure dans la colonne root_query_id.

Langage: Français