A data engineer has created a custom role ETL ROLE responsible for loading data into a Snowflake warehouse. This role needs to be able to truncate tables within a specific schema RAW DATA. Which of th

The correct answer is E. GRANT USAGE ON DATABASE TO ROLE ETL ROLE; GRANT TRUNCATE ON FUTURE. Option E is correct because it follows Snowflake's principle of least privilege combined with best practices for role setup: granting USAGE ON DATABASE establishes the necessary hierarchy access (you must be able to "see" the database before accessing objects within it), while TR

Snowflake Account and Security

Question

A data engineer has created a custom role ETL ROLE responsible for loading data into a Snowflake warehouse. This role needs to be able to truncate tables within a specific schema RAW DATA. Which of the following is the most secure and appropriate way to grant this privilege to 'ETL ROLE?

Options

AGRANT ALL PRIVILEGES ON SCHEMARAW DATA TO ROLE ETL ROLE;
BGRANT TRUNCATE ON ALL TABLES IN SCHEMA RAW DATA TO ROLE ETL ROLE;
CGRANT TRUNCATE ON FUTURE TABLES IN SCHEMA RAW DATA TO ROLE ETL ROLE;
DGRANT USAGE ON DATABASE TO ROLE ETL ROLE; GRANT TRUNCATE ON ALL TABLES IN
EGRANT USAGE ON DATABASE TO ROLE ETL ROLE; GRANT TRUNCATE ON FUTURE

How the community answered

(29 responses)

A
3% (1)
B
17% (5)
C
3% (1)
D
10% (3)
E
66% (19)

Explanation

Option E is correct because it follows Snowflake's principle of least privilege combined with best practices for role setup: granting USAGE ON DATABASE establishes the necessary hierarchy access (you must be able to "see" the database before accessing objects within it), while TRUNCATE ON FUTURE TABLES ensures the privilege automatically applies to newly created tables in the schema - critical for an ETL role in an evolving data pipeline.

Why the distractors fail:

A is the most dangerous: ALL PRIVILEGES ON SCHEMA massively over-grants, violating least privilege by giving far more than just TRUNCATE capability.
B lacks the USAGE ON DATABASE grant required to navigate the Snowflake object hierarchy, and ALL TABLES only covers currently existing tables - new ones added later would be missed.
C similarly omits the USAGE ON DATABASE requirement, making the grant functionally incomplete even if syntactically valid.
D includes the correct USAGE grant but uses ALL TABLES instead of FUTURE TABLES, meaning any new table added to the schema won't inherit the privilege automatically - requiring repeated manual grants.

Memory tip: Think "USAGE + FUTURE = set it and forget it." In Snowflake, FUTURE grants are the proactive approach - they auto-apply to new objects, which is exactly what a long-running ETL role needs. If you see an option missing USAGE ON DATABASE, eliminate it immediately, as Snowflake's privilege hierarchy always requires it.

Topics

#RBAC#Privilege grants#Table truncation#Least privilege

Community Discussion

No community discussion yet for this question.

Full SOL-C01 Practice