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 or the classic BigQuery web UI.
• Using the 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 tables. You can also use the view's SQL query to restrict the columns (fields) the users are able to query.

When you create the view, it must be created in a dataset separate from the source data queried by the view. Because you can assign access controls only at the dataset level, if the view is created in the same dataset as the source data, your users would have access to both the view and the data.

Note: Any copies of an authorized view should be created by a user with access to the underlying tables to ensure that data is shared in accordance with the data owner's policies.

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

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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 navigation panel, in the Resources section, expand your project and select a dataset.

2. Click Share dataset on the right side of the window.

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.

Classic UI

1. Click the drop-down arrow to the right of the dataset that contains the source tables and choose Share Dataset.

2. In the Share Dataset dialog, for Add People, click the drop-down to the left of the field, and choose Authorized View.

3. Click Select View.

4. In the Select View dialog:

   • For Project, verify the project name. If the view is in a different project, be sure to select it.
   • For Dataset, choose the dataset that contains the view.
   • For Table ID, type the name of the view you are authorizing.

   • Click OK.

     

5. Click Add and then click Save changes.

CLI

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.

   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
}

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()

# Assign access controls to the dataset containing the view
# shared_dataset_id = 'my_shared_dataset'
# analyst_group_email = '[e-mail]'
shared_dataset = client.get_dataset(
    client.dataset(shared_dataset_id)
)  # API request
access_entries = shared_dataset.access_entries
access_entries.append(
    bigquery.AccessEntry("READER", "groupByEmail", analyst_group_email)
)
shared_dataset.access_entries = access_entries
shared_dataset = client.update_dataset(
    shared_dataset, ["access_entries"]
)  # API request

# Authorize the view to access the source dataset
# project = 'my-project'
# source_dataset_id = 'my_source_dataset'
source_dataset = client.get_dataset(
    client.dataset(source_dataset_id)
)  # API request
view_reference = {
    "projectId": project,
    "datasetId": shared_dataset_id,
    "tableId": "my_shared_view",
}
access_entries = source_dataset.access_entries
access_entries.append(bigquery.AccessEntry(None, "view", view_reference))
source_dataset.access_entries = access_entries
source_dataset = client.update_dataset(
    source_dataset, ["access_entries"]
)  # API request

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

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.

Next steps

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