Introduction to BigQuery row-level security
This document 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 through 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 dataset-level, table-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. These policies act as filters to hide or display certain rows of data, depending on whether a user or group is in an allowed list. Any users or groups not specifically included in the allowed list are denied access.
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) can access certain
row data. The policy also includes the data on which you want to filter, called
filter_expression functions like a
clause in a typical query.
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
Filter row data based on region
Consider the case where a table
dataset1.table1 contains rows belonging to
different regions (denoted by the
Row-level security lets a Data Owner or Admin implement policies, such as "Users
group:apac can only see partners from the APAC region."
The resulting behavior is that users in the group
view only rows where
Region = "APAC". Similarly, users in the group
email@example.com can view only rows in the
US region. Users not in
US groups don't see any rows.
The row-level access policy named
us_filter grants access to
multiple entities, including the chief US salesperson
firstname.lastname@example.org, all of
whom can now access the rows belonging to the
Filter row data based on sensitive data
Now, consider a different use case, where we have a table of salary data.
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
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
Both row-level security and enforcing row-level access with an authorized view 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.
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 Best practices for row-level security.
Comparison of authorized views, row-level security, and separate tables
The following table compares the flexibility, performance, and security of authorized views, row-level access policies, and separate tables.
|Recommended for flexibility. Can be vulnerable to carefully crafted queries, query durations, and other types of side-channel attacks.||Authorized views are a good choice when you need to share data with others and flexibility and performance are important. For example, you can use authorized views to share data within your work group.|
|Row-level access policies||Recommended for a balance of flexibility and security. Can be vulnerable to query duration side-channel attacks.||Row-level access policies are a good choice when you need to share data with others and you want to provide additional security over views or table slices. For example, you can use row-level access policies to share data with people who all use the same dashboard, even if some people have access to more data than others.|
|Separate tables||Recommended for security. Users cannot infer data without access to the table.||Separate tables are a good choice when you need to share data with others and you need to keep data isolated. For example, you can use separate tables to share data with third-party partners and vendors, when the total number of rows must be secret.|
Create and manage row-level access policies
For information about how to create, update (re-create), list, view, and delete row-level access policies on a table, and how to query tables with row-level access policies, see Working with row-level access security.
For more information about quotas and limits for row-level security, see BigQuery Quotas and limits.
Row-level security is included with BigQuery at no additional cost. However, a row-level access policy can affect the cost of running a query in the following ways:
If a row-level access policy prevents a user from accessing a row in a query, that row is not processed or billed.
Row-level access policy filters don't participate in query pruning on partitioned and clustered tables. This means that a row-level security filter may cause more data to be processed than if they were not used.
For more information about BigQuery query pricing, see BigQuery pricing.
For information about limits for row-level security, see BigQuery Row-level security limits. The following sections document additional row-level security 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 security does not participate in query pruning, which is a feature of partitioned tables. For more information, see Partitioned and clustered tables.
You might experience a small performance degradation when you query tables with row-level security.
For more information about how row-level security interacts with some BigQuery features and services, see Using row-level security with other BigQuery features.
Row access policies are not compatible with Legacy SQL. Queries of tables with row-level access policies must use GoogleSQL. Legacy SQL queries are rejected with an error.
You cannot apply row-level access policies on JSON columns.
Some features of BigQuery are not compatible with row-level security. For more information, see Using row-level security.
Non-query operations, including service account jobs, that need full access to table data can use row-level security with the
TRUEfilter. Examples include table copying, dataproc workflows, and more. 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 Google Cloud console or the
Table sampling is not compatible with row-level security.
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
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
For more information about logging in BigQuery, see Introduction to BigQuery monitoring.
For more information about logging in Google Cloud, see Cloud Logging.
For information about managing row-level security, see Use 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.