ADA-C01 Question 37
Single answer1.4 Given a scenario, fine-tune access controls.A Snowflake administrator is onboarding a contractor team that must query only a masked version of the CUSTOMERS table in the PROD_DB.SALES schema. The team should be able to use an existing virtual warehouse for queries, but must not be able to see unmasked PII values, change masking policies, or grant access to other roles. The contractor users have been assigned the role CONTRACTOR_ANALYST. Which approach best meets these requirements while following least-privilege practices?
- A
Grant CONTRACTOR_ANALYST the SELECT privilege on PROD_DB.SALES.CUSTOMERS and the APPLY MASKING POLICY privilege on the schema so the role can use the masked columns without modifying the policy.
- B
Grant CONTRACTOR_ANALYST the imported privileges on the database, USAGE on the warehouse, and OWNERSHIP on the masking policy so the role can query the table but not change data.
- C
Grant CONTRACTOR_ANALYST USAGE on the database and schema, SELECT on PROD_DB.SALES.CUSTOMERS, and USAGE on the existing warehouse. Ensure the masking policy is already attached to the sensitive columns and do not grant OWNERSHIP or policy administration privileges to the role.
- D
Create a secure view over PROD_DB.SALES.CUSTOMERS and grant CONTRACTOR_ANALYST OWNERSHIP on the view instead of SELECT on the base table, because OWNERSHIP prevents the role from re-granting access.
- E
Grant CONTRACTOR_ANALYST REFERENCES on the masked columns and USAGE on the warehouse, because REFERENCES allows querying protected columns without exposing the underlying policy.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to grant only the minimum object privileges required for querying: USAGE on the database, USAGE on the schema, SELECT on the table, and USAGE on the warehouse. Snowflake masking policies are enforced transparently at query time once attached to columns, so roles that merely consume masked data do not need privileges like APPLY MASKING POLICY or OWNERSHIP on the policy. This is consistent with Snowflake best practices for least-privilege RBAC design: grant object access for data consumption separately from object administration. Also, OWNERSHIP should be tightly controlled because it confers full control and the ability to re-grant. Relevant Snowflake documentation areas include access control privileges, masking policies, and role-based access control guidance.
- A. Incorrect.
Incorrect. A consumer role that only queries masked data does not need APPLY MASKING POLICY. That privilege is used to set a masking policy on a table or view column, not to query data protected by an already attached policy. Granting it would exceed least privilege and could allow the role to participate in policy application workflows.
- B. Incorrect.
Incorrect. Imported privileges are used for objects such as shared databases and are not the right mechanism for standard access to a regular local database in this scenario. More importantly, granting OWNERSHIP on a masking policy is highly privileged and would allow the role to transfer ownership or manage the object, which violates the requirement that contractors must not change masking policies or delegate access.
- C. Correct.
Correct. To query a table in Snowflake, the role needs USAGE on the database and schema, SELECT on the table, and USAGE on a warehouse for compute. If a masking policy is already attached to sensitive columns, Snowflake evaluates that policy at query time based on the active role and policy logic. The querying role does not need privileges to administer the masking policy. Avoiding OWNERSHIP and grant-management privileges aligns with least-privilege access control.
- D. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and includes full control, including the ability to grant privileges on that object to other roles. Giving contractors OWNERSHIP on a secure view would directly conflict with the requirement that they must not be able to grant access to others. A secure view can be a valid design pattern for limiting exposure, but OWNERSHIP is not the correct privilege to grant here.
- E. Incorrect.
Incorrect. REFERENCES does not provide query access to table data in this scenario. It is used in more limited contexts, such as enabling certain object relationships, and does not replace SELECT for reading table contents. A role still needs SELECT to query the table or view.