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

Question

A data engineer needs to grant a business analyst role ('BI ANALYST) the ability to query data in a specific schema ('SALES DATA) within a database ('REPORTING DB'). The business analyst should also be able to create temporary tables for their analysis but should not be able to modify the underlying tables. Which of the following set of commands is the MOST SECURE and LEAST PRIVILEGED way to achieve this?

Options

• AGRANT USAGE ON DATABASE REPORTING DB TO ROLE BI ANALYST; GRANT SELECT ON
• BGRANT USAGE ON DATABASE REPORTING DB TO ROLE BI ANALYST; GRANT SELECT ON
• CGRANT ALL ON DATABASE REPORTING DB TO ROLE BI ANALYST; GRANT SELECT ON ALL
• DGRANT USAGE ON DATABASE REPORTING DB TO ROLE BI ANALYST; GRANT USAGE ON
• EGRANT USAGE ON DATABASE REPORTING DB TO ROLE BI ANALYST; GRANT SELECT ON

Explanation

Option D provides the most secure and least privileged access. USAGE on the database and schema allows access without full control. SELECT on tables allows querying. CREATE TEMPORARY TABLE allows the creation of temporary tables within the database. Granting SELECT ON FUTURE TABLES would grant access to tables that don't exist yet, unnecessary for this scenario. Option A does not grant USAGE on the schema. Option C is overly permissive.

No community discussion yet for this question.