Zugriffsverlauf

Unter diesem Thema werden Konzepte zum Benutzerzugriffsverlauf in Snowflake bereitgestellt.

Unter diesem Thema:

Übersicht

Der Zugriffsverlauf in Snowflake bezieht sich darauf, wann die Benutzerabfrage Spaltendaten liest und wann die SQL-Anweisung eine Datenschreiboperation wie INSERT, UPDATE und DELETE sowie Variationen des COPY-Befehls vom Quelldatenobjekt zum Zieldatenobjekt ausführt. Der Benutzerzugriffsverlauf kann durch Abfrage der Account Usage-Ansicht ACCESS_HISTORY abgerufen werden.

Jede Zeile in der ACCESS_HISTORY-Ansicht enthält einen einzelnen Datensatz pro SQL-Anweisung. Der Datensatz beschreibt die Spalten, auf die die Abfrage direkt und indirekt zugreift (d. h. die zugrunde liegenden Tabellen, aus denen die Daten für die Abfrage stammen). Mithilfe dieser Datensätze kann die Einhaltung gesetzlicher Bestimmungen einfacher geprüft werden, und sie bieten Einblick in beliebte und häufig abgefragte Tabellen und Spalten, denn es gibt eine direkte Verbindung zwischen dem Benutzer (d. h. Abfrageoperator), der Abfrage, der Tabelle bzw. Ansicht, der Spalte und den Daten.

Verfolgen von Lese- und Schreiboperationen

Die ACCESS_HISTORY-Ansicht enthält folgende Spalten:

QUERY_ID | QUERY_START_TIME | USER_NAME | DIRECT_OBJECTS_ACCESSED | BASE_OBJECTS_ACCESSED | OBJECTS_MODIFIED

Leseoperationen werden über die ersten fünf Spalten verfolgt, während die letzte Spalte OBJECTS_MODIFIED die Datenschreibinformationen angibt, die Snowflake-Spalten, -Tabellen und -Stagingbereiche betreffen.

Die Abfrage in Snowflake und die Methode für die Erstellung der Datenbankobjekte bestimmen die Informationen, die Snowflake für in den Spalten DIRECT_OBJECTS_ACCESSED, BASE_OBJECTS_ACCESSED und OBJECTS_MODIFIED zurückgibt.

Weitere Informationen zu den Spalten finden Sie im Abschnitt Spalten zur ACCESS_HISTORY-Ansicht.

Lesen

Betrachten Sie das folgende Szenario, um ein besseres Verständnis der Leseabfrage und der Erfassung dieser Informationen in der ACCESS_HISTORY-Ansicht zu erhalten:

  • Eine Reihe von Objekten: base_table » view_1 » view_2 » view_3.

  • Eine Leseabfrage auf view_2, wie z. B.:

    select * from view_2;
    

In diesem Beispiel gibt Snowflake Folgendes zurück:

  • view_2 als DIRECT_OBJECTS_ACCESSED, weil in der Abfrage view_2 angegeben ist.

  • base_table als BASE_OBJECTS_ACCESSED, da dies die ursprüngliche Quelle der Daten in view_2 ist.

Beachten Sie, dass view_1 und view_3 nicht in DIRECT_OBJECTS_ACCESSED und BASE_OBJECTS_ACCESSED enthalten sind, da keine dieser Ansichten in der Abfrage enthalten war und sie nicht das Basisobjekt sind, das als Quelle für die Daten in view_2 dient.

Schreiben

Betrachten Sie das folgende Szenario, um ein besseres Verständnis der Schreiboperation und der Erfassung dieser Informationen in der ACCESS_HISTORY-Ansicht zu erhalten:

  • Eine Datenquelle: base_table

  • Erstellen Sie eine Tabelle aus der Datenquelle (d. h. CTAS):

    create table table_1 as select * from base_table;
    

In diesem Beispiel gibt Snowflake Folgendes zurück:

  • base_table in den Spalten BASE_OBJECTS_ACCESSED und DIRECT_OBJECTS_ACCESSED, da auf die Tabelle direkt zugegriffen wurde und sie die Quelle der Daten ist.

  • table_1 in der Spalte OBJECTS_MODIFIED mit den Spalten, in die beim Erstellen der Tabelle geschrieben wurde.

Unterstützte Operationen

Eine vollständige Beschreibung der von der ACCESS_HISTORY-Ansicht unterstützten Lese- und Schreiboperationen finden Sie im Abschnitt Nutzungshinweise zur ACCESS_HISTORY-Ansicht.

