Row-level security in BigQuery

How BigQuery row access policies filter rows per user. Grantee lists, SESSION_USER(), what happens with several policies, and what a policy does to dry runs, cost statistics, time travel, DML and copies.

Row-level security in BigQuery is a row access policy: a FILTER USING expression attached to a table and a list of principals it applies to. When someone in the list queries the table, BigQuery adds the filter as if it were part of their WHERE clause. Once a table has any policy, everyone who is not in some policy’s list sees zero rows, including the table’s owner. The policy changes more than the result: it hides the bytes a query processed, blocks time travel, and makes DML and TABLESAMPLE behave differently.

Create a policy

CREATE ROW ACCESS POLICY eu_only
ON sales.orders
GRANT TO ("group:[email protected]", "user:[email protected]")
FILTER USING (region = "EU");

The filter can use the table’s columns, literals, functions and SESSION_USER(), which returns the email of whoever runs the query. That makes per-user rows possible without a policy per person:

CREATE ROW ACCESS POLICY own_rows
ON hr.salaries
GRANT TO ("domain:example.com")
FILTER USING (email = SESSION_USER());

We created a policy like this on a test table with one row that held our own address. A plain SELECT returned that one row, and the other 199,999 were invisible.

Creating a policy needs bigquery.rowAccessPolicies.create, which bigquery.dataOwner and bigquery.admin have. The principals in the list are granted roles/bigquery.filteredDataViewer automatically. Google says never to grant that role through IAM on a table, dataset or project, because then the holder sees the rows of every policy. bq ls --row_access_policies project:dataset.table shows each policy with its filter and grantees. Policy names that are SQL keywords are refused: a policy called lookup failed with a syntax error.

Filters behave like WHERE

A row is visible when the filter is TRUE. FALSE and NULL both hide it. This is the usual NULL trap, and it applies to every policy of the form column != 'x'. This is what a policy of region != 'EU' shows, written as a plain filter:

-- What a policy with FILTER USING (region != 'EU') shows: the filter acts like WHERE.
WITH orders AS (
  SELECT 1 AS order_id, 'EU' AS region UNION ALL
  SELECT 2, 'US' UNION ALL
  SELECT 3, NULL UNION ALL
  SELECT 4, 'APAC'
)
SELECT order_id, region
FROM orders
WHERE region != 'EU'
ORDER BY order_id

We created that policy on a test table with a NULL region, and the NULL row was hidden the same way. If NULL rows should stay visible, say it:

-- To keep NULL rows, say so in the filter.
WITH orders AS (
  SELECT 1 AS order_id, 'EU' AS region UNION ALL
  SELECT 2, 'US' UNION ALL
  SELECT 3, NULL UNION ALL
  SELECT 4, 'APAC'
)
SELECT order_id, region
FROM orders
WHERE region IS NULL OR region != 'EU'
ORDER BY order_id

See NULL handling.

Several policies are OR’ed

A user can be named in more than one policy. Their view is the union: a row is visible when any policy that names them passes. We granted the same user a policy with region = 'US' on a table with four regions: the user saw only US. We added a second policy for the same user with region = 'APAC', and the same query returned both US and APAC.

-- Two policies granted to the same user, region = 'US' and region = 'APAC', are combined with OR.
WITH orders AS (
  SELECT 1 AS order_id, 'EU' AS region UNION ALL
  SELECT 2, 'US' UNION ALL
  SELECT 3, 'LATAM' UNION ALL
  SELECT 4, 'APAC'
)
SELECT order_id, region
FROM orders
WHERE region = 'US' OR region = 'APAC'
ORDER BY order_id

Two rules complete the picture:

• A user named in no policy sees no rows. We granted only a service account on a table and ran SELECT COUNT(*) as the table’s owner: 0.
• To give someone every row, grant them a policy with FILTER USING (TRUE), the TRUE filter. We did, and the count went back to all 200,000 rows.

BigQuery refuses to remove the last policy by name, because that would show the whole table to everyone with table access:

Dropping the last row access policy would make the table accessible to all users
who have access to the table. If this is intended, please use a DROP ALL statement instead.

DROP ALL ROW ACCESS POLICIES ON table does it, and DROP ROW ACCESS POLICY IF EXISTS skips a policy that isn’t there. Google also recommends creating the new policies before dropping the old ones when you replace the last one.

Filters that look up another table

A filter can contain a subquery, so the rules can live in a table instead of in one policy per team:

CREATE ROW ACCESS POLICY by_lookup
ON sales.orders
GRANT TO ("domain:example.com")
FILTER USING (region IN (
  SELECT region FROM `my-project.admin.region_access` WHERE email = SESSION_USER()
));

The table in the subquery must be written with its project: without it, creation fails with table name ... cannot be resolved: cannot parse project name. We changed the lookup table and the next query saw the change, with no change to the policy. A policy with ‘US’ and ‘EU’ rows for the user gave two regions, and after we deleted the ‘US’ lookup row, one. When we dropped the lookup table itself, queries on the protected table failed with Not found: Table ... was not found. The query returned an error, not extra rows.

Google lists the limits: top-level IN subqueries can’t test FLOAT, STRUCT, ARRAY, JSON or GEOGRAPHY values, the subquery result is capped at 100 MB, and the Storage Read API can’t use subquery policies.

What the policy does to cost

Row-level security is free, but you can’t see what a query costs. BigQuery hides the byte counts on every query against a table with a policy, to stop users from learning about hidden rows from the bill.

We ran the same aggregate on a 200,000-row partitioned table before and after adding a policy:

The job statistics also carried a new field, rowLevelSecurityStatistics.rowLevelSecurityApplied: true. Google’s guidance lists the number of bytes and partitions processed, the bytes billed and the query plan stages as hidden “sensitive statistics” and says the execution graph isn’t available either. The practical effect is that a dry run can’t tell you what a query on these tables will read. See How a query is billed and Controlling costs.

Google also says that the policy filter doesn’t take part in partition or cluster pruning. A WHERE on the partition column still prunes. The filter is applied to the rows after that, so a policy of region = 'EU' doesn’t make the query read less than it would otherwise.

Caching

Results of queries on a table with a policy are cached. The second run of the same query returned cacheHit: true and zero bytes. The cache doesn’t outlive a policy change: after we replaced a policy’s filter, the same query text ran again (cacheHit: false) and returned the new region. The same happened when we added a second policy.

Time travel, change history and copies

Policies block history reads. For a table with a policy, or that has ever had one, we got these errors even as the table’s owner:

Access Denied: BigQuery BigQuery: Time travel on table ... is forbidden as it has (or has had) row access policies.
Access Denied: BigQuery BigQuery: Reading change history for table ... is forbidden as it has (or has had) row access policies.

The first came from FOR SYSTEM_TIME AS OF (even with CURRENT_TIMESTAMP()), the second from APPENDS. The permission that lifts this is bigquery.rowAccessPolicies.overrideTimeTravelRestrictions. It is in bigquery.admin; bigquery.dataOwner has the other policy permissions but not this one. See time travel.

Other operations that need the whole table:

• INSERT, UPDATE, DELETE and TRUNCATE TABLE on a table with a policy failed for us with User does not have full access for table ... due to row access policies. bq head failed the same way. Google says the TRUE filter is what gives a user or service account the full access these operations need, and that TRUNCATE leaves the policies in place.
• CREATE TABLE AS SELECT from a policy table copies only the visible rows. We got a one-row copy of a 200,000-row table.
• CREATE OR REPLACE TABLE deletes the table’s policies, even when the new table is built from the old one. Google lists the same for WRITE_TRUNCATE loads and for deleting the table.
• Google says a copy job needs the TRUE filter too, so give it to the service account that copies.
• Dropping or renaming a column that appears in a policy is refused: Column ... cannot be dropped because it is used in a row access policy.

Where policies don’t work

• Wildcard tables. FROM `dataset.sales_*` over a table with a policy fails: Wildcard table queries are not supported over tables with row access policies.
• Temporary tables, JSON columns and tables that reference other tables with policies. Google also lists Legacy SQL, which it rejects.
• BI Engine and materialized views work but get no speed-up from the policy table.
• Previewing a table in the console is not supported.

TABLESAMPLE is listed by Google as incompatible with row-level security. On our test table, both TABLESAMPLE SYSTEM (10 PERCENT) and (1 PERCENT) ran without an error and returned counts of the rows we were allowed to see. If you rely on that, test it on your table; the documentation says otherwise. See TABLESAMPLE.

A view over a policy table shows each user the rows that user may see; we queried one and got only our own row. Authorizing a view doesn’t remove the policy.

Limits of the guarantee

Google warns that policies can leak through side channels. The documented examples are query duration across many attempts on partition or cluster values, and billing data. For rows where even the count must stay secret, put them in separate tables. The table’s metadata also still shows the full row count: after we added a policy to a 200,000-row table, numRows stayed at 200,000. Don’t rely on a row access policy to hide how big a table is.

Google also recommends keeping write access to the table separate from policy access, because anyone who can load rows into the table can change what others see.