A pharmaceutical company stores sensitive clinical trial data in BigQuery. The data must be accessible only by specific analysts within the R&D department, and their access should be restricted to aggregated, depersonalized views of the data. Furthermore, due to an upcoming audit, the company needs to demonstrate that access to this sensitive data is strictly controlled and auditable. How should you design the access control and auditing strategy?
- AGrant BigQuery Data Viewer role to the R&D group and rely on BigQuery's default auditing logs.
- BImplement BigQuery row-level security policies to filter sensitive rows for unauthorized users and use Cloud Monitoring for auditing.
- CExport the sensitive data to Cloud Storage, depersonalize it with Cloud Dataflow, and then load it back into a new BigQuery table for R&D access.
- DCreate BigQuery authorized views that aggregate and depersonalize data, grant access to these views via IAM, and enable Cloud Audit Logs.
Show answer & explanationAnswer & explanation
Correct answer: D. Create BigQuery authorized views that aggregate and depersonalize data, grant access to these views via IAM, and enable Cloud Audit Logs.
BigQuery authorized views allow you to share query results with specific users or groups without giving them access to the underlying tables, ensuring depersonalization and aggregation. IAM controls access to these views, and Cloud Audit Logs provide a comprehensive, immutable record of all data access and administrative activities for auditing purposes.
Why the other options are wrong
- A. BigQuery Data Viewer role grants access to raw data, which violates the depersonalization requirement. Default auditing logs are good, but the core access control is flawed.
- B. Row-level security filters rows, but the requirement is for aggregated, depersonalized views, not just filtering. While Cloud Monitoring is useful for metrics, Cloud Audit Logs are specifically designed for comprehensive auditing of administrative activities and data access.
- C. This approach creates data duplication and additional processing overhead, which is less efficient and more complex than using native BigQuery features for dynamic depersonalization.
BigQuery Authorized Views & Cloud Audit Logs
BigQuery authorized views allow specific users or service accounts to query a view without having direct access to the underlying tables. Cloud Audit Logs record administrative activities and data access events across Google Cloud services.
- Authorized views enable data sharing with controlled access to derived data.
- Views can perform aggregation, depersonalization, and transformation.
- Cloud Audit Logs capture 'Admin Activity' and 'Data Access' events.
- Audit logs are immutable and crucial for compliance and forensics.
Memory trick: Authorized Views with an Audit Trail, keeps your BigQuery data safe without fail!