Vorteile

Der Zugriffsverlauf in Snowflake bietet die folgenden Vorteile:

Datenerkennung

Erkennen von nicht verwendeten Daten, um zu entscheiden, ob die Daten archiviert oder gelöscht werden sollen.

Verfolgen der Bewegung sensibler Daten

Verfolgen Sie Datenbewegungen von einem externen Cloudspeicherort (z. B. Amazon S3-Bucket) zur Snowflake-Zieltabelle und umgekehrt.

Verfolgen Sie interne Datenbewegungen von einer Snowflake-Tabelle zu einer anderen Snowflake-Tabelle.

Nachdem Sie die Bewegung sensibler Daten verfolgt haben, wenden Sie Richtlinien (Maskierung und Zeilenzugriff) an, um die Daten zu schützen, aktualisieren Sie die Zugriffssteuerungseinstellungen, um den Zugriff auf Stagingbereich und Tabelle weiter zu regulieren, und setzen Sie Tags, um sicherzustellen, dass Stagingbereiche, Tabellen und Spalten mit sensiblen Daten für Compliance-Anforderungen nachverfolgt werden können.

Datenvalidierung

Die Genauigkeit und Integrität von Berichten, Dashboards und Datenvisualisierungsprodukten wie Diagrammen und Grafiken werden validiert, da die Daten bis zu ihrer ursprünglichen Quelle zurückverfolgt werden können.

Datenverwalter können Benutzer benachrichtigen, bevor eine bestimmte Tabelle oder Ansicht gelöscht oder geändert wird.

Compliance-Prüfung

Identifizieren Sie den Snowflake-Benutzer, der eine Schreiboperation auf einer Tabelle oder einem Stagingbereich ausgeführt hat, und wann diese Schreiboperation stattgefunden hat, um Compliance-Anforderungen wie GDPR oder CCPA zu erfüllen.

Verbesserung der allgemeinen Data Governance

Die ACCESS_HISTORY-Ansicht bietet einen Gesamtüberblick, auf welche Daten zugegriffen wurde, wann der Datenzugriff stattfand und wie die Daten, auf die zugegriffen wurde, vom Datenquellenobjekt zum Datenzielobjekt verschoben wurden.

Abfragen der ACCESS_HISTORY-Ansicht

Die folgenden Unterabschnitte enthalten Beispielabfragen für die ACCESS_HISTORY-Ansicht.

Beachten Sie, dass einige der Beispielabfragen nach der Spalte QUERY_START_TIME filtern, um die Abfrageleistung zu erhöhen. Eine weitere Möglichkeit, die Leistung zu erhöhen, ist die Einengung des Zeitbereichs, auf dem die Abfrage ausgeführt wird.

Beispiele: Leseabfragen

In den folgenden Unterabschnitten wird beschrieben, wie die ACCESS_HISTORY-Ansicht für Leseoperationen in den folgenden Anwendungsfälle abgefragt werden kann:

  • Abrufen des Zugriffsverlaufs für einen bestimmten Benutzer.

  • Vereinfachen der Compliance-Prüfungen für den Zugriff auf sensible Daten in den letzten 30 Tagen auf Basis von object_id (z. B. einer Tabellen-ID), um die folgenden Fragen zu beantworten:

    • Wer hat auf die Daten zugegriffen?

    • Wann wurde auf die Daten zugegriffen?

    • Auf welche Spalten wurde zugegriffen?

Benutzerzugriffsverlauf zurückgeben

Gibt den Benutzerzugriffsverlauf zurück, sortiert nach Benutzer und Startzeit der Abfrage, beginnend mit dem letzten Zugriff.

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

Compliance-Prüfungen vereinfachen

Die folgenden Beispiele helfen, die Einhaltung gesetzlicher Bestimmungen zu vereinfachen:

  • Fügen Sie den object_id-Wert hinzu, um festzustellen, wer in den letzten 30 Tagen auf eine sensible Tabelle zugegriffen hat:

    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())
    ;
    
  • Bestimmen Sie anhand des object_id-Wertes von 32998411400350, wann der Zugriff in den letzten 30 Tagen erfolgte:

    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())
    ;
    
  • Bestimmen Sie anhand des object_id-Wertes von 32998411400350, auf welche Spalten in den letzten 30 Tagen zugegriffen wurde:

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

Beispiele: Schreiboperationen

