Use row-level security

This document explains how to use row-level security in BigQuery to restrict access to data at the table row level. Before you read this document, familiarize yourself with an overview about row-level security by reading Introduction to BigQuery row-level security.

You can perform the following tasks with row-level access policies:

• Create or update a row-level access policy on a table
• Combine row-level access policies on a table
• List a table's row-level access policies
• Delete a row-level access policy from a table
• Query a table with a row-level access policy

Limitations

When you select a table in the Google Cloud console, the Preview tab cannot show previews of tables with row-access policies. To view the contents of the table, run a query.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document. The permissions required to perform a task (if any) are listed in the "Required permissions" section of the task.

Create or update a row-level access policy

You can create or update a row-level access policy on a table in BigQuery with a data definition language (DDL) statement.

Required permissions

To create a row-level access policy on a BigQuery table, you need the following IAM permissions:

• bigquery.rowAccessPolicies.create
• bigquery.rowAccessPolicies.setIamPolicy
• bigquery.tables.getData (on the target table and any referenced tables in granted subquery row-level access policies)
• bigquery.jobs.create (to run the DDL query job)

To update a row-level access policy on a BigQuery table, you need the following IAM permissions:

• bigquery.rowAccessPolicies.update
• bigquery.rowAccessPolicies.setIamPolicy
• bigquery.tables.getData (on the target table and any referenced tables in granted subquery row-level access policies)
• bigquery.jobs.create (to run the DDL query job)

Each of the following predefined IAM roles includes the permissions that you need in order to create and update a row-level access policy:

• roles/bigquery.admin
• roles/bigquery.dataOwner

The bigquery.filteredDataViewer role

When you create a row-level access policy, BigQuery automatically grants the bigquery.filteredDataViewer role to the members of the grantee list. When you list a table's row-level access policies in the Google Cloud console, this role is displayed in association with the members of the policy's grantee list.

Create or update row-level access policies

When you set up row-level access on a table, you'll need at least two row access policies:

• A policy that grants full access to the table. The first row access policy should grant access to users and groups that require full access to the data in the table for data maintenance or support. For example, your BigQuery administrators and service accounts that use DML statements to transform table data.
• A second policy that filters access. This policy uses filters based on business logic to grant access to specific groups.

To create or update a row-level access policy, use one of the following DDL statements:

• The CREATE ROW ACCESS POLICY creates a new row-level access policy.

• The CREATE ROW ACCESS POLICY IF NOT EXISTS statement creates a new row-level access policy, if a row-level access policy with the same name does not already exist on the specified table.

• The CREATE OR REPLACE ROW ACCESS POLICY statement updates an existing row-level access policy with the same name on the specified table.

Examples

The following examples show you how to create and update row access policies for different types of principal identifiers including Google Accounts and federated identities. For more information on federated identities, see Workload identity federation.

Create a new policy and grant access to a Google Account

Create a new row access policy. Access to the table is restricted to the user abc@example.com. Only the rows where region = 'APAC' are visible:

CREATEROWACCESSPOLICYapac_filter
ONproject.dataset.my_table
GRANTTO('user:abc@example.com')
FILTERUSING(region='APAC');

Create a new policy and grant access to a single identity in a workforce identity pool

Create a new row access policy. Access to the table is restricted to a single identity in a workforce identity pool using this format: principal://iam.googleapis.com/locations/global/workforcePools/POOL_ID/subject/IDENTITY. Only the rows where region = 'APAC' are visible:

CREATEROWACCESSPOLICYapac_filter
ONproject.dataset.my_table
GRANTTO('principal://iam.googleapis.com/locations/global/workforcePools/example-contractors/subject/abc@example.com')
FILTERUSING(region='APAC');

Update a policy to grant access to a service account

Update the apac_filter access policy to apply to the service account example@exampleproject.iam.gserviceaccount.com:

CREATEORREPLACEROWACCESSPOLICYapac_filter
ONproject.dataset.my_table
GRANTTO('serviceAccount:example@exampleproject.iam.gserviceaccount.com')
FILTERUSING(region='APAC');

Create a policy and grant access to users and groups

Create a row access policy that grants access to a user and two groups:

