Authorized views and materialized views
This document describes how to create authorized views and materialized views in BigQuery.
Authorized views and authorized materialized views let you share query results with particular users and groups without giving them access to the underlying source data. The view or materialized view is given access to the data, instead of the user. You can also use the SQL query that creates the view or materialized view to restrict the columns and fields that users are able to query.
When making an authorized view or materialized view in another dataset, both the source data dataset and authorized view dataset must be in the same regional location.
For information about authorizing all of the views in a dataset, as opposed to authorizing individual views, see Authorized datasets.
Before you begin
Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.
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.
Permissions on the 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. You must also have permissions to query any tables that are referenced by the view's SQL query.
To create a view, you need the bigquery.tables.create
IAM
permission. The roles/bigquery.dataEditor
predefined IAM role
includes the permissions that you need to create a view.
Additionally, if you have the bigquery.datasets.create
permission, you can
create views in the datasets that you create. To create a view for data that you
don't own, you must have bigquery.tables.getData
permission for that table.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Permissions on the dataset that gives access to the view
To update dataset properties, you need the following IAM permissions:
bigquery.datasets.update
bigquery.datasets.setIamPolicy
(only required when updating dataset access controls in the Google Cloud console)
The roles/bigquery.dataOwner
predefined IAM role includes the
permissions that you need to update dataset properties.
Additionally, if you have the bigquery.datasets.create
permission, you can
update properties of the datasets that you create.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Authorize a view
To grant a view access to a dataset, follow these steps:
Console
Go to the BigQuery page in the Google Cloud console.
In the Explorer pane, expand your project and select a dataset.
Click
View actions and then click Open.In the Dataset info pane, click
Sharing and then select Authorize Views.For Authorize view, type the name of the view to authorize.
Click Add authorization.
Click Close.
bq
Write the existing dataset information (including access controls) to a JSON file using the
bq 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
bq 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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Remove authorization to a view
To remove authorization to a view:
Console
Go to the BigQuery page in the Google Cloud console.
In the Explorer pane, expand your project and select a dataset.
Click > Authorize views.
SharingClick
Remove authorization.Click Close.
Quotas and limits
- Authorized views are subject to dataset limits. For more information, see Dataset limits.
- If you remove an authorized view, it can take up to 24 hours for all references to the view to be removed from the system. To avoid errors, either wait 24 hours before reusing the name of a removed view, or create a unique name for your view.
Enforce 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 usernames 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 lets you provide a list of users for each row.
But even if you use a repeated field, storing usernames 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 usernames. For example:
{group:string, user_name:string}
. This approach lets you 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
What's next
- For a tutorial on creating an authorized view, see Create an authorized view.
- For information on creating views, see Create views.
- For information on creating materialized views, see Create materialized views.
- For information on getting view metadata, see Get information about views.
- For more information on managing views, see Manage views.
- For information about authorizing all of the views in a dataset, see Authorized datasets.