This page explains the concept of row-level security, how it works in BigQuery, when to use row-level security to secure your data, and other details.

What is row-level security?
Row-level security lets you filter data and enables access to specific rows in a table, based on qualifying user conditions.

BigQuery already supports access controls at the project, dataset, and table levels, as well as column-level security by using policy tags. Row-level security extends the principle of least privilege by enabling fine-grained access control to a subset of data in a BigQuery table, by means of row-level access policies.

One table can have multiple row-level access policies. Row-level access policies can coexist on a table with column-level security as well as table-level, dataset-level, and project-level access controls.

How row-level security works
At a high level, row-level security involves the creation of row-level access policies on a target BigQuery table. This policy then acts as a filter to hide or display certain rows of data, depending on whether a user or group is in an allowed list.

An authorized user, with the Identity and Access Management (IAM) roles BigQuery Admin or BigQuery DataOwner, can create row-level access policies on a BigQuery table.

When you create a row-level access policy, you specify the table by name, and which users or groups (called the grantee-list) should have access to certain row data. The policy also includes the data on which you wish to filter, called the filter_expression. The filter_expression functions like a WHERE clause in a typical query.

Remember: Like a WHERE clause, the filter_expression matches the data that you want to be visible to the members of the grantee_list. Users not in the grantee_list will not see any rows.
For instructions on how to create and use a row-level access policy, see Working with row-level security.

See the DDL reference for the complete syntax, usage and options when creating row-level access policies.

Example use cases
Filtering row data based on region
Consider the case where a table contains rows belonging to different regions, denoted by the region column in the table dataset1.table1.

Row-level security lets a Data Owner or Admin implement policies, such as "Users in the group:apac can only see partners from the APAC region."

Row level security use case for regions

The resulting behavior is that users in the group sales-apac@example.com can view only rows where Region = "APAC". Similarly, users in the group sales-us@example.com can view only rows in the US region. Users not in APAC or US groups don't see any rows.

Note that the row-level access policy named us_filter grants access to multiple entities, including the chief US salesperson jon@example.com, all of whom can now access the rows belonging to the US region.

Filtering row data based on sensitive data
Now, consider a different use case, where we have a table of salary data.

Row level security use case for salaries

The grantee_list restricts querying to members of the company domain. In addition, the use of the SESSION_USER() function further restricts access only to rows that belong to the user running the query, based on their own user email address. In this case, it is jim@example.com.

When to use row-level security vs other methods
Authorized views, row-level access policies and storing data in separate tables all provide different levels of security, performance, and convenience. Choosing the right mechanism for your use case is important to ensure the proper level of security for your data.

Comparison with authorized views: vulnerabilities
Row-level security and enforcing row-level access with an authorized view can both can have vulnerabilities, if used improperly.

When you use either authorized views or row-level access policies for row-level security, we recommend that you monitor for any suspicious activity using audit logging.

Carefully crafted queries can leak information through error messages. For example, a query crafted to trigger a division by zero on a specific value could reveal the presence of that value, even when it would be excluded by the view definition.

Other side-channels such as the query duration can leak information about rows that are at the edge of a storage shard. Such attacks would likely require either some knowledge of how the table is sharded, or a large number of queries.

For more information about preventing such side-channel attacks, see Security limitations and Best practices for row-level security in BigQuery.

Comparison of authorized views, row-level security, and separate tables
The following table below compares the performance and security of authorized views, row-level access policies, and separate tables.

Security Recommended for
Authorized
views Vulnerable to carefully crafted queries, query duration, and other types of side-channel attack. When flexibility and performance are most important.

Example: sharing data within the same work group.
Row-level access policies Vulnerable to carefully crafted queries and query duration side-channel attacks. When it is convenient to have all users query the same table. For instance, when everyone shares the same dashboard, but some users have access to more data.

To provide additional security over views.

Example: sharing table slices within your organization.
Separate tables Complete isolation. When isolation is paramount. For instance, when the total number of rows must be secret.

Example: sharing data outside your organization, such as with third-party partners and vendors.
Creating and managing row-level access policies
For information about how to create, update (re-create), list, view, and delete row-level access policies on a table, as well as details about querying tables with row-level access policies, see Working with row-level access security.

Quotas
For more information about quotas and limits for row-level security, see BigQuery Quotas and limits.

Pricing
Row-level security is included with BigQuery at no cost.

Billing costs for accessing a table's row-level access policy is similar to a query. However, row-level access policies might indirectly affect the number of bytes processed, in the following ways.

When a query is run against a table with a row-level access policy, the bytes billed is calculated in the same way as if you had composed an identical query with a WHERE clause, instead of the filter expression.
Row-level access policy filters do not participate in query pruning on partitioned and clustered tables.
For more information about BigQuery query pricing, see BigQuery pricing.

Limitations
Row-level security is subject to the following limitations.

Performance limitations
Some BigQuery features aren't accelerated when working with tables containing row-level access policies, such as BigQuery BI Engine and materialized views.

Row-level access policy filters do not participate in query pruning on partitioned and clustered tables.

For more information about how row-level security interacts with some BigQuery features and services, see Using row-level security with other BigQuery features.

Security limitations
Caution: In some cases, row-level security can be subject to side-channel attack.
Example

Suppose you have a table with revenue information in it. You protect this sensitive data with a row-level access policy to filter rows based on business unit. Even though there is a security filter predicate in place to prevent a user with access to this table from directly querying the protected rows, it is possible for that user to derive the revenue information for other business units, through repeated, carefully crafted queries and observing the resulting query error messages.

Specifically, a malicious user with access to the underlying table can derive the protected row values when the query returns a divide-by-zero exception.
A divide-by-zero exception results from a query, such as the following: SELECT * FROM dataset.table WHERE 1/(100000-revenue) = 1. The result could potentially let the malicious user learn that the revenue $100,000 exists in the table.
This type of attack often requires a large number of repeated attempts against a table with row-level security. We recommend that admins monitor Cloud audit logs for suspicious activity on tables with row-level security.
For more information about limiting side-channel attacks, see Best practices for row-level security in BigQuery.

Other limitations
Row access policies are not compatible with Legacy SQL. Queries of tables with row-level access policies must use Standard SQL. Legacy SQL queries are rejected with an error.

Some features of BigQuery are not compatible with row-level security. See Using row-level security for more information.

Non-query operations, including service account jobs, that need full access to table data can use row-level security with the "true filter". For more information, see Using row-level security.

Creating, replacing or deleting row-level access policies must be performed with DDL statements. Listing and viewing row-level access policies can be performed through the Cloud Console or the bq command-line tool.

Audit logging and monitoring
When data in a table with one or more row-level access policies is read, the row-level access policies authorized for the read access appear in the IAM authorization information for that read request.

Creation and deletion of row-level access policies are audit logged, and can be accessed through Cloud Logging. Audit logs include the name of the row-level access policy. However, the filter_expression and grantee_list definitions of a row-level access policy are omitted from logs, as they may contain user or other sensitive information. Listing and viewing of row-level access policies are not audit logged.

For more information about logging in BigQuery, see Introduction to BigQuery monitoring.

For more information about logging in Google Cloud, see Cloud Logging.

What's next
For information about managing row-level security, see Working with row-level security.

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.