CREATEROWACCESSPOLICYsales_us_filter
ONproject.dataset.my_table
GRANTTO('user:john@example.com',
'group:sales-us@example.com',
'group:sales-managers@example.com')
FILTERUSING(region='US');

Create a policy and grant access to workforce identities in groups

Create a row access policy that grants access to all workforce identities in groups using this format principal://iam.googleapis.com/locations/global/workforcePools/POOL_ID/subject/IDENTITY:

CREATEROWACCESSPOLICYsales_us_filter
ONproject.dataset.my_table
GRANTTO('principal://iam.googleapis.com/locations/global/workforcePools/example-contractors/subject/sales-us@example.com',
'principal://iam.googleapis.com/locations/global/workforcePools/example-contractors/subject/sales-managers@example.com')
FILTERUSING(region='US');

Create a policy and grant access to all authenticated users

Create a row access policy with allAuthenticatedUsers as the grantees:

CREATEROWACCESSPOLICYus_filter
ONproject.dataset.my_table
GRANTTO('allAuthenticatedUsers')
FILTERUSING(region='US');

Create a policy and filter based on the current user

Create a row access policy with a filter based on the current user:

CREATEROWACCESSPOLICYmy_row_filter
ONdataset.my_table
GRANTTO('domain:example.com')
FILTERUSING(email=SESSION_USER());

Create a policy and filter on a column

Create a row access policy with a filter on a column with an ARRAY type:

CREATEROWACCESSPOLICYmy_reports_filter
ONproject.dataset.my_table
GRANTTO('domain:example.com')
FILTERUSING(SESSION_USER()INUNNEST(reporting_chain));

Create a policy and use a region comparison

Create a row access policy with a subquery to replace multiple policies with a region comparison configured per user:

Consider the following table, lookup_table:

+-----------------+--------------+
| email | region |
+-----------------+--------------+
| xyz@example.com | europe-west1 |
| abc@example.com | us-west1 |
| abc@example.com | us-west2 |
+-----------------+--------------+

CREATEORREPLACEROWACCESSPOLICYapac_filter
ONproject.dataset.my_table
GRANTTO('domain:example.com')
FILTERUSING(regionIN(
SELECT
region
FROM
lookup_table
WHERE
email=SESSION_USER()));

Using the subquery on lookup_table lets you avoid creating multiple row access policies. For example, the preceding statement yields the same result as the following, with fewer queries:

CREATEORREPLACEROWACCESSPOLICYus_filter
ONproject.dataset.my_table
GRANTTO('user:abc@example.com')
FILTERUSING(regionIN('us-west1','us-west2'));
CREATEORREPLACEROWACCESSPOLICYeu_filter
ONproject.dataset.my_table
GRANTTO('user:xyz@example.com')
FILTERUSING(region='europe-west1');

For more information on the syntax and available options, see the CREATE ROW ACCESS POLICY DDL statement reference.

Combine row-level access policies

If two or more row-level access policies grant a user or group access to the same table, then the user or group has access to all of the data covered by any of the policies. For example, the following policies grant the user abc@example.com access to specified rows in the my_table table:

CREATEROWACCESSPOLICYshoes
ONproject.dataset.my_table
GRANTTO('user:abc@example.com')
FILTERUSING(product_category='shoes');

CREATEORREPLACEROWACCESSPOLICYblue_products
ONproject.dataset.my_table
GRANTTO('user:abc@example.com')
FILTERUSING(color='blue');

In the preceding example, the user abc@example.com has access to the rows in the my_table table that have the product_category field set to shoes, and abc@example.com also has access to the rows that have the color field set to blue. For example, abc@example.com would be able to access rows with information about red shoes and blue cars.

This access is equivalent to the access provided by the following single row-level access policy:

CREATEROWACCESSPOLICYshoes_and_blue_products
ONproject.dataset.my_table
GRANTTO('user:abc@example.com')
FILTERUSING(product_category='shoes'ORcolor='blue');

On the other hand, to specify access that is dependent on more than one condition being true, use a filter with an AND operator. For example, the following row-level access policy grants abc@example.com access only to rows that have both the product_category field set to shoes and the color field set to blue:

CREATEROWACCESSPOLICYblue_shoes
ONproject.dataset.my_table
GRANTTO('user:abc@example.com')
FILTERUSING(product_category='shoes'ANDcolor='blue');

