Authorized views

This document describes how to create authorized views in BigQuery.

You can create an authorized view in BigQuery by:

• Using the console.
• Using the bq update command.
• Calling the tables.patch API method.
• Using the client libraries.

Overview

An authorized view lets you share query results with particular users and groups without giving them access to the underlying source data. You can also use the view's SQL query to restrict the columns (fields) the users are able to query.

Alternative to authorized views: You can also control access to tables and views with access control set at the table level, within the same dataset. For more information, see Introduction to table access controls and read about how table-level access controls compare to authorized views.

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

For a tutorial on creating an authorized view, see Creating an authorized view.

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

Note: 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 will count against the authorized view limit. This can prevent the creation of 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 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.

Each of the following predefined IAM roles includes the permissions that you need in order to create a view:

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin

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.jobs.create permission for that table.

Note: To create or update an authorized view, or a view in an authorized dataset, you need additional permissions. For more information, see required permissions for authorized views and required permissions for views in authorized datasets.

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

Each of the following predefined IAM roles includes the permissions that you need in order to update dataset properties:

• roles/bigquery.dataOwner
• roles/bigquery.admin

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:

Console

1. In the Explorer panel, expand your project and select a dataset.

2. Expand the Actions option and click Open.

3. In the details panel, click Share dataset.

4. In the Dataset permissions panel, select the Authorized views tab.

5. In the Share authorized view section:

   • For Select project, verify the project name. If the view is in a different project, be sure to select it.
   • For Select dataset, choose the dataset that contains the view.
   • For Select view, select the view you are authorizing.

6. Click Add and then click Done.

bq

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

2. 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]"
      }
     }
    ],
   }
   

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

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

View on GitHub Feedback

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:     "[e-mail]",
		}),
	}
	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.

View on GitHub Feedback

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("[e-mail]"), 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.

View on GitHub Feedback

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 = "[e-mail]"
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 "[e-mail]" 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}")

Enforcing row-level access with a view

Alternative to authorized views: You can also control access to table data with row-level security. For more information, see Introduction to row-level security and read about how row-level security compares to authorized views.

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

Note: If you use this approach, ensure that there are no duplicate entries in the mapping table. Otherwise, your query results might contain duplicate records.

What's next

• For a tutorial on creating an authorized view, see Creating an authorized view.
• For information on creating views, see Creating views.
• For information on listing views, see Listing views.
• For information on getting view metadata, see Getting information about views.
• For information on updating views, see Updating views.
• For more information on managing views, see Managing views.
• For information about authorizing all of the views in a dataset, see Authorized datasets.