Introduction to column-level access control

Note: This feature may not be available when using reservations that are created with certain BigQuery editions. For more information about which features are enabled in each edition, seeIntroduction to BigQuery editions.

BigQuery provides fine-grained access to sensitive columns usingpolicy tags, or type-based classification, of data. UsingBigQuery column-level access control, you can create policies thatcheck, at query time, whether a user has proper access. For example, a policycan enforce access checks such as:

• You must be ingroup:high-access to see the columns containingTYPE_SSN.

To enhance column-level access control, you can optionally usedynamic data masking.Data masking lets you mask sensitive data by substituting null, default, orhashed content in place of the column's actual value.

Column-level access control workflow

To restrict data access at the column level:

1. Define a taxonomy and policy tags. Create and manage a taxonomy andpolicy tags for your data. For guidelines, seeBest practices for policy tags.

2. Assign policy tags to your BigQuery columns. InBigQuery, use schema annotations to assign a policy tag to eachcolumn where you want to restrict access.

3. Enforce access control on the taxonomy. Enforcing access control causesthe access restrictions defined for all of the policy tags in the taxonomyto be applied.

4. Manage access on the policy tags. UseIdentity and Access Management(IAM) policies to restrict access to each policy tag. The policyis in effect for each column that belongs to the policy tag.

When a user tries to access column data at query time, BigQuerychecks the column policy tag and its policy to see whether the user is authorized toaccess the data.

Note: Column-level access control is enforcedin addition to existingdatasetACLs. A user needs both dataset permission and policy tag permission inorder to access data protected by column-level access control.

Identify what needs to be tagged

To determine the types of sensitive data that you have and which columns needpolicy tags, consider generating profiles about your data across anorganization, folder, or project using Sensitive Data Protection.Data profiles containmetrics and metadata about your tables and help you determine wheresensitiveand high-risk data reside.Sensitive Data Protection reports these metrics at the project, table, and columnlevels. For more information, seeData profiles for BigQuerydata.

The following image shows a list of column data profiles (click to enlarge).Columns with high data-risk values might containhigh-sensitivitydata and have nocolumn-level access controls. Alternatively, those columns might containmoderate or high-sensitivity data that is accessible to a large number ofpeople.

Example use case

Consider an organization that needs to classify sensitive data into twocategories:High andMedium.

To set up column level security, a data steward, who has theappropriate permissions, would perform the following steps to set upahierarchy of data classification.

1. The data steward creates a taxonomy named "Business criticality". Thetaxonomy includes the nodes, orpolicy tagsHigh andMedium.

2. The data steward decides that the policy for theHigh node includes accessfor a group namedhigh-tier-access.

3. The data steward creates more levels of nodes in the taxonomy, underHigh andMedium. The lowest level node is a leaf node,such as theemployee_ssn leaf node. The data steward can create a differentaccess policy for theemployee_ssn leaf node, or not.

4. The data steward assigns a policy tag to specific table columns.In this example, the data steward assigns theHigh access policy to theemployee_ssn column in a table.

5. In theCurrent schema page of the console, the data steward can see thepolicy tag that governs a particular column. In this example, theemployee_ssn column is under theHigh policy tag, so when viewing theschema foremployee_ssn, the console displays the taxonomy name andthe policy tag in thePolicy tags field:Business criticality:High.

   

   For details on using the console to set a policy tag, seeSet a policy tag on a column.

   Alternatively, you can set the policy tag using thebq update command. Thenames field ofpolicyTags includes the ID of theHigh policy tag,projects/project-id/locations/location/taxonomies/taxonomy-id/policyTags/policytag-id:

   [...{"name":"ssn","type":"STRING","mode":"REQUIRED","policyTags":{"names":["projects/project-id/locations/location/taxonomies/taxonomy-id/policyTags/policytag-id"]}},...]

   For details on using thebq update command to set a policy tag, seeSet a policy tag on a column.

   Note: You can assign only one policy tag per column.

6. The admin performs similar steps for theMedium policy tag.

With this fine-grained access, you can manage access to many columns bycontrolling only a small number of data classification policy tags.

For details about these steps, seeRestricting access with column-level access control.

Roles used with column-level access control

The following roles are used for BigQuery column-level access control.

The Data Catalog Policy Tag Admin role is required for users whoneed to create and manage taxonomies and policy tags.