With the preceding row-level access policy, abc@example.com would be able to access information about blue shoes, but not about red shoes or blue cars.

List table row-level access policies

You can list and view all the row-level access policies on a table by using the Google Cloud console, bq command-line tool, or RowAccessPolicies.List API method.

Required permissions

To list row-level access policies on a BigQuery table, you need the bigquery.rowAccessPolicies.list IAM permission.

To view the members of a row-level access policy on a BigQuery table, you need the bigquery.rowAccessPolicies.getIamPolicy IAM permission.

Each of the following predefined IAM roles includes the permissions that you need in order to list and view row-level access policies:

• roles/bigquery.admin
• roles/bigquery.dataOwner

For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.

List table row-level access policies

To list row-level access policies, do the following:

 bq ls --row_access_policies dataset.table

 bq ls --row_access_policies my_dataset.my_table

Delete row-level access policies

You can delete one or all row-level access policies on a table by using a DDL statement, if you have the permissions to do so.

Required permissions

To drop a row-level access policy, you need the following IAM permissions:

• bigquery.rowAccessPolicies.delete
• bigquery.rowAccessPolicies.setIamPolicy
• bigquery.jobs.create (to run the DDL query job)

To drop all the row-level access policies on a table at the same time, you need the following IAM permissions:

• bigquery.rowAccessPolicies.delete
• bigquery.rowAccessPolicies.setIamPolicy
• bigquery.rowAccessPolicies.list
• bigquery.jobs.create (to run the DDL query job)

Each of the following predefined IAM roles includes the permissions that you need in order to delete row-level access policies:

• roles/bigquery.admin
• roles/bigquery.dataOwner

For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Delete row-level access policies

To delete a row access policy from a table, use the following DDL statements:

• The DROP ROW ACCESS POLICY statement deletes a row-level access policy on the specified table.

• The DROP ROW ACCESS POLICY IF EXISTS statement deletes a row-level access policy if the row access policy exists on the specified table.

• The DROP ALL ROW ACCESS POLICIES statement deletes all row-level access policies on the specified table.

Examples

Delete a row-level access policy from a table:

DROPROWACCESSPOLICYmy_row_filterONproject.dataset.my_table;

Delete all the row-level access policies from a table:

DROPALLROWACCESSPOLICIESONproject.dataset.my_table;

For more information about deleting a row-level access policy, see the DROP ROW ACCESS POLICY DDL statement reference.

Query tables with row access policies

A user must first have access to a BigQuery table to be able to query it, even if they are on the grantee_list of a row access policy on that table. Without that permission, the query fails with an access denied error.

Required permissions

To query a BigQuery table with row-level access policies, you must have the bigquery.tables.getData permission on the table. You also need the bigquery.rowAccessPolicies.getFilteredData permission.

To gain these permissions with predefined roles, you need to be granted the roles/bigquery.dataViewer role on the table using IAM, and you must be granted the roles/bigquery.filteredDataViewer IAM role on the table through the row-level access policy.

You must have the datacatalog.categories.fineGrainedGet permission on all relevant columns with column-level security. To gain this permission with predefined roles, you need the datacatalog.categoryFineGrainedReader role.

View query results

In the Google Cloud console, when you query a table with a row-level access policy, BigQuery displays a banner notice indicating that your results might be filtered by a row-level access policy. This notice displays even if you are a member of the grantee list for the policy.

Query result on table with row-level access policy

Job statistics

When you query a table with a row-level access policy using the Job API, BigQuery indicates whether the query reads any tables with row access policies in the Job response object:

Example

This Job object response has been truncated for simplicity:

{
 "configuration": {
 "jobType": "QUERY",
 "query": {
 "priority": "INTERACTIVE",
 "query": "SELECT * FROM dataset.table",
 "useLegacySql": false
 }
 },
 ...
 "statistics": {
 ...
 rowLevelSecurityStatistics: {
 rowLevelSecurityApplied: true
 },
 ...
 },
 "status": {
 "state": "DONE"
 },
 ...
}

What's next

• For information about how row-level security works with other BigQuery features and services, see Using row level security with other BigQuery features.

• For information about best practices for row-level security, see Best Practices for row-level security in BigQuery.