Snowflake Access History: 8 Wege, Ihren Account zu auditieren

Die Access History enthält pro ausgeführter Query in Ihrem Account eine Zeile. Zu jeder Query werden mehrere Spalten zu den Objekten gespeichert, auf die zugegriffen oder die verändert wurden.

Drei Spalten helfen zunächst dabei, die gewünschten Queries zu finden:

• query_id: die eindeutige Kennung der Query
• user_name: der User, der die Query ausgeführt hat
• query_start_time: Startzeitpunkt der Query

Brauchen Sie weitere Informationen zur Query – etwa die verwendete Rolle oder das Warehouse, auf dem sie lief –, können Sie die Access History mit der View Snowflake Query History verknüpfen.

Für die Objekte, auf die eine Query zugegriffen hat, liefert die View zwei Spalten:

• direct_objects_accessed: ein JSON-Array mit Datenobjekten, auf die die Query direkt zugreift
• base_objects_accessed: ein JSON-Array mit Datenobjekten, auf die eine Query direkt oder indirekt zugreift (also die zugrunde liegenden Tabellen, die eine View speisen)

Für Objekte, die eine Query verändert hat, gibt es ebenfalls zwei Spalten:

• objects_modified: ein JSON-Array, das die durch eine Query veränderten Objekte angibt. Diese Spalte wird bei INSERT-, UPDATE-, MERGE-, CREATE-Queries oder ähnlichen Operationen befüllt, die Datensätze in einer Tabelle aktualisieren, einfügen oder löschen.
• objects_modified_by_ddl: enthält Informationen zur DDL-Operation auf einer Datenbank, einem Schema, einer Tabelle, einer View und/oder einer Spalte.

Direct vs. Base Objects Accessed

Um den Unterschied zwischen direct und base objects accessed zu verstehen, betrachten wir folgende Query, die auf zwei Spalten einer View namens user_sales_summary zugreift:

select
	user_name,
	total_sales
from user_sales_summary

Die Spalte direct_objects_accessed würde einen Eintrag für den direkten Zugriff auf die View user_sales_summary enthalten, während base_objects_accessed zwei Einträge für die beiden zugrunde liegenden Tabellen (users und sales) enthielte, die die View speisen.

Datenaufbewahrung der Access History

Wie bei anderen Account-Usage-Views, etwa der Snowflake Query History, speichert Snowflake die Daten der letzten 365 Tage.

Ist die Access History für alle Snowflake-Kunden verfügbar?

Die Access-History-View steht nur Snowflake-Kunden ab der Snowflake Enterprise Edition oder höher zur Verfügung.

Nach diesen Grundlagen schauen wir uns nun konkrete Beispiele an, die Sie in Ihrem Account ausführen können, um gängige Fragen zu beantworten.

Die folgende Query zeigt, wie Sie alle Tabellen finden, auf die ein bestimmter User in den letzten 30 Tagen zugegriffen hat. Da die Spalte base_objects_accessed ein Array ist, kombinieren wir einen lateral-Join mit der Tabellenfunktion flatten, um jeden Array-Eintrag in eine eigene Zeile aufzulösen. Dieses Muster zieht sich durch den gesamten Beitrag.

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
    where
		    access_history.query_start_time > current_date - 30
)

Code anzeigen

Beachten Sie den Filter auf object_domain='Table'. Den können Sie nach Bedarf anpassen, um verwandte Fragen zu beantworten, etwa:

• Auf welche Views hat ein User zugegriffen?
• Welche Funktionen hat er verwendet?

Um alle Tabellen zu finden, auf die in einem bestimmten Schema zugegriffen wurde, nutzen wir die access_history_flattened-CTE von oben. Der object_name in der Access History ist immer der vollständig qualifizierte Name im Format database_name.schema_name.table_name. Wir parsen diesen Namen, um Datenbank und Schema zu extrahieren, und filtern dann entsprechend:

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
access_history_flattened_w_names as (
	select

Code anzeigen

Angenommen, Sie wollen herausfinden, welche User möglicherweise auf sensible Daten in einer Tabelle zugegriffen haben. Mit der Access-History-View ermitteln Sie die vollständige Liste der User im Handumdrehen:

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
    where
	   access_history.query_start_time > current_date - 30 -- adjust as needed
),

Code anzeigen

Wie Sie mithilfe der Access-History-View ungenutzte Tabellen identifizieren, habe ich bereits an anderer Stelle ausführlich beschrieben. Hier ist der Code, den Sie verwenden können:

with
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from snowflake.account_usage.access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
table_access_history as (
	select

Code anzeigen

Mit einer kleinen Anpassung der obigen Query lassen sich auch Views identifizieren, die in den letzten 30 Tagen nicht verwendet wurden. Ersetzen Sie dazu einfach object_domain = 'Table' durch object_domain = 'View':

with
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from snowflake.account_usage.access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
table_access_history as (
	select

Code anzeigen

Bisher haben sich die Beispiele ausschließlich um Tabellen- und Schemazugriffe gedreht. Wir können eine Ebene tiefer gehen und die Spaltennutzung einer Tabelle analysieren, indem wir das columns-Array in den Feldern base/direct_objects_accessed auswerten. Mit einem zusätzlichen lateral flatten erhalten wir einen Datensatz mit einer Zeile pro Spalte, auf die in einer Query zugegriffen wurde (siehe CTE access_history_flattened_columns).

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
    where
	   access_history.query_start_time > current_date - 30 -- adjust as needed
),

Code anzeigen

Aufbauend auf dem obigen Beispiel lassen sich auch User leicht identifizieren, die auf eine bestimmte Spalte zugegriffen haben:

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
    where
	   access_history.query_start_time > current_date - 30 -- adjust as needed
),

Code anzeigen

Wenn Sie nachvollziehen wollen, warum oder wie sich eine bestimmte Tabelle ändert, ist es hilfreich, schnell die Queries oder User zu finden, die das Objekt verändert haben. Oder Sie möchten sehen, wie häufig eine Tabelle aktualisiert wird. Mit einem ähnlichen Ansatz wie oben lassen sich alle Queries identifizieren, die eine Tabelle verändert haben – indem wir die Spalte objects_modified auflösen:

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_modified.value:objectName::text as object_name,
        objects_modified.value:objectDomain::text as object_domain

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.objects_modified) as objects_modified
    where
	   access_history.query_start_time > current_date - 30 -- adjust as needed
)
select

Code anzeigen

Ian Whitestone · Co-Founder & CEO von SELECT

Ian ist Co-Founder & CEO von SELECT, einer SaaS-Plattform für Kostenmanagement und Optimierung von Snowflake. Vor SELECT leitete er sechs Jahre lang Full-Stack-Data-Science- und Engineering-Teams bei Shopify und Capital One. Bei Shopify verantwortete er die Optimierung des Data Warehouse und den Ausbau der Kostentransparenz.