Controlling access to tables and views

This page shows you 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 BigQuery web UI
  • using the bq set-iam-policy command
  • calling the tables.setIamPolicy method

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 person that will perform the steps in this topic.

Creating an access policy

To create an access policy on a table or view:

Console

  1. Open the BigQuery web UI in the Cloud Console.

    Go to the Cloud Console

  2. In the navigation panel, in the Resources section, select your project.

  3. Select the dataset that contains the table or view.

  4. Select the table or view.

  5. If you are modifying access for a table, click Share table. If you are modifying access for a view, click Share view.

  6. The Table permissions or View permissions page opens. For Add members, enter the email address of the user that will receive access to the table or view.

  7. From the Select a role dropdown, select the role that you want to grant to the user. The following shows granting joe@example.com to the BigQuery Data Viewer (roles/bigquery.dataViewer) role.

    Table permissions

  8. Click Done.

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 that you are updating.
  2. If you haven't yet added any members to the policy, the contents of policy.json will contain an etag and no other fields.

  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
    

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.

After you create a policy, subsequent retrieval of the policy will show that its version field is 1. Do not change the version number. This version number refers to the Cloud IAM policy schema version, not your version of the table policy. For information about Cloud IAM policy schema versions, see Policy versions.

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

Updating an access policy

To update an access policy on a table or view:

Console

  1. Open the BigQuery web UI in the Cloud Console.

    Go to the Cloud Console

  2. In the navigation panel, in the Resources section, select your project.

  3. Select the dataset that contains the table or view.

  4. Select the table or view.

  5. If you are modifying access for a table, click Share table. If you are modifying access for view, click Share view.

  6. The Table permissions or View permissions page opens.

    • If you want to add new members, use the same technique shown in Creating an access policy.

    • If you want to remove access for a user, search for the user by using the Search members field. For each group where you want to remove the user, expand the group, and then click the Delete delete button for the user.

    • If you want to change group membership for a user, make additions and/or deletions as described in the immediate 2 steps above.

  7. Repeat as needed for other users whose access you want to add, modify, or remove. When you are done, click Done.

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.
  2. Modify policy.json as needed.

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

    Use the same etag value that was in the policy that you retrieved. Note that the version number remains 1. This version number refers to the Cloud IAM policy schema version, not your version of the table policy. For information about Cloud 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.

    Use the same etag value that was in the policy that you retrieved. Note that the version number remains 1. This version number refers to the Cloud IAM policy schema version, not your version of the table policy. For information about Cloud IAM policy schema versions, see Policy versions.

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

For more information about Cloud 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.