Stay organized with collections Save and categorize content based on your preferences.

Data control language (DCL) statements in Google Standard SQL

The BigQuery data control language (DCL) statements let you set up and control BigQuery resources using Google Standard SQL query syntax.

Use these statements to give or remove access to BigQuery resources.

For more information on controlling access to specific BigQuery resources, see:

Permissions required

The following permissions are required to run GRANT and REVOKE statements.

Resource Type Permissions
Dataset bigquery.datasets.update
Table bigquery.tables.setIamPolicy
View bigquery.tables.setIamPolicy

GRANT statement

Grants roles to users on BigQuery resources.

Syntax

GRANT role_list
  ON resource_type resource_name
  TO user_list

Where:

  • role_list is a role or list of comma separated roles that contains the permissions you want to grant. For more information on the types of roles available, see Understanding roles.

  • resource_type is the type of resource the role is applied to. Supported values include: SCHEMA (equivalent to dataset), TABLE, VIEW, EXTERNAL TABLE.

  • resource_name is the name of the resource you want to grant the permission on.

  • user_list is a comma separated list of users that the role is granted to.

user_list

Specify users using the following formats:

User Type Syntax Example
Google account user:$user@$domain user:first.last@example.com
Google group group:$group@$domain group:my-group@example.com
Service account serviceAccount:$user@$project.iam.gserviceaccount.com serviceAccount:robot@example.iam.gserviceaccount.com
Google domain domain:$domain domain:example.com
All Google accounts specialGroup:allAuthenticatedUsers specialGroup:allAuthenticatedUsers
All users specialGroup:allUsers specialGroup:allUsers

For more information about each type of user in the table, see Concepts related to identity.

Example

The following example grants the bigquery.dataViewer role to the users tom@example.com and sara@example.com on a dataset named mycompany:

GRANT `roles/bigquery.dataViewer` ON SCHEMA `mycompany`.revenue
TO "user:tom@example.com", "user:sara@example.com"

REVOKE statement

Removes roles from a list of users on BigQuery resources.

Syntax

REVOKE role_list
  ON {SCHEMA | TABLE | VIEW | EXTERNAL_TABLE} resource_name
  FROM user_list

Where:

  • role_list is a role or list of comma separated roles that contains the permissions you want to remove. For more information on the types of roles available, see Understanding roles.

  • resource type is the type of resource that the role will be removed from. Supported values include: SCHEMA (equivalent to dataset), TABLE, VIEW, EXTERNAL TABLE.

  • resource_name is the name of the resource you want to revoke the role on.

  • user_list is a comma separated list of users that the role is revoked from.

Example

The following example removes the bigquery.admin role on the myProject.myDataset dataset from the example-team@example.com group and a service account:

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