BigQuery Authorized Views and Why You Should Be Using them.
In the database world, we have heard about simple views, complex views and even materialized views, however BigQuery has come up with something called as Authorized Views.
A lot of organizations turn to BigQuery as a datawarehouse solution in the cloud. But as the amount of data increases and the number of users grows, it is easy to lose track of what data is accessed by whom. Authorized Views can help organize and control access to your sensitive data. They don’t require you to change your logical dataset design and still enable your applications and analysts to use the full power of BigQuery.
With an authorized view, you can create a SQL view on the source data that can excludes the private data, and authorize that view. If you put this view in a separate dataset (or even project), you can then set permissions for your user to access the view, without exposing the private data. If you change the view, you need to authorize it again. As a view is just a SQL query, you have the option to restrict columns or aggregate data to protect sensitive information.
As we have understood now the authorized view allows you to share results of a query to specific user or a group without giving access to the the underlying table that is been queried. Lets see an example, your source table has the below columns
Now, you want to restrict some set of users only to access few columns like shown below without giving access to the existing dataset or its tables.
So, this is where authorized views are helpful where you can write a query to select only those columns and save it as the authorized views, lets see how we can configure this in 5 simple steps.
Assuming you already have a source dataset and table, now you need to create a new dataset to store your authorized view.
- Create a separate dataset to store the view
Once you have a new dataset created, write your query on the BigQuery console to only select EMPID, EMPNAME, EMPEMAIL and EMPDEPT.
2. Create the view in the new dataset
Once done click save view and make sure your project and new dataset are selected, give a name to the view and then click save.
3. Assign access controls to the project
Now you can see this view been stored in the new dataset, now time to give access controls to the analyst accessing the views. Give the analyst permission to query in the project by assigning the BigQuery user role.
Now, the next step is to give the analyst permission to read the dataset that contain the view, click the new dataset and then click shared dataset option, select Add members and add the member email of the group you want to give the access, select BigQuery Data Viewer permission and click Add, finally click Done.
4. Assign access controls to the dataset containing the view
Finally, you need to give view itself access to the source dataset, go to the source dataset and click shared dataset option and select authorized views, select the dataset (second dataset in this case which was created) and the view to which you need to give access.
5. Authorize the view to access the source dataset
This completes the authorized view configuration, you can test this by login with the analyst account to whom the access was given, analyst will be able to access the new dataset and the view, however the analyst will not be able to access the source dataset which in this case is the expected output obtained by setting up the authorized views.
Hope this is helpful.
