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 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.
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.
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 shouldn't have write access to the filtered table.

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

Best practice: If there is only one row-level access policy on a table, don't recreate that row-level access policy with the CREATE OR REPLACE command. Instead, first remove all access to the table with table access controls, recreate the policies as needed, and then re-enable access.

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:

First remove all access to the table, by using table access controls.
Remove all row-level access policies.
Re-create the row-level access policies.
Re-enable access to the table.

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

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

Don't 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.

For subquery row-level access policies, create and search tables within organizations or projects. This leads to better security and simpler ACL configuration, as grantees must have the bigquery.tables.getData permission on the target and referenced tables in policies, as well as any relevant column-level security permissions.

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.

Manage the `Filtered Data Viewer` role through row-level access policies

Best practice: bigquery.filteredDataViewer is a system-managed role granted through row-level access policies. Manage the role only through row-level access policies. Don't apply the role through Identity and Access Management (IAM).

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 access policy with a DDL statement.

The bigquery.filteredDataViewer role must not be granted through IAM to a higher-level resource, such as a table, dataset, or project. Granting the role in this way lets users view rows defined by all row-level access policies within that scope, regardless of intended restrictions. While the union of row-level access policy filters might not encompass the entire table, this practice poses a significant security risk and undermines the purpose of row-level security.

We recommend managing the bigquery.filteredDataViewer role exclusively through row-level access policies. This method ensures that principals are granted the bigquery.filteredDataViewer role implicitly and correctly, respecting the defined filter predicates for each policy.

Performance impact of filters on partitioned columns

Row-level access policy filters don't 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.