Role/ID	Permissions	Description
Data Catalog Policy Tag Admin (datacatalog.categoryAdmin)	datacatalog.categories.getIamPolicy datacatalog.categories.setIamPolicy datacatalog.taxonomies.create datacatalog.taxonomies.delete datacatalog.taxonomies.get datacatalog.taxonomies.getIamPolicy datacatalog.taxonomies.list datacatalog.taxonomies.setIamPolicy datacatalog.taxonomies.update resourcemanager.projects.get resourcemanager.projects.list	Applies at the project level. This role grants the ability to do the following: Create, read, update, and delete taxonomies and policy tags. Get and set IAM policies on policy tags.

The BigQuery Data Policy Admin role, the BigQuery Admin role or the BigQuery Data Owner role is required in order to create and manage data policies. When you use theGoogle Cloud console to enforce access control on a taxonomy, the service silentlycreates a data policy for you.

Role/ID	Permissions	Description
BigQuery Data Policy Admin (bigquerydatapolicy.admin) BigQuery Admin (bigquery.admin) BigQuery Data Owner (bigquery.dataOwner)	bigquery.dataPolicies.create bigquery.dataPolicies.delete bigquery.dataPolicies.get bigquery.dataPolicies.getIamPolicy bigquery.dataPolicies.list bigquery.dataPolicies.setIamPolicy bigquery.dataPolicies.update	Thebigquery.dataPolicies.create andbigquery.dataPolicies.list permissions apply at the project level. The other permissions apply at the data policy level. This role grants the ability to do the following: Create, read, update, and delete data policies. Get and set IAM policies on data policies.

You also need thedatacatalog.taxonomies.get permission, which you can get from severalof theData Catalog predefined roles.

The Data Catalog Fine-Grained Reader role is required for userswho need access to data in secured columns.

Role/ID	Permissions	Description
Fine-Grained Reader/datacatalog.categoryFineGrainedReader	datacatalog.categories.fineGrainedGet	Applies at the policy tag level. This role grants the ability to access the content of columns restricted by a policy tag.

To learn more about Data Catalog roles, seeData Catalog Identity and Access Management (IAM).To learn more about BigQuery roles, seeAccess control with IAM.

Impact of writes

To read data from a column that is protected by column-level access control, the useris always required to have read permission through the fine-grained read accesson the policy tags for the column.

This applies to:

• Tables, including wildcard tables
• Views
• Copying tables

To write data to a row for a column that is protected by column-level access control,the user requirement depends on the type of write.

If the write operation is aninsert, fine-grained read access is not required.However, the user doesn't have access to read the data that was inserted, unlessthe user has fine-grained read access.

If a user runs anINSERT SELECT statement, then thefine-grained reader roleis required on the queried table.

If the write operation is anupdate,delete, ormerge, the user can'tperform the operation unless the user has fine-grained read access on theread columns.

A user can load data from local files or from Cloud Storage. When loadingdata to a table, 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. Likewise, a usercan load data from streaming, because streaming loads do not check policy tags.The user doesn't have access to read the data that was loaded from a stream,unless the user has fine-grained read access.

For more information, seeImpact on writes with column-level access control.

Query tables

If a user has dataset access and has the Data CatalogFine-Grained Reader role, the column data is available to the user. The userruns a query as normal.

If a user has dataset access but does not have the Data CatalogFine-Grained Reader role, the column data is not available to the user. If sucha user runsSELECT *, they receive an error which lists the columnsthat the user cannot access. To resolve the error, you can either:

• Modify the query to exclude the columns that the user cannot access. Forexample, if the user does not have access to thessn column, but does haveaccess to the remaining columns, the user can run the following query:

  SELECT*EXCEPT(ssn)FROM...

  In the preceding example, theEXCEPT clause excludes thessn column.

• Ask a Data Catalog Administrator to add the user as aData Catalog Fine-Grained Reader to the relevant data class. Theerror message provides the full name of the policy tag for which the user wouldneed access.

Query views

The impact of column-level security on views is independent of whether or notthe view is an authorized view. In both cases, column-levelsecurity is enforced transparently.

Anauthorized view is one of the following:

• A view that is explicitly authorized to access the tables in a dataset.
• A view that is implicitly authorized to access the tables in a dataset becauseit is contained in an authorized dataset.

