Getting access control metadata using INFORMATION_SCHEMA

INFORMATION_SCHEMA is a series of views that provide access to metadata about datasets, routines, tables, views, jobs, reservations, and streaming data.

INFORMATION_SCHEMA.OBJECT_PRIVILEGES contains metadata about access control bindings.

Required permissions

For OBJECT_PRIVILEGES, you must be granted the following permissions:

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

Syntax

Queries against this view must include a region qualifier. A project ID is optional. If no project ID is specified, then the project that the query runs in is used.

-- Returns metadata for the access control bindings for mydataset.
SELECT * FROM myproject.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
WHERE object_name = "mydataset";

`OBJECT_PRIVILEGES` view

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

The INFORMATION_SCHEMA.OBJECT_PRIVILEGES view has the following schema:

Column name	Data type	Value
`OBJECT_CATALOG`	`STRING`	The project ID of the project that contains the resource.
`OBJECT_SCHEMA`	`STRING`	The name of the dataset that contains the resource. This is null for dataset resource types.
`OBJECT_NAME`	`STRING`	The name of the table, view or dataset the policy applies to.
`OBJECT_TYPE`	`STRING`	The resource type, `SCHEMA` (dataset), `TABLE` or `VIEW`.
`PRIVLEGE_TYPE`	`STRING`	The role ID, such as `roles/bigquery.dataEditor`.
`GRANTEE`	`STRING`	The user type and user that the role is granted to.

Limitations

OBJECT_PRIVILEGES queries must contain a WHERE clause limiting queries to a single dataset, table, or view.
Queries to retrieve access control metadata for a dataset must specify the object_name.
Queries to retrieve access control metadata for a table or view must specify both object_name AND object_schema.

Examples

The following example retrieves all columns from the INFORMATION_SCHEMA.OBJECT_PRIVILEGES view.

To run the query against a project other than the project that the query is running 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 the mydataset dataset in the mycompany project:

SELECT *
FROM mycompany.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
WHERE object_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 the testdata table in the mydataset dataset:

SELECT *
FROM mycompany.`region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
WHERE object_schema = "mydataset" AND object_name = "testdata"