Access Control in BigQuery

Introduction

This article explains how we can control access in Big Query. While working on a GCP project in an organization ,security plays a vital role. As a best practice, we should follow the approach of assigning the least privileges' to the user. Once a user is having project-level IAM role for Big Query services then the user will be able to access all datasets/tables/views by default. We are going to see how we can restrict access at the Dataset level, at the Table level & also at the column and row level.

Restrict Access at Dataset Level

1. Go to cloud console and navigate to Bigquery console.
2. Expand the project to see available datasets.

3. Open Dataset e.g Demo in this demo

4. Click on sharing

5. Click on Add Principal

6. Enter the user email id and assign a required role.

7. Now verify access with user and user only able to see Demo dataset.

In this way, we can restrict access at the Dataset level.

Restrict Access at Table Level

Now suppose we have a requirement that we want the user to see only data from table-1 and not from table-2, then we need to restrict access at table level.

1. Go to table-1 and click on share

2. Click on Add Principal

3. Enter user email and assign a required role.

4. Note that after this step, you may not be able to see the table in Data Explorer but you will have access to the table. Try running queries on the table.

Note — If getting error on running query add role BigQuery Job User from IAM.

Able to access Table-1

As we only provided access to table-1, running a query on table-2 will throw an error.

Restrict Access at Column Level

Now we could see table-1 is having below three-column and the last column anonymous_string contains sensitive data we want to restrict this column to specific users only.

In this case, we need to create a view with the required column.

1. Create new dataset Test
2. After running a query, click the Save view button above the query results window to save the query as a view.

Give the dataset name we created i.e Test and desired view name i.e v_table-1

Once the view is created, add user access to View.

User is able to see the data from table-1 except sensitive column based on view.

Please find below video for demo.