Question

The IT Security team has identified that there is an ongoing credential stuffing attack on many of their organization’s system.

What is the BEST way to find recent and ongoing login attempts to Snowflake?

A. Call the LOGIN_HISTORY Information Schema table function.
B. Query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database.
C. View the History tab in the Snowflake UI and set up a filter for SQL text that contains the text “LOGIN”.
D. View the Users section in the Account tab in the Snowflake UI and review the last login column.

Answer

A. Call the LOGIN_HISTORY Information Schema table function.

Explanation 1

The best way to find recent and ongoing login attempts to Snowflake is to query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database. This view provides a record of all login attempts for all users in your account, including successful and failed attempts. You can use this view to identify the cause of user login failures.

Option A is incorrect because the LOGIN_HISTORY Information Schema table function does not exist. Option C is incorrect because it only shows the history of SQL commands that contain the text “LOGIN” and not login attempts. Option D is incorrect because it only shows the last login time for each user.

Explanation 2

According to the Snowflake website, the SnowPro Advanced: Architect certification exam will test your ability to design an end-to-end data flow from source to consumption using the Snowflake’s Platform, among other skills.

One of the topics covered by the exam is data security, which includes understanding and applying best practices for preventing and detecting unauthorized access to Snowflake.

Credential stuffing is a type of cyberattack that uses stolen usernames and passwords from one website to try to log in to other websites. This can result in account takeover, fraud, identity theft, and data breaches.

To find recent and ongoing login attempts to Snowflake, you can use different methods depending on your role and access level.

One method is to query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database. This view provides information about successful and unsuccessful login attempts for all users in the account for the past 365 days. You can filter the results by user name, IP address, date and time, or error code.

Another method is to call the LOGIN_HISTORY table function. This function returns similar information as the view, but it allows you to specify a start and end time for the query. You can also filter the results by user name or IP address.

A third method is to view the History tab in the Snowflake web interface and set up a filter for SQL text that contains the text “LOGIN”. This will show you the login queries executed by users in your account. You can also see other details such as query ID, status, warehouse, role, database, schema, and duration.

A fourth method is to view the Users section in the Account tab in the Snowflake web interface and review the Last Login column. This will show you the date and time of the most recent successful login for each user in your account. You can also see other information such as user name, display name, email, role, default warehouse, default namespace, and comment.

Based on this information, I think the best answer to your question is B. Query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database. This method provides comprehensive and historical data about login attempts for all users in your account and allows you to filter by various criteria. The other methods are either limited by time range, scope, or detail.

Explanation 3

The correct answer is B. The BEST way to find recent and ongoing login attempts to Snowflake is to query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database. Here is a detailed explanation:

B. Query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database.

This is true because the LOGIN_HISTORY view can be used to query login attempts by Snowflake users within the last 365 days (1 year). The view shows the time, user, client IP, authentication method, success status, error code and message, and other details for each login event. You can use SQL predicates to filter the view by any of these columns.

A. Call the LOGIN_HISTORY Information Schema table function. This is false because the LOGIN_HISTORY table function can only return login events within the last 7 days. If you want to find login attempts beyond this time range, you need to use the LOGIN_HISTORY view instead.

C. View the History tab in the Snowflake UI and set up a filter for SQL text that contains the text “LOGIN”. This is false because the History tab in the Snowflake UI shows the history of queries executed by users or roles in your account. It does not show the login attempts by users or roles, which are not considered as queries.

D. View the Users section in the Account tab in the Snowflake UI and review the last login column. This is false because the Users section in the Account tab in the Snowflake UI only shows the last login time for each user in your account. It does not show all the login attempts by each user, including unsuccessful ones, or provide any details about them.

Explanation 4

A. Call the LOGIN_HISTORY Information Schema table function.

The BEST way to find recent and ongoing login attempts to Snowflake is by calling the LOGIN_HISTORY Information Schema table function. This function returns login activity within the last 7 days and can be used to query login attempts by Snowflake users along various dimensions. You can further filter the results using SQL predicates to focus on specific users, time ranges, or other criteria.

The LOGIN_HISTORY table function provides more flexibility and granularity in retrieving login attempt information compared to other options. It allows you to programmatically analyze and monitor login attempts, which is essential for identifying and mitigating credential stuffing attacks.

Here’s an example of how to retrieve up to the last 100 login events of the current user:

sql
SELECT *
FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY())
ORDER BY EVENT_TIMESTAMP DESC
LIMIT 100;

For a specific user, you can use the LOGIN_HISTORY_BY_USER function:

