How to Create Authorized Views and Datasets in PAD

Authorized views and datasets allow you to share query results with certain users, groups or even entire projects without giving them access to the underlying tables. Some examples of when you might want to use authorized datasets or views include:

• Import data from your own BigQuery project to your PAD project and give all of your organization's PAD users access to it.
• Grant Viewer or Collaborator level users access to a dataset that contains views that reference other tables they should not directly have access to
• Share certain tables and datasets from a parent project with a child project in a Multi-tenant set up

It's up to you whether you authorize an entire dataset, or just individual views. Keep in mind that if you authorize an entire dataset, the project on the receiving end will be able to access all the tables in that dataset. So if you only want them to have access to some tables, then authorizing views is the right choice. If you want them to have access to all the tables, then authorizing the dataset is the right choice. 

Here's how to create authorized views or datasets:

1. Create a dataset in your PAD BigQuery project - we'll call this project B - with a view inside of it. There are quite a few ways to do this and any of the options explained in the linked Google Documentation will work.
2. In your source project,  which we’ll call Project A - the one with the data you want to expose - go to the dataset and authorize either the dataset created above in Project B, or just the individual view. To do so:  
   1. Click the three vertical dot Actions icon next to the dataset name and select Open.
   2. In the details pane that appears, click Sharing and select either the Authorize Views or the Authorize Datasets option.
      
   3. In the Authorized Dataset or Authorized View pane that appears depending on what you selected above, enter the Dataset ID or View ID of the dataset or view that you want to authorize, the one you just created in Project B, in the following format: PROJECT.AUTHORIZED_DATASET or PROJECT.DATASET.AUTHORIZED_VIEW.
   4. Click Add Authorization and then click Close.
3. You should then be able to, from Project B (make sure that is the project selected in the project dropdown in the console), create a view based on what you've authorized. If you've authorized a dataset, you'll be able to create a view based off any of the tables in Project A's dataset. If you've authorized a view, you'll only be able to create a view based of the authorized view in Project A. So for example, if you authorized a dataset, a query like the one below should now work:

CREATE VIEW dataset_in_project_b.foo(name, number) AS (
  SELECT
    name,
    number
  FROM
    project_a.federated_source_dataset.table
  WHERE
    gender = 'M'
  ORDER BY
    number DESC
;

Once the view is created, anyone in Project B can do a query on it by just writing a query against the view name "project_b.dataset_in_project_b.foo".

Have questions? Reach out to help@techallies.org.