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"

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 |
  +----------------+---------------+--------------+-------------+----------------------+------------------------------------+