ALICIA WILLIAMS: One of the benefits of a data warehouse,
like BigQuery, is the improved simplicity and speed
of bringing data to your analysts and decision-makers.
Data needs vary across a company based
on organizational function, geography,
and more, so it's important to be
able to provide customized access to your users
without adding unnecessary risk.
In today's episode of "BigQuery Spotlight,"
we're showing you how to create authorized views, which
give you the ability to define and share a specific subset
or view of your BigQuery data.
Stay tuned.
[MUSIC PLAYING]
BigQuery organizes data into containers called datasets.
These datasets function like top-level folders
that organize and control access to the underlying tables.
In our episode, "Managing Access with Cloud IAM,"
we talked about how to manage access
at the data set and table level using
the Identity and Access Management Service,
or Cloud IAM.
If you want to check it out, we've linked it below.
Cloud IAM can restrict access down to the table level.
But what if you want to restrict access for members
to parts of a table?
In BigQuery, one way to accomplish this task
is by creating authorized views.
An authorized view allows you to share
the results of a query with a specific user or group
without giving that user access to the tables that
were queried.
For example, let's say as a BigQuery data admin,
you wanted to give an analyst at your company access to a table
containing your service request data,
but you wanted to remove the full address of the service
location.
You could write a query that didn't include the address
column and then save the results as an authorized view
to share with the analyst.
Creating an authorized view follows a defined series
of steps.
Step one, start with your source dataset.
This is the dataset with the sensitive data
you don't want to share.
Step two, create a separate dataset to store the view.
Authorized views require the source data
sit in a separate dataset from the view.
Don't worry about the reason why.
You'll see in step six.
Step three, create a view in the new dataset.
In the new dataset, you create the view
you intend to share with your data analysts.
This view is created using a SQL query that
includes only the data the analysts need to see.
Step four, assign access controls to the project.
In order to query the view, your analyst
needs permission to run queries.
Assigning your analyst the BigQuery user role
gives them this ability.
This access does not give them the ability
to view or query any datasets within the project.
Step five, assign access controls
to the dataset containing the view.
In order for your analysts to query the view,
they need to be granted the BigQuery
data viewer role for that specific dataset that
contains the view.
And finally, step six, authorize the view
to access the source dataset.
This gives the view itself access to the source data.
We need to do this because the view takes
on the permissions of the person using it,
and since the analyst doesn't have access to the source
table, they'd otherwise get an error
if they tried to query this view.
We're going to jump into the console and see how it's done.
You can follow along in your own BigQuery sandbox, which
you can set up for free following the instructions
in the video link below.
We're using two sandboxes in order
to represent the perspectives of both the data
admin and the analyst.
OK.
Starting out as the data admin, step one,
you'll need a dataset to store your full source
data containing the sensitive address field.
From the BigQuery Console, select your project
and click CREATE DATASET.
Give your dataset a name and click the Create dataset
button.
Now you'll populate a table within the dataset using a SQL
query.
Click COMPOSE NEW QUERY and copy and paste
the Source Data Query that's linked in the description
below into the Query Editor.
Before running the query, go into Query Settings
and check the box for Set a destination table
for query results.
Verify your project and dataset name are selected,
then name your table service_requests.
Click Save.
Now click Run.
When the query completes, you can select your new table
in the left-hand nav and click Preview to see
that the data was populated.
Step two, time to create a separate dataset where you'll
put the view for your analyst.
You'll follow the same steps we just performed.
Select your project and click CREATE DATASET.
Name your dataset shared_views and click Create dataset.
Step three, now you'll create the view for your analysts.
Click COMPOSE NEW QUERY and copy and paste
the View Query linked in the description
below into the Query Editor.
This query selects all the columns from the source data,
except for the sensitive address field.
Now click save view.
Make sure your project and dataset are selected
and give the view a name.
Click SAVE.
You can see that the view is now populated in the left-hand nav.
We have our source table and our analyst view set up.
Time to start assigning access controls for our analyst.
Step four, give the analyst permission
to query in the project by assigning the BigQuery user
role.
From the navigation menu, head to IAM & Admin and click IAM.
Click ADD.
In the members field, enter the analyst's email address,
or as we are using, a Google Group
that contains your analysts.
In the roles field, click Select a role
and choose BigQuery User.
Click Save.
Head back to BigQuery by using the search bar in the console.
Step five, next, you'll give the analyst permission
to read the dataset that contains the view.
Select the shared_views dataset and then click SHARE DATASET.
Click Add members.
Again, enter the email or group for your analysts.
Select the role BigQuery Data Viewer,
then click Add, and finally, click Done.
Step six, you need to give the view itself
access to the source dataset.
This is what is meant by an authorized view.
From the source dataset, click SHARE DATASET.
And then this time, head to the AUTHORIZED VIEWS section.
Change the dataset to shared_views
and select your view for the table.
Click Add and then click Done.
Your authorized view configuration is now complete.
Let's flip over to the analyst's account
to see the result from their perspective.
The analyst is logged in and has selected the same project,
analytics-source-data.
Let's verify what data they are able to view.
As you can see, the analyst is able to both access the view
and run queries over it, but when
they try to view the source table, they do not have access.
Now you're ready to use this same process
to create any number of authorized views customized
for your groups and users at your company.
If you want to learn more about BigQuery,
check out the rest of our playlist.
And don't forget to subscribe to our channel
on YouTube for other great content.
Look out for our next episode of "BigQuery Spotlight,"
and remember, stay curious.
[MUSIC PLAYING]