COF-C03 Question 178
Single answerRow-level securityA retail company stores all regional sales records in a single SALES table. Analysts in the EAST role should see only rows where REGION = 'EAST', and analysts in the WEST role should see only rows where REGION = 'WEST'. The company wants this restriction enforced automatically for all queries against the table, without requiring users to add filters in their SQL. Which approach should be implemented?
- A
Create a row access policy that checks CURRENT_ROLE() and attach it to the SALES table on the REGION column
- B
Create a masking policy on the REGION column so unauthorized rows are hidden from users in other roles
- C
Grant SELECT on the SALES table only to the EAST and WEST roles, and rely on analysts to filter rows in their queries
- D
Create a secure view over SALES and grant access to the view; secure views automatically filter rows by role without additional logic
Show answer and explanation
Correct answer: A
Explanation
For row-level security in Snowflake, the correct mechanism is a row access policy. Row access policies evaluate an expression for each row and determine whether that row is returned to the querying role. This allows organizations to centralize row-level security logic and apply it directly to a table or view. In this scenario, a policy using CURRENT_ROLE() with REGION-based logic is the most direct and maintainable solution. Masking policies address column-value exposure, not row visibility. Standard GRANTs only control access to the object as a whole. Secure views can help with security and data sharing patterns, but they do not inherently provide automatic row filtering unless such logic is explicitly built into the view. This aligns with Snowflake best practices and documentation for row access policies and the distinction between row access policies, masking policies, and secure views.
- A. Correct.
Correct. A row access policy is Snowflake's native feature for row-level security. It can evaluate context functions such as CURRENT_ROLE() and return a Boolean condition that determines whether each row is visible. Attaching the policy to the SALES table on the REGION column enforces row filtering automatically for all queries against that protected object, which matches the requirement.
- B. Incorrect.
Incorrect. A masking policy is designed to obfuscate or transform column values, not remove rows from the result set. Even if REGION were masked, unauthorized users could still access rows they should not see. This is a common confusion between dynamic data masking and row-level security.
- C. Incorrect.
Incorrect. Granting SELECT controls object-level access, not row-level access. If both roles have SELECT on the same table, each role can query all rows unless additional controls are implemented. Relying on users to manually add WHERE clauses does not enforce security and does not meet the requirement for automatic restriction.
- D. Incorrect.
Incorrect. A secure view protects the view definition and can be used as part of a security design, but it does not automatically apply role-based row filtering by itself. To enforce row-level restrictions in a view, explicit filtering logic would still need to be written. The statement that secure views automatically filter rows by role is false.