Creating authorized views

This document describes how to create authorized views in BigQuery.

You can create an authorized view in BigQuery by:

  • Using the Cloud Console.
  • Using the bq command-line tool's bq mk command.
  • Calling the tables.insert API method.
  • Submitting a CREATE VIEW Data Definition Language (DDL) statement.
  • Using the client libraries.

Overview

Giving a view access to a dataset is also known as creating an authorized view in BigQuery. An authorized view allows you to 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.

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.

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.

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. At a minimum, to create a view, you must be granted bigquery.tables.create permissions. The following predefined IAM roles include bigquery.tables.create permissions:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

In addition, if a user has bigquery.datasets.create permissions, when that user creates a dataset, they are granted bigquery.dataOwner access to it. bigquery.dataOwner access gives the user the ability to create views in the dataset.

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

Dataset that gives access to the view

At a minimum, to update dataset properties, you must be granted bigquery.datasets.update and bigquery.datasets.get permissions. The following predefined IAM roles include bigquery.datasets.update and bigquery.datasets.get permissions:

  • bigquery.dataOwner
  • bigquery.admin

In addition, if a user has bigquery.datasets.create permissions, when that user creates a dataset, they are granted bigquery.dataOwner access to it. bigquery.dataOwner access gives users the ability to update properties for datasets they create.

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

Granting views access to datasets

To grant a view access to a dataset:

Console

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

  2. In the details panel, click Share dataset.

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

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

  5. Click Add and then click Done.

bq

  1. Write the existing dataset information (including access controls) to a JSON file using the 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 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.

    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. 

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:     "example-analyst-group@google.com",
		}),
	}
	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. 

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("example-analyst-group@google.com"), 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. 

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 = "data_analysts@example.com"
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 "data_analysts@example.com" 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

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 user names 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 allows you provide a list of users for each row, but, even if you use a repeated field, storing user names 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 user names. For example: {group:string, user_name:string}. This approach allows you to 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

Next steps