Using row-level security with otherBigQuery features

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

Before you read this document, familiarize yourself withrow-level security by readingIntroduction to BigQuery row-level securityandWorking with row-level security.

TheTRUE filter

Row-level access policies can filter the result data that you see when runningqueries. To run non-query operations, such as DML, you need fullaccess to all rows in the table. Full access is grantedby using a row access policy with the filter expression set toTRUE. Thisrow-level access policy is called theTRUE filter.

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

Examples of non-query operations are:

• Other BigQuery APIs, such as theBigQuery Storage Read API.
• Somebq command-line tool commands,such as thebq headcommand.
• Copying a table

TRUE filter example

CREATEROWACCESSPOLICYall_accessONproject.dataset.table1GRANTTO("group:all-rows-access@example.com")FILTERUSING(TRUE);

Features that work with theTRUE filter

When you use aDML operationon a table protected by row access policies, you must use aTRUE filter whichimplies access to the whole table. Any operations that don't alter the tableschema maintain any row access policies on the table.

For example, theALTER TABLE RENAMETOstatement copies row access policies from the original table to the new table.As another example, theTRUNCATETABLEstatement removes all of the rows from a table but maintains the table schema aswell as any row access policies.

Copy jobs

Tocopy a table with one or morerow-level access policies on it, you must first be grantedTRUE filter accessonthe source table. All row-level access policies on the source table are alsocopied to the new destination table. If you copy a source table withoutrow-level access policies onto a destination table that does have row-levelaccess 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 queriesmight be broken after the copy is complete if the queries contain invalid tablereferences in subquery policies.

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

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

Tabledata.list in BigQuery API

You needTRUE filter access in order to use thetabledata.list method in theBigQuery API on a table with row-level access policies.

DML

To execute a DML statement that updates a table that has row-level accesspolicies, you needTRUE filter access for the table.

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

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

Table snapshots

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

BigQuery table with JSON columns

Row-level access policies cannot be applied onJSON columns.To learn more about the limitations for row-level security, seeLimitations.

BigQuery BI Engine and Looker Studio

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

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

Column-level security

Row-level security and column-level security, which includes bothcolumn-level access control anddynamic data masking, are fullycompatible.

Key points are:

• You can apply a row-level access policy to filter data in any column, evenif you don't have access to the data in that column.
  • Attempts to access these columns with the subquery row-level access policyresults in an error indicating that access is denied. These columns aren'tconsidered system-referenced columns.
  • Attempts to access these columns with the non-subquery row-level access policybypass column-level security.
• If the column is restricted due to column-level security, and the column isnamed in the query'sSELECT statement or subquery row-level access policies,you receive an error.
• Column-level security also applies with aSELECT * query statement. TheSELECT * is treated the same as a query which explicitly names a restrictedcolumn.

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

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

The data

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

In this example, one user isAlice, whose email address isalice@example.com. A second user isBob, Alice's colleague.

The security

You want Alice to be able to see all the rows that have odd numbers in therank column, but not even-numbered rows. You don't want Bob to see any rows,even or odd. You don't want anyone to see any data in thefruit column.

• To restrict Alice from seeing the even-numbered rows, you create arow-level access policy which has a filter expression based on the datathat appears in therank column. To prevent Bob from seeing even or oddrows, you don't include him in the grantee list.

  CREATEROWACCESSPOLICYonly_oddONmy_dataset.my_tableGRANTTO('user:alice@example.com')FILTERUSING(MOD(rank,2)=1);

• To restrict all users from seeing data in the column namedfruit,you create a column-level security policy tag that prohibits all users fromaccessing any of its data.

Finally, you also restrict access to the column namedcolor in two ways:the column is governed both by a column-level security policy tag prohibitingall access by anyone,and is affected by a row-level access policy, whichfilters some of the row data in thecolor column.

• This second row-level access policy only displays rows with thevaluegreen in thecolor column.

  CREATEROWACCESSPOLICYonly_greenONmy_dataset.my_tableGRANTTO('user:alice@example.com')FILTERUSING(color="green");

Bob's query

If Alice's coworker Bob tries to query data frommy_dataset.my_table, hedoesn't see any rows, because Bob isn't in the grantee list for any row-levelaccess policy on the table.

Alice's queries

When Alice runs queries to access data frommy_dataset.my_table, herresults depend on the query she runs and the security, as shownin the following table.

TRUE filter access

Finally, as explained inthe section aboutTRUE filter access,if Alice or Bob hasTRUE filter access, then they cansee all of the rows in the table, and use it in non-query jobs. However, if thetable has column-level security, then it still applies and can affect theresults.

Execution graph

You can't use thequery execution graph forjobs with row-level access policies.

Extract jobs

If a table has row-level access policies, then only the data that you canview is exported toCloud Storage when you run an extract job.

Legacy SQL

Row-level access policies are not compatible with Legacy SQL. Queries overtables with row-level access policies must useGoogleSQL.Legacy SQL queries are rejected.

Partitioned and clustered tables

Row-level security does not participate in querypruning, which is a feature ofpartitioned tables.

While row-level security is compatible with partitioned and clusteredtables, the row-level access policies that filter row data aren't appliedduring partition pruning. You can still use partition pruning on a tablethat uses row-level security by specifying aWHERE clause that operateson the partition column. Similarly, row-level access policies themselvesdon't create any performance benefits for queries against clustered tables,but they don't interfere with other filtering that you apply.

Query pruning is performed during the execution of row-level access policiesusing the filters with the policies.

Rename a table

You don't needTRUE filter access to rename a table with one or more row accesspolicies on it. You canrename a table with a DDL statement.

As an alternative, you can also copy a table and give the destination table adifferent name. If the source table has a row-level access policy on it, seetable copy jobson this page for more information.

Streaming updates

To perform streaming tableUPDATE orDELETE operations withchange data capture, you must haveTRUE filter access.

Time travel

Only a table administrator can access historical data for a table that has, orhas previously had, row-level access policies. Other users get anaccessdenied error if they use a time travel decorator on a table that has hadrow-level access. For more information, seeTime travel and row-levelaccess.

Logical, materialized, and authorized views

This section describes different types of BigQuery views andhow they interact with row-level security.

Logical or materialized views

Logical or materialized views are built from queries against tables.The query results are usually a subset of the table data.

The data displayed in either type of view is filtered according to theunderlying source table's row-level access policies. However, you can'treference views or materialized views in row-level access policies.

Performance for materialized views

In addition, when amaterialized viewis derived from an underlying table that has row-level access policies, then thequery performance is the same as when you query the sourcetable directly. In other words, if the source table has row-level security, youdon't see the typical performance benefits of querying a materialized viewversus querying the source table.

Authorized views

You can also authorize a logical or materialized view, which means sharingthe view with specific users or groups (principals). Principals can thenquery a view, but don't have access to the underlying table. For moreinformation, seeAuthorized views.

Wildcard queries

Wildcard queries againsttables with row-level access policies fail with anINVALID_INPUT error.

What's next

• For information about best practices for row-level access policies, seeBest practices for row-level security in BigQuery.