Using BigQuery Row Level Security on an Entire Table

Problem Statement

I was recently working on a Google Cloud project where we needed to secure data on one specific table so only two people could see the data on that table. Problem was, that everyone with access to the project had an immense amount of Admin access.

Solution

What we landed on was to use BigQuery’s Row Level Security to hide ALL rows of the table from everyone except the two identified people in the company.

Setup

First you will need to craft a DDL statement where you will need 4 things:

A name for the policy (something that describes what you are doing
The table name in the format `project.dataset.table`
Representation of the 2 users who can have access
- This can be a group representing the 2 users
- Or this can be the 2 users listed out (example below)
A where clause that will return the entire table

So for my demo environment this was:

CREATE ROW ACCESS POLICY bank_additional_demo
ON `financial-data-demo.bank_additional.bank-additional-full`
GRANT TO (‘user:admin@example.com’)
FILTER USING (age>0);

NOTE: you can either use groups or lists of users. This example from the documentation:

CREATE ROW ACCESS POLICY sales_us_filter
ON project.dataset.my_table
GRANT TO ('user:john@example.com',
          'group:sales-us@example.com',
          'group:sales-managers@example.com')
FILTER USING (region = 'US');

From: https://cloud.google.com/bigquery/docs/managing-row-level-security#examples

NOTE: you will see this Access Policy going forward if you click on the white button at the bottom “VIEW ROW ACCESS POLICIES” on the table “SCHEMA” page.

I granted to my Admin all rows with age higher than 0. And my Admin user can still see all of the data in the Preview in BigQuery (and can query all of the data).

Now in IAM I have granted another user “Developer Blythe” both “Owner” and “BigQuery Admin”, so this user should be able to see all BigQuery data (other than what we just restricted… which we will see in a minute).

When I log into that user and attempt to look at the data, I see:

NOTE: I am now logging into another Chrome Tab with a different user (this one with a purple banner representing a Developer, the previous was an Admin with an orange banner)