ACCESS_HISTORY View¶

This Account Usage view can be used to query the access history of Snowflake objects (e.g. table, view, column) within the last 365 days (1 year).

Columns¶

Column Name	Data Type	Description
QUERY_ID	TEXT	An internal, system-generated identifier for the SQL statement. This value is also mentioned in the QUERY_HISTORY View.
QUERY_START_TIME	TIMESTAMP_LTZ	The statement start time (UTC time zone).
USER_NAME	TEXT	The user who issued the query.
DIRECT_OBJECTS_ACCESSED	VARIANT	A JSON array of data objects such as tables, views, and columns directly named in the query explicitly or through shortcuts such as using an asterisk (i.e. `*`). Virtual columns can be returned in this field.
BASE_OBJECTS_ACCESSED	VARIANT	A JSON array of all base data objects, specifically, columns of tables to execute the query. . Note: This field will never specify view names or view columns, including virtual columns.

The query in Snowflake and how the database objects were created determines the information Snowflake returns for DIRECT_OBJECTS_ACCESSED and BASE_OBJECTS_ACCESSED.

For example, consider the following series of objects: base_table » view_1 » view_2 » view_3.

If a query is made on view_2, Snowflake returns view_2 as the DIRECT_OBJECTS_ACCESSED because view_2 was specified in the query. The base_table is logged as the BASE_OBJECTS_ACCESSED because that is the original source of the data in view_2.

In this example, view_1 and view_3 should not be included in the DIRECT_OBJECTS_ACCESSED and BASE_OBJECTS_ACCESSED because neither of those views were included in the query and they are not the base object that serves as the source for the data in view_2.

The fields for DIRECT_OBJECTS_ACCESSED and BASE_OBJECTS_ACCESSED are described below.

Field	Data Type	Description
columnId	NUMBER	A column ID that is unique within the account. This value is identical to the columnID in the COLUMNS view.
columnName	TEXT	The name of the accessed column.
objectId	NUMBER	An identifier for the object, which is unique within a given account and domain. . This number will match the `TABLE_ID` number for a table, view, and materialized view. . See `objectDomain`.
objectName	TEXT	The fully qualified name of the object that was accessed.
objectDomain	TEXT	One of the following: TABLE, VIEW, MATERIALIZED_VIEW, EXTERNAL_TABLE, STREAM.

The following is an example of the JSON array:

[
  {
    "objectDomain": <string>,
    "objectName": <string>,
    "objectId": <number>,
    "columns": [
      {
        "columnName": <string>,
        "columnId": <number>
      },
      {
        "columnName": <string>,
        "columnId": <number>
      },
        ...
      ]
  },
  ...
]

Usage Notes¶

The view displays data starting from February 22, 2021.
This view supports read queries of the following type:
- SELECT, including CREATE TABLE … AS SELECT (i.e. CTAS).
  - Snowflake records the SELECT subquery in a CTAS operation.
- CREATE TABLE … CLONE
  - Snowflake records the source table in a CLONE operation.
- COPY INTO … TABLE
  - Snowflake logs this query only when the table is specified as the source in a FROM clause.
- DML operations that read data (e.g. contains a SELECT subquery, specifies certain columns in WHERE or JOIN): INSERT … SELECT, UPDATE, DELETE, and MERGE.
- User-defined functions (i.e. UDFs) and SQL UDTFs (User-Defined Table Functions) if tables are included in queries inside the functions. This is logged in the BASE_OBJECTS_ACCESSED field.
This view does not log accesses of the following types:
- Write operations (e.g. INSERT, UPDATE, DELETE), TRUNCATE, Snowpipe, and table metadata.
  
  For example, if a view column is produced by an external tokenization service, the base logs would not include accesses to that column, since the data is not coming from a Snowflake table.
- Table Functions or other Account Usage views.
- RESULT_SCAN to obtain prior results.
Additionally, this view does not support:
- Sequences, including generating new values.
- Data that enters or leaves Snowflake while using an External Function.
- Intermediate views accessed between the base table and direct object.
  
  For example, consider a query on View_A with the following object structure: View_A » View_B » View_C » Base_Table.
  
  The ACCESS_HISTORY view records the query on View_A and the Base_Table, not View_B and View_C.
If a Data Sharing Provider account shares objects to Data Sharing Consumer accounts through a share:
- Data Sharing Provider accounts: the queries and logs on the shared objects executed in the Provider account will not be visible to Data Sharing Consumer accounts.
- Data Sharing Consumer accounts: the queries on the data share executed in the Consumer account will be logged and only visible to the Consumer account, not the Data Sharing Provider account. The base tables accessed by the data share will not be logged.
Secure Views. The log record contains the underlying base table (i.e. BASE_OBJECTS_ACCESSED) to generate the view. Examples include queries on other Account Usage views and queries on base tables for extract, transform, and load (i.e. ETL) operations.

Examples¶

Return the user access history, ordered by user and query start time, starting from the most recent access.

SELECT user_name
       , query_id
       , query_start_time
       , direct_objects_accessed
       , base_objects_accessed
FROM access_history
ORDER BY 1, 3 desc
;

The following examples help to facilitate compliance audits:

Add the object_id value to determine who accessed a sensitive table in the last 30 days:

SELECT distinct user_name
FROM access_history
     , lateral flatten(base_objects_accessed) f1
WHERE f1.value:"objectId"::int=<fill_in_object_id>
AND f1.value:"objectDomain"::string='Table'
AND query_start_time >= dateadd('day', -30, current_timestamp())
;

Using the object_id value of 32998411400350, determine when the access occurred in the last 30 days:

SELECT query_id
       , query_start_time
FROM access_history
     , lateral flatten(base_objects_accessed) f1
WHERE f1.value:"objectId"::int=32998411400350
AND f1.value:"objectDomain"::string='Table'
AND query_start_time >= dateadd('day', -30, current_timestamp())
;

Using the object_id value of 32998411400350, determine which columns were accessed in the last 30 days:

SELECT distinct f4.value AS column_name
FROM access_history
     , lateral flatten(base_objects_accessed) f1
     , lateral flatten(f1.value) f2
     , lateral flatten(f2.value) f3
     , lateral flatten(f3.value) f4
WHERE f1.value:"objectId"::int=32998411400350
AND f1.value:"objectDomain"::string='Table'
AND f4.key='columnName'
;