Create an authorized view

In this tutorial, you create an authorized view in BigQuery that is used by your data analysts. Authorized views let you share query results with particular users and groups without giving them access to the underlying source data. The view is given access to the source data instead of a user or group. You can also use the view's SQL query to exclude columns and fields from the query results.

An alternative approach to using an authorized view would be to set up column-level access controls on the source data and then give your users access to a view that queries the access-controlled data. For more information on column-level access controls, see Introduction to column-level access control.

If you have multiple authorized views that access the same source dataset, you can authorize the dataset that contains the views instead of authorizing an individual view.

Objectives

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

Costs

In this document, you use the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use the pricing calculator.

New Google Cloud users might be eligible for a free trial.

When you finish the tasks that are described in this document, you can avoid continued billing by deleting the resources that you created. For more information, see Clean up.

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  3. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

    4.

  4. Verify that billing is enabled for your Google Cloud project.

  5. Enable the BigQuery API.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

    Enable the API

  6. Ensure that you have the necessary permissions to perform the tasks in this document.

Create a dataset to store your source data

You begin by creating a dataset to store your source data.

To create your source dataset, choose one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the left pane, click Explorer:

    Highlighted button for the Explorer pane.

    If you don't see the left pane, click Expand left pane to open the pane.

  3. In the Explorer pane, beside the project where you want to create the dataset, click View actions > Create dataset.

  4. On the Create dataset page, do the following:

    1. For Dataset ID, enter github_source_data.

    2. For Location type, verify that Multi-region is selected.

    3. For Multi-region, choose US or EU. All the resources you create in this tutorial should be in the same multi-region location.

    4. Click Create dataset.

SQL

Use the CREATE SCHEMA DDL statement:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    CREATE SCHEMA github_source_data;

  3. Click Run.

For more information about how to run queries, see Run an interactive query.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries. 

// Create a source dataset to store your table.
Dataset sourceDataset = bigquery.create(DatasetInfo.of(sourceDatasetId));

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries. 

from google.cloud import bigquery
from google.cloud.bigquery.enums import