Data control language (DCL) statements in GoogleSQL
The BigQuery data control language (DCL) statements let you set up and control BigQuery resources using GoogleSQL 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
Arguments
role_list
: 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
: The type of resource the role is applied to. Supported values include:SCHEMA
(equivalent to dataset),TABLE
,VIEW
,EXTERNAL TABLE
.resource_name
: The name of the resource you want to grant the permission on.user_list
: 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
raha@example-pet-store.com
and sasha@example-pet-store.com
on a dataset named
myDataset
:
GRANT `roles/bigquery.dataViewer` ON SCHEMA `myProject`.myDataset
TO "user:raha@example-pet-store.com", "user:sasha@example-pet-store.com"
REVOKE
statement
Removes roles from a list of users on BigQuery resources.
Syntax
REVOKE role_list ON resource_type resource_name FROM user_list
Arguments
role_list
: 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
: The type of resource that the role will be removed from. Supported values include:SCHEMA
(equivalent to dataset),TABLE
,VIEW
,EXTERNAL TABLE
.resource_name
: The name of the resource you want to revoke the role on.user_list
: A comma separated list of users that the role is revoked from.
Example
The following example removes the bigquery.admin
role on the myDataset
dataset from the example-team@example-pet-store.com
group and a service
account:
REVOKE `roles/bigquery.admin` ON SCHEMA `myProject`.myDataset
FROM "group:example-team@example-pet-store.com", "serviceAccount:user@test-project.iam.gserviceaccount.com"