Impact on writes from column-level access control

This page explains the impact to writes when you use BigQuerycolumn-level access control to restrict access to data at the column level. Forgeneral information about column-level access control, seeIntroduction toBigQuery column-level access control.

Column-level access control requires a user to have read permission for columns thatare protected by policy tags. Some write operations need to read column databefore actually writing into a column. For those operations,BigQuery checks the user's read permission to ensure the user hasaccess to the column. For example, if a user is updating data that includeswriting to a protected column, the user must have read permission for theprotected column. If the user is inserting a new data row that includes writingto a protected column, the user doesn't need read access for the protectedcolumn. But, the user who writes such a row won't be able to read the newlywritten data unless the user has read permission for the protected columns.

The following sections provide details about different types of writeoperations. The examples in this topic usecustomers tables with the followingschema:

Using BigQuery data manipulation language (DML)

Inserting data

For anINSERT statement, BigQuery does not check Fine-GrainedReader permission on the policy tags on either the scanned columns or theupdated columns. This is because anINSERT does not require reading any of thecolumn data. But, even if you successfully insert values into columns where youdon't have read permission, once inserted, the values are protected as expected.

Deleting, updating, and merging data

ForDELETE,UPDATE, andMERGE statements, BigQuery checksfor the Fine-Grained Reader permission on the scanned columns. Columns aren'tscanned by these statements unless you include aWHERE clause,or some other clause or subquery that requires the query to read data.

Loading data

When loading data (for example, from Cloud Storage or local files) to atable, BigQuery does not check the Fine-Grained Readerpermission on the columns of the destination table. This is because loading datadoes not require reading content from the destination table.

Streaming into BigQuery is similar toLOAD andINSERT.BigQuery lets you stream data into a destination tablecolumn, even if you don't have the Fine-Grained Reader permission.

Copying data

For a copy operation, BigQuery checks whether the user hasthe Fine-Grained Reader permission on the source table. BigQuerydoes not check whether the user has the Fine-Grained Reader permission to thecolumns in the destination table. LikeLOAD,INSERT, and streaming,once the copy is complete, you won't be able to read the data that was justwritten, unless you have the Fine-Grained Reader permission to the destinationtable's columns.

DML examples

INSERT

Example:

INSERTINTOcustomersVALUES('alice',85,'123-456-7890');

UPDATE

Example:

UPDATEcustomersSETcredit_score=0WHEREuser_idLIKE'alice%'ANDcredit_score <30

DELETE

Example:

DELETEcustomersWHEREcredit_score=0

Load examples

Loading from a local file or Cloud Storage

Example:

load--source_format=CSVsamples.customers\./customers_data.csv\./customers_schema.json

Streaming

No policy tags are checked when streaming with the legacyinsertAll streaming API or theStorage Write API. ForBigQuery change data capture ingestion, the policy tags are checked on the primary key columns.

Copy examples

Appending data to an existing table

Example:

cp-asamples.customerssamples.customers_dest

Saving query results to a destination table

Example:

query--use_legacy_sql=false\--max_rows=0\--destination_tablesamples.customers_dest\--append_table"SELECT * FROM samples.customers LIMIT 10;"