This document describes how to create authorized views in BigQuery.
You can create an authorized view in BigQuery by:
- Using the Cloud Console.
- Using the
bq
command-line tool'sbq mk
command. - Calling the
tables.insert
API method. - Using the client libraries.
Overview
Giving a view access to a dataset is also known as creating an authorized view in BigQuery. An authorized view allows you to share query results with particular users and groups without giving them access to the underlying source data. You can also use the view's SQL query to restrict the columns (fields) the users are able to query.
When making an authorized view in another dataset, both the source data dataset and authorized view dataset must be in the same regional location.
For a tutorial on creating an authorized view, see Creating an authorized view.
Required permissions
To create or update an authorized view, you need permissions on the dataset that contains the view and on the dataset that provides access to the view.
Dataset that contains the view
Views are treated as table resources in BigQuery so creating a
view requires the same permissions as creating a table. At a minimum, to create
a view, you must be granted bigquery.tables.create
permissions. The following
predefined IAM roles include bigquery.tables.create
permissions:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
In addition, if a user has bigquery.datasets.create
permissions, when that
user creates a dataset, they are granted bigquery.dataOwner
access to it.
bigquery.dataOwner
access gives the user the ability to create views in the
dataset.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Dataset that gives access to the view
At a minimum, to update dataset properties, you must be granted
bigquery.datasets.update
and bigquery.datasets.get
permissions. The
following predefined IAM roles include bigquery.datasets.update
and bigquery.datasets.get
permissions:
bigquery.dataOwner
bigquery.admin
In addition, if a user has bigquery.datasets.create
permissions, when that
user creates a dataset, they are granted bigquery.dataOwner
access to it.
bigquery.dataOwner
access gives users the ability to update properties for
datasets they create.
For more information on IAM roles and permissions in BigQuery, see Access control.
Granting views access to datasets
To grant a view access to a dataset:
Console
In the Explorer panel, expand your project and select a dataset.
In the details panel, click Share dataset.
In the Dataset permissions panel, select the Authorized views tab.
In the Share authorized view section:
- For Select project, verify the project name. If the view is in a different project, be sure to select it.
- For Select dataset, choose the dataset that contains the view.
- For Select view, select the view you are authorizing.
Click Add and then click Done.
bq
Write the existing dataset information (including access controls) to a JSON file using the
show
command. If the dataset is in a project other than your default project, add the project ID to the dataset name in the following format:project_id:dataset
.bq show \ --format=prettyjson \ project_id:dataset > path_to_file
Where:
- project_id is your project ID.
- dataset is the name of your dataset.
- path_to_file is the path to the JSON file on your local machine.
Examples:
Enter the following command to write the access controls for
mydataset
to a JSON file.mydataset
is in your default project.bq show --format=prettyjson mydataset > /tmp/mydataset.json
Enter the following command to write the access controls for
mydataset
to a JSON file.mydataset
is inmyotherproject
.bq show --format=prettyjson \ myotherproject:mydataset > /tmp/mydataset.json
Add the authorized view to the "access" section of the JSON file.
For example, the access section of a dataset's JSON file would look like the following:
{ "access": [ { "role": "READER", "specialGroup": "projectReaders" }, { "role": "WRITER", "specialGroup": "projectWriters" }, { "role": "OWNER", "specialGroup": "projectOwners" } { "role": "READER", "specialGroup": "allAuthenticatedUsers" } { "role": "READER", "domain": "[DOMAIN_NAME]" } { "role": "WRITER", "userByEmail": "[USER_EMAIL]" } { "role": "READER", "groupByEmail": "[GROUP_EMAIL]" }, { "view":{ "datasetId": "[DATASET_NAME]", "projectId": "[PROJECT_NAME]", "tableId": "[VIEW_NAME]" } } ], }
When your edits are complete, use the
update
command and include the JSON file using the--source
flag. If the dataset is in a project other than your default project, add the project ID to the dataset name in the following format:project_id:dataset
.bq update \ --source path_to_file \ project_id:dataset
Where:
- path_to_file is the path to the JSON file on your local machine.
- project_id is your project ID.
- dataset is the name of your dataset.
Examples:
Enter the following command to update the access controls for
mydataset
.mydataset
is in your default project.bq update --source /tmp/mydataset.json mydataset
Enter the following command to update the access controls for
mydataset
.mydataset
is inmyotherproject
.bq update --source /tmp/mydataset.json myotherproject:mydataset
To verify your access control changes, enter the
show
command again without writing the information to a file.bq show --format=prettyjson [DATASET]
or
bq show --format=prettyjson [PROJECT_ID]:[DATASET]
API
Call the datasets.patch
and use the access
property to update your access controls. For
more information, see Datasets.
Because the datasets.update
method replaces the entire dataset resource,
datasets.patch
is the preferred method for updating access controls.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation.
Python
Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.
Enforcing row-level access with a view
Views can be used to restrict access to particular columns (fields). If you want
to restrict access to individual rows in your table, you do not need to create
separate views for each user or group. Instead, you can use the SESSION_USER()
function to return the email address of the current user.
To display different rows to different users, add another field to your table
containing the user who is allowed to see the row. Then, create a view that uses
the SESSION_USER()
function. In the following example, the user names are
stored in the allowed_viewer
field:
SELECT COLUMN_1, COLUMN_2 FROM `dataset.view` WHERE allowed_viewer = SESSION_USER()
The limitation of this approach is that you can grant access to only one
user at a time. You can work around this limitation by making allowed_viewer
a repeated field. This approach allows you provide a list of users for each row,
but, even if you use a repeated field, storing user names in the table still
requires you to manually track the individual users that have access to each
row.
Instead, populate the allowed_viewer
field with group names, and create a
separate table that maps groups to users. The table that maps groups to users
would have a schema that stores group names and user names. For example:
{group:string, user_name:string}
. This approach allows you to manage the user
and group information separately from the table that contains the data.
If the mapping table is named
private.access_control
, the SQL query used to create the authorized view would
be:
SELECT c.customer, c.id FROM `private.customers` c INNER JOIN ( SELECT group FROM `private.access_control` WHERE SESSION_USER() = user_name) g ON c.allowed_group = g.group
Next steps
- For a tutorial on creating an authorized view, see Creating an authorized view.
- For information on creating views, see Creating views.
- For information on listing views, see Listing views.
- For information on getting view metadata, see Getting information about views.
- For information on updating views, see Updating views.
- For more information on managing views, see Managing views.