Authorized views and materialized views

This document describes how to create authorized views and materialized views in BigQuery.

Authorized views and authorized materialized views let you share query results with particular users and groups without giving them access to the underlying source data. The view or materialized view is given access to the data, instead of the user. You can also use the SQL query that creates the view or materialized view to restrict the columns and fields that users are able to query.

When making an authorized view or materialized view in another dataset, both the source data dataset and authorized view dataset must be in the same regional location.

For information about authorizing all of the views in a dataset, as opposed to authorizing individual views, see Authorized datasets.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.

Required permissions

To create or update an authorized view, you need permissions on the dataset that contains the view and on the dataset that provides access to the view.

Permissions on the dataset that contains the view

Views are treated as table resources in BigQuery, so creating a view requires the same permissions as creating a table. You must also have permissions to query any tables that are referenced by the view's SQL query.

To create a view, you need the bigquery.tables.create IAM permission. The roles/bigquery.dataEditor predefined IAM role includes the permissions that you need to create a view.

Additionally, if you have the bigquery.datasets.create permission, you can create views in the datasets that you create. To create a view for data that you don't own, you must have bigquery.tables.getData permission for that table.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Permissions on the dataset that gives access to the view

To update dataset properties, you need the following IAM permissions:

bigquery.datasets.update
bigquery.datasets.setIamPolicy (only required when updating dataset access controls in the Google Cloud console)

The roles/bigquery.dataOwner predefined IAM role includes the permissions that you need to update dataset properties.

Additionally, if you have the bigquery.datasets.create permission, you can update properties of the datasets that you create.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Authorize a view

To grant a view access to a dataset, follow these steps:

Console

Go to the BigQuery page in the Google Cloud console.

Go to BigQuery
In the Explorer pane, expand your project and select a dataset.
Click View actions and then click Open.
In the Dataset info pane, click Sharing and then select Authorize Views.
For Authorize view, type the name of the view to authorize.
Click Add authorization.
Click Close.

bq

Write the existing dataset information (including access controls) to a JSON file using the bq show command. If the dataset is in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.
```
bq show \
--format=prettyjson \
project_id:dataset > path_to_file
```
Where:
- project_id is your project ID.
- dataset is the name of your dataset.
- path_to_file is the path to the JSON file on your local machine.
Examples:

Enter the following command to write the access controls for mydataset to a JSON file. mydataset is in your default project.
```
bq show --format=prettyjson mydataset > /tmp/mydataset.json
```
Enter the following command to write the access controls for mydataset to a JSON file. mydataset is in myotherproject.
```
bq show --format=prettyjson \
myotherproject:mydataset > /tmp/mydataset.json
```

Add the authorized view to the "access" section of the JSON file.

For example, the access section of a dataset's JSON file would look like the following:

{
 "access": [
  {
   "role": "READER",
   "specialGroup": "projectReaders"
  },
  {
   "role": "WRITER",
   "specialGroup": "projectWriters"
  },
  {
   "role": "OWNER",
   "specialGroup": "projectOwners"
  }
  {
   "role": "READER",
   "specialGroup": "allAuthenticatedUsers"
  }
  {
   "role": "READER",
   "domain": "[DOMAIN_NAME]"
  }
  {
   "role": "WRITER",
   "userByEmail": "[USER_EMAIL]"
  }
  {
   "role": "READER",
   "groupByEmail": "[GROUP_EMAIL]"
  },
  {
   "view":{
   "datasetId": "[DATASET_NAME]",
   "projectId": "[PROJECT_NAME]",
   "tableId": "[VIEW_NAME]"
   }
  }
 ],
}

When your edits are complete, use the bq update command and include the JSON file using the --source flag. If the dataset is in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.

Caution: When you apply the JSON file that contains the access controls, the existing access controls are overwritten.
```
bq update \
--source path_to_file \
project_id:dataset
```
Where:
- path_to_file is the path to the JSON file on your local machine.
- project_id is your project ID.
- dataset is the name of your dataset.
Examples:

