OBJECT_PRIVILEGES view

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

TheINFORMATION_SCHEMA.OBJECT_PRIVILEGES view contains metadata about accesscontrol bindings that are explicitly set on BigQuery objects.This view does not contain metadata about the inherited access control bindings.

Required permissions

To query theINFORMATION_SCHEMA.OBJECT_PRIVILEGES view, you need followingIdentity and Access Management (IAM) permissions:

• bigquery.datasets.get for datasets.
• bigquery.tables.getIamPolicy for tables and views.

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

When you query theINFORMATION_SCHEMA.OBJECT_PRIVILEGES view, the queryresults contain one row for each access control binding for a resource.

TheINFORMATION_SCHEMA.OBJECT_PRIVILEGES view has the following schema:

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include aregion qualifier.A project ID is optional. If no project ID is specified, then the project thatthe query runs in is used. The following table explains the region scope forthis view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: anydataset region name. For example,`region-us`.

  Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Example

-- Returns metadata for the access control bindings for mydataset.SELECT*FROMmyproject.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGESWHEREobject_name="mydataset";

Limitations

• OBJECT_PRIVILEGES queries must contain aWHERE clause limiting queries toa single dataset, table, or view.
• Queries to retrieve access control metadata for a dataset must specify theobject_name.
• Queries to retrieve access control metadata for a table or view must specifybothobject_name ANDobject_schema.

Examples

The following example retrieves all columns from theINFORMATION_SCHEMA.OBJECT_PRIVILEGES view.

To run the query against a project other than the project that the query isrunning in, add the project ID to the region in the following format:`project_id`.`region_id`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES.

The following example gets all access control metadata for themydataset datasetin themycompany project:

SELECT*FROMmycompany.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGESWHEREobject_name="mydataset"

The results should look like the following:

  +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+  | object_catalog | object_schema | object_name | object_type |  privilege_type           | grantee                           |  +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+  | mycompany      | NULL          | mydataset   | SCHEMA      | roles/bigquery.dataEditor | projectEditor:mycompany           |  +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+  | mycompany      | NULL          | mydataset   | SCHEMA      | roles/bigquery.dataOwner  | projectOwner:mycompany            |  +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+  | mycompany      | NULL          | mydataset   | SCHEMA      | roles/bigquery.dataOwner  | user:cloudysanfrancisco@gmail.com |  +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+  | mycompany      | NULL          | mydataset   | SCHEMA      | roles/bigquery.dataViwer  | projectViewer:mycompany           |  +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+

The following example gets all access control information for thetestdata tablein themydataset dataset:

SELECT*FROMmycompany.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGESWHEREobject_schema="mydataset"ANDobject_name="testdata"

The results should look like the following:

  +----------------+---------------+--------------+-------------+----------------------+------------------------------------+  | object_catalog | object_schema |  object_name | object_type |  privilege_type      | grantee                            |  +----------------+---------------+--------------+-------------+----------------------+------------------------------------+  | mycompany      | mydataset     | testdata     | TABLE       | roles/bigquery.admin | user:baklavainthebalkans@gmail.com |  +----------------+---------------+--------------+-------------+----------------------+------------------------------------+

TheINFORMATION_SCHEMA.OBJECT_PRIVILEGES view only shows access controlbindings that are explicitly set. The first example shows that the usercloudysanfrancisco@gmail.comhas thebigquery.dataOwner role on themydataset dataset. The usercloudysanfrancisco@gmail.com inherits permissions to create, update, anddelete tables inmydataset, including thetestdata table. However, sincethose permissions were not explicitly granted on thetestdata table, theydon't appear in the results of the second example.