# 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.

Source: https://docs.querylab.io/bigquery/row-level-security/

Updated: 2026-10-06

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

```sql
CREATE ROW ACCESS POLICY eu_only
ON sales.orders
GRANT TO ("group:eu-analysts@example.com", "user:jon@example.com")
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:

```sql
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:

**a != filter also hides NULL regions**

```sql
-- 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
```

Result in BigQuery (2 rows):

| order_id | region |
| --- | --- |
| 2 | US |
| 4 | APAC |

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:

**keeping NULL rows with IS NULL**

```sql
-- 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
```

Result in BigQuery (3 rows):

| order_id | region |
| --- | --- |
| 2 | US |
| 3 | NULL |
| 4 | APAC |

See [NULL handling](https://docs.querylab.io/bigquery/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 are the same as OR**

```sql
-- 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
```

Result in BigQuery (2 rows):

| order_id | region |
| --- | --- |
| 2 | US |
| 4 | APAC |

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:

```text
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:

```sql
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:

|                                                      | No policy                                   | With policy                                      |
| ---------------------------------------------------- | ------------------------------------------- | ------------------------------------------------ |
| Dry run `totalBytesProcessed`                        | 3,200,000                                   | missing                                          |
| `bq` dry run message                                 | "will process upper bound of 3200000 bytes" | "No information about number of bytes processed" |
| Job `totalBytesProcessed` and `totalBytesBilled`     | reported                                    | missing                                          |
| `INFORMATION_SCHEMA.JOBS` `total_bytes_billed`       | filled                                      | NULL                                             |
| `rowLevelSecurityStatistics.rowLevelSecurityApplied` | absent                                      | `true`                                           |

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](https://docs.querylab.io/bigquery/how-queries-are-billed/) and [Controlling costs](https://docs.querylab.io/bigquery/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:

```text
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](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery/table-sampling/).

A [view](https://docs.querylab.io/bigquery/views/) 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.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to BigQuery row-level security](https://cloud.google.com/bigquery/docs/row-level-security-intro)

- [Using row-level security with other BigQuery features](https://cloud.google.com/bigquery/docs/using-row-level-security-with-features)

- [Best practices for row-level security in BigQuery](https://cloud.google.com/bigquery/docs/best-practices-row-level-security)