Google Cloud BigQuery Security

BigQuery Encryption

BigQuery automatically encrypts all data before it is written to disk

By default, Google uses the Default Encryption at Rest and manages the key encryption keys used for data protection.
BigQuery also supports customer-managed encryption keys, to encrypt individual values within a table.

BigQuery uses TLS for data in transit encryption
Cloud Data Loss Prevention (Cloud DLP) can be used to scan the BigQuery tables and to protect sensitive data and meet compliance requirements.

BigQuery IAM Roles

BigQuery supports access control of datasets and tables using IAM

Primitive Roles
- primitive roles act at the project level
- By default, granting access to a project also grants access to datasets within it unless overridden
- are not limited to BigQuery resources only
- can separate data access permissions from job-running permissions
- Viewer
  - View all datasets
  - Run Jobs/Queries
  - View and update all jobs that they started
- Editor
  - All Viewer access
  - Modify or delete all tables
  - Create new datasets
- Owner
  - All Editor access
  - list, modify, or delete all datasets
  - View all jobs
Predefined Roles
- dataViewer, dataEditor, and dataOwner roles
  - are similar to the primitive roles except
    - can be assigned for individual datasets
    - don’t give users permission to run jobs or queries
- user, jobUser roles
  - give users permission to run jobs or queries
  - A jobUser can only start jobs and cancel jobs, but cannot list datasets or tables
  - A user, on the other hand, can perform a variety of other tasks, such as listing or creating datasets
  - User or group granted the user role at the project level can create datasets and can run query jobs against tables in those datasets.
  - user role does not give permission to query data, view table data, or view table schema details for datasets the user did not create. Need to have the dataViewer role for the same.

Authorized Views

Authorized views help provide view access to a dataset

Use authorized views to restrict access at a lower resource level such as the table, column, row, or cell.
An authorized view allows sharing query results with particular users and groups without giving them access to the underlying tables.
Authorized View’s SQL query can be used to restrict the columns (fields) the users are able to query.

Authorized views HAVE to be created in a separate dataset from the source dataset. As access controls can be assigned only at the dataset level, if the view is created in the same dataset as the source data, the users would have access to both the view and the data.
Authorized View creation process
- Create a separate dataset to store the view.
- Create the view in the new dataset
- Give the group read access to the dataset containing the view
- Authorize the view to access the source dataset
- Give the group bigquery.user role to run jobs, including query jobs within the project
Project-level bigquery.user role does not give the users the ability to view or query table data in the dataset containing the tables queried by the view. They need READER access to the dataset containing the view.

Fine-Grained Access Control

BigQuery supports access controls at the project, dataset, and table levels

BigQuery also supports fine-grained row and column level security
BigQuery provides fine-grained access to sensitive columns using policy tags, or type-based classification, of data.
Using BigQuery column-level security, you can create policies that check, at query time, whether a user has proper access.

Row-level security extends the principle of least privilege by enabling fine-grained access control to a subset of data in a BigQuery table, by means of row-level access policies.

GCP Certification Exam Practice Questions

Questions are collected from Internet and the answers are marked as per my knowledge and understanding (which might differ with yours).

GCP services are updated everyday and both the answers and questions might be outdated soon, so research accordingly.

GCP exam questions are not updated to keep up the pace with GCP updates, so even if the underlying feature has changed the question might not be updated

Open to further feedback, discussion and correction.

You have multiple Data Analysts who work with the dataset hosted in BigQuery within the same project. As a BigQuery Administrator, you are required to grant the data analyst only the privilege to create jobs/queries and the ability to cancel self-submitted jobs. Which role should assign to the user?
1. User
2. Jobuser
3. Owner
4. Viewer
Your analytics system executes queries against a BigQuery dataset. The SQL query is executed in batch and passes the contents of a SQL file to the BigQuery CLI. Then it redirects the BigQuery CLI output to another process. However, you are getting a permission error from the BigQuery CLI when the queries are executed. You want to resolve the issue. What should you do?
1. Grant the service account BigQuery Data Viewer and BigQuery Job User roles.
2. Grant the service account BigQuery Data Editor and BigQuery Data Viewer roles.
3. Create a view in BigQuery from the SQL query and SELECT * from the view in the CLI.
4. Create a new dataset in BigQuery, and copy the source table to the new dataset Query the new dataset and table from the CLI.

You are responsible for the security and access control to a BigQuery dataset hosted within a project. Multiple users from multiple teams need to have access to the different tables within the dataset. How can access be control?
1. Create Authorized views for tables in a separate project and grant access to the teams
2. Create Authorized views for tables in the same project and grant access to the teams
3. Create Materialized views for tables in a separate project and grant access to the teams
4. Create Materialized views for tables in the same project and grant access to the teams

References

Google_Cloud_BigQuery_Data_Goverance