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

Question

You have a Snowflake database named 'SALES DB' containing schemas 'RAW' and 'TRANSFORMED'. The 'RAW' schema holds raw data ingested from various sources, and the 'TRANSFORMED' schema contains cleansed and transformed data ready for reporting. You need to grant the 'DATA ANALYST' role the ability to read all tables in the 'TRANSFORMED' schema but prevent them from modifying any objects. What is the MOST efficient and secure way to achieve this?

Options

• AGRANT SELECT ON ALL TABLES IN SCHEMA SALES DB.TRANSFORMED TO ROLE DATA
• BGRANT USAGE ON DATABASE SALES DB TO ROLE DATA ANALYST; GRANT SELECT ON
• CGRANT USAGE ON DATABASE SALES DB TO ROLE DATA ANALYST; GRANT USAGE ON
• DGRANT ALL PRIVILEGES ON SCHEMA SALES DB.TRANSFORMED TO ROLE DATA
• EGRANT SELECT ON SCHEMA SALES DB.TRANSFORMED TO ROLE DATA ANALYST;

Explanation

The most efficient and secure approach is to grant USAGE on the DATABASE and SCHEMA, and SELECT on FUTURE TABLES (C). USAGE privilege on the database allows the role to access objects within the database. USAGE on the schema allows access to the schema. Granting SELECT on FUTURE TABLES ensures that any new tables created in the TRANSFORMED schema will automatically inherit the SELECT privilege for the DATA ANALYST role. Option A only grants SELECT on existing tables, not future ones. Option D grants ALL PRIVILEGES, which is too permissive. Option E is insufficient as it doesn't grant required privileges on Database and Schema. Option B is partially correct but misses granting usage on

No community discussion yet for this question.