OBJECT_PRIVILEGES view

Preview

This product or feature is covered by the Pre-GA Offerings Terms of the Google Cloud Terms of Service. Pre-GA products and features might have limited support, and changes to pre-GA products and features might not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

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

Required permissions

To query the INFORMATION_SCHEMA.OBJECT_PRIVILEGES view, you need following Identity and Access Management (IAM) permissions:

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

For more information about BigQuery permissions, see Access control with IAM.

Schema

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, such as SCHEMA (dataset), TABLE, VIEW, and EXTERNAL.
PRIVLEGE_TYPE	STRING	The role ID, such as roles/bigquery.dataEditor.
GRANTEE	STRING	The user type and user that the role is granted to.

Scope and 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. The following table explains the region scope for this view:

View name	Resource scope	Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES	Project level	REGION

Replace the following:

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

Example

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

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:[e-mail] |
  +----------------+---------------+-------------+-------------+---------------------------+-----------------------------------+
  | 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:[e-mail] |
  +----------------+---------------+--------------+-------------+----------------------+------------------------------------+