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, 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. |
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
ANDobject_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 | +----------------+---------------+--------------+-------------+----------------------+------------------------------------+