Restrict Query Results With Row Level Security In BigQuery

  • Post last modified:July 16, 2022
  • Reading time:2 mins read

Introduction

BigQuery released Row level security feature to provide granular access controls. In this blog, we will use this feature to protect our query results based on certain conditions. Let’s get started!

BigQuery Data Source

Our BigQuery dataset consists of top headlines information that I have collected from News API.
We will use BigQuery’s new feature called Row Level Security Policy which will allow analysts to query data based on the assigned news source. For example analyst A will only get news data from “CNN” while analyst B will get data from source “CNBC”.

Creating Row-level Security Policy

Let’s create a row-based policy that will allow users to run queries on a subset of data based on news source “CNN”. That means the granted user will not be able to see the news source from another source.

If you try to see a preview of the table it will show you access denied based on the row access policy that we created before.

Now Lets Query the table and confirm if we can only get news from Source = “CNN”;

Our query only returns the result from source = “CNN”. That means the row-level policy is working.

If the user tries to access news from another source such as the “CNBC” query will return an empty result. And we can confirm it by looking at additional information which says “Your query results may be limited because you don’t have access to certain rows

Conclusion

Google BigQuery already supports the project, dataset, and table-level restrictions. Additionally, it also supports column-level security. But the release of row-level security extends the concept of least privilege by providing fine-grained controls on result queries.

Please try this feature and let me know your experience with Bigquery in the comments.
Happy analyzing! ✌️

Leave a Reply