Data control language (DCL) statements in standard SQL

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

Access control statements

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.

  • 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 my_dataset:

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} 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.

  • 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"

Reservations statements

Use these statements to create and delete capacity commitments, reservations, and reservation assignments. For more information, see Introduction to Reservations.

CREATE CAPACITY statement

Purchases slots by creating a new capacity commitment.

CREATE CAPACITY
project_id.location_id.commitment_id
AS JSON
capacity_json_object

Where:

  • project_id is the project ID of the administration project that will maintain ownership of this commitment.
  • location_id is the location of the project.
  • commitment_id is the ID of the commitment. The value must be unique to the project and location. It must start and end with a lowercase letter or a number and contain only lowercase letters, numbers and dashes.
  • capacity_json_object is a JSON string that describes the capacity commitment.

capacity_json_object

Specifies a JSON object that contains the following fields:

NAME TYPE Details
plan String The commitment plan to purchase. Supported values include: FLEX, MONTHLY, ANNUAL. For more information, see Commitment plans.
renewal_plan String The commitment renewal plan. Applies only when plan is ANNUAL. For more information, see Annual commitment plans.
slot_count Integer The number of slots in the commitment.

Example

The following example creates a capacity commitment of 100 Flex slots that are located in the region-us region and managed by a project admin_project:

CREATE CAPACITY `admin_project.region-us.my-commitment`
AS JSON """{
 "slot_count": 100,
 "plan": "FLEX"
}"""

CREATE RESERVATION statement

Creates a reservation.

CREATE RESERVATION
project_id.location_id.reservation_id
AS JSON
reservation_json_object

Where:

  • project_id is the project ID of the administration project where the capacity commitment was created.
  • location_id is the location of the project.
  • reservation_id is the reservation ID.
  • reservation_json_object is a JSON string that describes the reservation.

reservation_json_object

Specifies a JSON object that contains the following fields:

NAME TYPE Details
ignore_idle_slots Boolean If the value is true, then the reservation uses only the slots that are provisioned to it. The default value is false. For more information, see Idle slots.
slot_capacity Integer The number of slots to allocate to the reservation.

Example

The following example creates a reservation of 100 slots in the project admin_project:

CREATE RESERVATION `admin_project.region-us.prod`
AS JSON """{
 "slot_capacity": 100
}"""

CREATE ASSIGNMENT statement

Assigns a project, folder, or organization to a reservation.

CREATE ASSIGNMENT
project_id.location_id.reservation_id.assignment_id
AS JSON
assignment_json_object

Where:

  • project_id is the project ID of the administration project where the reservation was created.
  • location_id is the location of the project.
  • reservation_id is the reservation ID.
  • assignment_id is the ID of the assignment. The value must be unique to the project and location. It must start and end with a lowercase letter or a number and contain only lowercase letters, numbers and dashes.
  • assignment_json_object is a JSON string that describes the assignment.

To remove a project from any reservations and use on-demand billing instead, set reservation_id to none.

assignment_json_object

Specifies a JSON object that contains the following fields:

NAME TYPE Details
assignee String The ID of the project, folder, or organization to assign to the reservation.
job_type String The type of job to assign to this reservation. Supported values include QUERY, PIPELINE, and ML_EXTERNAL. For more information, see Assignments.

Example

The following example assigns the project my_project to the prod reservation for query jobs:

CREATE ASSIGNMENT `admin_project.region-us.prod.my_assignment`
AS JSON """{
 "assignee": "projects/my_project",
 "job_type": "QUERY"
}"""

The following example assigns an organization to the prod reservation for pipeline jobs, such as load and export jobs:

CREATE ASSIGNMENT `admin_project.region-us.prod.my_assignment`
AS JSON """{
 "assignee": "organizations/1234",
 "job_type": "PIPELINE"
}"""

DROP CAPACITY statement

Deletes a capacity commitment.

DROP CAPACITY [IF EXISTS]
project_id.location_id.capacity-commitment-id

Where:

  • IF EXISTS: If you include this clause and the commitment doesn't exist, then the statement succeeds with no action. If you omit this clause and the commitment doesn't exist, then the statement returns an error.
  • project_id is the project ID of the administration project where the reservation was created.
  • location_id is the location of the project.
  • capacity-commitment-id is the capacity commitment ID.

You can find the capacity commitment ID by querying the INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT table.

Example

The following example deletes the capacity commitment:

DROP RESERVATION `admin_project.region-us.1234`

DROP RESERVATION statement

Deletes a reservation.

DROP RESERVATION [IF EXISTS]
project_id.location_id.reservation_id

Where:

  • IF EXISTS: If you include this clause and the reservation doesn't exist, then the statement succeeds with no action. If you omit this clause and the reservation doesn't exist, then the statement returns an error.
  • project_id is the project ID of the administration project where the reservation was created.
  • location_id is the location of the project.
  • reservation_id is the reservation ID.

Example

The following example deletes the reservation prod:

DROP RESERVATION `admin_project.region-us.prod`

DROP ASSIGNMENT statement

Deletes a reservation assignment.

DROP ASSIGNMENT [IF EXISTS]
project_id.location_id.reservation_id.assignment_id

Where:

  • IF EXISTS: If you include this clause and the assignment doesn't exist, then the statement succeeds with no action. If you omit this clause and the assignment doesn't exist, then the statement returns an error.
  • project_id is the project ID of the administration project where the reservation was created.
  • location_id is the location of the project.
  • reservation_id is the reservation ID.
  • assignment_id is the assignment ID.

You can find the assignment ID by querying the INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT table.

Example

The following example deletes an assignment from the reservation named prod:

DROP ASSIGNMENT `admin_project.region-us.prod.1234`