Control access to individual tables with IAM

This document shows you how to grant and revoke BigQuery Identity and Access Management (IAM) roles for individual Dataform tables and views.

Dataform and BigQuery use IAM for access control. For more information about Dataform roles and permissions in IAM, see Control access with IAM.

When Dataform executes a table or view, it creates the resource in BigQuery. During development in Dataform, you can grant BigQuery roles to individual tables and views to control their access in BigQuery after execution.

For more information about granting and revoking access to resources, see Grant access to a resource.

Before you begin

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

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

    Go to project selector

  3. Make sure that billing is enabled for your Google Cloud project.

  4. Enable the BigQuery and Dataform APIs.

    Enable the APIs

    5.

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

    Go to project selector

  6. Make sure that billing is enabled for your Google Cloud project.

  7. Enable the BigQuery and Dataform APIs.

    Enable the APIs

    8.

Grant BigQuery roles to a table or view

You can grant BigQuery roles to a table or view in Dataform by adding a post_operations block with the GRANT DCL statement to the .sqlx definition file of the selected table or view.

To grant BigQuery roles to a selected table or view, follow these steps:

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

    Go to the Dataform page

  2. Select a repository, and then a select a workspace.

  3. In the Files pane, expand the definitions/ directory.

  4. Select the .sqlx definition file of the table or view that you want to grant access on.

  5. In the file, enter the following code snippet:

    post_operations {
    GRANT "ROLE_LIST"
    ON "RESOURCE_TYPE" ${self()}
    TO "USER_LIST"
}

    Replace the following:

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

    • RESOURCE_TYPE: TABLE or VIEW.

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

      For a list of valid formats, see user_list.

  6. Optional: Click Format.

  7. Execute the table or view.

  8. If you granted access on an incremental table, remove the GRANT statement from the table definition file after the first execution.

The following code sample shows the BigQuery Viewer role granted on a table to a user:

config { type: "table" }

SELECT ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON TABLE ${self()}
  TO "user:[email protected]"
}

Revoke BigQuery roles from a table or view

You can revoke BigQuery roles from a table or view by adding a post_operations block with the REVOKE DCL statement. to the .sqlx definition file of the selected table or view.

To revoke BigQuery roles from a selected table or view, follow these steps:

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

    Go to the Dataform page

  2. Select a repository, and then a select a workspace.

  3. In the Files pane, expand the definitions/ directory.

  4. Select the .sqlx definition file of the table or view that you want to revoke access on.

  5. In the post_operations block, enter the following REVOKE statement:

        REVOKE "ROLE_LIST"
    ON "RESOURCE_TYPE" ${self()}
    TO "USER_LIST"

    Replace the following:

    • ROLE_LIST: the BigQuery role or list of comma-separated BigQuery roles that you want to revoke.
    • RESOURCE_TYPE: TABLE or VIEW.
    • USER_LIST: the comma-separated list of users that the role is revoked from. For a list of valid formats, see user_list.

  6. To revoke access granted in a GRANT statement in the file, replace the GRANT statement with a REVOKE statement.

  7. Optional: Click Format.

  8. Execute the table or view.

  9. If you revoked access to an incremental table, remove the REVOKE statement from the table definition file after the first execution.

The following code sample shows the BigQuery Viewer role revoked from a user on a table:

config { type: "table" }

SELECT ...

post_operations {
  REVOKE `roles/bigquery.dataViewer`
  ON TABLE ${self()}
  FROM "user:[email protected]"
}

Collectively manage BigQuery roles for tables and views

To control BigQuery access to individual tables and views in a single location, you can create a dedicated type: "operations" file with GRANT and REVOKE DCL statements.

To manage BigQuery table access in a single type: "operations" file, follow these steps:

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

    Go to the Dataform page

  2. Select a repository, and then a select a workspace.

  3. In the Files pane, next to definitions/, click the More menu.

  4. Click Create file.

  5. In the Add a file path field, enter the name of the file followed by .sqlx after definitions/. For example, definitions/table-access.sqlx.

    Filenames can only include numbers, letters, hyphens, and underscores.

  6. Click Create file.

  7. In the Files pane, expand the definitions/ directory, and select the newly created file.

  8. In the file, enter the following code snippet:

      config { type: "operations" }

  GRANT "ROLE_LIST"
  ON RESOURCE_TYPE RESOURCE_NAME
  TO "USER_LIST"

  REVOKE "ROLE_LIST"
  ON { "<var>" }}RESOURCE_TYPE RESOURCE_NAME
  TO "USER_LIST"

    Replace the following:

    • ROLE_LIST: the BigQuery role or list of comma-separated BigQuery roles that you want to grant or revoke.
    • RESOURCE_TYPE: TABLE or VIEW.
    • RESOURCE_NAME: the name of the table or view.
    • USER_LIST: the comma-separated list of users that the role is granted to or revoked from. For a list of valid formats, see user_list.

  9. Add GRANT and REVOKE statements as needed.

    1. To revoke access granted in a GRANT statement in the file, replace the GRANT statement with a REVOKE statement.

      Removing the GRANT statement without adding the REVOKE statement does not revoke access.

  10. Optional: Click Format.

  11. Execute the file after each update.

    1. If you granted or revoked access on an incremental table, remove the GRANT or REVOKE statement from the file after the first execution of the statement.

What's next