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.

Overview

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

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

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.

  Key points to remember:

  • Each row-level access policy on a table must have a unique name.
  • Like a WHERE clause, the filter_expression matches the data that you want to be visible to the members of the grantee_list.
  • You can combine a series of users and groups in the grantee_list list, if they are comma-separated and quoted separately.
  • You cannot apply row-level access policies on JSON columns. To learn about additional limitations for row-level security, see Limitations

Examples

Create a new row access policy. Access to the table is restricted to the user [e-mail]. Only the rows where region = 'APAC' are visible:

CREATE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('user:[e-mail]')
FILTER USING (region = 'APAC');

Update the access policy to apply to the service account [e-mail] instead:

CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('serviceAccount:[e-mail]')
FILTER USING (region = 'APAC');

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

CREATE ROW ACCESS POLICY sales_us_filter
ON project.dataset.my_table
GRANT TO ('user:[e-mail]',
          'group:[e-mail]',
          'group:[e-mail]')
FILTER USING (region = 'US');

Create a row access policy with allAuthenticatedUsers as the grantees:

CREATE ROW ACCESS POLICY us_filter
ON project.dataset.my_table
GRANT TO ('allAuthenticatedUsers')
FILTER USING (region = 'US');

Create 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());

Create 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));

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

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

To provide feedback or request support with this feature, send email to [e-mail].

Consider the following table, lookup_table:

+-----------------+--------------+
|      email      |    region    |
+-----------------+--------------+
| [e-mail] | europe-west1 |
| [e-mail] | us-west1     |
| [e-mail] | us-west2     |
+-----------------+--------------+

CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('domain:example.com')
FILTER USING (region IN (
    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:

CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('user:[e-mail]')
FILTER USING (region = 'us-west1');

CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('user:[e-mail]')
FILTER USING (region IN 'us-west1', 'us-west2');

CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('user:[e-mail]')
FILTER USING (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 [e-mail] access to specified rows in the my_table table:

CREATE ROW ACCESS POLICY shoes
ON project.dataset.my_table
GRANT TO ('user:[e-mail]')
FILTER USING (product_category = 'shoes');

CREATE OR REPLACE ROW ACCESS POLICY blue_products
ON project.dataset.my_table
GRANT TO ('user:[e-mail]')
FILTER USING (color = 'blue');

In the preceding example, the user [e-mail] has access to the rows in the my_table table that have the product_category field set to shoes, and [e-mail] also has access to the rows that have the color field set to blue. For example, [e-mail] 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:

CREATE ROW ACCESS POLICY shoes_and_blue_products
ON project.dataset.my_table
GRANT TO ('user:[e-mail]')
FILTER USING (product_category = 'shoes' OR color = '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 [e-mail] access only to rows that have both the product_category field set to shoes and the color field set to blue:

CREATE ROW ACCESS POLICY blue_shoes
ON project.dataset.my_table
GRANT TO ('user:[e-mail]')
FILTER USING (product_category = 'shoes' AND color = 'blue');

With the preceding row-level access policy, [e-mail] 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:

Console

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

   Go to BigQuery

2. Click the table name to see its details, and then click 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.

   

4. To see all the roles and users affected by a row-level access policy, click VIEW next to the policy. For example, in the image below, you can see in the View permissions panel that members of the grantee list have the bigquery.filteredDataViewer role.

   

   Important: adding members to a policy and removing members from a policy are currently only supported using DDL statements.

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

Important: You cannot delete the last row-level access policy from a table using DROP ROW ACCESS POLICY. Attempting to do so results in an error. To delete the last row-level access policy on a table, you must use DROP ALL ROW ACCESS POLICIES instead. For more information about dropping the last row-level access policy on a table, see Best practices for row-level security.

Examples

Delete a row-level access policy from a table:

DROP ROW ACCESS POLICY my_row_filter ON project.dataset.my_table;

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

DROP ALL ROW ACCESS POLICIES ON project.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.