Using subqueries with Row Level Security in BigQuery

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.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

---

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

• Row-level access security in BigQuery
• Why basic roles in BigQuery are a bad idea
• Cross-dataset foreign key relationships in BigQuery