Datawise
Practical SQL

Using subqueries with Row Level Security in BigQuery

Constantin LunguUpdated 1 min read

Photo by FlyD on Unsplash

I've previously posted about row-level security in BigQuery.

One of the gotchas back then was the fact that you needed to manually specify what values should the access be filtered for. If only a sub-query was allowed there!

Well, a new feature, currently in preview, allows for it, even though the CREATE ROW ACCESS POLICY DDL reference still says it's not.

Now, one can refer to a lookup table and use that in conjunction with the SESSION_USER function (which we've also previously looked at) to filter the values that a principal should see.

The result is the same, but this adds a degree of simplicity and easiness when managing row-level access security in BigQuery.

Input data: a customer table with CustomerId, FirstName, LastName, Country and FirstOrderDate, four rows: Michelle Dubois (FR), Jane Springer (UK), Bianca Moretti (IT) and John Doe (US).

CREATE ROW ACCESS POLICY us_uk_policy ON `learning.customer_data`

GRANT TO ('serviceAccount:test-rowlevel-security@<redacted>.iam.gserviceaccount.com')

FILTER USING (country IN ('US', 'UK'));

NEW (in preview):

CREATE ROW ACCESS POLICY us_uk_policy ON `learning.customer_data`

GRANT TO ('serviceAccount:test-rowlevel-security@<redacted>.iam.gserviceaccount.com')

FILTER USING (country IN (
    SELECT
      country
    FROM
      `<redacted>.learning.lookup_table`
    LEFT JOIN UNNEST(country_list) AS country
    WHERE
      user_principal = SESSION_USER()));

Preview of lookup_table: one row whose user_principal is the test-rowlevel-security service account (project id hidden) and whose country_list holds US and UK.

Output: what the service account sees, only customer 4 Jane Springer (UK, 2022-03-01) and customer 1 John Doe (US, 2021-01-01).

Enjoyed this? Here are some related articles you might find useful: