Using row-level security with other BigQuery features

This document describes using 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 users see when running queries. Non-query operations (such as DML) can be denied access to the data entirely, unless an Admin grants the user full access to all rows in the table via a row access policy with "TRUE" as a filter. This is also known as the "true filter".

The user being granted "true filter" access can be any IAM user, including a service account.

Examples of non-query operations are:

Example

Creating the "true filter"

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"

Copy jobs

To copy a table with one or more row-level access policies on it, you must first be granted the "true filter" on the source table. All row-level access policies on the source table are also copied to the new destination table. In the case of copying a table without any row-level access policies to overwrite a table that does have row-level access policies, the result is a table without any row-level access policies - just like the source table did not.

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 permissions required to copy a table without a row-level access policy.

Permission Resource
bigquery.rowAccessPolicies.list The source table.
bigquery.rowAccessPolicies.getIamPolicy The source table.
The "true filter" The source table.
bigquery.rowAccessPolicies.create The destination table.
bigquery.rowAccessPolicies.setIamPolicy The destination table.

Storage Read API, tabledata.list in BigQuery API

Users must be granted the "true filter" in order to use either the Storage Read API or the tabledata.list method in the BigQuery API on a table with row-level access policies.

This includes workloads for DataProc, which uses the Storage Read API.

DML

Users (including service accounts) must be granted the "true filter" in order to execute DML statements on a table with row-level access policies.

MERGE target tables cannot contain row-level access policies. However, MERGE source tables are compatible with row-level security, and can have one or more row-level access policies.

Note that if there are one or more row-level access policies on the source table, the MERGE statement only acts upon the rows that are visible to the user account executing the query, according to those row-level access policies.

Table snapshots

Table snapshots support row-level security. The user must have the same permissions for the base table (source table) and the table snapshot (destination table) as described in Required permissions to copy a table with a row-level access policy. Time travel is not supported for base tables that have one or more row-level security policies. For more information, see Time travel.

BigQuery BI Engine and Google Data Studio

Queries on tables with one or more row-level access policies are not accelerated by BigQuery BI Engine, and will instead be run as a standard query 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 are fully compatible.

Key points are:

  • You can apply a row-level access policy to filter on data in any column, even if the user does not have access to the data in that column.
  • If the column is restricted due to column-level security, and the column is named in the query's SELECT statement, the user will receive an access denied 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

In this detailed example, you will walk through 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 columns are populated with data as shown below.

Our primary example user will be Alice, whose email address is alice@example.com. A secondary user will be Bob, Alice's colleague.

rank fruit color
1 apple red
2 orange orange
3 lemon yellow
4 lime green

The security

You want Alice to be able to see all the rows that have odd numbers in the rank 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 the fruit column.

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

    CREATE ROW ACCESS POLICY only_odd ON my_dataset.my_table GRANT
    TO ('user:alice@example.com') FILTER USING (MOD(rank, 2) = 1);
    
  • To restrict all users from seeing data in the column named fruit, you create a column-level security policy tag that prohibits all users from accessing any of its data.

Finally, you also restrict access to the column named color in two ways: the column is governed both by a column-level security policy tag prohibiting all access by anyone, and is affected by a row-level access policy, which filters some of the row data in the color column.

  • This second row-level access policy will only display rows with the value green in the color column.

    CREATE ROW ACCESS POLICY only_green ON my_dataset.my_table
    GRANT TO ('user:alice@example.com') FILTER USING (color="green");
    

Bob's query

If Alice's coworker Bob tries to query data from my_dataset.my_table, he won't see any rows, because Bob isn't in the grantee list for any row-level access policy on the table.

Query my_dataset.my_table Comments
rank

(Some data is affected by the row access policy only_odd)
fruit

(All data is secured by a CLS policy tag)
color

(All data is secured by a CLS policy tag, and some data is affected by the row access policy only_green)
SELECT rank FROM my_dataset.my_table
(0) rows returned.
Bob is not a member of the row-level access policy's grantee list; therefore this query succeeds, but no row data is returned.

A message is displayed to Bob that says his results may be filtered by the row access policy.

Alice's queries

When Alice runs queries to access data from my_dataset.my_table, her results will differ, depending on the query she runs and the security, as shown below.

Query my_dataset.my_table Comments
rank

(Some data is affected by the row access policy only_odd)
fruit

(All data is secured by a CLS policy tag)
color

(All data is secured by a CLS policy tag, and some data is affected by the row access policy only_green)

SELECT rank FROM my_dataset.my_table


(2) odd-numbered rows are returned.
Alice is a member of the grantee list for the only_odd row-level access policy on data in the rank column. Therefore, Alice will see only the odd-numbered row data. Even-numbered rows will be hidden by the row-level access policy named only_odd.

A message is displayed to Alice that says her results may be filtered by the row access policy.

SELECT fruit FROM my_dataset.my_table


access denied

The fruit column was explicitly named in the query.

The column-level security applies.

Access is denied.

SELECT color FROM my_dataset.my_table


access denied

The color column was explicitly named in the query.

The column-level security applies, before the row-level access policy on data in the color column is engaged.

Access is denied.

SELECT rank, fruit FROM my_dataset.my_table


access denied

The `fruit` column was explicitly named in the query.

The column-level security applies, before the row-level access policy on data in the rank column is engaged.

Access is denied.

SELECT rank, color FROM my_dataset.my_table


access denied

The color column was explicitly named in the query

The column-level security on the color column applies, before row-level access policies on data in the rank and the color columns are engaged.

Access is denied.

SELECT fruit, color FROM my_dataset.my_table


access denied


access denied

The fruit and color columns were explicitly named in the query.

The column-level security on the fruit and color columns applies, before the row-level access policy on data in the color column is engaged.

Access is denied.

SELECT * FROM my_dataset.my_table


access denied


access denied

The fruit and color columns were implicitly named by using "SELECT *" in the query.

The column-level security on the fruit and the color columns applies, before the row-level access policies on data in the rank or the color columns are engaged.

Access is denied.

The "true filter"

Finally, as explained in the section about the "true filter", if an Admin or DataOwner grants the "true filter" to Alice or Bob, then they can see all the row data in the table, for use in non-query jobs. However, column-level security still applies if it is present on the table, so that can have an effect on the job results.

Extract jobs

Exporting of tables to Cloud Storage by using an extract job is affected if the table has one or more row-level access policies. Only the data that the current user is allowed to see, according to the row-level access policies, is extracted.

Legacy SQL

Row-level access policies are not compatible with Legacy SQL. Queries over tables with row-level access policies must use Standard SQL. Legacy SQL queries will be rejected.

Partitioned and clustered tables

Row-level security does not participate in query pruning, which is a feature of partitioned tables. Partitioning divides a large table into smaller partitions, improving query performance, and thus controlling costs by reducing the number of bytes read by a query to partitioned and clustered tables.

Renaming a table

You do not need the "true filter" to rename a table with one or more row access policies on it. You can rename a table with a DDL statement.

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

Time travel

The time travel feature does not support row-level security. You get an accesss denied error if you use a time travel decorator with a table that has, or previously had, one or more row-level access policies. If you need to recover table data using time travel, contact Cloud Customer Care.

Views and materialized views

The data displayed in a view or a materialized view is filtered according to the underlying source table's row-level access policies.

In addition, when a materialized view is derived from an underlying table with one or more row-level access policies on it, the query performance is the same as it is when you query the source table directly. In other words, if the source table has row-level security, you won't see the typical performance benefits of querying a materialized view versus querying the source table.

Wildcard queries

Wildcard queries against tables with row-level access policies fail with an INVALID_INPUT error.

What's next