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.
Go to BigQuery
In the
Explorer
pane, expand your project and select a dataset.
Click
more_vert
View actions
and then click
Open
.
In the
Dataset info
pane, click
person_add
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 in
myotherproject
.
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 in
myotherproject
.
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.
Remove authorization to a view
To remove authorization to a view:
Console
Go to the BigQuery page in the Google Cloud console.
Go to BigQuery
In the
Explorer
pane, expand your project and select a dataset.
Click
person_add
Sharing
>
Authorize views
.
Click
delete
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