History access in snowflake can provide us many insights regarding who accessed what. Say we have sensitive info on which we wish to find who accessed it. In challenge 7 of Frosty Friday we want to find out just that. Our challenge is to figure out who accessed data that was tagged with ‘Level Super Secret A+++++++’.
As mentioned in the challenge, you should decide on the best environment configuration for your Snowflake instance and run the relevant SQL code. Consider a data latency of max 2 hours before proceeding with the challenge.
Tag References
Snowflake documentation specifies several ways to tap into and track tag usage. In our case, I chose to use the Account Usage TAG_REFERENCES view to determine all of the objects with tag_value = 'Level Super Secret A+++++++'. From that view we will get object_id, tag_name and tag_value.
SELECT object_id, tag_name, tag_value
FROM snowflake.account_usage.tag_references
WHERE tag_value = 'Level Super Secret A+++++++'Access History
Now that we know the needed information, let’s go down the ACCESS_HISTORY view and check it out. This Account Usage view can be used to query the access history of Snowflake objects in the last 365 days.
Although we can simply pick query_id from the view, other important information is stored in BASE_OBJECTS_ACCESSED, a JSON array of all base data objects, specifically, columns of tables to execute the query.
To tap into BASE_OBJECTS_ACCESSED we would use a LATERAL FLATTEN method in the same manner we have used it in another blog post, while parsing a JSON in Frosty challenge #4. For a more robust code we will use a CTE just like in our former challenge dealing with geo-spatial data.
// Getting information from TAG_REFERENCES regarding the tag needed
WITH tags_hist AS (
SELECT object_id, tag_name, tag_value
FROM snowflake.account_usage.tag_references
WHERE tag_value = 'Level Super Secret A+++++++'
),
// Lateral join based on object_id
access_history_flatten AS (
SELECT ah.query_id,
f1.value:"objectId"::INT as object_id
FROM snowflake.account_usage.access_history ah,
LATERAL flatten(base_objects_accessed) f1
WHERE f1.value:"objectId"::INT in (SELECT object_id FROM tags_hist)
AND f1.value:"objectDomain"::STRING='Table'
)
// Building the results table
SELECT f.query_id,
t.tag_name, t.tag_value,
qh.database_name, qh.role_name
FROM access_history_flatten f
LEFT JOIN snowflake.account_usage.query_history qh
ON f.query_id = qh.query_id
LEFT JOIN tags_hist t ON t.object_id = f.object_id
;Let’s recap the code above:
While tapping into snowflake.account_usage.access_history, we cross join base_objects_accessed in a lateral flatten fashion, based on a list of object_id‘s, nested inside the WHERE clause.
In the last part of the CTE, we are simply constructing the final table.
And we are done!
I hope you find this blog post helpful.
Disclaimer: Each challenge presents us with a feature or aspect within the Snowflake ecosystem. It offers us a way to practice the building blocks of different processes, further developing our Snowflake skillset.
To better understand the terminology, concepts and opportunities a challenge offers, it is essential to read the snowflake documentation and understand the different aspects around the challenge.