Executing Queries and DML Statements:
Warehouse Requirements: Running queries and DML statements in Snowflake requires two conditions:
-A running warehouse.
-The warehouse specified as the current warehouse for the session.
Session Scope: Each Snowflake session can only have one current warehouse at a time.
Warehouse Selection: Use the USE WAREHOUSE command to set or change the current warehouse for the session.
Query Processing: Once set, queries and DML statements within the session are processed by the current warehouse. You can view the warehouse used for each query/statement in the Classic Console's History and Worksheets pages.
Delegating Warehouse Management:
Default Access: The ACCOUNTADMIN role holds default privileges to manage all warehouses in the account (alter, suspend, describe, etc.).
Custom Role Delegation: For finer control, use the GRANT command to grant the MANAGE WAREHOUSES privilege to a custom role. This grants equivalent privileges (MODIFY, MONITOR, OPERATE) to all account warehouses.
Example: The following demonstrates delegating warehouse management to a custom role named manage_wh_role:
1. Create and Own a New Warehouse:
SQL
CREATE ROLE create_wh_role;
GRANT
CREATE WAREHOUSE ON ACCOUNT TO ROLE create_wh_role;
GRANT ROLE create_wh_role TO ROLE SYSADMIN;
CREATE ROLE manage_wh_role;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE manage_wh_role;
GRANT ROLE manage_wh_role TO ROLE SYSADMIN;
Use code with caution. Learn more
2. Use manage_wh_role to modify "test_wh" owned by create_wh_role:
SQL
USE ROLE manage_wh_role;
ALTER WAREHOUSE test_wh ...;
Delegating Warehouse Management with Roles:
This example demonstrates creating two roles to manage Snowflake warehouses:
1. create_wh_role:
This role has the CREATE WAREHOUSE privilege, allowing it to:
Create and own new warehouses.
Grant ownership of warehouses to other roles.
2. manage_wh_role:
This role has the MANAGE WAREHOUSES privilege, granting it comprehensive control over all warehouses in the account, regardless of ownership. This includes the ability to:
Suspend and resume warehouses.
Resize warehouses.
Describe warehouse properties.
Scenario:
Create both roles and grant them the necessary privileges:
SQL
CREATE ROLE create_wh_role;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE create_wh_role;
GRANT ROLE create_wh_role TO ROLE SYSADMIN;
CREATE ROLE manage_wh_role;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE manage_wh_role;
GRANT ROLE manage_wh_role TO ROLE SYSADMIN;
Use code with caution. Learn more
Create a new warehouse with create_wh_role:
SQL
USE ROLE create_wh_role;
CREATE OR REPLACE WAREHOUSE test_wh WITH WAREHOUSE_SIZE = XSMALL;
Use code with caution. Learn more
Switch to manage_wh_role and manage the warehouse:
SQL
USE ROLE manage_wh_role;
ALTER WAREHOUSE test_wh SUSPEND; -- Suspend the warehouse
ALTER WAREHOUSE test_wh SET WAREHOUSE_SIZE = SMALL; -- Resize the warehouse
DESC WAREHOUSE test_wh; -- Describe the warehouse properties