Use row-level security

This document explains how to use row-level security in BigQuery torestrict access to data at the table row level. Before you read this document,familiarize yourself with an overview about row-level security by readingIntroduction 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 atable
• Combine row-level access policies on atable
• List a table's row-level access policies
• Delete a row-level access policy from atable
• Query a table with a row-level access policy

Limitations

When you select a table in the Google Cloud console, thePreview tabcannot show previews of tables with row-access policies. To view thecontents of the table, run a query.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissionsto perform each task in this document. The permissions required to perform atask (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 inBigQuery with a data definition language (DDL) statement.

Required permissions

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

• bigquery.rowAccessPolicies.create
• bigquery.rowAccessPolicies.setIamPolicy
• bigquery.tables.getData (on the target table and any referenced tables ingranted 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 needthe following IAM permissions:

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

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

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

Thebigquery.filteredDataViewer role

When you create a row-level access policy, BigQueryautomatically grants thebigquery.filteredDataViewer role to the members ofthe grantee list. When youlist a table's row-level access policiesin the Google Cloud console, this role is displayed in association with the membersof the policy's grantee list.

Create or update row-level access policies

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

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

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

• TheCREATE OR REPLACE ROW ACCESS POLICY statement updates an existingrow-level access policy with the same name on the specified table.

  Key points to remember:
  • Each row-level access policy on a table must have a unique name.
  • Like aWHERE clause, thefilter_expression matches the data that you want to be visible to the members of thegrantee_list.
  • You can combine a series of users and groups in thegrantee_list list, if they are comma-separated and quoted separately.
  • You cannot apply row-level access policies onJSON columns. To learn about additional limitations for row-level security, seeLimitations

Examples

The following examples show you how to create and update row access policies fordifferent types ofprincipal identifiersincluding Google Accounts and federated identities. For more informationon federated identities, seeWorkload 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 theuserabc@example.com. Only the rows whereregion = 'APAC' arevisible:

CREATEROWACCESSPOLICYapac_filterONproject.dataset.my_tableGRANTTO('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 singleidentity in a workforce identity pool using this format:principal://iam.googleapis.com/locations/global/workforcePools/POOL_ID/subject/IDENTITY.Only the rows whereregion = 'APAC' are visible:

CREATEROWACCESSPOLICYapac_filterONproject.dataset.my_tableGRANTTO('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 theapac_filter access policy to apply to the service accountexample@exampleproject.iam.gserviceaccount.com:

CREATEORREPLACEROWACCESSPOLICYapac_filterONproject.dataset.my_tableGRANTTO('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_filterONproject.dataset.my_tableGRANTTO('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 inin groups using this formatprincipal://iam.googleapis.com/locations/global/workforcePools/POOL_ID/subject/IDENTITY:

CREATEROWACCESSPOLICYsales_us_filterONproject.dataset.my_tableGRANTTO('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 withallAuthenticatedUsers as the grantees:

CREATEROWACCESSPOLICYus_filterONproject.dataset.my_tableGRANTTO('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_filterONdataset.my_tableGRANTTO('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 anARRAY type:

CREATEROWACCESSPOLICYmy_reports_filterONproject.dataset.my_tableGRANTTO('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 witha 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 onlookup_table lets you avoid creating multiple row accesspolicies. For example, the preceding statement yields the same result as thefollowing, with fewer queries:

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 theCREATE ROW ACCESS POLICY DDL statementreference.

Combine row-level access policies

If two or more row-level access policies grant a user or group access to thesame table, then the user or group has access to all of the data covered by anyof the policies.For example, the following policies grant the userabc@example.com access to specified rows in themy_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 userabc@example.com has access to the rowsin themy_table tablethat have theproduct_category field set toshoes, andabc@example.comalso has access to the rows that have thecolor field set toblue.For example,abc@example.com would be able to access rows with informationabout red shoes and blue cars.

This access is equivalent to the access provided by the following singlerow-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 onecondition being true, use a filterwith anAND operator. For example, the following row-level access policygrantsabc@example.com access only to rows that have both theproduct_category field set toshoes and thecolor field set toblue:

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 toaccess 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 tableby using the Google Cloud console, bq command-line tool, orRowAccessPolicies.List APImethod.

Required permissions

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

To view the members of a row-level access policy on a BigQuerytable, you need thebigquery.rowAccessPolicies.getIamPolicy IAMpermission.

Each of the following predefined IAM roles includes thepermissions 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 inBigQuery, seePredefined 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 DDLstatement, if you have the permissions to do so.

Required permissions

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

• 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 needthe 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 thepermissions 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 inBigQuery, seePredefined roles and permissions.

Delete row-level access policies

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

• TheDROP ROW ACCESS POLICY statement deletes a row-level access policy onthe specified table.

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

• TheDROP ALL ROW ACCESS POLICIES statement deletes all row-level accesspolicies 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 theDROP ROW ACCESS POLICY DDL statementreference.

Query tables with row access policies

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

Required permissions

To query a BigQuery table with row-level access policies, youmust have thebigquery.tables.getData permission on the table. You also needthebigquery.rowAccessPolicies.getFilteredData permission.

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

You must have thedatacatalog.categories.fineGrainedGet permission on allrelevant columns withcolumn-levelsecurity. To gain this permissionwith predefined roles, you need thedatacatalog.categoryFineGrainedReaderrole.

View query results

In the Google Cloud console, when you query a table with a row-levelaccess policy, BigQuery displays a banner notice indicatingthat your results might be filtered by a row-level access policy. Thisnotice 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 withrow access policies in theJob response object:

Example

ThisJob 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 otherBigQuery features and services, seeUsing row level security with other BigQuery features.

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