Control access to query BQ tables from specific project

[meln]

Hi all,

So we have 2 GCP project, let's call them `real` and `playground`. There are BQ tables under both, and if someone has access to both projects he/she should be able to see tables under both projects on BQ (assume no dataset or table restrictions).

Now I want to avoid the scenario of someone (intentionally or accidentally) querying a table in `real` and uploading that results into `playground`, because there are users in `playground` who should not have the ability to see any data in `real`. That person querying from `real` should ideally be using the `real` GCP project, and can only upload any results within `real`.

Is this possible in BigQuery?

[ms4446]

Yes, it is possible to prevent users from querying data in the "real" project and uploading the results to the "playground" project in BigQuery. This can be achieved by implementing appropriate access controls using IAM roles and permissions at the project or dataset level.

Here's a step-by-step approach:

1. Define IAM Roles: Instead of creating entirely new IAM roles, consider using BigQuery's predefined roles such as roles/bigquery.dataViewer for read-only access, roles/bigquery.dataEditor for read/write access, and roles/bigquery.dataOwner for full dataset control.

2. Assign Permissions at the Project or Dataset Level:

   • For the real project, assign roles to users who need access. For example, use roles/bigquery.dataViewer for users who only need to view data, and roles/bigquery.dataEditor or roles/bigquery.dataOwner for users who need to modify or manage datasets.
   • For the playground project, similarly assign roles based on the level of access required. Ensure that users who should not access the real project data do not have permissions to view or edit datasets in the real project.

3. Role Assignment:

   • Assign the appropriate BigQuery role to users based on their need to access the real or playground projects. Be cautious with roles that allow data modification or dataset management.

4. Prevent Cross-Project Data Access:

   • Ensure that users with access to sensitive data in the real project do not have permissions to create, write, or edit datasets and tables in the playground project.
   • Conversely, users in the playground project should not have access to the real project's datasets.

5. Implement Monitoring and Auditing: Use BigQuery's audit logs to monitor and track all activities, including queries and data movements. This helps in identifying and preventing unauthorized access or data transfers.

6. Educate Users and Establish Policies: Clearly communicate the data security policies to all users and educate them about the importance of maintaining data integrity between the two projects.

With these measures in place, you can effectively manage access and prevent unauthorized querying and data movement between the real and playground projects in BigQuery.

[meln]

Thank you for your reply,

Is it correct to understand that the only way to achieve this is to restrict the user's write access in `playground`? (assume that user has access to `real` that is). 

[ms4446]

Yes, your understanding is correct. If a user has access to sensitive data in the real project and you want to prevent them from transferring this data to the playground project, one effective approach is to restrict their write access in the playground project. Here's how this works:

1. Restrict Write Access in playground: By limiting the user's ability to create or modify datasets and tables in the playground project, you effectively prevent them from uploading or storing data from the real project there. This can be done by not granting them roles like roles/bigquery.dataEditor or roles/bigquery.dataOwner in the playground project.

2. Read-Only Access in playground: If the user needs to access data in the playground project, you can assign them a role like roles/bigquery.dataViewer, which allows them to view and query data but not create, modify, or delete datasets and tables.

3. Monitor and Audit: Even with these restrictions, it's important to monitor and audit activities to ensure compliance with your data access policies. BigQuery's audit logs can be used for this purpose.

This approach focuses on controlling what a user can do within the playground project, assuming they already have necessary access in the real project. It's a practical way to manage data security and integrity across different projects in BigQuery

[shern2tsc]

Hi! With the above solution, the user is still able to read data from the BigQuery UI in `playground` to accidentally query the datasets in `real`, which is one of the key considerations since `real` may contain sensitive/private data. The data would still be available for export/extraction.

Is it possible to use something like VPC Service Controls https://cloud.google.com/vpc-service-controls/docs/service-perimeters to control the behavior, such that the user is restricted from querying the datasets in `real` project at all?

https://cloud.google.com/vpc-service-controls/docs/service-perimeters

[ms4446]

Yes, VPC Service Controls can be a viable solution to control the behavior you described. VPC Service Controls allow you to define a security perimeter around Google Cloud resources like BigQuery datasets to control the flow of data and help mitigate data exfiltration risks.