In den folgenden Unterabschnitten wird beschrieben, wie die ACCESS_HISTORY-Ansicht für Schreiboperationen in den folgenden Anwendungsfälle abgefragt werden kann:

  • Laden von Daten aus einem Stagingbereich in eine Tabelle

  • Entladen von Daten aus einer Tabelle in einen Stagingbereich

  • Verwenden des PUT-Befehls zum Hochladen einer lokale Datei in einen Stagingbereich

  • Verwenden des GET-Befehls zum Abrufen von Datendateien aus einem Stagingbereich in ein lokales Verzeichnis

  • Verfolgen der Bewegung sensibler Stagingbereichsdaten

Daten aus einem Stagingbereich in eine Tabelle laden

Laden Sie eine Menge von Werten aus einer Datendatei in einen externen Cloudspeicher in Spalten einer Zieltabelle.

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

In den Spalten DIRECT_OBJECTS_ACCESSED und BASE_OBJECTS_ACCESSED wird angegeben, dass auf einen externen benannten Stagingbereich zugegriffen wurde:

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

In der Spalte OBJECTS_MODIFIED wird angegeben, dass Daten in zwei Spalten der Tabelle geschrieben wurden:

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

Daten aus einer Tabelle in einen Stagingbereich entladen

Entladen Sie eine Menge von Werten aus einer Snowflake-Tabelle in den Cloudspeicher.

copy into @mystage1/data1.csv
from table1;

In den Spalten DIRECT_OBJECTS_ACCESSED und BASE_OBJECTS_ACCESSED werden die Tabellenspalten angegeben, auf die zugegriffen wurde:

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

In der Spalte OBJECTS_MODIFIED wird der Stagingbereich angegeben, in den die abgerufenen Daten geschrieben wurden:

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

PUT-Befehl zum Hochladen einer lokalen Datei in einen Stagingbereich verwenden

Kopieren Sie eine Datendatei in einen internen (d. h. Snowflake) Stagingbereich.

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

In den Spalten DIRECT_OBJECTS_ACCESSED und BASE_OBJECTS_ACCESSED wird der lokale Pfad zu der Datei angegeben, auf die zugegriffen wurde:

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

In der Spalte OBJECTS_MODIFIED wird der Stagingbereich angegeben, in den die abgerufenen Daten geschrieben wurden:

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

GET-Befehl zum Abrufen von Datendateien aus einem Stagingbereich in ein lokales Verzeichnis verwenden

Rufen Sie eine Datendatei aus einem internen Stagingbereich in ein Verzeichnis auf dem lokalen Rechner ab.

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

In den Spalten DIRECT_OBJECTS_ACCESSED und BASE_OBJECTS_ACCESSED werden der Stagingbereich und das lokale Verzeichnis angegeben, auf die zugegriffen wurde:

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

In der Spalte OBJECTS_MODIFIED wird das Verzeichnis angegeben, in das die abgerufenen Daten geschrieben wurden:

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

Bewegung sensibler Stagingbereichsdaten verfolgen

Verfolgen Sie sensible Stagingbereichsdaten, während diese eine Serie von Abfragen in chronologischer Reihenfolge durchlaufen.

Führen Sie die folgenden Abfragen aus. Beachten Sie, dass fünf der Anweisungen auf Stagingbereiche zugreifen. Wenn Sie also die ACCESS_HISTORY-Ansicht für den Stagingbereichszugriff abfragen, sollte das Resultset fünf Zeilen enthalten.

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;

Wobei:

  • T1, T2T7 geben die Namen der Tabellen an.

  • S1 und S2 geben die Namen der Stagingbereiche an.

Führen Sie eine Abfrage des Zugriffsverlaufs aus, um den Zugriff auf Stagingbereich S1 zu ermitteln.

Die Daten der Spalten DIRECT_OBJECTS_ACCESSED, BASE_OBJECTS_ACCESSED und OBJECTS_MODIFIED sind in der folgenden Tabelle aufgeführt.

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

Beachten Sie, dass die Abfrage im folgenden Beispiel einen rekursiven allgemeinen Tabellenausdruck verwendet.

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;

Die Abfrage liefert das folgende Resultset in Bezug auf die Datenbewegung in Stagingbereich 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

Tabelle

[„CONTENT“]

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

TEST_DB.TEST_SCHEMA.S2

118

Stagingbereich

[]

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

TEST_DB.TEST_SCHEMA.T2

66568

Tabelle

[„NAME“,“ID“]

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

TEST_DB.TEST_SCHEMA.T4

66572

Tabelle

[„ID“,“NAME“]

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

TEST_DB.TEST_SCHEMA.T3

66570

Tabelle

[„CUSTOMER_INFO“]