Working with 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.

Overview

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

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

Creating a row-level access policy on a BigQuery table requires the following permissions.

  • bigquery.rowAccessPolicies.create
  • bigquery.rowAccessPolicies.setIamPolicy
  • bigquery.tables.getData on the target table
  • bigquery.jobs.create to run the DDL query job

Updating a row-level access policy on a BigQuery table requires the following permissions.

  • bigquery.rowAccessPolicies.update
  • bigquery.rowAccessPolicies.setIamPolicy
  • bigquery.tables.getData on the target table
  • bigquery.jobs.create to run the DDL query job

The following predefined Identity and Access Management (IAM) roles already include all the required permissions for managing row-level access policies, including creating and updating:

  • bigquery.admin
  • bigquery.dataOwner

The bigquery.filteredDataViewer role

When you successfully create a row-level access policy, you automatically grant the bigquery.filteredDataViewer role to the members of the grantee list. The bigquery.filteredDataViewer role grants the ability to view the rows defined by the policy's filter expression. 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.

See our recommended Best practices for row-level security when using the bigquery.filteredDataViewer role with IAM.

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

Creating or updating a row-level access policy

Use the following commands in your DDL statement to create or replace a row access policy.

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

  • CREATE ROW ACCESS POLICY IF NOT EXISTS 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.

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

Examples

See the DDL reference for the complete syntax and options for creating a row-level access policy.

SQL

You can run these DDL statements in the Cloud Console page.

On the BigQuery page, enter the statement into the query editor.

Go to BigQuery

Creating a row access policy, and then modifying the grantees later

CREATE ROW ACCESS POLICY My_apac_filter
ON project.dataset.My_table
GRANT TO ("user:abc@example.com")
FILTER USING (region = "apac");
CREATE OR REPLACE ROW ACCESS POLICY My_apac_filter
ON project.dataset.My_table
GRANT TO ("user:xyz@example.com")
FILTER USING (region = "apac");

Creating a row access policy with multiple grantees

CREATE ROW ACCESS POLICY My_us_filter
ON project.dataset.My_table
GRANT TO ("user:john@example.com", "group:sales-us@example.com", "group:sales-managers@example.com")
FILTER USING (region = "us");

Creating a row access policy with allAuthenticatedUsers as the grantees

CREATE ROW ACCESS POLICY My_us_filter
ON project.dataset.My_table
GRANT TO ("allAuthenticatedUsers")
FILTER USING (region = "us");

Creating a row access policy with a filter based on the current user

CREATE ROW ACCESS POLICY My_row_filter
ON dataset.My_table
GRANT TO ("domain:example.com")
FILTER USING (email = SESSION_USER());

Creating a row access policy with a filter on a column with an ARRAY type

CREATE ROW ACCESS POLICY My_reports_filter
ON project.dataset.My_table
GRANT TO ("domain:example.com")
FILTER USING (SESSION_USER() IN UNNEST(reporting_chain));

List a table's row-level access policies

You can list and view all the row-level access policies that exist on a table in the Cloud Console or using the bq command-line tool, if you have the correct permissions to do so.

Required permissions

Listing the row-level access policies on a BigQuery table requires the following permissions.

  • bigquery.rowAccessPolicies.list

Viewing the members of a row-level access policy on a BigQuery table requires the following permissions.

  • bigquery.rowAccessPolicies.getIamPolicy

The following predefined Identity and Access Management (IAM) roles already include all the required permissions for managing row-level access policies, including listing and viewing:

  • bigquery.admin
  • bigquery.dataOwner

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

Listing a table's row-level access policies

Console

  1. To view row-level access policies, go to the BigQuery page in the Cloud Console.

    Go to BigQuery

  2. Click on the table name to see its details, and then click on the View row access policies button.

    View row access policies

  3. When the Row access policies panel opens, you see a list of all the row-level access policies on the table, by name, and the filter_expression for each policy.

    Row access policies detail

  4. If you click VIEW next to the policy, then the View permissions panel opens, and you will see a list of all the roles and users affected by the row-level access policy. Member of the grantee list have the bigquery.filteredDataViewer role.

    Row access policies detail

bq

Enter the bq ls command and supply the --row_access_policies flag. The dataset and table names are required.

    bq ls --row_access_policies dataset.table

For example, the following command lists information about the row-level access policies on a table named My_table in a dataset with the ID My_dataset.

    bq ls --row_access_policies My_dataset.My_table

API

Use the RowAccessPolicies.List method in the REST API reference section.

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 correct permissions to do so.

Required permissions

To drop a row access policy, you must be granted the following permissions:

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

To drop all the row access policies on a table at the same time, you must be granted the following permissions:

  • bigquery.rowAccessPolicies.update
  • bigquery.rowAccessPolicies.setIamPolicy
  • bigquery.rowAccessPolicies.list
  • bigquery.jobs.create to run the DDL query job

The following predefined Identity and Access Management (IAM) roles already include all the required permissions for managing row-level access policies, including deleting:

  • bigquery.admin
  • bigquery.dataOwner

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

Deleting a row-level access policy

Use the following commands in your DDL statement to delete a row access policy from a table.

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

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

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

Examples

See the DDL reference for the complete syntax and options for deleting a row-level access policy.

SQL

You can run these DDL statements in the Cloud Console page.

On the BigQuery page, enter the statement into the query editor.

Go to BigQuery

Deleting a row-level access policy from a table

DROP ROW ACCESS POLICY My_row_filter ON project.dataset.My_table;

Deleting all the row-level access policies from a table

DROP ALL ROW ACCESS POLICIES ON project.dataset.My_table;

Querying 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

Querying a BigQuery table with one or more row-level access policies requires the following permissions.

Viewing query results

Console

In the 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