Best practices for row-level security in BigQuery

This document explains best practices when using row-level security.

Before you read this document, familiarize yourself with row-level security by reading Introduction to BigQuery row-level security and Working with row-level security.

Restrict user permissions to limit side-channel attacks

A side-channel attack is a security attack based on information gained from the system itself. An attacker with broader permissions than necessary can more easily mount side-channel attacks, and learn sensitive data by observing or searching billing, logging, or system messages.

To mitigate such opportunities, BigQuery hides sensitive statistics on all queries against tables with row-level security. These sensitive statistics include the number of bytes and partitions processed, the number of bytes billed, and the query plan stages.

We recommend that admins should refrain from granting the following permissions to users who should only see filtered data, to avoid giving access to sensitive data.

Permissions Sensitive data
Project Owner or Project Creator role Project owners can view bytes processed and related data in audit logs. Project creators can create new projects for which they are the owner, and view billing and audit logs.
BigQuery Data Edit, Owner, or Viewer roles View error messages on queries.
BigQuery Stackdriver Metrics Monitoring Viewer roles View metrics on queries, including bytes processed and related data.
Cloud Billing viewer permissions View BigQuery billing.

Examples

  • Through repeated observation of query duration when querying tables with row-level access policies, a user could infer the values of rows that otherwise might be protected by row-level access policies. This type of attack requires many repeated attempts over a range of key values in partitioning or clustering columns. Even though there is inherent noise when observing or measuring query duration, with repeated attempts, an attacker could obtain a reliable estimate. If you are sensitive to this level of protection, we recommend using separate tables to isolate rows with different access control requirements, instead.
  • Through repeated, carefully crafted queries, it is possible for a malicious user to learn sensitive information by observing the resulting error messages. Specifically, a malicious user with access to the underlying table can potentially derive protected row values when the query returns a divide-by-zero exception, even though there is a security predicate in place to prevent that user from directly querying the same data. This type of attack often requires many repeated attempts against a table with row-level security.
  • An attacker could search for the bytes processed by a query by monitoring the errors that occur when the query job limits (such as maximum bytes billed or custom cost controls) are exceeded. However, this attack requires a high volume of queries.
  • Through repeated queries and observing the BigQuery billing amount in Cloud Billing, a user could infer the values of rows that otherwise might be protected by row-level access policies. This type of attack requires many repeated attempts over a range of key values in partitioning or clustering columns. If you are sensitive to this level of protection, we recommend that you limit access to billing data for queries.

We also recommend that admins monitor Cloud Audit Logs(/bigquery/docs/reference/auditlogs) for suspicious activity on tables with row-level security, such as unexpected additions, modifications, and deletions of row-level access policies.

Restrict user permissions to limit data tampering

Users with write permissions to a table can insert data into the table with the bq load command or with the BigQuery Storage Write API. This can allow the user with write permissions to alter the query results of other users.

We recommend that admins create separate Google groups for table write access and row-levels access policies. Users that should only see filtered table results should not have write access to the filtered table.

Avoid inadvertent access when re-creating row-level access policies

When you add a row access policy on a table for the first time, you immediately begin filtering data in query results. When you remove the last row-level access policy on a table, even if you intend to only re-create the row-level access policy, you may inadvertently grant unfiltered access to a wider-than-intended audience.

We recommend that admins pay special attention when recreating the last row-level access policy on a table, by following these guidelines:

  1. First remove all access to the table, by using table access controls.
  2. Remove all row-level access policies.
  3. Re-create the desired row-level access policies.
  4. Re-enable access to the table, with table access controls.

Alternatively, you can first create new row-level access policies on the table, then delete the older row-level access policies that are no longer needed.

Use row-level security only within organizations, not across organizations

Do not use the row-level security feature across organizations, to help prevent data leakage through side-channel attacks, and to maintain greater control over access to sensitive data.

We recommend using row-level security feature for within-organization security constraints only (such as for sharing data within an organization/enterprise/company), and not for cross-organizational or public security.

Example

Outside of your organization, you have less control over who has access to data. Within your organization, you can control who has been granted access to billing information of queries against tables with row-level access policies. Billing information is a vector for side-channel attacks.

Use the Filtered Data Viewer role with caution

When you create a row-level access policy, the principals in the policy are automatically granted the bigquery.filteredDataViewer role. You can only add or remove principals from the policy with a DDL statement.

However, it is possible to grant the bigquery.filteredDataViewer role through IAM to a higher-level resource, such as a table, dataset, or project. Granting the bigquery.filteredDataViewer role to a user gives them the ability to view the rows defined by all the row-level access policies in that table, dataset, or project.

However, granting the bigquery.filteredDataViewer role on a table does not necessarily mean that the user can see all rows on the table. The union of all the row-level access policies' filter expressions may or may not form a closure over the entire table.

We recommend that you use caution before granting the bigquery.filteredDataViewer role on any resource.

Filtering on partitioned columns impacts performance

Row-level access policy filters do not participate in query pruning on partitioned and clustered tables.

If your row-level access policy names a partitioned column, your query does not receive the performance benefits of query pruning.