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
- 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.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery and Dataform APIs.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery and Dataform APIs.
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:
In the Google Cloud console, go to the Dataform page.
Select a repository, and then a select a workspace.
In the Files pane, expand the
definitions/
directory.Select the
.sqlx
definition file of the table or view that you want to grant access on.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
orVIEW
.USER_LIST: the comma-separated list of users that the role is granted to.
For a list of valid formats, see user_list.
Optional: Click Format.
Execute the table or view.
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:
In the Google Cloud console, go to the Dataform page.
Select a repository, and then a select a workspace.
In the Files pane, expand the
definitions/
directory.Select the
.sqlx
definition file of the table or view that you want to revoke access on.In the
post_operations
block, enter the followingREVOKE
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
orVIEW
. - USER_LIST: the comma-separated list of users that the role is revoked from. For a list of valid formats, see user_list.
To revoke access granted in a
GRANT
statement in the file, replace theGRANT
statement with aREVOKE
statement.Optional: Click Format.
Execute the table or view.
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:
In the Google Cloud console, go to the Dataform page.
Select a repository, and then a select a workspace.
In the Files pane, next to
definitions/
, click the More menu.Click Create file.
In the Add a file path field, enter the name of the file followed by
.sqlx
afterdefinitions/
. For example,definitions/table-access.sqlx
.Filenames can only include numbers, letters, hyphens, and underscores.
Click Create file.
In the Files pane, expand the
definitions/
directory, and select the newly created file.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
orVIEW
. - 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.
Add
GRANT
andREVOKE
statements as needed.To revoke access granted in a
GRANT
statement in the file, replace theGRANT
statement with aREVOKE
statement.Removing the
GRANT
statement without adding theREVOKE
statement does not revoke access.
Optional: Click Format.
Execute the file after each update.
- If you granted or revoked access on an incremental table, remove the
GRANT
orREVOKE
statement from the file after the first execution of the statement.
- If you granted or revoked access on an incremental table, remove the
What's next
- To learn more about IAM, see IAM overview.
- To learn more about roles and permissions, see Understanding roles.
- To learn more about managing access to resources, see Manage access to projects, folders, and organizations.