Authorized views, datasets and routines in BigQuery

An authorized view lets people query a slice of a table without any access to the table. How the authorization is stored, who needs which role, why it attaches to a view's name, and how authorized datasets and routines extend it.

Normally a user who queries a view needs read access to the view and to every table behind it. An authorized view removes the second requirement. You add the view to the access list of the dataset that holds the source tables. From then on the view can read that dataset on behalf of whoever queries it, so you can give a group the view and nothing else. They see the columns and rows the view’s SQL returns, and the table stays closed to them.

The same idea exists for two other objects. An authorized dataset authorizes every current and future view in a dataset at once. An authorized routine does it for a user-defined function, a table function or a stored procedure. All three are entries in the access list of the dataset being shared.

Set it up in three steps

Say finance.payments has card numbers that analysts must not see.

1. Create a second dataset for the view, for example finance_shared. Google says the view must live in a different dataset from its source tables, and both datasets must be in the same location.

2. Create the view there, leaving out the sensitive columns:

   CREATE VIEW finance_shared.payments_public AS
   SELECT payment_id, amount, country, paid_at
   FROM finance.payments;

3. Add the view to the access list of the source dataset, finance.

Authorizing is not an SQL statement. You edit the dataset’s access list in the console (Sharing, then Authorize views), with bq or the API, or with Terraform. With bq, you export the dataset’s metadata, add an entry and write it back:

bq show --format=prettyjson my-project:finance > finance.json
# add to the "access" array:
#   { "view": { "projectId": "my-project",
#               "datasetId": "finance_shared",
#               "tableId": "payments_public" } }
bq update --source finance.json my-project:finance

We ran this against BigQuery and the view entry showed up in the access list after the update. The analysts get two grants of their own, and none on finance: roles/bigquery.dataViewer on finance_shared, and roles/bigquery.jobUser on the project where their queries run.

Who needs what

The query job runs in the caller’s project, so that project is the one billed for the bytes. If the view is in a different project than the source, people need jobUser only in the project where they run queries. See how queries are billed for the bytes themselves, which are the base-table columns the view reads.

The authorization belongs to a name

The access entry stores the view’s project, dataset and name. It does not store an ID. We checked what that means for a view that is replaced or deleted:

So anyone who can create views in the view’s dataset can read the source dataset through a view with that name. Authorized views protect the source data only as well as the dataset that holds them. Give edit access to that dataset to the same small group that could edit the source. Google adds that a deleted view can take up to 24 hours to leave the list, and counts against the limit meanwhile.

Because replacing a view keeps the authorization, you can change the SQL of an authorized view without touching the source dataset’s access list.

Authorized datasets: all the views in a dataset

Authorizing views one by one gets long. A dataset’s access list holds up to 2,500 entries in total, counting authorized views, datasets and routines together. An authorized dataset authorizes every view in another dataset, including views created later:

{ "dataset": {
    "dataset": { "projectId": "my-project", "datasetId": "finance_shared" },
    "targetTypes": ["VIEWS"] } }

We added this entry with bq update and it was accepted. Adding a second entry for the same dataset failed with Duplicate authorized dataset. The VIEWS target type is the only one in Google’s documentation. Users then need access to the shared dataset’s views, as before, and nothing on the source. Google recommends this for new designs, and especially for multi-tenant ones. It also makes the replaced-name issue above worse: everything created in the authorized dataset is authorized.

Authorized routines

A user-defined function, table function or stored procedure normally runs with the caller’s access, so the caller must be able to read the tables it touches. Authorizing the routine on the source dataset lets it read them for callers who can’t. This is how you expose an aggregate:

CREATE FUNCTION finance_shared.payment_count()
AS ((SELECT COUNT(*) FROM finance.payments));

{ "routine": { "projectId": "my-project",
               "datasetId": "finance_shared",
               "routineId": "payment_count" } }

For a stored procedure, the entry can carry a role, one of the routine roles (roles/bigquery.routineDataViewer, routineDataEditor, routineAdmin or routineMetadataViewer), which limits what the procedure can do in the source dataset. Authorizing a view that doesn’t exist fails, and so does authorizing a routine that doesn’t exist.

Routines behave differently from views when they change. Google says that updating a routine makes its authorization expire, and that BigQuery removes the stale entry within 24 hours. We replaced a function with CREATE OR REPLACE FUNCTION and the entry was still listed 30 seconds later. Re-check the access list after you change an authorized routine and authorize it again if it is gone. See user-defined functions.

Limits

• The view and the source data must be in the same location.
• You can’t create an authorized view in a secondary dataset replica.
• Authorized views work with materialized views too, called authorized materialized views. See materialized views.
• With VPC Service Controls, the ingress and egress rules must allow the caller to reach both the view’s project and the source project.

When to use something else

An authorized view hides columns and filters rows, but the filter is part of the view’s SQL, one view per audience. Google lists the alternatives. Row-level security attaches filters to the table itself, so one table serves many users. Column-level security hides columns with policy tags. A separate table is the most isolated option and the least flexible, and it also hides how many rows exist. Google recommends authorized views when flexibility and performance matter, and separate tables when security matters most, since crafted queries and query timing can leak through a view.