Snowflake SnowPro Core Practice Test - Questions Answers, Page 44

When sharing data in Snowflake, the provider needs to grant the following privileges along with a share:

A . USAGE on the specific tables in the database: This privilege allows the consumers of the share to access the specific tables included in the share.

D . USAGE on the database and the schema containing the tables to share: This privilege is necessary for the consumers to access the database and schema levels, enabling them to access the tables within those schemas.

These privileges are crucial for setting up secure and controlled access to the shared data, ensuring that only authorized users can access the specified resources.

Reference to Snowflake documentation on sharing data and managing access:

Data Sharing Overview

Privileges Required for Sharing Data

The LOGIN_HISTORY view in SNOWFLAKE.ACCOUNT_USAGE shows from which IP address a user connected to Snowflake. This view is particularly useful for auditing and monitoring purposes, as it helps administrators track login attempts, successful logins, and the geographical location of users based on their IP addresses.

Reference to Snowflake documentation on LOGIN_HISTORY:

Monitoring Login Attempts

In Snowflake, shares are used to share data across different Snowflake accounts securely. When you create a share, you can include various database objects that you want to share with consumers. According to Snowflake's documentation, the types of objects that can be shared include tables, secure views, secure materialized views, and streams. Secure functions and stored procedures are not shareable objects. Tasks also cannot be shared directly. Therefore, the correct answers are streams (C) and tables (D).

To share a stream or a table, you use the GRANT statement to grant privileges on these objects to a share. The syntax for sharing a table or stream involves specifying the type of object, the object name, and the share to which you are granting access. For example:

GRANT SELECT ON TABLE my_table TO SHARE my_share; GRANT SELECT ON STREAM my_stream TO SHARE my_share;

These commands grant the SELECT privilege on a table named my_table and a stream named my_stream to a share named my_share. This enables the consumer of the share to access these objects according to the granted privileges.

To monitor updates, such as SCIM API requests sent to Snowflake by the identity provider, an administrator can use the REST EVENT HISTORY feature. This feature allows administrators to query historical data about REST API calls made to Snowflake, including those related to user and role management through SCIM (System for Cross-domain Identity Management).

The REST EVENT HISTORY table function returns information about REST API calls made over a specified period. It is particularly useful for auditing and monitoring purposes, especially when integrating Snowflake with third-party identity providers that use SCIM for automated user provisioning and deprovisioning.

An example query to check for SCIM API requests might look like this:

SELECT * FROM TABLE(information_schema.rest_event_history(date_range_start=>dateadd('hours',-1,current_timestamp()))) WHERE request_type = 'SCIM';

This query returns details on SCIM API requests made in the last hour, including the request type, the identity provider's details, and the outcome of each request.

In Snowflake, permissions for accessing database objects, including tables, are not granted directly to users but rather to roles. A role encapsulates a collection of privileges on various Snowflake objects. Users are then granted roles, and through those roles, they inherit the permissions necessary to read a table or perform other actions. This approach adheres to the principle of least privilege, allowing for granular control over database access and simplifying the management of user permissions.

Stored procedures in Snowflake can be created with either 'owner's rights' or 'caller's rights'. A stored procedure created with caller's rights executes with the privileges of the role that calls the procedure, not the privileges of the role that owns the procedure. This is particularly useful in scenarios where the procedure needs to perform operations that depend on the caller's access permissions, ensuring that the procedure can only access objects that the caller is authorized to access.

The correct function to use with the COPY INTO <location> statement to convert rows from a relational table into a single variant column and to unload rows into a JSON file is TO VARIANT. The TO VARIANT function is used to explicitly convert a value of any supported data type into a VARIANT data type. This is particularly useful when needing to aggregate multiple columns or complex data structures into a single JSON-formatted string, which can then be unloaded into a file.

In the context of unloading data, the COPY INTO <location> statement combined with TO VARIANT enables the conversion of structured data from Snowflake tables into a semi-structured VARIANT format, typically JSON, which can then be efficiently exported and stored. This approach is often utilized for data integration scenarios, backups, or when data needs to be shared in a format that is easily consumed by various applications or services that support JSON.

Snowflake Documentation on Data Unloading: Unloading Data

Snowflake Documentation on VARIANT Data Type: Working with JSON

The role that can by default create resource monitors in Snowflake is the ACCOUNTADMIN role. Resource monitors are a crucial feature in Snowflake that allows administrators to track and control the consumption of compute resources, ensuring that usage stays within specified limits. The creation and management of resource monitors involve defining thresholds for credits usage, setting up notifications, and specifying actions to be taken when certain thresholds are exceeded.

Given the significant impact that resource monitors can have on the operational aspects and billing of a Snowflake account, the capability to create and manage them is restricted to the ACCOUNTADMIN role. This role has the broadest set of privileges in Snowflake, including the ability to manage all aspects of the account, such as users, roles, warehouses, databases, and resource monitors, among others.

Snowflake Documentation on Resource Monitors: Managing Resource Monitors

The privileges granted to Snowflake's system-defined roles cannot be revoked. System-defined roles, such as SYSADMIN, ACCOUNTADMIN, SECURITYADMIN, and others, come with a set of predefined privileges that are essential for the roles to function correctly within the Snowflake environment. These privileges are intrinsic to the roles and ensure that users assigned these roles can perform the necessary tasks and operations relevant to their responsibilities.

The design of Snowflake's role-based access control (RBAC) model ensures that system-defined roles have a specific set of non-revocable privileges to maintain the security, integrity, and operational efficiency of the Snowflake environment. This approach prevents accidental or intentional modification of privileges that could disrupt the essential functions or compromise the security of the Snowflake account.

Snowflake Documentation on Access Control: Understanding Role-Based Access Control (RBAC)