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

Question

A data engineer needs to grant a group of analysts the ability to query data in the 'SALES DB.PUBLIC.ORDERS table, but prevent them from seeing any personally identifiable information (PII) in the 'CUSTOMER NAME column. Furthermore, the analysts should be able to create temporary tables based on the data. Which of the following steps would BEST accomplish this?

Options

• AGrant the `SELECT privilege on the 'SALES DB.PUBLIC.ORDERS table to a custom role, then
• BGrant the 'SELECT privilege on the 'SALES DB.PUBLIC.ORDERS table and 'CREATE
• CGrant the `SELECT privilege on the 'SALES DB.PUBLIC.ORDERS table to a custom role, create a
• DGrant the `SELECT privilege on the 'SALES DB.PUBLIC.ORDERS table to a custom role. Create
• EGrant the `SELECT privilege on the 'SALES DB.PUBLIC.ORDERS table to a custom role. Create

Explanation

Option A is the most secure and efficient. Granting 'SELECT on the base table and then creating a view ensures the analysts only see the data they are authorized to see. Secure views provide extra security by preventing unauthorized access even through side-channel attacks. Additionally, 'CREATE TEMPORARY TABLE grants the necessary permission for analysts to create temporary tables for their analysis. Masking policy requires the APPLY MASKING POLICY privilege which isn't a common practice for analysts, Option B uses PUBLIC role which is bad practice. Option D is incorrect because analysts need to create TEMPORARY tables, not persistent ones.

No community discussion yet for this question.