SnowPro Advanced: Security Engineer Question 379
Single answerExamine login history (login_history) to trace the source IP, client application, and authentication methods usedA Security Engineer is investigating a report that a service account used to access Snowflake outside of its normal ETL window. The team needs to determine whether the account connected from an unexpected source IP, what client application was used, and which authentication method was involved for the successful logins during the past 24 hours. Which query is the MOST appropriate to answer this requirement?
- A
SELECT event_timestamp, user_name, client_ip, reported_client_type, first_authentication_factor, second_authentication_factor FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY WHERE user_name = 'ETL_SVC' AND is_success = 'YES' AND event_timestamp >= DATEADD('hour', -24, CURRENT_TIMESTAMP()) ORDER BY event_timestamp DESC;
- B
SELECT query_start_time, user_name, client_ip, query_type, authn_event_id FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE user_name = 'ETL_SVC' AND query_start_time >= DATEADD('hour', -24, CURRENT_TIMESTAMP()) ORDER BY query_start_time DESC;
- C
SELECT created_on, name, login_name, default_role, must_change_password FROM SNOWFLAKE.ACCOUNT_USAGE.USERS WHERE name = 'ETL_SVC';
- D
SELECT start_time, user_name, warehouse_name, role_name, client_generated_statement_id FROM SNOWFLAKE.ACCOUNT_USAGE.SESSIONS WHERE user_name = 'ETL_SVC' AND start_time >= DATEADD('hour', -24, CURRENT_TIMESTAMP()) ORDER BY start_time DESC;
Show answer and explanation
Correct answer: A
Explanation
For login investigations in Snowflake, SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY is the correct audit view to use when tracing authentication activity. It is designed to capture login attempts and includes fields such as USER_NAME, EVENT_TIMESTAMP, CLIENT_IP, REPORTED_CLIENT_TYPE, IS_SUCCESS, FIRST_AUTHENTICATION_FACTOR, and SECOND_AUTHENTICATION_FACTOR. These columns enable a Security Engineer to determine where a login came from, what type of client initiated it, and whether password, key pair, SSO, MFA-related factors, or other supported authentication flows were involved. QUERY_HISTORY and SESSIONS are useful for downstream activity correlation, but they are not the primary source for authentication event analysis. This aligns with Snowflake best practice: use LOGIN_HISTORY for login auditing and investigation, then correlate with other Account Usage views only if deeper activity tracing is needed.
- A. Correct.
Correct. SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY is the appropriate source for examining login events. The selected columns directly support the investigation goal: CLIENT_IP identifies the source IP, REPORTED_CLIENT_TYPE identifies the client application type, and FIRST_AUTHENTICATION_FACTOR plus SECOND_AUTHENTICATION_FACTOR help determine the authentication method used. Filtering on USER_NAME, IS_SUCCESS = 'YES', and the last 24 hours aligns with the scenario requirement to review successful logins for a specific account.
- B. Incorrect.
Incorrect. QUERY_HISTORY is for SQL statement execution analysis, not login forensics. Although it may help correlate user activity after authentication, it does not provide the authoritative login event details needed to determine the exact source IP, client application used at login, or authentication factors. A candidate might choose this because it contains user activity records, but it is not the best fit for tracing login events.
- C. Incorrect.
Incorrect. USERS contains account metadata about the Snowflake user object, such as login name and password-related settings, but it does not record historical login events. This option reflects a common misconception that static user configuration can be used to reconstruct when and how a login occurred.
- D. Incorrect.
Incorrect. SESSIONS can help analyze active or historical session characteristics, but it is not the primary source for investigating authentication details such as first and second authentication factors. It also does not provide the same purpose-built login audit visibility as LOGIN_HISTORY. Someone might choose it because sessions are related to connections, but the requirement is specifically about login source IP, client app, and authentication method.