A company uses BigQuery for analytics and needs to enforce row-level security based on user department. Only users from the 'Sales' department should see rows where department = 'Sales'. Which BigQuery feature should they use?
Trap 1: Custom IAM roles with fine-grained permissions
Custom IAM roles grant or deny actions on datasets, tables and jobs; they cannot express a predicate such as department = 'Sales' inside query results. They suit coarse permission boundaries, whereas row-level security policies evaluate each user's rows at query time.
Trap 2: Column-level security with classification tags
Column-level security with classification tags restricts which columns a user may read, not which rows; department-based filtering of rows is untouched. It is the right choice when specific fields, such as salary or national insurance number, must be hidden from certain groups.
Trap 3: Authorized views with a WHERE clause
Authorized views grant access to a whole view, so every authorised user sees identical rows; they cannot vary output by the viewer's department. They are correct for sharing a curated subset with a group, not for per-user row filtering, which requires row-level access policies.
- A
Custom IAM roles with fine-grained permissions
Why it fails: Custom IAM roles grant or deny actions on datasets, tables and jobs; they cannot express a predicate such as department = 'Sales' inside query results. They suit coarse permission boundaries, whereas row-level security policies evaluate each user's rows at query time.
- B
Column-level security with classification tags
Why it fails: Column-level security with classification tags restricts which columns a user may read, not which rows; department-based filtering of rows is untouched. It is the right choice when specific fields, such as salary or national insurance number, must be hidden from certain groups.
- C
Authorized views with a WHERE clause
Why it fails: Authorized views grant access to a whole view, so every authorised user sees identical rows; they cannot vary output by the viewer's department. They are correct for sharing a curated subset with a group, not for per-user row filtering, which requires row-level access policies.
- D
Row-level access policies using a filter on the department column
Row-level access policies apply filter predicates on columns such as department, restricting each user to rows matching their department. This satisfies the requirement that Sales users see only department = 'Sales' rows, which column-level or dataset-level controls cannot achieve.