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.

Don't manually grant the bigquery.filteredDataViewer with IAM. For more information, see best practices for row-level security.

Create or update row-level access policies

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

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_filterONproject.dataset.my_tableGRANTTO('user:abc@example.com')FILTERUSING(region='APAC');

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

CREATEORREPLACEROWACCESSPOLICYapac_filterONproject.dataset.my_tableGRANTTO('serviceAccount:example@exampleproject.iam.gserviceaccount.com')FILTERUSING(region='APAC');

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

CREATEROWACCESSPOLICYsales_us_filterONproject.dataset.my_tableGRANTTO('user:john@example.com','group:sales-us@example.com','group:sales-managers@example.com')FILTERUSING(region='US');

Create a row access policy with allAuthenticatedUsers as the grantees:

CREATEROWACCESSPOLICYus_filterONproject.dataset.my_tableGRANTTO('allAuthenticatedUsers')FILTERUSING(region='US');

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

CREATEROWACCESSPOLICYmy_row_filterONdataset.my_tableGRANTTO('domain:example.com')FILTERUSING(email=SESSION_USER());

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

CREATEROWACCESSPOLICYmy_reports_filterONproject.dataset.my_tableGRANTTO('domain:example.com')FILTERUSING(SESSION_USER()INUNNEST(reporting_chain));

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_filterONproject.dataset.my_tableGRANTTO('domain:example.com')FILTERUSING(regionIN(SELECTregionFROMlookup_tableWHEREemail=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:

CREATEORREPLACEROWACCESSPOLICYapac_filterONproject.dataset.my_tableGRANTTO('user:abc@example.com')FILTERUSING(region='us-west1');CREATEORREPLACEROWACCESSPOLICYapac_filterONproject.dataset.my_tableGRANTTO('user:abc@example.com')FILTERUSING(regionIN'us-west1','us-west2');CREATEORREPLACEROWACCESSPOLICYapac_filterONproject.dataset.my_tableGRANTTO('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:

CREATEROWACCESSPOLICYshoesONproject.dataset.my_tableGRANTTO('user:abc@example.com')FILTERUSING(product_category='shoes');

CREATEORREPLACEROWACCESSPOLICYblue_productsONproject.dataset.my_tableGRANTTO('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_productsONproject.dataset.my_tableGRANTTO('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_shoesONproject.dataset.my_tableGRANTTO('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 need the bigquery.tables.getData IAM permission, and the bigquery.rowAccessPolicies.getFilteredData IAM permission. You must have the bigquery.tables.getData IAM permission on all relevant tables.

To gain these permissions with predefined roles, you need to be granted the roles/bigquery.dataViewer and roles/bigquery.filteredDataViewer IAM roles.

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.

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.