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:
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.
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:
CREATE
ROW ACCESS POLICY
apac_filter
ON project.dataset.my_table
GRANT TO ('user:abc@example.com')
FILTER USING
(region = 'APAC');
Update the access policy to apply to the service account
example@exampleproject.iam.gserviceaccount.com
instead:
CREATE OR
REPLACE ROW ACCESS POLICY
apac_filter
ON project.dataset.my_table
GRANT TO ('serviceAccount:example@exampleproject.iam.gserviceaccount.com')
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:john@example.com',
'group:sales-us@example.com',
'group:sales-managers@example.com')
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:
To provide feedback or request support with this feature, send email to
bigquery-row-level-security-support@google.com.
Consider the following table,
lookup_table
:
+-----------------+--------------+
| email | region |
+-----------------+--------------+
| xyz@example.com | europe-west1 |
| abc@example.com | us-west1 |
| abc@example.com | 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:abc@example.com')
FILTER USING
(region = 'us-west1');
CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('user:abc@example.com')
FILTER USING
(region IN 'us-west1', 'us-west2');
CREATE OR REPLACE ROW ACCESS POLICY apac_filter
ON project.dataset.my_table
GRANT TO ('user:xyz@example.com')
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
abc@example.com
access to specified rows in the
my_table
table:
CREATE
ROW ACCESS POLICY
shoes
ON project.dataset.my_table
GRANT TO ('user:abc@example.com')
FILTER USING
(product_category = 'shoes');
CREATE OR
REPLACE ROW ACCESS POLICY
blue_products
ON project.dataset.my_table
GRANT TO ('user:abc@example.com')
FILTER USING
(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:
CREATE
ROW ACCESS POLICY
shoes_and_blue_products
ON project.dataset.my_table
GRANT TO ('user:abc@example.com')
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
abc@example.com
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:abc@example.com')
FILTER USING
(product_category = 'shoes' AND color = '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:
Console
To view row-level access policies, go to the BigQuery page
in the Google Cloud console.
Go to BigQuery
Click the table name to see its details, and then click
View row access policies
.
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.
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
.
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
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