Controlling access to tables and views

This document describes how to use BigQuery Table ACL to control access to tables and views. For an overview of BigQuery Table ACL, see Introduction to table access controls.

After you create a table or view, you can set its policy in the following ways:

  • using the Cloud Console
  • using the bq set-iam-policy command
  • calling the tables.setIamPolicy method
  • using the GRANT or REVOKE data control language statements

You can use BigQuery Table ACL to set access on both logical views and dataset-level authorized views. A logical view can also reference other source tables and views that you shared using BigQuery Table ACL.

Before you begin

  1. Create the table or view that you want to use with BigQuery Table ACL.

  2. Grant the BigQuery Data Owner (roles/bigquery.dataOwner) role or the BigQuery Admin (roles/bigquery.admin) role to the user that will perform the steps in this topic.

  3. For more information about Identity and Access Management (IAM) policies, see Understanding policies and the Policy reference topic.

Creating an access policy

To create an access policy on a table or view:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

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

  3. Expand the dataset and select a table or view.

  4. Click Share.

  5. On the Share page, to add a user (or principal), click Add principal.

  6. On the Add principals page, do the following:

    • For New principals, enter a user. You can add individual users, groups, service accounts, and workspace domains.
    • From the Select a role drop-down list, select the role that you want to grant to the user.
    • Click Add condition to add conditional access for the user.
  7. Click Save to save the changes for the new user.

  8. Click Close to close the Share page.

SQL

Use the following GRANT statement to grant the Data Viewer (roles/bigquery.dataViewer) role to user joe@example.com on a table in your dataset.

  GRANT `roles/bigquery.dataViewer`
  ON TABLE DATASET.TABLE_OR_VIEW
  TO "user:joe@example.com"
 

Replace DATASET with the name of the dataset that the resource is in.

Replace TABLE_OR_VIEW with the table or view that you are granting access to.

For more information on the GRANT DCL statement, see Data control language statements in standard SQL.

bq

  1. Retrieve the existing policy to a local file.

    bq get-iam-policy \
     project-id:dataset.table_or_view \
     > policy.json
    

    where:

    • project-id is your project ID.
    • dataset is the name of the dataset that contains the resource (table or view) that you are updating.
    • table_or_view is the name of the resource that you are updating.

    More examples of identifying a table or view, and redirecting the policy output to a file:

    • bq get-iam-policy dataset1.table1 > policy.json
    • bq get-iam-policy --project_id=project1 -t dataset1.table1 > policy.json
    • bq get-iam-policy project1:dataset1.table1 > policy.json
  2. If you haven't yet added any members to the policy, the policy.json file will contain an etag value and no other fields. See For more information about how to format the policy.json file, see Understanding policies.

  3. To add the first member, add a bindings field to the policy. For example, to grant the BigQuery Data Viewer (roles/bigquery.dataViewer) role to joe@example.com:

    "bindings": [
     {
       "members": [
         "user:joe@example.com"
       ],
       "role": "roles/bigquery.dataViewer"
     }
    ]
    

    If you need to add more members to an existing binding, just add the member. This example shows granting jane@example.com the BigQuery Data Viewer (roles/bigquery.dataViewer) role, for a binding that already exists.

    "members": [
           "user:joe@example.com",
           "user:jane@example.com"
         ],
         "role": "roles/bigquery.dataViewer"
       }
    
  4. Update the policy.

    bq set-iam-policy \
     project-id:dataset.table_or_view \
     policy.json
    

    For information about IAM policy schema versions, see Policy versions.

API

  1. Call tables.getIamPolicy to retrieve the current policy.

  2. Edit the policy to add members and/or bindings. See the bq example for the format of the policy.

  3. Call tables.setIamPolicy to write the new policy.

For information about IAM policy schema versions, see Policy versions.

Java

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

import com.google.cloud.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;

// Sample to create iam policy for table
public class CreateIamPolicy {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createIamPolicy(datasetName, tableName);
  }

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

      TableId tableId = TableId.of(datasetName, tableName);

      Policy policy = bigquery.getIamPolicy(tableId);
      policy
          .toBuilder()
          .addIdentity(Role.of("roles/bigquery.dataViewer"), Identity.allUsers())
          .build();
      bigquery.setIamPolicy(tableId, policy);
      System.out.println("Iam policy created successfully");
    } catch (BigQueryException e) {
      System.out.println("Iam policy was not created. \n" + e.toString());
    }
  }
}

Updating an access policy

To update an access policy on a table or view:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

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

  3. Expand the dataset and select a table or view.

  4. Click Share.

  5. On the Share page, do the following:

    • To add new users, follow the steps as shown in Creating an access policy.

    • To remove access for a user, either search for the user by using the Search field or expand the role to which the user belongs. For the user you want to remove, click Delete .

    • To modify access for a user, click Edit .

  6. Click Save when you have made the changes.

  7. Click Close to close the Share page.

SQL

Use the following REVOKE statement to remove the Data Viewer (roles/bigquery.dataViewer) role from user joe@example.com on a table in your dataset.

  REVOKE `roles/bigquery.dataViewer`
  ON TABLE DATASET.TABLE_OR_VIEW
  FROM "user:joe@example.com"
 

Replace DATASET with the name of the dataset that the resource is in.

Replace TABLE_OR_VIEW with the table or view that you are revoking access from.

For more information on the REVOKE DCL statement, see Data control language statements in standard SQL.

bq

  1. Retrieve the existing policy to a local file.

    bq get-iam-policy --format=prettyjson \
     project-id:dataset.table_or_view \
     > policy.json
    

    where:

    • project-id is your project ID.
    • dataset is the name of the dataset that contains the table that you are updating.
    • table_or_view is the name of the table or view you are updating.

    More examples of identifying a table or view, and redirecting the policy output to a file:

    • bq get-iam-policy dataset1.table1 > policy.json
    • bq get-iam-policy --project_id=project1 -t dataset1.table1 > policy.json
    • bq get-iam-policy project1:dataset1.table1 > policy.json
  2. Modify policy.json as needed.

    For more information about how to format the policy.json file, see Understanding policies.

    For information about IAM policy schema versions, see Policy versions.

  3. Update the policy.

    bq set-iam-policy \
     project-id:dataset.table_or_view \
     policy.json
    

API

  1. Call tables.getIamPolicy to retrieve the current policy.

  2. Edit the policy to add members and/or bindings.

    For the format required for the policy, see the Policy reference topic.

  3. Call tables.setIamPolicy to write the updated policy.

Java

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

import com.google.cloud.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.Map;
import java.util.Set;

// Sample to update iam policy in table
public class UpdateIamPolicy {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    updateIamPolicy(datasetName, tableName);
  }

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

      TableId tableId = TableId.of(datasetName, tableName);

      Policy policy = bigquery.getIamPolicy(tableId);
      Map<Role, Set<Identity>> binding = new HashMap<>(policy.getBindings());
      binding.remove(Role.of("roles/bigquery.dataViewer"));

      policy.toBuilder().setBindings(binding).build();
      bigquery.setIamPolicy(tableId, policy);

      System.out.println("Iam policy updated successfully");
    } catch (BigQueryException e) {
      System.out.println("Iam policy was not updated. \n" + e.toString());
    }
  }
}

For more information about Identity and Access Management policies, see Understanding policies and the Policy reference topic.

What's next

  • Read the FAQ.
  • Learn about audit logging of BigQuery Table ACL admin activities at Audit logging.