Authorized views

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

Overview

As a data administrator, you can create an authorized view to share a subset of data in a dataset to specific users and groups (principals). Principals can view the data you share and run queries on it, but they can't access the source dataset directly.

View types

A logical view is the default view type for BigQuery, and a materialized view is a precomputed view that periodically caches the results of a query for increased performance and efficiency.

An authorized view for a logical view is called an authorized view, but an authorized view for a materialized view is called an authorized materialized view.

If a logical view relies on a large or computationally expensive query, then you can create a materialized view instead. However, querying only a subset of your data or using other techniques can often improve performance without the need to create a materialized view.

For more information, see the following resources:

High-level steps for creating authorized views

To create and share a view, review these high-level steps, which are the same for authorized logical views and authorized materialized views.

Create a dataset to contain your source data.
Run a query to load data into a destination table in the source dataset.
Create a dataset to contain your authorized view.
Create an authorized view from a SQL query that restricts the columns that your data analysts can see in the query results.
Grant your data analysts permission to run query jobs.
Grant your data analysts access to the dataset that contains the authorized view.
Grant the authorized view access to the source dataset.

Alternatives

Although authorized views are flexible and scalable, one of the following methods might better apply to your use case:

Set row-level policies on a table.
Set column-level policies on a table.
Store data in a separate table.
Share all views in a dataset (authorized datasets).

Use row-level or column-level security, or separate tables

By setting row-level access policies on a table, or by creating a separate table to hold sensitive data, a data administrator can restrict a user's ability to view that data. Storing data in a separate table isolates the data and removes the ability to see how many rows exist in the table.

In addition, by creating and applying policy tags, a data administrator can restrict the user's ability to view columns in a table.

Storing data in a separate table is the most secure but least flexible method. Setting row-level policies is flexible and secure, while sharing authorized views is flexible and provides the best performance.

To compare these methods in detail, see the following resources:

If you want to give a collection of views access to a dataset without having to authorize each individual view, you can group the views together into a dataset, and then give the dataset that contains the views access to the dataset that contains the data.

You can then give principals access to the dataset containing the group of views, or to individual views in the dataset, as needed. A dataset that has access to another dataset is called an authorized dataset. The dataset that authorizes another dataset to access its data is called the shared dataset.

For more information, see Authorized datasets and Authorize a dataset.

Limitations

When you make an authorized view or authorized materialized view in another dataset, the source data dataset and authorized view dataset must be in the same regional location.
When you delete an authorized view, it can take up to 24 hours to remove the authorized view from the list of views. During this time, you cannot access the authorized view, but the deleted authorized view can appear in the list of views, and counts against the authorized view limit. This limit can prevent the creation of additional authorized views if the new authorized view would exceed that limit.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to query the authorized views or authorized materialized views that you share.

Required roles

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

You also need to grant users or groups access to the project and dataset that contain the view.

Admin 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.

Admin permissions on the second 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.

User permissions on the project and dataset for the view

To share an authorized view with users or groups, you must grant the users or groups the following IAM permissions:

The roles/bigquery.user IAM role to the project that contains the authorized view.
The roles/bigquery.dataViewer IAM role to the dataset that contains the authorized view.

Work with authorized views

The following sections describe how to work with authorized views and authorized materialized views.

Create, share, and delete an authorized view

For complete steps to authorize, share, and delete an authorized view, see the tutorial Create an authorized view.

Manage users or groups for authorized views

After authorizing a view, you can maintain access to it by completing the following tasks for a dataset, table, or view:

View the access policy.
Grant access.
Revoke access.
Deny access.

For more information, see Control access to resources using IAM.

Remove authorization to a view

To remove authorization to a view, select one of the following options:

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 to Remove authorization.
Click Close.

bq

To remove authorization from a view, use the bq rm command. Enter the table_id for the view you want to remove authorization from.

    bq rm \
    project_id:dataset:table_id

API

Call the tables.delete method and use the projectID,datasetID, and tableID properties to remove the authorized view for your dataset. For more information, see Tables.

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.

Advanced topics

The following sections describe advanced methods of using authorized views.

Combine row-level security with authorized views

The data displayed in a logical view or a materialized view is filtered according to the underlying source table's row-level access policies.

For details about how row-level security interacts with materialized views, see Use row-level security with other BigQuery features.

Combine column-level security with authorized views

The impact of column-level security on views is independent of whether or not the view is an authorized view.

For a detailed description of how permissions are applied, see Query views for column-level security.

Use BigQuery sharing with authorized views

BigQuery sharing (formerly Analytics Hub) is a data exchange platform with the following capabilities:

Lets you share data and insights at scale across organizational boundaries.
Uses a robust security and privacy framework.
Supports publishing a BigQuery dataset, called a shared dataset, and its associated authorized views and authorized datasets, to a set of subscribers.

A linked dataset is a read-only BigQuery dataset that serves as a pointer or reference to a shared dataset. Subscribing to a Sharing listing creates a linked dataset in your project but not a copy of the dataset, so subscribers can read the data but cannot add or update objects within it.

Materialized views that refer to tables in the linked dataset are not supported.

For more information, see Introduction to Sharing.

What's next

For a tutorial on creating an authorized view, see Create an authorized view.
To create a logical view, see Create logical views.
To create a materialized view, which supports other types of access control, see Create materialized views.
To get view metadata, see Getting information about views.
To manage views, see Manage views.