Introduction to BigQuery row-levelsecurity

This document explains the concept of row-level security, how it works inBigQuery, when to use row-level security to secure your data, and otherdetails.

What is row-level security?

Row-level security lets you filter data and enables access tospecific rows in a table based on qualifying user conditions.

BigQuery supports access controls at the project, dataset, andtable levels, aswell ascolumn-level security throughpolicy tags. Row-level security extends the principle of least privilege by enabling fine-grained access control to a subset of data ina BigQuery table, by means of row-level access policies.

One table can have multiple row-level access policies. Row-level access policiescancoexist on a tablewithcolumn-level security as well asdataset-level,table-level,andproject-level access controls.

How row-level security works

At a high level, row-level security involves the creation of row-levelaccess policies on a target BigQuery table. These policies actas filters to hide or display certain rows of data, depending on whether a useror group is in an allowed list. Any users or groups not specifically included inthe allowed list are denied access.

An authorized user, with the Identity and Access Management (IAM) rolesBigQuery Admin orBigQuery 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, andwhich users or groups (called thegrantee-list) can access certainrow data. The policy also includes the data on which you want to filter, calledthefilter_expression. Thefilter_expression functions like aWHEREclause in a typical query.

For instructions on how to create and use a row-level access policy, seeManaging row-level security.

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

Example use cases

The following examples demonstrate potential use cases for row-level security.

Filter row data based on region

Consider the case where the tabledataset1.table1 contains rows belonging todifferent regions (denoted by theregion column).

You can create and populate the example table by using the following query:

CREATETABLEIFNOTEXISTSdataset1.table1(partnerSTRING,contactSTRING,countrySTRING,regionSTRING);INSERTINTOdataset1.table1(partner,contact,country,region)VALUES('Example Customers Corp','alice@examplecustomers.com','Japan','APAC'),('Example Enterprise Group','bob@exampleenterprisegroup.com','Singapore','APAC'),('Example HighTouch Co.','carrie@examplehightouch.com','USA','US'),('Example Buyers Inc.','david@examplebuyersinc.com','USA','US');

Row-level security lets a data owner or administrator implement policies. Thefollowing statement implements a policy that restricts users in the APAC mailinggroup to see only partners from the APAC region:

CREATEROWACCESSPOLICYapac_filterONdataset1.table1GRANTTO("group:sales-apac@example.com")FILTERUSING(region="APAC");

The resulting behavior is that users in thesales-apac@example.com group canview only rows where the value forregion isAPAC.

The followingstatement implements a policy that restricts both individuals and groups tosee only partners from the US region:

CREATEROWACCESSPOLICYus_filterONdataset1.table1GRANTTO("group:sales-us@example.com","user:jon@example.com")FILTERUSING(region="US");

The resulting behavior is that users in the groupsales-us@example.com and theuserjon@example.com can view only rows where the value forregion isUS.

Users that aren't in theAPAC orUS groups don't see any rows.

Filter row data based on sensitive data

Now, consider a different use case, where you have a table that contains salaryinformation.

You can create and populate the example table by using the following query:

CREATEORREPLACETABLEdataset1.table1(nameSTRING,departmentSTRING,salaryINT64,emailSTRING);INSERTINTOdataset1.table1(name,department,salary,email)VALUES('Jim D','HR',100000,'jim@example.com'),('Anna K','Finance',100000,'anna@example.com'),('Bruce L','Engineering',100000,'bruce@example.com'),('Carrie F','Business',100000,'carrie@example.com');

The row access policy in the following statement restricts querying to membersof the company domain. In addition, the use of theSESSION_USER() functionrestricts access only to rows that belong to the user running the query, basedon their user email address.

CREATEROWACCESSPOLICYsalary_personalONdataset1.table1GRANTTO("domain:example.com")FILTERUSING(Email=SESSION_USER());

The following image demonstrates how the row access policy restricts the table containing salaryinformation. In this example, the user is named Jim, with the email addressjim@example.com.

For additional row-level security examples, seeUse row-level security.

Filter row data based on lookup table

With subquery support, row access policies can reference other tables and usethem as lookup tables. Data used in filtering rules can be stored in a table anda single subquery row access policy can replace multiple configured row accesspolicies. To update the row access policies, you only need to update the lookuptable, which replaces multiple row access policies. You don't need toupdate each individual row access policy.

For examples of filtering row data, seeUse row-level security.

When to use row-level security versus other methods

Authorized views,row-level access policies, and storing data in separate tablesall provide different levels of security, performance, and convenience.Choosing the right mechanism for your use case is important to ensure theproper level of security for your data.

Comparison with authorized views: vulnerabilities

Both row-level security andenforcing row-level accesswith an authorized viewcan have vulnerabilities, if used improperly.

When you use eitherauthorized views or row-level access policies for row-level security, werecommend that you monitor for any suspicious activity usingaudit logging.

Side channels, such as the query duration, can leak information aboutrows that are at the edge of a storage shard. Such attacks would likelyrequire either some knowledge of how the table is sharded, or a large numberof queries.

For more information about preventing such side-channel attacks, seeBest practices for row-level security.

Comparison of authorized views, row-level security, and separate tables

The following table compares the flexibility, performance, and security ofauthorized views, row-level access policies, and separate tables.

Create and manage row-level access policies

For information about how to create, update (re-create), list, view, and deleterow-level access policies on a table, and how to query tableswith row-level access policies, seeWorking with row-level access security.

Implicit deletion of row-level access policies

Row access policies can be implicitly (automatically) removed from a table underseveral conditions.

The general principle for automatically deleting row access policies are:

