GCP — BigQuery — Data Security at rest (Part 5)
This is last part of the blog where we will be discussing on the row level access policies which restricts the access of rows being queried to principals as per policy
This blog is a part of 5 part series on “Bigquery — Data Security at rest”.
Visit here for part 4 of the series
Visit here for the series menu
Row Level Access Policies
The row level access policies extends the capability of Bigquery to allow security controls based on filter criteria. This control can be used in tandem with Bigquery column security controls and IAM policies applied.
lets consider the same table that we had used in the previous blog to explain the column level security with policy tags.
We have added another column policy tag on company that allows principal to see the masked values in company
Currently the user is only able to query the below columns and check the data
We will be creating Row Access policy to allow the principal to view data from “Ozu” company only.
CREATE ROW ACCESS POLICY ozu_filter
ON `<redacted>.data_security_demo_raw.customers_raw`
GRANT TO ('user:training-user-1@<redacted>')
FILTER USING (company = 'Ozu');
The above result shows the row access policy applied on the column followed by the column level security of hashing applied on company.
The intent was to showcase both row level access policies and column level policies can be applied together.
Let’s see a different case to understand the effective policies applied.
Lets create another row filter which says allow access on records with gender = “Male”
CREATE OR REPLACE ROW ACCESS POLICY gender_filter
ON `<redacted>.customers_raw`
GRANT TO ('user:training-user-1@<redacted>')
FILTER USING (gender = 'Male');Also let’s remove the hashing rule on company for a while to check the results.
This lets me see the companies which are not Ozu as per the first row filter.
Important Note 1 — There is nothing broken here , the idea is row filters follow union of allowed results from both filters and provides the outcome.
So if you want to restrict on multiple conditions, combine them as part of single row policy using “and” conditions.Important Note 2 — Any table with Row access policies automatically denies access to every one else. Therefore it is necessary to have a explicit =True filter to allow unrestricted access for others. This also applies for performing any DML operations on entire table.
Important Note 3 — JSON columns do not support row access policies
Important Note 4 — BI engine acceleration do not work on table with multiple row access policies
Hope this series was helpful in getting insights on Bigquery security mechanisms at rest.
Please connect with me on https://www.linkedin.com/in/murli-krishnan-a1319842/ for any queries.
Happy Learning.
