Not able to get the dbname, rolename, userid, last access date, read/write access for a Snowflake target table

Question

As a part of auditing in my project and I need to find the db name, user name, last access date, read/write access, role name for the tables present in the Snowflake target. Can someone please help me with this?

Answer 1

Snowflake provides a database view in the "Account Usage" share called "tables", which will provide you a list of all the tables in your Snowflake account, along with their schema, database, and role that owns the table. https://docs.snowflake.net/manuals/sql-reference/account-usage/tables.html

As for access (reads via SELECT, unloads via COPY INTO LOCATION commands) and writes (INSERT, UPDATE, DELETE, and load via COPY INTO TABLE commands), you can query a different view in the "Account Usage" share called query_history and search for the table name using the iLike('%tableNameHere%') function on the QUERY_TEXT column which works well. https://docs.snowflake.net/manuals/sql-reference/account-usage/query_history.html