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 d’une colonne 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.

Chaque ligne de la vue ACCESS_HISTORY contient un seul enregistrement par instruction SQL. L’enregistrement décrit les colonnes auxquelles la requête a accédé directement et indirectement (c’est-à-dire les tables sous-jacentes d’où proviennent les données de la requête). Ces enregistrements 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.

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

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.

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;
    

Dans cet exemple, Snowflake renvoie :

  • view_2 comme DIRECT_OBJECTS_ACCESSED car la requête spécifie view_2.

  • base_table comme 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 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;
    

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.

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 la section Notes sur l’utilisation de la vue ACCESS_HISTORY.

Avantages

L’historique des accès dans Snowflake offre les avantages suivants :

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.

Interrogation de la vue ACCESS_HISTORY Vue

Les sous-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
;

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())
    ;
    
  • 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())
    ;
    
  • 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'
    ;
    

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

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

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
}

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;

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

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

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;

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

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

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

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

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

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;

Où :

  • T1, T2T7 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"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68611,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66566,
    "objectName": "TEST_DB.TEST_SCHEMA.T6"
  }
]
[
  {
    "objectDomain": "Stage",
    "objectId": 117,
    "objectName": "TEST_DB.TEST_SCHEMA.S1",
    "stageKind": "External Named"
  }
]
[
  {
    "objectDomain": "Stage",
    "objectId": 117,
    "objectName": "TEST_DB.TEST_SCHEMA.S1",
    "stageKind": "External Named"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68613,
        "columnName": "ID"
      },
      {
        "columnId": 68612,
        "columnName": "NAME"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66568,
    "objectName": "TEST_DB.TEST_SCHEMA.T2"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
[
  {
    "objectDomain": "Stage",
    "objectId": 118,
    "objectName": "TEST_DB.TEST_SCHEMA.S2",
    "stageKind": "External Named"
  }
]
[
  {
    "objectDomain": "Stage",
    "objectId": 117,
    "objectName": "TEST_DB.TEST_SCHEMA.S1",
    "stageKind": "External Named"
  }
]
[
  {
    "objectDomain": "Stage",
    "objectId": 117,
    "objectName": "TEST_DB.TEST_SCHEMA.S1",
    "stageKind": "External Named"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68614,
        "columnName": "CUSTOMER_INFO"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66570,
    "objectName": "TEST_DB.TEST_SCHEMA.T3"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68610,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66564,
    "objectName": "TEST_DB.TEST_SCHEMA.T1"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68615,
        "columnName": "NAME"
      },
      {
        "columnId": 68616,
        "columnName": "ID"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66572,
    "objectName": "TEST_DB.TEST_SCHEMA.T4"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68611,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66566,
    "objectName": "TEST_DB.TEST_SCHEMA.T6"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68611,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66566,
    "objectName": "TEST_DB.TEST_SCHEMA.T6"
  }
]
[
  {
    "columns": [
      {
        "columnId": 68618,
        "columnName": "CONTENT"
      }
    ],
    "objectDomain": "Table",
    "objectId": 66574,
    "objectName": "TEST_DB.TEST_SCHEMA.T7"
  }
]

Notez que la requête de l’exemple suivant utilise une expression de table commune récursive.

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;

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 »]