For more information,seeAuthorized views andAuthorized datasets.

If the view is not an authorized view:

If the user has IAM access to the view's underlying tables and dataset as well as column-level access to the view's underlying tables, then the user can query the columns in the view. Otherwise, the usercannot query the columns in the view.

If the view is an authorized view:

Only the column-level security on the columns in theview's underlying tables controls the access. Table-level and dataset-level IAMpolicies, if any, are not used to check access. If the user has access to thepolicy tags used in the authorized view's underlying tables, then the user can querythe columns in the authorized view.

The following diagram shows how access to a view is evaluated.

Impact of time travel and materialized views with max_staleness

BigQuery lets you query a table in an earlier state. Thiscapability lets you query the rows from a previous point in time. It alsolets you restore a table from a point in time.

In legacy SQL, you query historical data by usingtime decorators on thetable name. In GoogleSQL, you query historical data by using theFOR SYSTEM_TIME AS OF clause on the table.

Materialized views with themax_staleness option set return historical datafrom within their staleness interval. This behavior is similar to a query usingFOR SYSTEM_TIME AS OF at the time of the view's last refresh, as itallows BigQuery to query records that have been deleted or updated.Suppose that you query a table's historical data at timet. In that case:

• If the schema at timet is identical to, or a subset of, the table's currentschema, then BigQuery checks against the latest column-levelsecurity on the current table. If the user is allowed to read the currentcolumns, then the user can query the historical data of those columns.In order to delete or mask sensitive data of columns that areprotected by column-level security, the column-level security can be safelyrelaxed only afterthe configured time travel windowhas passed since the clean-up of the sensitive data.

• If the schema at timet differs from the current schema for the columns inthe query, the query fails.

Location considerations

When you choose a location for your taxonomy, consider the followinglimitations.

Policy tags

Taxonomies are regional resources, like BigQuery datasets andtables. When you create a taxonomy, you specify the region, orlocation, forthe taxonomy.

You can create a taxonomy and apply policy tags to tables inall regions where BigQuery is available.However, to apply policy tags from a taxonomy to a table column, the taxonomy and the table must exist in the same regional location.

Although you cannot apply a policy tag to a table column that exists in adifferent location, you can copy the taxonomy to another location by explicitlyreplicating it there.

If you want to use the same taxonomy and policy tags across multiple regionallocations, learn more about replicating taxonomies inManaging policy tags across locations.

Organizations

You can't use references across organizations. A table and any policy tags thatyou want to apply to its columns must exist within the same organization.

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.

• BigQuery only supports column-level access control forBigLake tables,BigQuery tables,andBigQuery Omni tables.

• If you overwrite to a destination table, any existing policy tags are removedfrom the table, unless you use the--destination_schema flag to specify aschema with policy tags. The following example shows how to use--destination_schema.

  bqquery--destination_tablemydataset.mytable2\--use_legacy_sql=false--destination_schema=schema.json\'SELECT * FROM mydataset.mytable1'

  Schema changes happen in a separate operation from query execution.If you write query results to a table by specifying the--destination_table flag, and the query subsequently raises an exception,it is possible that any schema changes will be skipped. If this occurs,check the destination table schema andmanually update it if necessary.

• A column can have only one policy tag.

• A table can have at most 1,000 unique policy tags.

• You can't use legacy SQL if you enabled column-level access control. Any legacy SQLqueries are rejected if there are any policy tags on the target tables.

• A policy tag hierarchy can be no more than five levels deep from the root nodeto the lowest-level subtag, as shown in the following screenshot:

  

• Taxonomy names must be unique among all projects within an organization.

• You can't copy a table across regions if you enabled column-level or row-levelaccess control. Any copies of tables across regions are rejected if there areany policy tags on the source tables.

Pricing

Column-level access control requires the use of both BigQuery andData Catalog. For pricing information about these products, see thefollowing topics:

• BigQuery pricing
• Data Catalog pricing

Audit logging

When table data with policy tags is read, we save the referenced policy tags inCloud Logging. However, the policy tag check is not associated with thequery that triggered the check.

Through Cloud Logging, auditors can understand who has what kind of access towhich categories of sensitive data. For more information, seeAuditing policy tags.

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 details about using column-level access control, seeRestricting access with column-level access control.

• For information about best practices for policy tags, seeBigQuery best practices: Using policy tags.