Authorized views

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

Overview

As a data administrator, you can create anauthorized view to share a subsetof data in a dataset to specific users and groups (principals). Principals canview the data you share and run queries on it, but they can't access the sourcedataset directly.

View types

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

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

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

For more information, see the following resources:

• Introduction to logical views
• Introduction to materialized views

High-level steps for creating authorized views

To create and share a view, review these high-level steps, which are the samefor 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 columnsthat 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 authorizedview.
• Grant the authorized view access to the source dataset.

Alternatives

Although authorized views are flexible and scalable, one of the followingmethods 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 tableto hold sensitive data, a data administrator can restrict a user's abilityto view that data. Storing data in a separate table isolates the data andremoves the ability to see how many rows exist in the table.

In addition, by creating and applying policy tags, a data administrator canrestrict 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 authorizedviews is flexible and provides the best performance.

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

• Comparison of authorized views, row-level security, and separate tables
• Introduction to row-level security
• Example use cases for row-level security
• Introduction to column-level access control

Share all views in a dataset

If you want to give a collection of views access to a dataset without having toauthorize 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 thatcontains the data.

You can then give principals access to the datasetcontaining the group of views, or to individual views in the dataset, asneeded. A dataset that has access to another dataset is called anauthorized dataset. The dataset that authorizes another dataset to access itsdata is called theshared dataset.

A dataset's access control list can have up to 2,500 total authorized resources, includingauthorized views,authorized datasets, andauthorized functions. If you exceed this limit due to a large number of authorized views, consider grouping the views into authorized datasets. As a best practice, group related views into authorized datasets when you design new BigQuery architectures, especially multi-tenant architectures.

For more information, seeAuthorized datasetsandAuthorize a dataset.

Limitations

• When you make an authorized view or authorized materialized view in anotherdataset, the source data dataset and authorized view dataset must be inthe same regionallocation.
• When you delete an authorized view, it can take up to 24 hours to removethe authorized view from the list of views. During this time, you cannot accessthe authorized view, but the deleted authorized view can appear in the list ofviews, and counts against theauthorized viewlimit. This limit can prevent the creation ofadditional authorized views if the new authorized view would exceed that limit.

Before you begin

Grant Identity and Access Management (IAM) rolesthat give users the necessary permissionsto 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 thatcontains 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 thatcontain the view.

For more information on IAM roles and permissions inBigQuery, seePredefined roles andpermissions.

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 accesscontrols in the Google Cloud console)

Theroles/bigquery.dataOwner predefined IAM role includes thepermissions that you need to update dataset properties.

Additionally, if you have thebigquery.datasets.create permission, you canupdate properties of the datasets that you create.

For more information on IAM roles and permissions inBigQuery, seePredefined 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 orgroups the following IAM permissions:

• Theroles/bigquery.user IAM role to the projectthat contains the authorized view.
• Theroles/bigquery.dataViewer IAM role to thedataset that contains the authorized view.

Work with authorized views

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

Create an authorized view

To create an authorized view, choose one of the following options. For completesteps to authorize, share, and delete an authorized view, see thetutorialCreate an authorized view.

# Creates an authorized view.# Create a dataset to contain the view.resource "google_bigquery_dataset" "view_dataset" {  dataset_id  = "view_dataset"  description = "Dataset that contains the view"  location    = "us-west1"}# Create the view to authorize.resource "google_bigquery_table" "movie_view" {  project     = google_bigquery_dataset.view_dataset.project  dataset_id  = google_bigquery_dataset.view_dataset.dataset_id  table_id    = "movie_view"  description = "View to authorize"  view {    query          = "SELECT item_id, avg(rating) FROM `movie_project.movie_dataset.movie_ratings` GROUP BY item_id ORDER BY item_id;"    use_legacy_sql = false  }}# Authorize the view to access the dataset# that the query data originates from.resource "google_bigquery_dataset_access" "view_authorization" {  project    = "movie_project"  dataset_id = "movie_dataset"  view {    project_id = google_bigquery_table.movie_view.project    dataset_id = google_bigquery_table.movie_view.dataset_id    table_id   = google_bigquery_table.movie_view.table_id  }}# Specify the IAM policy for principals that can access# the authorized view. These users should already# have the roles/bigqueryUser role at the project level.data "google_iam_policy" "principals_policy" {  binding {    role = "roles/bigquery.dataViewer"    members = [      "group:example-group@example.com",    ]  }}# Set the IAM policy on the authorized  view.resource "google_bigquery_table_iam_policy" "authorized_view_policy" {  project     = google_bigquery_table.movie_view.project  dataset_id  = google_bigquery_table.movie_view.dataset_id  table_id    = google_bigquery_table.movie_view.table_id  policy_data = data.google_iam_policy.principals_policy.policy_data}

Manage users or groups for authorized views

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

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

For more information, seeControl access to resources using IAM.

Remove authorization to a view

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

bqrm\project_id:dataset:table_id

Quotas and limits

• Authorized views are subject to dataset limits. For more information, seeDataset limits.
• If you remove an authorized view, it can take up to 24 hours for allreferences 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 aunique 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 filteredaccording to the underlying source table's row-level access policies.

For details about how row-level security interacts with materialized views, seeUse 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 notthe view is an authorized view.

For a detailed description of how permissions are applied, seeQuery views for column-levelsecurity.

Use BigQuery sharing with authorized views

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

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

Alinked dataset is a read-only BigQuery dataset that serves asa pointer or reference to a shared dataset. Subscribing to aSharinglisting creates a linked dataset in your projectbut not a copy of the dataset, so subscribers can read the data but cannot addor update objects within it.

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

For more information, seeIntroduction to Sharing.

What's next

• For a tutorial on creating an authorized view, seeCreate an authorized view.
• To create a logical view, seeCreate logical views.
• To create a materialized view, which supports other types of access control,seeCreate materialized views.
• To get view metadata, seeGetting information about views.
• To manage views, seeManage views.