Snowflake Access History: 8 formas de auditar tu cuenta

El Access History almacena 1 fila por cada query ejecutada en tu cuenta. Para cada query guarda varias columnas con información sobre los objetos a los que se accedió o que se modificaron.

Para empezar, hay tres columnas útiles para ubicar las queries que te interesan:

• query_id: el identificador único de la query
• user_name: el usuario que ejecutó la query
• query_start_time: cuándo se inició la query

Si necesitas información adicional sobre la query, como el rol con el que se ejecutó o el warehouse en el que corrió, puedes hacer un join entre el dataset de Access History y la vista Snowflake Query History.

En cuanto a los objetos a los que accedió la query, la vista entrega dos columnas:

• direct_objects_accessed: un array JSON con los objetos de datos a los que la query accede directamente
• base_objects_accessed: un array JSON con los objetos de datos a los que la query accede directa o indirectamente (es decir, las tablas subyacentes que alimentan una vista)

Para los objetos que la query modificó, hay dos columnas:

• objects_modified: un array JSON que especifica los objetos modificados por la query. Se completa para queries de tipo INSERT, UPDATE, MERGE, CREATE o similares que actualizan, insertan o eliminan registros en una tabla
• objects_modified_by_ddl: contiene información sobre la operación DDL aplicada a una base de datos, esquema, tabla, vista o columna.

Direct vs. Base Objects Accessed

Para entender la diferencia entre direct y base objects accessed, fíjate en la siguiente query, que accede a dos columnas de una vista llamada user_sales_summary:

select
	user_name,
	total_sales
from user_sales_summary

La columna direct_objects_accessed incluiría 1 entrada por el acceso directo a la vista user_sales_summary, mientras que base_objects_accessed tendría dos entradas correspondientes a las dos tablas subyacentes (users y sales) que alimentan la vista.

Retención de datos del Access History

Igual que ocurre con otras vistas de account usage como el Snowflake Query History, Snowflake conserva los datos de los últimos 365 días.

¿El Access History está disponible para todos los clientes de Snowflake?

La vista Access History solo está disponible para clientes de Snowflake con edición Enterprise de Snowflake o superior.

Ya cubrimos lo básico; ahora pasemos a algunos ejemplos reales que puedes ejecutar en tu cuenta para resolver una variedad de preguntas comunes.

La query a continuación muestra cómo encontrar todas las tablas a las que accedió un usuario determinado en los últimos 30 días. Como la columna base_objects_accessed es un array, hay que usar un join lateral junto con la función de tabla flatten para expandir cada entrada del array en una fila aparte. Verás este patrón a lo largo de todo el post.

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
)

Expand Code

Fíjate en que filtro por object_domain='Table'. Puedes modificarlo según necesites para responder preguntas relacionadas como:

• ¿A qué vistas accedió un usuario?
• ¿Qué funciones utilizó?

Para encontrar todas las tablas a las que se accedió dentro de un esquema en particular, podemos apoyarnos en el CTE access_history_flattened de arriba. El object_name presente en Access History siempre es el nombre totalmente calificado, es decir, con el formato database_name.schema_name.table_name. Por eso parseamos ese nombre para obtener el nombre de la base de datos y del esquema, y luego filtramos según lo que necesitemos:

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

Expand Code

Imagina que quieres identificar a los usuarios que pudieron haber accedido a datos sensibles dentro de una tabla. Con la vista access_history puedes obtener rápidamente la lista completa de usuarios:

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

Expand Code

Hace un tiempo escribí sobre cómo identificar tablas sin uso apoyándote en la vista Access History. Puedes revisar ese post para ver la explicación a detalle. Aquí tienes el código que puedes usar:

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

Expand Code

La query anterior se puede ajustar fácilmente para identificar vistas que no se hayan usado en los últimos 30 días. Basta con cambiar object_domain = 'Table' por 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

Expand Code

Hasta aquí los ejemplos se han centrado solo en el acceso a tablas y esquemas. Podemos bajar un nivel más y analizar el uso de columnas para una tabla dada apoyándonos en el array columns presente en los campos base/direct_objects_accessed. Tras aplicar un lateral flatten adicional, obtenemos un dataset con 1 fila por cada columna accedida en una query (ver el 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
),

Expand Code

A partir del ejemplo anterior, podemos identificar fácilmente a los usuarios que accedieron a una columna específica:

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

Expand Code

Cuando investigas por qué o cómo cambió una tabla, puede ser útil ubicar rápidamente las queries o los usuarios que la modificaron. O tal vez quieras ver con qué frecuencia se actualiza. Con un enfoque parecido al de los ejemplos anteriores, podemos identificar todas las queries que modificaron una tabla haciendo flatten de la columna objects_modified:

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

Expand Code

Ian Whitestone·Co-founder & CEO of SELECT

Ian es Co-founder & CEO de SELECT, una plataforma SaaS de gestión y optimización de costos de Snowflake. Antes de fundar SELECT, pasó 6 años liderando equipos full stack de data science e ingeniería en Shopify y Capital One. En Shopify estuvo al frente del trabajo para optimizar el data warehouse y mejorar la observabilidad de costos.