Enter the following command to update the access controls for mydataset. mydataset is in your default project.
```
 bq update --source /tmp/mydataset.json mydataset
```
Enter the following command to update the access controls for mydataset. mydataset is in myotherproject.
```
 bq update --source /tmp/mydataset.json myotherproject:mydataset
```
To verify your access control changes, enter the show command again without writing the information to a file.
```
bq show --format=prettyjson [DATASET]
```
or
```
bq show --format=prettyjson [PROJECT_ID]:[DATASET]
```

API

Call the datasets.patch and use the access property to update your access controls. For more information, see Datasets.

Because the datasets.update method replaces the entire dataset resource, datasets.patch is the preferred method for updating access controls.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// updateViewDelegated demonstrates the setup of an authorized view, which allows access to a view's results
// without the caller having direct access to the underlying source data.
func updateViewDelegated(projectID, srcDatasetID, viewDatasetID, viewID string) error {
	// projectID := "my-project-id"
	// srcDatasetID := "sourcedata"
	// viewDatasetID := "views"
	// viewID := "myview"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	srcDataset := client.Dataset(srcDatasetID)
	viewDataset := client.Dataset(viewDatasetID)
	view := viewDataset.Table(viewID)

	// First, we'll add a group to the ACL for the dataset containing the view.  This will allow users within
	// that group to query the view, but they must have direct access to any tables referenced by the view.
	vMeta, err := viewDataset.Metadata(ctx)
	if err != nil {
		return err
	}
	vUpdateMeta := bigquery.DatasetMetadataToUpdate{
		Access: append(vMeta.Access, &bigquery.AccessEntry{
			Role:       bigquery.ReaderRole,
			EntityType: bigquery.GroupEmailEntity,
			Entity:     "[email protected]",
		}),
	}
	if _, err := viewDataset.Update(ctx, vUpdateMeta, vMeta.ETag); err != nil {
		return err
	}

	// Now, we'll authorize a specific view against a source dataset, delegating access enforcement.
	// Once this has been completed, members of the group previously added to the view dataset's ACL
	// no longer require access to the source dataset to successfully query the view.
	srcMeta, err := srcDataset.Metadata(ctx)
	if err != nil {
		return err
	}
	srcUpdateMeta := bigquery.DatasetMetadataToUpdate{
		Access: append(srcMeta.Access, &bigquery.AccessEntry{
			EntityType: bigquery.ViewEntity,
			View:       view,
		}),
	}
	if _, err := srcDataset.Update(ctx, srcUpdateMeta, srcMeta.ETag); err != nil {
		return err
	}
	return nil
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import com.google.cloud.bigquery.Acl;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;
import com.google.cloud.bigquery.DatasetId;
import com.google.cloud.bigquery.Table;
import java.util.ArrayList;
import java.util.List;

// Sample to grant view access on dataset
public class GrantViewAccess {

  public static void runGrantViewAccess() {
    // TODO(developer): Replace these variables before running the sample.
    String srcDatasetId = "MY_DATASET_ID";
    String viewDatasetId = "MY_VIEW_DATASET_ID";
    String viewId = "MY_VIEW_ID";
    grantViewAccess(srcDatasetId, viewDatasetId, viewId);
  }

  public static void grantViewAccess(String srcDatasetId, String viewDatasetId, String viewId) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Dataset srcDataset = bigquery.getDataset(DatasetId.of(srcDatasetId));
      Dataset viewDataset = bigquery.getDataset(DatasetId.of(viewDatasetId));
      Table view = viewDataset.get(viewId);

      // First, we'll add a group to the ACL for the dataset containing the view. This will allow
      // users within that group to query the view, but they must have direct access to any tables
      // referenced by the view.
      List<Acl> viewAcl = new ArrayList<>();
      viewAcl.addAll(viewDataset.getAcl());
      viewAcl.add(Acl.of(new Acl.Group("[email protected]"), Acl.Role.READER));
      viewDataset.toBuilder().setAcl(viewAcl).build().update();

      // Now, we'll authorize a specific view against a source dataset, delegating access
      // enforcement. Once this has been completed, members of the group previously added to the
      // view dataset's ACL no longer require access to the source dataset to successfully query the
      // view
      List<Acl> srcAcl = new ArrayList<>();
      srcAcl.addAll(srcDataset.getAcl());
      srcAcl.add(Acl.of(new Acl.View(view.getTableId())));
      srcDataset.toBuilder().setAcl(srcAcl).build().update();
      System.out.println("Grant view access successfully");
    } catch (BigQueryException e) {
      System.out.println("Grant view access was not success. \n" + e.toString());
    }
  }
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

