Google Cloud Platform – Data Security in BigQuery

 One of the benefits of a data warehouse, like BigQuery, is the improved simplicity and speed of bringing data to your analysts and decision-makers. Data needs to vary across a company based on organizational function, geography, and more, so it’s important to be able to provide customized access to your users without adding unnecessary risk. 

In this article, we’re going to look into how to create authorized views, which give you the ability to define and share a specific subset or view of your BigQuery data. BigQuery organizes data into containers called datasets. These datasets function like top-level folders that organize and control access to the underlying tables. 

IAM can restrict access down to the table level. But what if you want to restrict access for members to parts of a table? In BigQuery, one way to accomplish this task is by creating authorized views. An authorized view allows you to share the results of a query with a specific user or group without giving that user access to the tables that were queried. For example, let’s say as a BigQuery data admin, you wanted to give an analyst at your company access to a table containing your service request data, but you wanted to remove the full address of the service location. You could write a query that didn’t include the address column and then save the results as an authorized view to share with the analyst. 

Creating an authorized view follows a defined series of steps.  To define an authorized view follow the below steps:

Step 1: Start with your source dataset. This is the dataset with the sensitive data you don’t want to share. 

Step 2: Create a separate dataset to store the view. Authorized views require the source data to sit in a separate dataset from the view.

Step 3: Create a view in the new dataset. In the new dataset, you create the view you intend to share with your data analysts. This view is created using an SQL query that includes only the data the analysts need to see. 

Step 4: Assign access controls to the project. In order to query the view, your analyst needs permission to run queries. Assigning your analyst the BigQuery user role gives them this ability. This access does not give them the ability to view or query any datasets within the project. 

Step 5: Assign access controls to the dataset containing the view. In order for your analysts to query the view, they need to be granted the BigQuery data viewer role for that specific dataset that contains the view. 

Step 6: Authorize the view to access the source dataset. This gives the view itself access to the source data. We need to do this because the view takes on the permissions of the person using it, and since the analyst doesn’t have access to the source table, they’d otherwise get an error if they tried to query this view.