Using row-level security with other BigQuery features

This document describes how to use row-level access security with other BigQuery features.

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.

The TRUE filter

Row-level access policies can filter the result data that you see when running queries. To run non-query operations, such as DML, you need full access to all rows in the table. Full access is granted by using a row access policy with the filter expression set to TRUE. This row-level access policy is called the TRUE filter.

Any user can be granted TRUE filter access, including a service account.

Examples of non-query operations are:

• Other BigQuery APIs, such as the BigQuery Storage Read API.
• Some bq command-line tool commands, such as the bq head command.
• Copying a table

TRUE filter example

CREATE ROW ACCESS POLICY all_access ON project.dataset.table1
GRANT TO ("group:all-rows-access@example.com")
FILTER USING (TRUE);

Features that work with the TRUE filter

When you use a DML operation on a table protected by row access policies, you must use a TRUE filter which implies access to the whole table. Any operations that don't alter the table schema maintain any row access policies on the table.

For example, the ALTER TABLE RENAME TO statement copies row access policies from the original table to the new table. As another example, the TRUNCATE TABLE statement removes all of the rows from a table but maintains the table schema as well as any row access policies.

Copy jobs

To copy a table with one or more row-level access policies on it, you must first be granted TRUE filter access on the source table. All row-level access policies on the source table are also copied to the new destination table. If you copy a source table without row-level access policies onto a destination table that does have row-level access policies, then the row-level access policies are removed from the destination table, unless the --append_table flag is used or "writeDisposition": "WRITE_APPEND" is set.

Cross-region copies are allowed and all policies are copied. Subsequent queries might be broken after the copy is complete if the queries contain invalid table references in subquery policies.

Row-level access policies on a table must have unique names. A collision in row-level access policy names during the copy results in an invalid input error.

Required permissions to copy a table with a row-level access policy

To copy a table with one or more row-level access policies, you must have the following permissions, in addition to the roles to copy tables and partitions.

Tabledata.list in BigQuery API

You need TRUE filter access in order to use the tabledata.list method in the BigQuery API on a table with row-level access policies.

DML

To execute a DML statement that updates a table that has row-level access policies, you need TRUE filter access for the table.

In particular, MERGE statements interact with row-level access policies as follows:

• If a target table contains row-level access policies, then you need TRUE filter access to the target table.
• If a source table contains row-level access policies, then the MERGE statement only acts on the rows that are visible to the user.

Table snapshots

Table snapshots support row-level security. The permissions that you need for the base table (source table) and the table snapshot (destination table) are described in Required permissions to copy a table with a row-level access policy.

BigQuery table with JSON columns

Row-level access policies cannot be applied on JSON columns. To learn more about the limitations for row-level security, see Limitations.

BigQuery BI Engine and Data Studio

BigQuery BI Engine does not accelerate queries that are run on tables with one or more row-level access policies; those queries are run as standard queries in BigQuery.

The data in a Data Studio dashboard is filtered according to the underlying source table's row-level access policies.

Column-level security

Row-level security and column-level security, which includes both column-level access control and dynamic data masking, are fully compatible.

Key points are:

• You can apply a row-level access policy to filter data in any column, even if you don't have access to the data in that column.
  • Attempts to access these columns with the subquery row-level access policy results in an error indicating that access is denied. These columns aren't considered system-referenced columns.
  • Attempts to access these columns with the non-subquery row-level access policy bypass column-level security.
• If the column is restricted due to column-level security, and the column is named in the query's SELECT statement or subquery row-level access policies, you receive an error.
• Column-level security also applies with a SELECT * query statement. The SELECT * is treated the same as a query which explicitly names a restricted column.

Example of row-level security and column-level security interacting

This example walks you through the steps for securing a table and then querying it.

The data

Suppose that you have the DataOwner role for a dataset named my_dataset which includes a table with three columns, named my_table. The table contains the data shown in the following table.

In this example, one user is Alice, whose email address is alice@example.com. A second user is Bob, Alice's colleague.