• Operations using aWRITE_TRUNCATE write dispositionalways overwrite any existing row access policies on the destination table.
• For operations with aWRITE_APPEND write disposition, the destinationtable's current row access policies are preserved, and the source table'spolicies are added to the destination table.

Specifically, row access policies are implicitly removed in the followingsituations:

• Replacing a table: when a table is replaced using theCREATE OR REPLACETABLEDDL statement, all existing row access policies on the original table aredropped. This occurs even if the replacement query is based on the originaltable's data.

• Loading or querying withWRITE_TRUNCATE: operations that use theWRITE_TRUNCATE write disposition remove all existing row access policies.This includes loading data using thebq load --replacecommand and running a query with the status ofwriteDisposition set toWRITE_TRUNCATE. Such operations completely overwrite the table, and rowaccess policies aren't carried over.

• Table deletion or expiration: if a table is explicitly deleted or if itreaches its expiration time and is automatically removed, all associated rowaccess policies are also deleted.

• Table copy operations: when copying a table without row access policies to adestination table that has row access policies, the policies on thedestination table are removed, unless the--append_table flagor"writeDisposition": "WRITE_APPEND" is used. SeeUsing row-levelsecurity with other BigQueryfeaturesfor more information on table copy jobs.

Using theTRUNCATETABLEDML statement, which removes all rows from a table while maintaining its schema,doesn't remove row access policies.

Quotas

For more information about quotas and limits for row-level security, seeBigQueryQuotas and limits.

Pricing

Row-level security is included with BigQuery at no additionalcost. However, a row-level access policy can affect the cost of runninga query in the following ways:

• Additional billing can be caused by row-level access policies, specificallypolicies that include subqueries that reference other tables.

• Row-level access policy filters don't participate in query pruning onpartitioned and clusteredtables.This does not mean it reads more data during the main query execution. Itdoesn't take advantage of row access policy predicates to prune any further.

• With row-level access policy filters, not all user filters are applied early.This might increase the data read from tables and might read and bill for morerows.

For more information about BigQuery query pricing, seeBigQuery pricing.

Limitations

For information about limits for row-level security, see BigQueryRow-level security limits.The following sections document additional row-level security limitations.

Performance limitations

• Some BigQuery features aren't accelerated whenworking with tables containing row-level access policies, such asBigQuery BI Engineandmaterialized views.

• Row-level security does not participate in querypruning, which is a feature ofpartitioned tables. For more information,seePartitioned and clusteredtables.This limitation doesn't slow down the main query execution.

• You might experience a small performance degradation when you query tableswith row-level security.

For more information about how row-level security interacts with someBigQuery features and services, seeUsing row-level security with other BigQuery features.

Other limitations

• This feature may not be available when using reservations that are createdwith certain BigQuery editions. For more information aboutwhich features are enabled in each edition, seeIntroduction to BigQuery editions.

• Row access policies are not compatible with Legacy SQL. Queries of tableswith row-level access policies must use GoogleSQL. Legacy SQL queries arerejected with an error.

• You cannot apply row-level access policies onJSON columns.

• Wildcard table queries are not supported over tables with row access policies.

• Row access policies cannot be applied to temporary tables.

• You cannot apply row-level access policies to tables that reference othertables that have row-level security.

• Some features of BigQuery are not compatible with row-levelsecurity. For more information, seeUsing row-level security.

  • Row access policies that incorporatesubqueries aren't compatible withtheBigQuery Storage Read API.The BigQuery Storage Read API only supports simple filter predicates.

• Non-query operations, including service account jobs, that need full accessto table data can use row-level security with theTRUE filter.Examples includetable copying,dataproc workflows,and more. For more information, seeUsing row-level security.

• You can create, replace, or delete row-level access policieswith DDL statements orrow access policyAPIs. Youcan also perform all available actions in the row access policy APIs in thebq command-line tool. You can list and viewrow-level access policies in theGoogle Cloud console.

• Previewing or browsing tablesis incompatible with row-level security.

• Table sampling is not compatiblewith row-level security.

• Top-level subquery policy results are limited to 100 MB. This limit applies perrow-level access policy.

• Top-levelINsubquerieswhere the type ofsearch_value isFLOAT,STRUCT,ARRAY,JSON orGEOGRAPHY aren't available in row access policies.

• If the row-level access policy predicate cannot be evaluated due to thedeletion of any referenced table, the query fails.

• Subquery row-level access policies only support BigQuerytables, BigLake external tables, and BigLake managedtables.

• Columnrenaminganddropping statementsthat modify table schema and could impact row access policies aren't permitted.

• Data masking is only compatible withqueries that have non-subquery row access policies. Data masking is applied on top ofrow-level security. For example, if there is a row access policy applied onlocation = "US" andlocation is masked, then users are able to see rowswherelocation = "US" but the location field is masked in the results. Queriesinvolving a subquery row access policy will require Fine-Grained Reader accesson columns referenced by row access policies.

Audit logging and monitoring

When data in a table with one or more row-level access policies is read, therow-level access policies authorized for the read access and any correspondingtables referenced in subqueries appear in theIAM authorization information for that read request.

Creation and deletion of row-level access policies are audit logged, and can beaccessed throughCloud Logging. Audit logsinclude the name of the row-level access policy. However, thefilter_expression andgrantee_list definitions of a row-level accesspolicy are omitted from logs, as they may contain user or other sensitiveinformation. Listing and viewing of row-level access policies are not auditlogged.

For more information about logging in BigQuery, seeIntroduction to BigQuery monitoring.

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

What's next

• For information about managing row-level security, seeUse row-level security.

• For information about how row-level security works with otherBigQuery features and services, seeUsing row-level security with other BigQuery features.

• For information about best practices for row-level security, seeBest Practices for row-level security in BigQuery.