Controlling Access In BigQuery

16 August 2021

Bigquery allows us to control which user or group has access to which data in BigQuery. The admin has to give permission by granting roles to a service account/user/group.

There are three Resource levels in Bigquery:

• Organization or GCP Project Level
• Dataset Level
• Table or View Level

Organization Level: 

This level provides permission to run the BigQuery Jobs and access all of a Project’s BigQuery Resources. When a project is created, BigQuery grants the Owner role to the user who created the project.

Dataset Level: 

In this level, a user/group/service account gets permission to access the tables, views and the table data in a dataset.

Access Controls can be applied after the dataset is created through Cloud Console, Command line tool (bq), API Method, Client Libraries and by using ‘grant’ and ‘revoke’ DCL statements.

Table or View Level: 

BigQuery allows you to set table-level permissions on tables and views.

Here a user or a group or a service account can access a table or view without having complete access to the entire dataset.

You can use the Access Control Policy through Identity and Access Management Policy.

The default way to give access to a table through the cloud console is by clicking on the table’s schema and sharing the table by adding the members and granting them roles.

Also, if you want to give access to some filtered data, first run the query and then store the results to a new table in a dataset. Later, grant roles to the members you want to give access.

Apart from these access control levels, BigQuery provides row-level security and column-level security as well.

• In Row-level security,  you can filter the data and enable access to specific rows in a table, based on qualifying user conditions.
  
• Column-level security, provides fine-grained access to sensitive columns using policy tags, or type-based classification, of data.