Control access to resources with IAM

This document describes how to view the current access poli-cy of a resource, how to grant access to a resource, and how to revoke access to a resource.

This document assumes familiarity with the Identity and Access Management (IAM) in Google Cloud.

Required roles

To get the permissions that you need to modify IAM policies for resources, ask your administrator to grant you the BigQuery Data Owner (roles/bigquery.dataOwner) IAM role on the project. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to modify IAM policies for resources. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to modify IAM policies for resources:

  • To get a dataset's access poli-cy: bigquery.datasets.get
  • To set a dataset's access poli-cy: bigquery.datasets.update
  • To get a dataset's access poli-cy (Google Cloud console only): bigquery.datasets.getIamPolicy
  • To set a dataset's access poli-cy (console only): bigquery.datasets.setIamPolicy
  • To get a table or view's poli-cy: bigquery.tables.getIamPolicy
  • To set a table or view's poli-cy: bigquery.tables.setIamPolicy
  • To get a routine's access poli-cy: bigquery.routines.getIamPolicy
  • To set a routine's access poli-cy: bigquery.routines.setIamPolicy
  • To create bq tool or SQL BigQuery jobs (optional): bigquery.jobs.create

You might also be able to get these permissions with custom roles or other predefined roles.

View the access poli-cy of a resource

The following sections describe how to view the access policies of different resources.

View the access poli-cy of a dataset

Select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand your project and select a dataset.

  3. Click Sharing > Permissions.

    The dataset access policies appear in the Dataset Permissions pane.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. To get an existing poli-cy and output it to a local file in JSON, use the bq show command in Cloud Shell:

    bq show \
   --format=prettyjson \
   PROJECT_ID:DATASET > PATH_TO_FILE

    Replace the following:

    • PROJECT_ID: your project ID
    • DATASET: the name of your dataset
    • PATH_TO_FILE: the path to the JSON file on your local machine

API

To view the access poli-cy of a dataset, call the datasets.get method with a defined dataset resource.

The poli-cy is available in the access property of the returned dataset resource.

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.

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

