COF-C03 Question 179
Single answerRow-level securityA company stores sales transactions in a single Snowflake table named SALES. Regional managers should only see rows for their own region, while users with the SECURITYADMIN role must be able to see all rows for audit investigations. The company wants this restriction enforced automatically for any query against the table, without requiring analysts to remember to add filters in their SQL. Which solution best meets this requirement?
- A
Create a row access policy on the SALES table that evaluates CURRENT_ROLE() and the REGION column, returning TRUE for SECURITYADMIN and for rows matching the manager's assigned region.
- B
Create a masking policy on the REGION column so unauthorized users see NULL for regions they should not access, and keep the SALES table otherwise unchanged.
- C
Create a secure view on top of SALES with a WHERE clause that filters by CURRENT_ROLE(), and grant users access only to the underlying SALES table for flexibility.
- D
Grant regional managers the global MANAGE GRANTS privilege and rely on role hierarchy so each manager inherits access only to rows for their region.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a row access policy attached to the SALES table. In Snowflake, row access policies provide centralized row-level security by evaluating a Boolean expression at query time to determine whether each row is visible to the querying context. This is the correct mechanism when access must be enforced automatically and consistently, without relying on users to remember WHERE clauses.
Masking policies are for column-level protection, not row filtering. Secure views can help implement filtered access patterns, but they are only effective if users access the view instead of the base table. Since the requirement is to protect the table itself for any query, a row access policy is the most direct and reliable solution. This aligns with Snowflake best practices for dynamic data governance using row access policies and context functions such as CURRENT_ROLE().
- A. Correct.
Correct. A row access policy is Snowflake's purpose-built feature for row-level security. When attached to a table or view, it is evaluated automatically for queries against that object, so users do not need to add manual filters. Using policy logic based on context functions such as CURRENT_ROLE() and row values such as REGION is a standard way to enforce per-role row visibility. The policy can explicitly allow SECURITYADMIN to see all rows while restricting regional managers to matching rows.
- B. Incorrect.
Incorrect. Masking policies protect sensitive column values, not row visibility. Returning NULL for REGION would still allow users to query the row and potentially infer or access other columns in the same record. This is a common misconception because both masking and row access policies are governance features, but only row access policies determine whether a row is visible at all.
- C. Incorrect.
Incorrect. A secure view can encapsulate filtering logic, but this option says users are granted access to the underlying SALES table. If users can query the base table directly, they can bypass the view's filter. The requirement is to enforce the restriction automatically for any query against the table, which is better satisfied by attaching a row access policy directly to the table.
- D. Incorrect.
Incorrect. Privileges and role hierarchy control object-level access, such as whether a user can query a table, but they do not filter individual rows within a table. MANAGE GRANTS is also a powerful administrative privilege unrelated to row-level security and would violate least-privilege principles in this scenario.