sql
SELECT *
FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY_BY_USER(USER_NAME => 'username'))
ORDER BY EVENT_TIMESTAMP DESC
LIMIT 100;

Remember to replace ‘username’ with the actual username you want to check login attempts for.

Explanation 5

SELECT *
FROM ACCOUNT_USAGE.LOGIN_HISTORY
WHERE TIME_RANGE_START BETWEEN CURRENT_TIMESTAMP - INTERVAL '7 DAY' AND CURRENT_TIMESTAMP;

Explanation 6

The BEST way to find recent and ongoing login attempts to Snowflake in the given scenario is option B: Query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database.

Here’s an explanation of why option B is the most suitable choice:

Snowflake provides the LOGIN_HISTORY view in the ACCOUNT_USAGE schema, which contains information about user logins and login attempts. By querying this view, you can retrieve a comprehensive history of login activity, including successful logins and failed login attempts.

Here are the key benefits of using the LOGIN_HISTORY view:

1. Comprehensive login activity: The LOGIN_HISTORY view provides a detailed record of all login attempts, including the username, login time, status (success or failure), IP address, and more. This allows you to analyze login patterns, identify potential threats, and monitor ongoing login attempts.

2. Real-time information: The LOGIN_HISTORY view is updated in near real-time, providing you with the most up-to-date login information. This is crucial in identifying ongoing login attempts during a credential stuffing attack and taking immediate action to mitigate the threat.

3. Customizable query capabilities: You can apply various filters and conditions to the query on the LOGIN_HISTORY view to narrow down the results based on your specific requirements. For example, you can filter by login status, timeframe, specific usernames, or IP addresses to focus on relevant login attempts.

While the other options (A, C, and D) provide some level of information about login attempts, they are not as comprehensive or specifically designed for this purpose as the LOGIN_HISTORY view in the ACCOUNT_USAGE schema. Option A refers to the LOGIN_HISTORY Information Schema table function, which may not provide the same level of detail as the view. Option C suggests using the Snowflake UI’s History tab, but it may not be as efficient for analyzing large amounts of login data. Option D focuses on the Users section in the Snowflake UI, which only provides information about the last login and may not be sufficient for identifying ongoing login attempts.

Therefore, to effectively identify recent and ongoing login attempts to Snowflake during a credential stuffing attack, querying the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database is the most appropriate and reliable approach.

Explanation 7

Explanation 8

SELECT
  user_name,
  ip_address,
  login_time,
  success
FROM
  snowflake.account_usage.LOGIN_HISTORY
WHERE
  login_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

Explanation 9

The BEST way to find recent and ongoing login attempts to Snowflake when there is an ongoing credential stuffing attack is:

B. Query the LOGIN_HISTORY view in the ACCOUNT_USAGE schema in the SNOWFLAKE database.

Explanation:

The LOGIN_HISTORY view in the ACCOUNT_USAGE schema of the SNOWFLAKE database provides a record of all logins to Snowflake, including failed logins. This view is the best source for identifying recent and ongoing login attempts during an attack.

Option A, calling the LOGIN_HISTORY Information Schema table function, is not the best approach because the view in the ACCOUNT_USAGE schema is more comprehensive and provides more details.

Option C, viewing the History tab in the Snowflake UI and setting up a filter for SQL text that contains the text “LOGIN”, is not the best approach because it only shows SQL statements that contain the word “LOGIN” and may not capture all login attempts.

Option D, viewing the Users section in the Account tab in the Snowflake UI and reviewing the last login column, is not the best approach because it only shows the last login time for each user and does not provide a comprehensive history of login attempts, including failed attempts.

Reference

• How to view failed user login attempts (snowflake.com)
• Snowflake Security Overview and Best Practices
• Securing Snowflake | Snowflake Documentation
• Credential stuffing | OWASP Foundation
• Credential stuffing definition, detection, and prevention | CSO Online
• The State of Credential Stuffing Attacks (securityintelligence.com)
• LOGIN_HISTORY , LOGIN_HISTORY_BY_USER | Snowflake Documentation
• LOGIN_HISTORY View | Snowflake Documentation
• Access History | Snowflake Documentation
• QUERY_HISTORY View | Snowflake Documentation
• LOGIN_HISTORY View (Account Usage): New Column (snowflake.com)
• Account Usage | Snowflake Documentation

Snowflake SnowPro Advanced Architect certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Snowflake SnowPro Advanced Architect exam and earn Snowflake SnowPro Advanced Architect certification.

The post SnowPro Advanced Architect Q&A: What is the BEST way to find recent and ongoing login attempts to Snowflake? appeared first on PUPUWEB - Information Resource for Emerging Technology Trends and Cybersecurity.