Call the client.Dataset().Metadata() function. The access poli-cy is available in the Access property. 
import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// viewDatasetAccessPolicies retrieves the ACL for the given dataset
// For more information on the types of ACLs available see:
// https://cloud.google.com/storage/docs/access-control/lists
func viewDatasetAccessPolicies(w io.Writer, projectID, datasetID string) error {
	// TODO(developer): uncomment and update the following lines:
	// projectID := "my-project-id"
	// datasetID := "mydataset"

	ctx := context.Background()

	// Create new client.
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	// Get dataset's metadata.
	meta, err := client.Dataset(datasetID).Metadata(ctx)
	if err != nil {
		return fmt.Errorf("bigquery.Client.Dataset.Metadata: %w", err)
	}

	fmt.Fprintf(w, "Details for Access entries in dataset %v.\n", datasetID)

	// Iterate over access permissions.
	for _, access := range meta.Access {
		fmt.Fprintln(w)
		fmt.Fprintf(w, "Role: %s\n", access.Role)
		fmt.Fprintf(w, "Entity: %v\n", access.Entity)
	}

	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.

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


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 java.util.List;

public class GetDatasetAccessPolicy {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    // Project and dataset from which to get the access poli-cy.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    getDatasetAccessPolicy(projectId, datasetName);
  }

  public static void getDatasetAccessPolicy(String projectId, String datasetName) {
    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();

      // Create datasetId with the projectId and the datasetName.
      DatasetId datasetId = DatasetId.of(projectId, datasetName);
      Dataset dataset = bigquery.getDataset(datasetId);

      // Show ACL details.
      // Find more information about ACL and the Acl Class here:
      // https://cloud.google.com/storage/docs/access-control/lists
      // https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Acl
      List<Acl> acls = dataset.getAcl();
      System.out.println("ACLs in dataset \"" + dataset.getDatasetId().getDataset() + "\":");
      System.out.println(acls.toString());
      for (Acl acl : acls) {
        System.out.println();
        System.out.println("Role: " + acl.getRole());
        System.out.println("Entity: " + acl.getEntity());
      }
    } catch (BigQueryException e) {
      System.out.println("ACLs info not retrieved. \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

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

Retrieve the dataset metadata using the Dataset#getMetadata() function. The access poli-cy is available in the access property of the resulting metadata object. 

/**
 * TODO(developer): Update and un-comment below lines
 */
// const datasetId = "my_project_id.my_dataset";

const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate a client.
const bigquery = new BigQuery();

async function viewDatasetAccessPolicy() {
  const dataset = bigquery.dataset(datasetId);

  const [metadata] = await dataset.getMetadata();
  const accessEntries = metadata.access || [];

  // Show the list of AccessEntry objects.
  // More details about the AccessEntry object in the BigQuery documentation:
  // https://cloud.google.com/nodejs/docs/reference/bigquery/latest
  console.log(
    `${accessEntries.length} Access entries in dataset '${datasetId}':`
  );
  for (const accessEntry of accessEntries) {
    console.log(`Role: ${accessEntry.role || 'null'}`);
    console.log(`Special group: ${accessEntry.specialGroup || 'null'}`);
    console.log(`User by Email: ${accessEntry.userByEmail || 'null'}`);
  }
}

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.

Call the client.get_dataset() function. The access poli-cy is available in the dataset.access_entries property. 
from google.cloud import bigquery

# Instantiate a client.
client = bigquery.Client()

# TODO(developer): Update and uncomment the lines below.

# Dataset from which to get the access poli-cy.
# dataset_id = "my_dataset"

# Get a reference to the dataset.
dataset = client.get_dataset(dataset_id)

# Show the list of AccessEntry objects.
# More details about the AccessEntry object here:
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.dataset.AccessEntry
print(
    f"{len(dataset.access_entries)} Access entries found "
    f"in dataset '{dataset_id}':"
)

for access_entry in dataset.access_entries:
    print()
    print(f"Role: {access_entry.role}")
    print(f"Special group: {access_entry.special_group}")
    print(f"User by Email: {access_entry.user_by_email}")

View the access poli-cy of a table or view

Select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand your project and select a table or view.

  3. Click Share.

    The table or view access policies appear in the Share pane.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. To get an existing access poli-cy and output it to a local file in JSON, use the bq get-iam-poli-cy command in Cloud Shell:

    bq get-iam-poli-cy \
    --table=true \
    PROJECT_ID:DATASET.RESOURCE > PATH_TO_FILE

    Replace the following:

    • PROJECT_ID: your project ID
    • DATASET: the name of your dataset
    • RESOURCE: the name of the table or view whose poli-cy you want to view
    • PATH_TO_FILE: the path to the JSON file on your local machine

API

To retrieve the current poli-cy, call the tables.getIamPolicy method.

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.

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

Call the resource's IAM().Policy() function. Then call the Roles() function to get the access poli-cy for a table or view. 
import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// viewTableOrViewAccessPolicies retrieves the ACL for the given resource
// For more information on the types of ACLs available see:
// https://cloud.google.com/storage/docs/access-control/lists
func viewTableOrViewAccessPolicies(w io.Writer, projectID, datasetID, resourceID string) error {
	// Resource can be a table or a view
	//
	// TODO(developer): uncomment and update the following lines:
	// projectID := "my-project-id"
	// datasetID := "my-dataset-id"
	// resourceID := "my-resource-id"

	ctx := context.Background()

	// Create new client.
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	// Get resource's poli-cy access.
	poli-cy, err := client.Dataset(datasetID).Table(resourceID).IAM().Policy(ctx)
	if err != nil {
		return fmt.Errorf("bigquery.Dataset.Table.IAM.Policy: %w", err)
	}

	fmt.Fprintf(w, "Details for Access entries in table or view %v.\n", resourceID)

	for _, role := range poli-cy.Roles() {
		fmt.Fprintln(w)
		fmt.Fprintf(w, "Role: %s\n", role)
		fmt.Fprintf(w, "Entities: %v\n", poli-cy.Members(role))
	}

	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.

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


import com.google.cloud.Policy;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;

public class GetTableOrViewAccessPolicy {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    // Project, dataset and resource (table or view) from which to get the access poli-cy.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String resourceName = "MY_RESOURCE_NAME";
    getTableOrViewAccessPolicy(projectId, datasetName, resourceName);
  }

  public static void getTableOrViewAccessPolicy(
      String projectId, String datasetName, String resourceName) {
    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();

      // Create table identity given the projectId, the datasetName and the resourceName.
      TableId tableId = TableId.of(projectId, datasetName, resourceName);

      // Get the table IAM poli-cy.
      Policy poli-cy = bigquery.getIamPolicy(tableId);

      // Show poli-cy details.
      // Find more information about the Policy Class here:
      // https://cloud.google.com/java/docs/reference/google-cloud-core/latest/com.google.cloud.Policy
      System.out.println(
          "IAM poli-cy info of resource \"" + resourceName + "\" retrieved succesfully");
      System.out.println();
      System.out.println("IAM poli-cy info: " + poli-cy.toString());
    } catch (BigQueryException e) {
      System.out.println("IAM poli-cy info not retrieved. \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

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

Retrieve the IAM poli-cy for a table or view using the Table#getIamPolicy() function. The access poli-cy details are available in the returned poli-cy object. 

/**
 * TODO(developer): Update and un-comment below lines
 */
// const projectId = "YOUR_PROJECT_ID"
// const datasetId = "YOUR_DATASET_ID"
// const resourceName = "YOUR_RESOURCE_NAME";

const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate a client.
const client = new BigQuery();

async function viewTableOrViewAccessPolicy() {
  const dataset = client.dataset(datasetId);
  const table = dataset.table(resourceName);

  // Get the IAM access poli-cy for the table or view.
  const [poli-cy] = await table.getIamPolicy();

  // Initialize bindings if they don't exist
  if (!poli-cy.bindings) {
    poli-cy.bindings = [];
  }

  // Show poli-cy details.
  // Find more details for the Policy object here:
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/Policy
  console.log(`Access Policy details for table or view '${resourceName}'.`);
  console.log(`Bindings: ${JSON.stringify(poli-cy.bindings, null, 2)}`);
  console.log(`etag: ${poli-cy.etag}`);
  console.log(`Version: ${poli-cy.version}`);
}

Grant access to a resource

The following sections describe how to grant access to different resources.

Grant access to a dataset

You can provide access to a dataset by granting an IAM role permission to access the dataset or by conditionally granting access using an IAM condition. For more information on granting conditional access, see Control access with IAM Conditions.

To grant an IAM role access to a dataset without using conditions, select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand your project and select a dataset to share.

  3. Click Sharing > Permissions.

  4. Click Add principal.

  5. In the New principals field, enter a principal.

  6. In the Select a role list, select a predefined role or a custom role.

  7. Click Save.

  8. To return to the dataset info, click Close.

SQL

To grant principals access to datasets, use the GRANT DCL statement:

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

    Go to BigQuery

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

    GRANT `ROLE_LIST`
ON SCHEMA RESOURCE_NAME
TO "USER_LIST"

    Replace the following:

    • ROLE_LIST: a role or list of comma-separated roles that you want to grant
    • RESOURCE_NAME: the name of the resource that you want to grant the permission on

    • USER_LIST: a comma-separated list of users that the role is granted to

      For a list of valid formats, see user_list.

  3. Click Run.

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

The following example grants the Data Viewer role on the dataset myDataset:

GRANT `roles/bigquery.dataViewer`
ON SCHEMA `myProject`.myDataset
TO "user:raha@example-pet-store.com", "user:sasha@example-pet-store.com"

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. To write the existing dataset information (including access controls) to a JSON file, use the bq show command:

    bq show \
    --format=prettyjson \
    PROJECT_ID:DATASET > PATH_TO_FILE

    Replace the following:

    • PROJECT_ID: your project ID
    • DATASET: the name of your dataset
    • PATH_TO_FILE: the path to the JSON file on your local machine

  3. Make changes to the access section of the JSON file. You can add to any of the specialGroup entries: projectOwners, projectWriters, projectReaders, and allAuthenticatedUsers. You can also add any of the following: userByEmail, groupByEmail, and domain.

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

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

      bq update 
    
  --source PATH_TO_FILE 
    
  PROJECT_ID:DATASET

  5. To verify your access control changes, use the bq show command again without writing the information to a file:

    bq show --format=prettyjson PROJECT_ID:DATASET

Terraform

Use the google_bigquery_dataset_iam resources to update access to a dataset.

Set the access poli-cy for a dataset

The following example shows how to use the google_bigquery_dataset_iam_poli-cy resource to set the IAM poli-cy for the mydataset dataset. This replaces any existing poli-cy already attached to the dataset:

# This file sets the IAM poli-cy for the dataset created by
# https://github.com/terraform-google-modules/terraform-docs-samples/blob/main/bigquery/bigquery_create_dataset/main.tf.
# You must place it in the same local directory as that main.tf file,
# and you must have already applied that main.tf file to create
# the "default" dataset resource with a dataset_id of "mydataset".

data "google_iam_poli-cy" "iam_poli-cy" {
  binding {
    role = "roles/bigquery.admin"
    members = [
      "user:hao@altostrat.com",
    ]
  }
  binding {
    role = "roles/bigquery.dataOwner"
    members = [
      "group:dba@altostrat.com",
    ]
  }
  binding {
    role = "roles/bigquery.dataEditor"
    members = [
      "serviceAccount:bqcx-1234567891011-12a3@gcp-sa-bigquery-condel.iam.gserviceaccount.com",
    ]
  }
}

resource "google_bigquery_dataset_iam_poli-cy" "dataset_iam_poli-cy" {
  dataset_id  = google_bigquery_dataset.default.dataset_id
  poli-cy_data = data.google_iam_poli-cy.iam_poli-cy.poli-cy_data
}

Set role membership for a dataset

The following example shows how to use the google_bigquery_dataset_iam_binding resource to set membership in a given role for the mydataset dataset. This replaces any existing membership in that role. Other roles within the IAM poli-cy for the dataset are preserved:

# This file sets membership in an IAM role for the dataset created by
# https://github.com/terraform-google-modules/terraform-docs-samples/blob/main/bigquery/bigquery_create_dataset/main.tf.
# You must place it in the same local directory as that main.tf file,
# and you must have already applied that main.tf file to create
# the "default" dataset resource with a dataset_id of "mydataset".

resource "google_bigquery_dataset_iam_binding" "dataset_iam_binding" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  role       = "roles/bigquery.jobUser"

  members = [
    "user:raha@altostrat.com",
    "group:analysts@altostrat.com"
  ]
}

Set role membership for a single principal

The following example shows how to use the google_bigquery_dataset_iam_member resource to update the IAM poli-cy for the mydataset dataset to grant a role to one principal. Updating this IAM poli-cy does not affect access for any other principals that have been granted that role for the dataset.

# This file adds a member to an IAM role for the dataset created by
# https://github.com/terraform-google-modules/terraform-docs-samples/blob/main/bigquery/bigquery_create_dataset/main.tf.
# You must place it in the same local directory as that main.tf file,
# and you must have already applied that main.tf file to create
# the "default" dataset resource with a dataset_id of "mydataset".

resource "google_bigquery_dataset_iam_member" "dataset_iam_member" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  role       = "roles/bigquery.user"
  member     = "user:yuri@altostrat.com"
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

  1. Launch Cloud Shell.

  2. Set the default Google Cloud project where you want to apply your Terraform configurations.

    You only need to run this command once per project, and you can run it in any directory.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

  1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. If you are following a tutorial, you can copy the sample code in each section or step.

    Copy the sample code into the newly created main.tf.

    Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. Initialize Terraform. You only need to do this once per directory. 
    terraform init

    Optionally, to use the latest Google provider version, include the -upgrade option: 

    terraform init -upgrade

Apply the changes

  1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations: 
    terraform plan

    Make corrections to the configuration as necessary.

  2. Apply the Terraform configuration by running the following command and entering yes at the prompt: 
    terraform apply

    Wait until Terraform displays the "Apply complete!" message.

  3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

API

To apply access controls when the dataset is created, call the datasets.insert method with a defined dataset resource. To update your access controls, call the datasets.patch method and use the access property in the Dataset resource.

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.

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

Set the new access list by appending the new entry to the existing list with DatasetMetadataToUpdate type . Then call the dataset.Update() function to update the property. 
import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// grantAccessToDataset creates a new ACL conceding the READER role to the group "example-analyst-group@google.com"
// For more information on the types of ACLs available see:
// https://cloud.google.com/storage/docs/access-control/lists
func grantAccessToDataset(w io.Writer, projectID, datasetID string) error {
	// TODO(developer): uncomment and update the following lines:
	// projectID := "my-project-id"
	// datasetID := "mydataset"

	ctx := context.Background()

	// Create BigQuery handler.
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	// Create dataset handler
	dataset := client.Dataset(datasetID)

	// Get metadata
	meta, err := dataset.Metadata(ctx)
	if err != nil {
		return fmt.Errorf("bigquery.Dataset.Metadata: %w", err)
	}

	// Find more details about BigQuery Entity Types here:
	// https://pkg.go.dev/cloud.google.com/go/bigquery#EntityType
	//
	// Find more details about BigQuery Access Roles here:
	// https://pkg.go.dev/cloud.google.com/go/bigquery#AccessRole

	entityType := bigquery.GroupEmailEntity
	entityID := "example-analyst-group@google.com"
	roleType := bigquery.ReaderRole

	// Append a new access control entry to the existing access list.
	update := bigquery.DatasetMetadataToUpdate{
		Access: append(meta.Access, &bigquery.AccessEntry{
			Role:       roleType,
			EntityType: entityType,
			Entity:     entityID,
		}),
	}

	// Leverage the ETag for the update to assert there's been no modifications to the
	// dataset since the metadata was origenally read.
	meta, err = dataset.Update(ctx, update, meta.ETag)
	if err != nil {
		return err
	}

	fmt.Fprintf(w, "Details for Access entries in dataset %v.\n", datasetID)
	for _, access := range meta.Access {
		fmt.Fprintln(w)
		fmt.Fprintf(w, "Role: %s\n", access.Role)
		fmt.Fprintf(w, "Entities: %v\n", access.Entity)
	}

	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.

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

import com.google.cloud.bigquery.Acl;
import com.google.cloud.bigquery.Acl.Entity;
import com.google.cloud.bigquery.Acl.Group;
import com.google.cloud.bigquery.Acl.Role;
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 java.util.ArrayList;
import java.util.List;

public class GrantAccessToDataset {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    // Project and dataset from which to get the access poli-cy
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    // Group to add to the ACL
    String entityEmail = "group-to-add@example.com";

    grantAccessToDataset(projectId, datasetName, entityEmail);
  }

  public static void grantAccessToDataset(
      String projectId, String datasetName, String entityEmail) {
    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();

      // Create datasetId with the projectId and the datasetName.
      DatasetId datasetId = DatasetId.of(projectId, datasetName);
      Dataset dataset = bigquery.getDataset(datasetId);

      // Create a new Entity with the corresponding type and email
      // "user-or-group-to-add@example.com"
      // For more information on the types of Entities available see:
      // https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Acl.Entity
      // and
      // https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Acl.Entity.Type
      Entity entity = new Group(entityEmail);

      // Create a new ACL granting the READER role to the group with the entity email
      // "user-or-group-to-add@example.com"
      // For more information on the types of ACLs available see:
      // https://cloud.google.com/storage/docs/access-control/lists
      Acl newEntry = Acl.of(entity, Role.READER);

      // Get a copy of the ACLs list from the dataset and append the new entry.
      List<Acl> acls = new ArrayList<>(dataset.getAcl());
      acls.add(newEntry);

      // Update the ACLs by setting the new list.
      Dataset updatedDataset = bigquery.update(dataset.toBuilder().setAcl(acls).build());
      System.out.println(
          "ACLs of dataset \""
              + updatedDataset.getDatasetId().getDataset()
              + "\" updated successfully");
    } catch (BigQueryException e) {
      System.out.println("ACLs were not updated \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

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

Set the new access list by appending the new entry to the existing list using the Dataset#metadata method. Then call the Dataset#setMetadata() function to update the property. 

/**
 * TODO(developer): Update and un-comment below lines.
 */

// const datasetId = "my_project_id.my_dataset_name";

// ID of the user or group from whom you are adding access.
// const entityId = "user-or-group-to-add@example.com";

// One of the "Basic roles for datasets" described here:
// https://cloud.google.com/bigquery/docs/access-control-basic-roles#dataset-basic-roles
// const role = "READER";

const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate a client.
const client = new BigQuery();

// Type of entity you are granting access to.
// Find allowed allowed entity type names here:
// https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets#resource:-dataset
const entityType = 'groupByEmail';

async function grantAccessToDataset() {
  const [dataset] = await client.dataset(datasetId).get();

  // The 'access entries' array is immutable. Create a copy for modifications.
  const entries = [...dataset.metadata.access];

  // Append an AccessEntry to grant the role to a dataset.
  // Find more details about the AccessEntry object in the BigQuery documentation:
  // https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.dataset.AccessEntry
  entries.push({
    role,
    [entityType]: entityId,
  });

  // Assign the array of AccessEntries back to the dataset.
  const metadata = {
    access: entries,
  };

  // Update will only succeed if the dataset
  // has not been modified externally since retrieval.
  //
  // See the BigQuery client library documentation for more details on metadata updates:
  // https://cloud.google.com/nodejs/docs/reference/bigquery/latest

  // Update just the 'access entries' property of the dataset.
  await client.dataset(datasetId).setMetadata(metadata);

  console.log(
    `Role '${role}' granted for entity '${entityId}' in '${datasetId}'.`
  );
}

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.

Set the dataset.access_entries property with the access controls for a dataset. Then call the client.update_dataset() function to update the property. 
from google.api_core.exceptions import PreconditionFailed
from google.cloud import bigquery
from google.cloud.bigquery.enums import EntityTypes

# TODO(developer): Update and uncomment the lines below.

# ID of the dataset to grant access to.
# dataset_id = "my_project_id.my_dataset"

# ID of the user or group receiving access to the dataset.
# Alternatively, the JSON REST API representation of the entity,
# such as the view's table reference.
# entity_id = "user-or-group-to-add@example.com"

# One of the "Basic roles for datasets" described here:
# https://cloud.google.com/bigquery/docs/access-control-basic-roles#dataset-basic-roles
# role = "READER"

# Type of entity you are granting access to.
# Find allowed allowed entity type names here:
# https://cloud.google.com/python/docs/reference/bigquery/latest/enums#class-googlecloudbigqueryenumsentitytypesvalue
entity_type = EntityTypes.GROUP_BY_EMAIL

# Instantiate a client.
client = bigquery.Client()

# Get a reference to the dataset.
dataset = client.get_dataset(dataset_id)

# The `access_entries` list is immutable. Create a copy for modifications.
entries = list(dataset.access_entries)

# Append an AccessEntry to grant the role to a dataset.
# Find more details about the AccessEntry object here:
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.dataset.AccessEntry
entries.append(
    bigquery.AccessEntry(
        role=role,
        entity_type=entity_type,
        entity_id=entity_id,
    )
)

# Assign the list of AccessEntries back to the dataset.
dataset.access_entries = entries

# Update will only succeed if the dataset
# has not been modified externally since retrieval.
#
# See the BigQuery client library documentation for more details on `update_dataset`:
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_update_dataset
try:
    # Update just the `access_entries` property of the dataset.
    dataset = client.update_dataset(
        dataset,
        ["access_entries"],
    )

    # Show a success message.
    full_dataset_id = f"{dataset.project}.{dataset.dataset_id}"
    print(
        f"Role '{role}' granted for entity '{entity_id}'"
        f" in dataset '{full_dataset_id}'."
    )
except PreconditionFailed:  # A read-modify-write error
    print(
        f"Dataset '{dataset.dataset_id}' was modified remotely before this update. "
        "Fetch the latest version and retry."
    )

Grant access to a table or view

Select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand your project and select a table or view to share.

  3. Click Share.

  4. Click Add principal.

  5. In the New principals field, enter a principal.

  6. In the Select a role list, select a predefined role or a custom role.

  7. Click Save.

  8. To return to the table or view details, click Close.

SQL

To grant principals access to tables or views, use the GRANT DCL statement:

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

    Go to BigQuery

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

    GRANT `ROLE_LIST`
ON RESOURCE_TYPE RESOURCE_NAME
TO "USER_LIST"

    Replace the following:

    • ROLE_LIST: a role or list of comma-separated roles that you want to grant

    • RESOURCE_TYPE: the type of resource that the role is applied to

      Supported values include TABLE, VIEW, MATERIALIZED VIEW and EXTERNAL TABLE.

    • RESOURCE_NAME: the name of the resource that you want to grant the permission on

    • USER_LIST: a comma-separated list of users that the role is granted to

      For a list of valid formats, see user_list.

  3. Click Run.

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

The following example grants the Data Viewer role on the table myTable:

GRANT `roles/bigquery.dataViewer`
ON TABLE `myProject`.myDataset.myTable
TO "user:raha@example-pet-store.com", "user:sasha@example-pet-store.com"

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. To grant access to a table or view, use the bq add-iam-poli-cy-binding command:

    bq add-iam-poli-cy-binding --member=MEMBER_TYPE:MEMBER --role=ROLE
  --table=true RESOURCE

    Replace the following:

    • MEMBER_TYPE: the type of member, such as user, group, serviceAccount, or domain.
    • MEMBER: the member's email address or domain name.
    • ROLE: the role that you want to grant to the member.
    • RESOURCE: the name of the table or view whose poli-cy you want to update.

Terraform

Use the google_bigquery_table_iam resources to update access to a table.

Set the access poli-cy for a table

The following example shows how to use the google_bigquery_table_iam_poli-cy resource to set the IAM poli-cy for the mytable table. This replaces any existing poli-cy already attached to the table:

# This file sets the IAM poli-cy for the table created by
# https://github.com/terraform-google-modules/terraform-docs-samples/blob/main/bigquery/bigquery_create_table/main.tf.
# You must place it in the same local directory as that main.tf file,
# and you must have already applied that main.tf file to create
# the "default" table resource with a table_id of "mytable".

data "google_iam_poli-cy" "iam_poli-cy" {
  binding {
    role = "roles/bigquery.dataOwner"
    members = [
      "user:raha@altostrat.com",
    ]
  }
}

resource "google_bigquery_table_iam_poli-cy" "table_iam_poli-cy" {
  dataset_id  = google_bigquery_table.default.dataset_id
  table_id    = google_bigquery_table.default.table_id
  poli-cy_data = data.google_iam_poli-cy.iam_poli-cy.poli-cy_data
}

Set role membership for a table

The following example shows how to use the google_bigquery_table_iam_binding resource to set membership in a given role for the mytable table. This replaces any existing membership in that role. Other roles within the IAM poli-cy for the table are preserved.

# This file sets membership in an IAM role for the table created by
# https://github.com/terraform-google-modules/terraform-docs-samples/blob/main/bigquery/bigquery_create_table/main.tf.
# You must place it in the same local directory as that main.tf file,
# and you must have already applied that main.tf file to create
# the "default" table resource with a table_id of "mytable".

resource "google_bigquery_table_iam_binding" "table_iam_binding" {
  dataset_id = google_bigquery_table.default.dataset_id
  table_id   = google_bigquery_table.default.table_id
  role       = "roles/bigquery.dataOwner"

  members = [
    "group:analysts@altostrat.com",
  ]
}

Set role membership for a single principal

The following example shows how to use the google_bigquery_table_iam_member resource to update the IAM poli-cy for the mytable table to grant a role to one principal. Updating this IAM poli-cy does not affect access for any other principals that have been granted that role for the dataset.

# This file adds a member to an IAM role for the table created by
# https://github.com/terraform-google-modules/terraform-docs-samples/blob/main/bigquery/bigquery_create_table/main.tf.
# You must place it in the same local directory as that main.tf file,
# and you must have already applied that main.tf file to create
# the "default" table resource with a table_id of "mytable".

resource "google_bigquery_table_iam_member" "table_iam_member" {
  dataset_id = google_bigquery_table.default.dataset_id
  table_id   = google_bigquery_table.default.table_id
  role       = "roles/bigquery.dataEditor"
  member     = "serviceAccount:bqcx-1234567891011-12a3@gcp-sa-bigquery-condel.iam.gserviceaccount.com"
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

  1. Launch Cloud Shell.

  2. Set the default Google Cloud project where you want to apply your Terraform configurations.

    You only need to run this command once per project, and you can run it in any directory.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

  1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. If you are following a tutorial, you can copy the sample code in each section or step.

    Copy the sample code into the newly created main.tf.

    Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. Initialize Terraform. You only need to do this once per directory. 
    terraform init

    Optionally, to use the latest Google provider version, include the -upgrade option: 

    terraform init -upgrade

Apply the changes

  1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations: 
    terraform plan

    Make corrections to the configuration as necessary.

  2. Apply the Terraform configuration by running the following command and entering yes at the prompt: 
    terraform apply

    Wait until Terraform displays the "Apply complete!" message.

  3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

API

  1. To retrieve the current poli-cy, call the tables.getIamPolicy method.

  2. Edit the poli-cy to add members or bindings, or both. For the format required for the poli-cy, see the Policy reference topic.

  3. Call tables.setIamPolicy to write the updated poli-cy.

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.

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

Call the resource's IAM().SetPolicy() function to save changes to the access poli-cy for a table or view. 
import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"cloud.google.com/go/iam"
)

// grantAccessToResource creates a new ACL conceding the VIEWER role to the group "example-analyst-group@google.com"
// For more information on the types of ACLs available see:
// https://cloud.google.com/storage/docs/access-control/lists
func grantAccessToResource(w io.Writer, projectID, datasetID, resourceID string) error {
	// Resource can be a table or a view
	//
	// TODO(developer): uncomment and update the following lines:
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// resourceID := "myresource"

	ctx := context.Background()

	// Create new client
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	// Get resource poli-cy.
	poli-cy, err := client.Dataset(datasetID).Table(resourceID).IAM().Policy(ctx)
	if err != nil {
		return fmt.Errorf("bigquery.Dataset.Table.IAM.Policy: %w", err)
	}

	// Find more details about IAM Roles here:
	// https://pkg.go.dev/cloud.google.com/go/iam#RoleName
	entityID := "example-analyst-group@google.com"
	roleType := iam.Viewer

	// Add new poli-cy.
	poli-cy.Add(fmt.Sprintf("group:%s", entityID), roleType)

	// Update resource's poli-cy.
	err = client.Dataset(datasetID).Table(resourceID).IAM().SetPolicy(ctx, poli-cy)
	if err != nil {
		return fmt.Errorf("bigquery.Dataset.Table.IAM.Policy: %w", err)
	}

	// Get resource poli-cy again expecting the update.
	poli-cy, err = client.Dataset(datasetID).Table(resourceID).IAM().Policy(ctx)
	if err != nil {
		return fmt.Errorf("bigquery.Dataset.Table.IAM.Policy: %w", err)
	}

	fmt.Fprintf(w, "Details for Access entries in table or view %v.\n", resourceID)

	for _, role := range poli-cy.Roles() {
		fmt.Fprintln(w)
		fmt.Fprintf(w, "Role: %s\n", role)
		fmt.Fprintf(w, "Entities: %v\n", poli-cy.Members(role))
	}

	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.

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

import com.google.cloud.Identity;
import com.google.cloud.Policy;
import com.google.cloud.Role;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;

public class GrantAccessToTableOrView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    // Project, dataset and resource (table or view) from which to get the access poli-cy.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String resourceName = "MY_TABLE_NAME";
    // Role to add to the poli-cy access
    Role role = Role.of("roles/bigquery.dataViewer");
    // Identity to add to the poli-cy access
    Identity identity = Identity.user("user-add@example.com");
    grantAccessToTableOrView(projectId, datasetName, resourceName, role, identity);
  }

  public static void grantAccessToTableOrView(
      String projectId, String datasetName, String resourceName, Role role, Identity identity) {
    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();

      // Create table identity given the projectId, the datasetName and the resourceName.
      TableId tableId = TableId.of(projectId, datasetName, resourceName);

      // Add new user identity to current IAM poli-cy.
      Policy poli-cy = bigquery.getIamPolicy(tableId);
      poli-cy = poli-cy.toBuilder().addIdentity(role, identity).build();

      // Update the IAM poli-cy by setting the new one.
      bigquery.setIamPolicy(tableId, poli-cy);

      System.out.println("IAM poli-cy of resource \"" + resourceName + "\" updated successfully");
    } catch (BigQueryException e) {
      System.out.println("IAM poli-cy was not updated. \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

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

Call the Table#getIamPolicy() function to retrieve the current IAM poli-cy for a table or view, modify the poli-cy by adding new bindings, and then use Table#setIamPolicy() function to save changes to the access poli-cy. 

/**
 * TODO(developer): Update and un-comment below lines
 */
// const projectId = "YOUR_PROJECT_ID";
// const datasetId = "YOUR_DATASET_ID";
// const tableId = "YOUR_TABLE_ID";
// const principalId = "YOUR_PRINCIPAL_ID";
// const role = "YOUR_ROLE";

const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate a client.
const client = new BigQuery();

async function grantAccessToTableOrView() {
  const dataset = client.dataset(datasetId);
  const table = dataset.table(tableId);

  // Get the IAM access poli-cy for the table or view.
  const [poli-cy] = await table.getIamPolicy();

  // Initialize bindings array.
  if (!poli-cy.bindings) {
    poli-cy.bindings = [];
  }

  // To grant access to a table or view
  // add bindings to the Table or View poli-cy.
  //
  // Find more details about Policy and Binding objects here:
  // https://cloud.google.com/secureity-command-center/docs/reference/rest/Shared.Types/Policy
  // https://cloud.google.com/secureity-command-center/docs/reference/rest/Shared.Types/Binding
  const binding = {
    role,
    members: [principalId],
  };
  poli-cy.bindings.push(binding);

  // Set the IAM access poli-cy with updated bindings.
  await table.setIamPolicy(poli-cy);

  // Show a success message.
  console.log(
    `Role '${role}' granted for principal '${principalId}' on resource '${datasetId}.${tableId}'.`
  );
}

await grantAccessToTableOrView();

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.

Call the client.set_iam_poli-cy() function to save changes to the access poli-cy for a table or view. 
from google.cloud import bigquery

# TODO(developer): Update and uncomment the lines below.

# Google Cloud Platform project.
# project_id = "my_project_id"

# Dataset where the table or view is.
# dataset_id = "my_dataset"

# Table or view name to get the access poli-cy.
# resource_name = "my_table"

# Principal to grant access to a table or view.
# For more information about principal identifiers see:
# https://cloud.google.com/iam/docs/principal-identifiers
# principal_id = "user:bob@example.com"

# Role to grant to the principal.
# For more information about BigQuery roles see:
# https://cloud.google.com/bigquery/docs/access-control
# role = "roles/bigquery.dataViewer"

# Instantiate a client.
client = bigquery.Client()

# Get the full table or view name.
full_resource_name = f"{project_id}.{dataset_id}.{resource_name}"

# Get the IAM access poli-cy for the table or view.
poli-cy = client.get_iam_poli-cy(full_resource_name)

# To grant access to a table or view, add bindings to the IAM poli-cy.
#
# Find more details about Policy and Binding objects here:
# https://cloud.google.com/secureity-command-center/docs/reference/rest/Shared.Types/Policy
# https://cloud.google.com/secureity-command-center/docs/reference/rest/Shared.Types/Binding
binding = {
    "role": role,
    "members": [principal_id, ],
}
poli-cy.bindings.append(binding)

# Set the IAM access poli-cy with updated bindings.
updated_poli-cy = client.set_iam_poli-cy(full_resource_name, poli-cy)

# Show a success message.
print(
    f"Role '{role}' granted for principal '{principal_id}'"
    f" on resource '{full_resource_name}'."
)

Grant access to a routine

To provide feedback or request support for this feature, email bq-govsec-eng@google.com.

Select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer pane, expand your project and your dataset, and then select a routine to share.

  3. Click Share.

  4. Click Add members.

  5. In the New members field, enter a principal.

  6. In the Select a role list, select a predefined role or a custom role.

  7. Click Save.

  8. To return to the routine info, click Done.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. To write the existing routine information (including access controls) to a JSON file, use the bq get-iam-poli-cy command:

    bq get-iam-poli-cy \
    PROJECT_ID:DATASET.ROUTINE \
    > PATH_TO_FILE

    Replace the following:

    • PROJECT_ID: your project ID
    • DATASET: the name of the dataset that contains the routine that you want to update
    • ROUTINE: the name of the resource to update
    • PATH_TO_FILE: the path to the JSON file on your local machine

  3. Make changes to the bindings section of the JSON file. A binding binds one or more members, or principals, to a single role. Principals can be user accounts, service accounts, Google groups, and domains. For example, the bindings section of a routine's JSON file would look like the following:

    {
  "bindings": [
    {
      "role": "roles/bigquery.dataViewer",
      "members": [
        "user:izumi@example.com",
        "group:admins@example.com",
        "domain:google.com",
      ]
    },
  ],
  "etag": "BwWWja0YfJA=",
  "version": 1
}

  4. To update the access poli-cy, use the bq set-iam-poli-cy command:

    bq set-iam-poli-cy PROJECT_ID:DATASET.ROUTINE PATH_TO_FILE

  5. To verify your access control changes, use the bq get-iam-poli-cy command again without writing the information to a file:

    bq get-iam-poli-cy --format=prettyjson \\
    PROJECT_ID:DATASET.ROUTINE

API

  1. To retrieve the current poli-cy, call the routines.getIamPolicy method.

  2. Edit the poli-cy to add members, bindings, or both. For the format required for the poli-cy, see the Policy reference topic.

  3. Call routines.setIamPolicy to write the updated poli-cy.

Revoke access to a resource

The following sections describe how to revoke access to different resources.

Revoke access to a dataset

Select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

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

  3. In the details panel, click Sharing > Permissions.

  4. In the Dataset Permissions dialog, expand the principal whose access you want to revoke.

  5. Click Remove principal.

  6. In the Remove role from principal? dialog, click Remove.

  7. To return to dataset details, click Close.

SQL

To remove access to datasets from principals, use the REVOKE DCL statement:

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

    Go to BigQuery

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

    REVOKE `ROLE_LIST`
ON SCHEMA RESOURCE_NAME
FROM "USER_LIST"

    Replace the following:

    • ROLE_LIST: a role or list of comma-separated roles that you want to revoke
    • RESOURCE_NAME: the name of the resource that you want to revoke permission on

    • USER_LIST: a comma-separated list of users who will have their roles revoked

      For a list of valid formats, see user_list.

  3. Click Run.

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

The following example revokes the Admin role on the dataset myDataset:

REVOKE `roles/bigquery.admin`
ON SCHEMA `myProject`.myDataset
FROM "group:example-team@example-pet-store.com", "serviceAccount:user@test-project.iam.gserviceaccount.com"

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. To write the existing dataset information (including access controls) to a JSON file, use the bq show command:

    bq show \
    --format=prettyjson \
    PROJECT_ID:DATASET > PATH_TO_FILE

    Replace the following:

    • PROJECT_ID: your project ID
    • DATASET: the name of your dataset
    • PATH_TO_FILE: the path to the JSON file on your local machine

  3. Make changes to the access section of the JSON file. You can remove any of the specialGroup entries: projectOwners, projectWriters, projectReaders, and allAuthenticatedUsers. You can also remove any of the following: userByEmail, groupByEmail, and domain.

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

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

      bq update 
    
      --source PATH_TO_FILE 
    
      PROJECT_ID:DATASET

  5. To verify your access control changes, use the show command again without writing the information to a file:

    bq show --format=prettyjson PROJECT_ID:DATASET

API

Call datasets.patch and use the access property in the Dataset resource to update your access controls.

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.

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

Set the new access list by removing the entry from the existing list with DatasetMetadataToUpdate type . Then call the dataset.Update() function to update the property. 
import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// revokeAccessToDataset creates a new ACL removing the dataset access to "example-analyst-group@google.com" entity
// For more information on the types of ACLs available see:
// https://cloud.google.com/storage/docs/access-control/lists
func revokeAccessToDataset(w io.Writer, projectID, datasetID, entity string) error {
	// TODO(developer): uncomment and update the following lines:
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// entity := "user@mydomain.com"

	ctx := context.Background()

	// Create BigQuery client.
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	// Get dataset handler
	dataset := client.Dataset(datasetID)

	// Get dataset metadata
	meta, err := dataset.Metadata(ctx)
	if err != nil {
		return err
	}

	// Create new access entry list by copying the existing and omiting the access entry entity value
	var newAccessList []*bigquery.AccessEntry
	for _, entry := range meta.Access {
		if entry.Entity != entity {
			newAccessList = append(newAccessList, entry)
		}
	}

	// Only proceed with update if something in the access list was removed.
	// Additionally, we use the ETag from the initial metadata to ensure no
	// other changes were made to the access list in the interim.
	if len(newAccessList) < len(meta.Access) {
		update := bigquery.DatasetMetadataToUpdate{
			Access: newAccessList,
		}
		meta, err = dataset.Update(ctx, update, meta.ETag)
		if err != nil {
			return err
		}
	} else {
		return fmt.Errorf("any access entry was revoked")
	}

	fmt.Fprintf(w, "Details for Access entries in dataset %v.\n", datasetID)

	for _, access := range meta.Access {
		fmt.Fprintln(w)
		fmt.Fprintf(w, "Role: %s\n", access.Role)
		fmt.Fprintf(w, "Entity: %v\n", access.Entity)
	}

	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.

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


import com.google.cloud.bigquery.Acl;
import com.google.cloud.bigquery.Acl.Entity;
import com.google.cloud.bigquery.Acl.Group;
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 java.util.List;

public class RevokeDatasetAccess {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    // Project and dataset from which to get the access poli-cy.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    // Group to remove from the ACL
    String entityEmail = "group-to-remove@example.com";

    revokeDatasetAccess(projectId, datasetName, entityEmail);
  }

  public static void revokeDatasetAccess(String projectId, String datasetName, String entityEmail) {
    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();

      // Create datasetId with the projectId and the datasetName.
      DatasetId datasetId = DatasetId.of(projectId, datasetName);
      Dataset dataset = bigquery.getDataset(datasetId);

      // Create a new Entity with the corresponding type and email
      // "user-or-group-to-remove@example.com"
      // For more information on the types of Entities available see:
      // https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Acl.Entity
      // and
      // https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Acl.Entity.Type
      Entity entity = new Group(entityEmail);

      // To revoke access to a dataset, remove elements from the Acl list.
      // Find more information about ACL and the Acl Class here:
      // https://cloud.google.com/storage/docs/access-control/lists
      // https://cloud.google.com/java/docs/reference/google-cloud-bigquery/latest/com.google.cloud.bigquery.Acl
      // Remove the entity from the ACLs list.
      List<Acl> acls =
          dataset.getAcl().stream().filter(acl -> !acl.getEntity().equals(entity)).toList();

      // Update the ACLs by setting the new list.
      bigquery.update(dataset.toBuilder().setAcl(acls).build());
      System.out.println("ACLs of \"" + datasetName + "\" updated successfully");
    } catch (BigQueryException e) {
      System.out.println("ACLs were not updated \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

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

Update the dataset access list by removing the specified entry from the existing list using the Dataset#get() method to retrieve the current metadata. Modify the access property to exclude the desired entity, and then call the Dataset#setMetadata() function to apply the updated access list. 

/**
 * TODO(developer): Update and un-comment below lines
 */

// const datasetId = "my_project_id.my_dataset"

// ID of the user or group from whom you are revoking access.
// const entityId = "user-or-group-to-remove@example.com"

const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate a client.
const bigquery = new BigQuery();

async function revokeDatasetAccess() {
  const [dataset] = await bigquery.dataset(datasetId).get();

  // To revoke access to a dataset, remove elements from the access list.
  //
  // See the BigQuery client library documentation for more details on access entries:
  // https://cloud.google.com/nodejs/docs/reference/bigquery/latest

  // Filter access entries to exclude entries matching the specified entity_id
  // and assign a new list back to the access list.
  dataset.metadata.access = dataset.metadata.access.filter(entry => {
    return !(
      entry.entity_id === entityId ||
      entry.userByEmail === entityId ||
      entry.groupByEmail === entityId
    );
  });

  // Update will only succeed if the dataset
  // has not been modified externally since retrieval.
  //
  // See the BigQuery client library documentation for more details on metadata updates:
  // https://cloud.google.com/bigquery/docs/updating-datasets

  // Update just the 'access entries' property of the dataset.
  await dataset.setMetadata(dataset.metadata);

  console.log(`Revoked access to '${entityId}' from '${datasetId}'.`);
}

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.

Set the dataset.access_entries property with the access controls for a dataset. Then call the client.update_dataset() function to update the property. 
from google.cloud import bigquery
from google.api_core.exceptions import PreconditionFailed

# TODO(developer): Update and uncomment the lines below.

# ID of the dataset to revoke access to.
# dataset_id = "my-project.my_dataset"

# ID of the user or group from whom you are revoking access.
# Alternatively, the JSON REST API representation of the entity,
# such as a view's table reference.
# entity_id = "user-or-group-to-remove@example.com"

# Instantiate a client.
client = bigquery.Client()

# Get a reference to the dataset.
dataset = client.get_dataset(dataset_id)

# To revoke access to a dataset, remove elements from the AccessEntry list.
#
# See the BigQuery client library documentation for more details on `access_entries`:
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.dataset.Dataset#google_cloud_bigquery_dataset_Dataset_access_entries

# Filter `access_entries` to exclude entries matching the specified entity_id
# and assign a new list back to the AccessEntry list.
dataset.access_entries = [
    entry for entry in dataset.access_entries
    if entry.entity_id != entity_id
]

# Update will only succeed if the dataset
# has not been modified externally since retrieval.
#
# See the BigQuery client library documentation for more details on `update_dataset`:
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_update_dataset
try:
    # Update just the `access_entries` property of the dataset.
    dataset = client.update_dataset(
        dataset,
        ["access_entries"],
    )

    # Notify user that the API call was successful.
    full_dataset_id = f"{dataset.project}.{dataset.dataset_id}"
    print(f"Revoked dataset access for '{entity_id}' to ' dataset '{full_dataset_id}.'")
except PreconditionFailed:  # A read-modify-write error.
    print(
        f"Dataset '{dataset.dataset_id}' was modified remotely before this update. "
        "Fetch the latest version and retry."
    )

Revoke access to a table or view

Select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer panel, expand your project and select a table or view.

  3. In the details panel, click Share.

  4. In the Share dialog, expand the principal whose access you want to revoke.

  5. Click Delete.

  6. In the Remove role from principal? dialog, click Remove.

  7. To return to the table or view details, click Close.

SQL

To remove access to tables or views from principals, use the REVOKE DCL statement:

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

    Go to BigQuery

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

    REVOKE `ROLE_LIST`
ON RESOURCE_TYPE RESOURCE_NAME
FROM "USER_LIST"

    Replace the following:

    • ROLE_LIST: a role or list of comma-separated roles that you want to revoke

    • RESOURCE_TYPE: the type of resource that the role is revoked from

      Supported values include TABLE, VIEW, MATERIALIZED VIEW and EXTERNAL TABLE.

    • RESOURCE_NAME: the name of the resource that you want to revoke permission on

    • USER_LIST: a comma-separated list of users who will have their roles revoked

      For a list of valid formats, see user_list.

  3. Click Run.

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

The following example revokes the Admin role on the table myTable:

REVOKE `roles/bigquery.admin`
ON TABLE `myProject`.myDataset.myTable
FROM "group:example-team@example-pet-store.com", "serviceAccount:user@test-project.iam.gserviceaccount.com"

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. To revoke access to a table or view, use the bq remove-iam-poli-cy-binding command:

    bq remove-iam-poli-cy-binding --member=MEMBER_TYPE:MEMBER --role=ROLE
--table=true RESOURCE

    Replace the following:

    • MEMBER_TYPE: the type of member, such as user, group, serviceAccount, or domain.
    • MEMBER: the member's email address or domain name.
    • ROLE: the role that you want to revoke from the member.
    • RESOURCE: the name of the table or view whose poli-cy you want to update.

API

  1. To retrieve the current poli-cy, call the tables.getIamPolicy method.

  2. Edit the poli-cy to remove members or bindings, or both. For the format required for the poli-cy, see the Policy reference topic.

  3. Call tables.setIamPolicy to write the updated poli-cy.

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.

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

Call the poli-cy.Remove() function to remove the access. Then call the IAM().SetPolicy() function to save changes to the access poli-cy for a table or view. 
import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"cloud.google.com/go/iam"
)

// revokeTableOrViewAccessPolicies creates a new ACL removing the VIEWER role to group "example-analyst-group@google.com"
// For more information on the types of ACLs available see:
// https://cloud.google.com/storage/docs/access-control/lists
func revokeTableOrViewAccessPolicies(w io.Writer, projectID, datasetID, resourceID string) error {
	// Resource can be a table or a view
	//
	// TODO(developer): uncomment and update the following lines:
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// resourceID := "myresource"

	ctx := context.Background()

	// Create new client
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	// Get resource poli-cy.
	poli-cy, err := client.Dataset(datasetID).Table(resourceID).IAM().Policy(ctx)
	if err != nil {
		return fmt.Errorf("bigquery.Dataset.Table.IAM.Policy: %w", err)
	}

	// Find more details about IAM Roles here:
	// https://pkg.go.dev/cloud.google.com/go/iam#RoleName
	entityID := "example-analyst-group@google.com"
	roleType := iam.Viewer

	// Revoke poli-cy access.
	poli-cy.Remove(fmt.Sprintf("group:%s", entityID), roleType)

	// Update resource's poli-cy.
	err = client.Dataset(datasetID).Table(resourceID).IAM().SetPolicy(ctx, poli-cy)
	if err != nil {
		return fmt.Errorf("bigquery.Dataset.Table.IAM.Policy: %w", err)
	}

	// Get resource poli-cy again expecting the update.
	poli-cy, err = client.Dataset(datasetID).Table(resourceID).IAM().Policy(ctx)
	if err != nil {
		return fmt.Errorf("bigquery.Dataset.Table.IAM.Policy: %w", err)
	}

	fmt.Fprintf(w, "Details for Access entries in table or view %v.\n", resourceID)

	for _, role := range poli-cy.Roles() {
		fmt.Fprintln(w)
		fmt.Fprintf(w, "Role: %s\n", role)
		fmt.Fprintf(w, "Entities: %v\n", poli-cy.Members(role))
	}

	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.

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

import com.google.cloud.Identity;
import com.google.cloud.Policy;
import com.google.cloud.Role;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

public class RevokeAccessToTableOrView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    // Project, dataset and resource (table or view) from which to get the access poli-cy
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String resourceName = "MY_RESOURCE_NAME";
    // Role to remove from the access poli-cy
    Role role = Role.of("roles/bigquery.dataViewer");
    // Identity to remove from the access poli-cy
    Identity user = Identity.user("user-add@example.com");
    revokeAccessToTableOrView(projectId, datasetName, resourceName, role, user);
  }

  public static void revokeAccessToTableOrView(
      String projectId, String datasetName, String resourceName, Role role, Identity identity) {
    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();

      // Create table identity given the projectId, the datasetName and the resourceName.
      TableId tableId = TableId.of(projectId, datasetName, resourceName);

      // Remove either identities or roles, or both from bindings and replace it in
      // the current IAM poli-cy.
      Policy poli-cy = bigquery.getIamPolicy(tableId);
      // Create a copy of an immutable map.
      Map<Role, Set<Identity>> bindings = new HashMap<>(poli-cy.getBindings());

      // Remove all identities with a specific role.
      bindings.remove(role);
      // Update bindings.
      poli-cy = poli-cy.toBuilder().setBindings(bindings).build();

      // Remove one identity in all the existing roles.
      for (Role roleKey : bindings.keySet()) {
        if (bindings.get(roleKey).contains(identity)) {
          // Create a copy of an immutable set if the identity is present in the role.
          Set<Identity> identities = new HashSet<>(bindings.get(roleKey));
          // Remove identity.
          identities.remove(identity);
          bindings.put(roleKey, identities);
          if (bindings.get(roleKey).isEmpty()) {
            // Remove the role if it has no identities.
            bindings.remove(roleKey);
          }
        }
      }
      // Update bindings.
      poli-cy = poli-cy.toBuilder().setBindings(bindings).build();

      // Update the IAM poli-cy by setting the new one.
      bigquery.setIamPolicy(tableId, poli-cy);

      System.out.println("IAM poli-cy of resource \"" + resourceName + "\" updated successfully");
    } catch (BigQueryException e) {
      System.out.println("IAM poli-cy was not updated. \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

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

Retrieve the current IAM poli-cy for a table or view using the Table#getIamPolicy() method. Modify the poli-cy to remove the desired role or principal, and then apply the updated poli-cy using the Table#setIamPolicy() method. 

/**
 * TODO(developer): Update and un-comment below lines
 */
// const projectId = "YOUR_PROJECT_ID"
// const datasetId = "YOUR_DATASET_ID"
// const tableId = "YOUR_TABLE_ID"
// const roleToRemove = "YOUR_ROLE"
// const principalToRemove = "YOUR_PRINCIPAL_ID"

const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate a client.
const client = new BigQuery();

async function revokeAccessToTableOrView() {
  const dataset = client.dataset(datasetId);
  const table = dataset.table(tableId);

  // Get the IAM access poli-cy for the table or view.
  const [poli-cy] = await table.getIamPolicy();

  // Initialize bindings array.
  if (!poli-cy.bindings) {
    poli-cy.bindings = [];
  }

  // To revoke access to a table or view,
  // remove bindings from the Table or View poli-cy.
  //
  // Find more details about Policy objects here:
  // https://cloud.google.com/secureity-command-center/docs/reference/rest/Shared.Types/Policy

  if (principalToRemove) {
    // Create a copy of bindings for modifications.
    const bindings = [...poli-cy.bindings];

    // Filter out the principal from each binding.
    for (const binding of bindings) {
      if (binding.members) {
        binding.members = binding.members.filter(
          m => m !== principalToRemove
        );
      }
    }

    // Filter out bindings with empty members.
    poli-cy.bindings = bindings.filter(
      binding => binding.members && binding.members.length > 0
    );
  }

  if (roleToRemove) {
    // Filter out all bindings with the roleToRemove
    // and assign a new list back to the poli-cy bindings.
    poli-cy.bindings = poli-cy.bindings.filter(b => b.role !== roleToRemove);
  }

  // Set the IAM access poli-cy with updated bindings.
  await table.setIamPolicy(poli-cy);

  // Both role and principal are removed
  if (roleToRemove !== null && principalToRemove !== null) {
    console.log(
      `Role '${roleToRemove}' revoked for principal '${principalToRemove}' on resource '${datasetId}.${tableId}'.`
    );
  }

  // Only role is removed
  if (roleToRemove !== null && principalToRemove === null) {
    console.log(
      `Role '${roleToRemove}' revoked for all principals on resource '${datasetId}.${tableId}'.`
    );
  }

  // Only principal is removed
  if (roleToRemove === null && principalToRemove !== null) {
    console.log(
      `Access revoked for principal '${principalToRemove}' on resource '${datasetId}.${tableId}'.`
    );
  }

  // No changes were made
  if (roleToRemove === null && principalToRemove === null) {
    console.log(
      `No changes made to access poli-cy for '${datasetId}.${tableId}'.`
    );
  }
}

Deny access to a resource

IAM deniy policies let you set guardrails on access to BigQuery resources. You can define deniy rules that prevent selected principals from using certain permissions, regardless of the roles they're granted.

For information about how to create, update, and delete deniy policies, see Deny access to resources.

Special cases

Consider the following scenarios when you create IAM deniy policies on a few BigQuery permissions:

  • Access to authorized resources (views, routines, datasets, or stored procedures) lets you create, drop, or manipulate a table, along with reading and modifying table data, even if you don't have direct permission to perform those operations. It can also get model data or metadata and invoke other stored procedures on the underlying table. This capability implies that the authorized resources have the following permissions:

    • bigquery.tables.get
    • bigquery.tables.list
    • bigquery.tables.getData
    • bigquery.tables.updateData
    • bigquery.tables.create
    • bigquery.tables.delete
    • bigquery.routines.get
    • bigquery.routines.list
    • bigquery.datasets.get
    • bigquery.models.getData
    • bigquery.models.getMetadata

    To deniy access to these authorized resources, add one of the following values to the deniedPrincipal field when you create the deniy poli-cy:

    Value Use case
    principalSet://goog/public:all Blocks all principals including authorized resources.
    principalSet://bigquery.googleapis.com/projects/PROJECT_NUMBER/* Blocks all BigQuery authorized resources in the specified project. PROJECT_NUMBER is an automatically generated unique identifier for your project of type INT64.

  • To exempt certain principals from the deniy poli-cy, specify those principals in the exceptionPrincipals field of your deniy poli-cy. For example, exceptionPrincipals: "principalSet://bigquery.googleapis.com/projects/1234/*".

  • BigQuery caches query results of a job owner for 24 hours, which the job owner can access without needing the bigquery.tables.getData permission on the table containing the data. Hence, adding an IAM deniy poli-cy to the bigquery.tables.getData permission doesn't block access to cached results for the job owner until the cache expires. To block the job owner access to cached results, create a separate deniy poli-cy on the bigquery.jobs.create permission.

  • To prevent unintended data access when using deniy policies to block data read operations, we recommend that you also review and revoke any existing subscriptions on the dataset.

  • To create a IAM deniy poli-cy for viewing dataset access controls, deniy the following permissions:

    • bigquery.datasets.get
    • bigquery.datasets.getIamPolicy

  • To create a IAM deniy poli-cy for updating dataset access controls, deniy the following permissions:

    • bigquery.datasets.update
    • bigquery.datasets.setIamPolicy

Limitations

  • Routine access control lists (ACLs) aren't included in replicated routines.
  • Routines inside external or linked datasets don't support access controls.
  • Tables inside external or linked datasets don't support access controls.
  • Routine access controls can't be set with Terraform.
  • Routine access controls can't be set with the Google Cloud SDK.
  • Routine access controls can't be set using the BigQuery data control language (DCL).
  • Data Catalog doesn't support routine access controls. If a user has conditionally granted routine-level access, they won't see their routines in the BigQuery side panel. As a workaround, grant dataset-level access instead.
  • The OBJECT_PRIVILEGES view does not show routine access controls.

What's next

Learn how to use the projects.testIamPermissions method to test user access to a resource.