from google.cloud import bigquery

client = bigquery.Client()

# To use a view, the analyst requires ACLs to both the view and the source
# table. Create an authorized view to allow an analyst to use a view
# without direct access permissions to the source table.
view_dataset_id = "my-project.my_view_dataset"
# Make an API request to get the view dataset ACLs.
view_dataset = client.get_dataset(view_dataset_id)

analyst_group_email = "[email protected]"
access_entries = view_dataset.access_entries
access_entries.append(
    bigquery.AccessEntry("READER", "groupByEmail", analyst_group_email)
)
view_dataset.access_entries = access_entries

# Make an API request to update the ACLs property of the view dataset.
view_dataset = client.update_dataset(view_dataset, ["access_entries"])
print(f"Access to view: {view_dataset.access_entries}")

# Group members of "[email protected]" now have access to the view,
# but they require access to the source table to use it. To remove this
# restriction, authorize the view to access the source dataset.
source_dataset_id = "my-project.my_source_dataset"
# Make an API request to set the source dataset ACLs.
source_dataset = client.get_dataset(source_dataset_id)

view_reference = {
    "projectId": "my-project",
    "datasetId": "my_view_dataset",
    "tableId": "my_authorized_view",
}
access_entries = source_dataset.access_entries
access_entries.append(bigquery.AccessEntry(None, "view", view_reference))
source_dataset.access_entries = access_entries

# Make an API request to update the ACLs property of the source dataset.
source_dataset = client.update_dataset(source_dataset, ["access_entries"])
print(f"Access to source: {source_dataset.access_entries}")

Remove authorization to a view

To remove authorization to a view:

Console

Go to the BigQuery page in the Google Cloud console.

Go to BigQuery
In the Explorer pane, expand your project and select a dataset.
Click Sharing > Authorize views.
Click Remove authorization.
Click Close.

Quotas and limits

Authorized views are subject to dataset limits. For more information, see Dataset limits.
If you remove an authorized view, it can take up to 24 hours for all references to the view to be removed from the system. To avoid errors, either wait 24 hours before reusing the name of a removed view, or create a unique name for your view.

Enforce row-level access with a view

Views can be used to restrict access to particular columns (fields). If you want to restrict access to individual rows in your table, you do not need to create separate views for each user or group. Instead, you can use the SESSION_USER() function to return the email address of the current user.

To display different rows to different users, add another field to your table containing the user who is allowed to see the row. Then, create a view that uses the SESSION_USER() function. In the following example, the usernames are stored in the allowed_viewer field:

SELECT
  COLUMN_1,
  COLUMN_2
FROM
  `dataset.view`
WHERE
  allowed_viewer = SESSION_USER()

The limitation of this approach is that you can grant access to only one user at a time. You can work around this limitation by making allowed_viewer a repeated field. This approach lets you provide a list of users for each row. But even if you use a repeated field, storing usernames in the table still requires you to manually track the individual users that have access to each row.

Instead, populate the allowed_viewer field with group names, and create a separate table that maps groups to users. The table that maps groups to users would have a schema that stores group names and usernames. For example: {group:string, user_name:string}. This approach lets you manage the user and group information separately from the table that contains the data.

If the mapping table is named private.access_control, the SQL query used to create the authorized view would be:

SELECT
  c.customer,
  c.id
FROM
  `private.customers` c
INNER JOIN (
  SELECT
    group
  FROM
    `private.access_control`
  WHERE
    SESSION_USER() = user_name) g
ON
  c.allowed_group = g.group

What's next

For a tutorial on creating an authorized view, see Create an authorized view.
For information on creating views, see Create views.
For information on creating materialized views, see Create materialized views.
For information on getting view metadata, see Get information about views.
For more information on managing views, see Manage views.
For information about authorizing all of the views in a dataset, see Authorized datasets.