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 could learn sensitive data through observing or searching through billing, logging, or system messages.

To mitigate such opportunities, BigQuery hides sensitive statistics on all queries against tables with row-level security, including the number of bytes processed, the number of 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 of which they are owner of, and view billing, 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 a large number of repeated attempts over a range of key values in partitioning or clustering columns. Note that 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 a large number of repeated attempts against a table with row-level security.
  • An attacker could, through setting query job limits, either via job maximum bytes billed or custom cost controls, search for the bytes processed by a query by monitoring the error that occurs when those query job limits 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 a large number of repeated attempts over a range of key values in partitioning or clustering columns. We recommend that you limit access to billing data for queries, if you are sensitive to this level of protection.

We also recommend that admins monitor Cloud audit logs for suspicious activity on tables with row-level security, such as unexpected additions, modifications, and deletions of row-level access policies.

Avoid inadvertently opening 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 members of the policy will automatically be granted the bigquery.filteredDataViewer role. You can only add or remove members 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 will impact 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 will not receive the performance benefits of query pruning.