SOL-C01 Question #32: Real Exam Question with Answer & Explanation

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

Explanation

Option E provides the most secure and flexible approach. It grants 'USAGE on the database (required to access the schema) and then grants the 'TRUNCATE' privilege on future tables in the specified schema. This means that any new tables created in the 'RAW_DATA' schema will automatically inherit the TRUNCATE privilege for the 'ETL_ROLE'. This avoids the need to manually grant `TRUNCATE each time a new table is created. Using 'future grants' ensures maintainability. Option A grants all privileges on the schema, violating the principle of least privilege. Option B only grants truncate on currently existing tables. Option D grants truncate on ALL tables in the RAW DATA schema. It doesn't grant truncate to tables that don't currently exist but may exist in the future.

No community discussion yet for this question.