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.
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
SELECT object_id, tag_name, tag_value FROM snowflake.account_usage.tag_references WHERE tag_value = 'Level Super Secret A+++++++'
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
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.