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

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 [email protected]. Only the rows where region = 'APAC' are visible:

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

Update the access policy to apply to the service account [email protected] instead:

CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('serviceAccount:[email protected]')
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:[email protected]',
          'group:[email protected]',
          'group:[email protected]')
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 a region comparison configured per user:

Consider the following table, lookup_table:

+-----------------+--------------+
|      email      |    region    |
+-----------------+--------------+
| [email protected] | europe-west1 |
| [email protected] | us-west1     |
| [email protected] | 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:[email protected]')
FILTER USING (region = 'us-west1');

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

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

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

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

In the preceding example, the user [email protected] has access to the rows in the my_table table that have the product_category field set to shoes, and [email protected] also has access to the rows that have the color field set to blue. For example, [email protected] 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:[email protected]')
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 [email protected] 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:[email protected]')
FILTER USING (product_category = 'shoes' AND color = 'blue');

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

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.