Restrict access with column-level access control

This page explains how to use BigQuery column-level access control to restrict access to BigQuery data at the column level. For general information about column-level access control, see Introduction to BigQuery column-level access control.

The instructions in this page use both BigQuery and Data Catalog.

You need to update the table schema to set a policy tag on a column. You can use the Google Cloud console, the bq command-line tool, and the BigQuery API to set a policy tag on a column. Additionally, you can create a table, specify the schema, and specify policy tags within one operation, using the following techniques:

• The bq command-line tool's bq mk and bq load commands.
• The tables.insert API method.
• The Create table page in the Google Cloud console. If you use the Google Cloud console, you must select Edit as text when you add or edit the schema.

Note: You cannot use the DDL CREATE TABLE statement to specify policy tags.

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

Before you begin

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. Enable the Data Catalog and BigQuery Data Policy APIs.

   Enable the APIs

5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

6. Make sure that billing is enabled for your Google Cloud project.

7. Enable the Data Catalog and BigQuery Data Policy APIs.

   Enable the APIs

8. BigQuery is automatically enabled in new projects, but you might need to activate it in a preexisting project.

   Enable the BigQuery API.

   Enable the API

Roles and permissions

There are several roles related to policy tags for users and service accounts.

• Users or service accounts that administer policy tags are required to have the Data Catalog Policy Tag Admin role. The Policy Tag Admin role can manage taxonomies and policy tags, and can grant or remove IAM roles associated with policy tags.
• Users or service accounts that enforce access control for column-level access control are required to have the BigQuery Admin role or the BigQuery Data Owner role. The BigQuery roles can manage data policies, which are used to enforce access control on a taxonomy.
• To view taxonomies and policy tags for all projects in an organization in the Google Cloud console, users need the Organization Viewer role. Otherwise, the console displays only taxonomies and policy tags associated with the selected project.
• Users or service accounts that query data that is protected by column-level access control must have the Data Catalog Fine-Grained Reader role to access that data.

For more information about all policy tag-related roles, see Roles used with column-level access control.

The Data Catalog Policy Tag Admin role

The Data Catalog Policy Tag Admin role can create and manage data policy tags.

To grant the Policy Tag Admin role, you must have the resourcemanager.projects.setIamPolicy permission on the project for which you want to grant the role. If you don't have the resourcemanager.projects.setIamPolicy permission, ask a Project Owner to either grant you the permission, or perform the following steps for you.

1. In the Google Cloud console, go to the IAM page.

   Open the IAM page

2. If the email address of the user to grant the role is in the list, select the email address and click Edit (the pencil icon). Then click Add another role.

   If the user email address is not in the list, click Add, then enter the email address in the New principals box.

3. Click the Select a role drop-down list.

4. Click Data Catalog, and then click Policy Tag Admin.

5. Click Save.

The BigQuery Data Policy Admin, BigQuery Admin, and BigQuery Data Owner roles

The BigQuery Data Policy Admin, BigQuery Admin and BigQuery Data Owner roles can manage data policies.

To grant either of these roles, you must have the resourcemanager.projects.setIamPolicy permission on the project for which you want to grant the role. If you don't have the resourcemanager.projects.setIamPolicy permission, ask a Project Owner to either grant you the permission, or perform the following steps for you.

1. In the Google Cloud console, go to the IAM page.

   Open the IAM page

2. If the email address of the user to grant the role is in the list, select the email address and click Edit (the pencil icon). Then click Add another role.

   If the user email address is not in the list, click Add, then enter the email address in the New principals box.

3. Click the Select a role drop-down list.

4. Click BigQuery, and then click BigQuery Data Policy Admin or BigQuery Admin or *BigQuery Data Owner.

5. Click Save.

The Organization Viewer role

The Organization Viewer role lets users view details about their organization resource. To grant this role, you must have the resourcemanager.organizations.setIamPolicy permission on the organization.

The Data Catalog Fine-Grained Reader role

Users that need access to the data protected with column-level access control need the Data Catalog Fine-Grained Reader role. This role is assigned to principals as part of configuring a policy tag.

To grant a user the Fine-Grained Reader role on a policy tag, you must have the datacatalog.taxonomies.setIamPolicy permission on the project that contains that policy tag's taxonomy. If you don't have datacatalog.taxonomies.setIamPolicy permission, ask a Project Owner to either grant you the permission, or to perform the action for you.

For instructions, see Set permissions on policy tags.

Set up column-level access control

Set up column-level access control by completing these tasks:

• Create a taxonomy of policy tags.
• Associate principals with the policy tags and grant the principals the Data Catalog Fine-Grained Reader role.
• Associate the policy tags with BigQuery table columns.
• Enforce access control on the taxonomy containing the policy tags.

Create taxonomies

The user or service account that creates a taxonomy must be granted the Data Catalog Policy Tag Admin role.

Console

1. Open the Policy tag taxonomies page in the Google Cloud console.

   Open the Policy tag taxonomies page

2. Click Create taxonomy.

3. On the New taxonomy page:

   1. For Taxonomy name, enter the name of the taxonomy that you want to create.
   2. For Description, enter a description.
   3. If needed, change the project listed under Project.
   4. If needed, change the location listed under Location.
   5. Under Policy Tags, enter a policy tag name and description.
   6. To add a child policy tag for a policy tag, click Add subtag.
   7. To add a new policy tag at the same level as another policy tag, click + Add policy tag.
   8. Continue adding policy tags and child policy tags as needed for your taxonomy.
   9. When you are done creating policy tags for your hierarchy, click Create.

API

To use existing taxonomies, call taxonomies.import in place of the first two steps of the following procedure.

1. Call taxonomies.create to create a taxonomy.
2. Call taxonomies.policytag.create to create a policy tag.

Set permissions on policy tags

The user or service account that creates a taxonomy must be granted the Data Catalog Policy Tag Admin role.

Console

1. Open the Policy tag taxonomies page in the Google Cloud console.

   Open the Policy tag taxonomies page

2. Click the name of the taxonomy that contains the relevant policy tags.

3. Select one or more policy tags.

4. If the Info panel is hidden, click Show info panel.

5. In the Info panel, you can see the roles and principals for the selected policy tags. Add the Policy Tags Admin role to accounts that create and manage policy tags. Add the Fine-Grained Reader role to accounts that are intended to have access to the data protected by column-level access control. You also use this panel to remove roles from accounts or modify other permissions.

6. Click Save.

API

Call taxonomies.policytag.setIamPolicy to grant access to a policy tag by assigning principals to appropriate roles.

Set policy tags on columns

The user or service account that sets a policy tag needs the datacatalog.taxonomies.get and bigquery.tables.setCategory permissions. datacatalog.taxonomies.get is included in the Data Catalog Policy Tags Admin and Project Viewer roles. bigquery.tables.setCategory is included in the BigQuery Admin (roles/bigquery.admin) and BigQuery Data Owner (roles/bigquery.dataOwner) roles.

To view taxonomies and policy tags across all projects in an organization in Google Cloud console, users need the resourcemanager.organizations.get permission, which is included in the Organization Viewer role.

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

Console

Set the policy tag by modifying a schema using the Google Cloud console.

1. Open the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. In the BigQuery Explorer, locate and select the table that you want to update. The table schema for that table opens.

3. Click Edit Schema.

4. In the Current schema screen, select the target column and click Add policy tag.

5. In the Add a policy tag screen, locate and select the policy tag that you want to apply to the column.

6. Click Select. Your screen should look similar to the following:

   

7. Click Save.

bq

1. Write the schema to a local file.

   bq show --schema --format=prettyjson \
      project-id:dataset.table > schema.json
   

   where:

   • project-id is your project ID.
   • dataset is the name of the dataset that contains the table you're updating.
   • table is the name of the table you're updating.

2. Modify schema.json to set a policy tag on a column. For the value of the names field of policyTags, use the policy tag resource name.

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

3. Update the schema.

   bq update \
      project-id:dataset.table schema.json
   

API

For existing tables, call tables.patch, or for new tables call tables.insert. Use the schema property of the Table object that you pass in to set a policy tag in your schema definition. See the command-line example schema to see how to set a policy tag.

When working with an existing table, the tables.patch method is preferred, because the tables.update method replaces the entire table resource.

Other ways to set policy tags on columns

You can also set policy tags when you:

• Use bq mk to create a table. Pass in a schema to use for creation of the table.
• Use bq load to load data to a table. Pass in a schema to use when you load the table.

For general schema information, see Specifying a schema.

Enforce access control

Use these instructions to turn enforcement of access control on or off.

Enforcing access control requires a data policy to be created. This is done for you if you enforce access control by using the Google Cloud console. If you want to enforce access control by using the BigQuery Data Policy API, you must explicitly create the data policy.

The user or service account that enforces access control must have either the BigQuery Admin role or the BigQuery Data Owner role. They must also have either the Data Catalog Admin role or the Data Catalog Viewer role.

Console

To enforce access control, follow these steps:

1. Open the Policy tag taxonomies page in the Google Cloud console.

   Open the Policy tag taxonomies page

2. Click the taxonomy whose column-level access control you want to enforce.

3. If Enforce access control is not already on, click Enforce access control to enable it.

To stop enforcement of access control if it is on, click Enforce access control to toggle the control.

If you have data policies associated with any of the policy tags in the taxonomy, you must delete all of the data policies in the taxonomy before you stop enforcement of access control. If you delete the data policies by using the BigQuery Data Policy API, you must delete all data policies with a dataPolicyType of DATA_MASKING_POLICY. For more information, see Delete data policies.

API

To enforce access control, call create and pass in a DataPolicy resource where the dataPolicyType field is set to COLUMN_LEVEL_SECURITY_POLICY.

To stop enforcement of access control if it is on, delete the data policy associated with the taxonomy. You can do this by calling the delete method for that data policy.

If you have data policies associated with any of the policy tags in the taxonomy, you can't stop enforcement of access control without first deleting all of the data policies in the taxonomy. For more information, see Delete data policies.

Work with policy tags

Use this section to learn to how to view, modify, and delete policy tags.

View policy tags

To view the policy tags that you created for a taxonomy:

1. Open the Policy tag taxonomies page in the Google Cloud console.

   Open the Policy tag taxonomies page

2. Click the taxonomy whose policy tags you want to view. The Taxonomies page shows the policy tags in the taxonomy.

View policy tags in schema

You can view policy tags applied to a table when you examine the table schema. You can see the schema using the Google Cloud console, the bq command-line tool, the BigQuery API, and the client libraries. For details on how to view the schema, see Getting table information.

View permissions on policy tags

1. Open the Policy tag taxonomies page in the Google Cloud console.

   Open the Policy tag taxonomies page

2. Click the name of the taxonomy that contains the relevant policy tags.

3. Select one or more policy tags.

4. If the Info panel is hidden, click Show info panel.

5. In the Info panel, you can see the roles and principals for the selected policy tags.

Update permissions on policy tags

The user or service account that creates a taxonomy must be granted the Data Catalog Policy Tag Admin role.

Console

1. Open the Policy tag taxonomies page in the Google Cloud console.

   Open the Policy tag taxonomies page

2. Click the name of the taxonomy that contains the relevant policy tags.

3. Select one or more policy tags.

4. If the Info panel is hidden, click Show info panel.

5. In the Info panel, you can see the roles and principals for the selected policy tags. Add the Policy Tags Admin role to accounts that create and manage policy tags. Add the Fine-Grained Reader role to accounts that are intended to have access to the data protected by column-level access control. You also use this panel to remove roles from accounts or modify other permissions.

6. Click Save.

API

Call taxonomies.policytag.setIamPolicy to grant access to a policy tag by assigning principals to appropriate roles.

Retrieve policy tag resource names

You need the policy tag resource name when you apply the policy tag to a column.

To retrieve the policy tag resource name:

1. View the policy tags for the taxonomy that contains the policy tag.

2. Find the policy tag whose resource name you want to copy.

3. Click the Copy policy tag resource name icon.

   

Clear policy tags

Update the table schema to clear a policy tag from a column. You can use the Google Cloud console, the bq command-line tool, and the BigQuery API method to clear a policy tag from a column.

Console

In the Current schema page, under Policy tags, click X.

bq

Note: To clear a policy tag, you must explicitly set the names field of policyTags to an empty list, []. If you delete the policyTags field, it has no effect on existing policy tags. This is by design, to prevent accidental removal of policy tags that would expose sensitive data.

1. Retrieve the schema and save it to a local file.

   bq show --schema --format=prettyjson \
      project-id:dataset.table > schema.json
   

   where:

   • project-id is your project ID.
   • dataset is the name of the dataset that contains the table you're updating.
   • table is the name of the table you're updating.

2. Modify schema.json to clear a policy tag from a column.

   [
    ...
    {
      "name": "ssn",
      "type": "STRING",
      "mode": "REQUIRED",
      "policyTags": {
        "names": []
      }
    },
    ...
   ]
   

3. Update the schema.

   bq update \
      project-id:dataset.table schema.json
   

API

Note: To clear a policy tag, you must explicitly set the names field of policyTags to an empty list, []. If you delete the policyTags field, it has no effect on existing policy tags. This is by design, to prevent accidental removal of policy tags that would expose sensitive data.

Call tables.patch and use the schema property to clear a policy tag in your schema definition. See the command-line example schema to see how to clear a policy tag.

Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

Delete policy tags

You can delete one or more policy tags in a taxonomy, or you can delete the taxonomy and all of the policy tags it contains. Deleting a policy tag automatically removes the association between the policy tag and any columns it was applied to.

When you delete a policy tag that has a data policy associated with it, it can take up to 30 minutes for the data policy to also be deleted. You can delete the data policy directly if you want it to be deleted immediately.

To delete one or more policy tags in a taxonomy, follow these steps:

1. Open the Policy tag taxonomies page in the Google Cloud console.

   Open the Policy tag taxonomies page

2. Click the name of the taxonomy containing the tags to delete.
3. Click Edit.
4. Click next to the policy tags to delete.
5. Click Save.
6. Click Confirm.

To delete an entire taxonomy, follow these steps:

1. Open the Policy tag taxonomies page in the Google Cloud console.

   Open the Policy tag taxonomies page

2. Click the name of the taxonomy containing the tags to delete.
3. Click Delete Policy Tag Taxonomy.
4. Type the taxonomy name and then click Delete.

Query data with column-level access control

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

If a user has dataset access but does not have the Data Catalog Fine-Grained Reader role, the column data is not available to the user. If such a user runs SELECT *, they receive an error which lists the columns that the user cannot access. To resolve the error, you can either:

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

  SELECT * EXCEPT (ssn) FROM ...
  

  In the preceding example, the EXCEPT clause excludes the ssn column.

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

FAQs

Does BigQuery column-level sec work for views?

Yes. Views are derived from an underlying table. The same column-level access control on the table applies when the protected columns are accessed through a view.

There are two kinds of views in BigQuery, logical views and authorized views. Both types of views are derived from a source table, and both are consistent with the table's column-level access control.

Does column-level access control work on STRUCT or RECORD columns?

Yes. You can apply policy tags to the leaf fields only, and only those fields are protected.

Can I use both legacy SQL and GoogleSQL?

You can use GoogleSQL to query tables that are protected by column-level access control.

Any legacy SQL queries are rejected if there are any policy tags on the target tables.

Are queries logged in Cloud Logging?

The policy tags check is logged in Logging. For more information, see Audit logging for column-level access control.

Is copying a table impacted by column-level access control?

Yes. You cannot copy columns if you don't have access to them.

The following operations verify column-level permissions.

• SELECT queries with destination tables
• Table copy jobs
• Data export jobs (for example, to Cloud Storage)

When I copy data to a new table, are policy tags automatically propagated?

In most cases, no. If you copy the results of a query into a new table, the new table doesn't automatically have policy tags assigned. So the new table doesn't have column-level access control. The same is true if you export data to Cloud Storage.

The exception is if you use a table copy job. Because table copy jobs don't apply any data transformation, policy tags are automatically propagated to the target tables. This exception doesn't apply to cross-region table copy jobs, because cross-region table copy jobs don't support copying policy tags.

Is column-level access control compatible with Virtual Private Cloud?

Yes, column-level access control and VPC are compatible and complementary.

VPC leverages IAM to control access to services, such as BigQuery and Cloud Storage. Column-level access control provides granular security of individual columns within BigQuery itself.

To enforce VPC for policy tags and data policies for column-level access control and dynamic data masking, you must restrict the following APIs in the perimeter:

• Data Catalog API
• BigQuery API
• BigQuery Data Policy API

Troubleshoot

I cannot see the Data Catalog roles

If you cannot see roles such as Data Catalog Fine-Grained Reader, it is possible that you have not enabled the Data Catalog API in your project. To learn how to enable the Data Catalog API, see Before you begin. The Data Catalog roles should appear several minutes after you enable the Data Catalog API.

I cannot view the Taxonomies page

You need additional permissions in order to view the Taxonomies page. For example, the Data Catalog Policy Tags Admin role has access to the Taxonomies page.

I enforced policy tags, but it doesn't seem to work

If you are still receiving query results for an account that shouldn't have access, it is possible the account is receiving cached results. Specifically, if you previously ran the query successfully and then you enforced policy tags, you could be getting results from the query result cache. By default, query results are cached for 24 hours. The query should fail immediately if you disable the result cache. For more details about caching, see Impact of column-level access control.

In general, IAM updates take about 30 seconds to propagate. Changes in the policy tag hierarchy can take up to 30 minutes to propagate.

I don't have the permission to read from a table with column-level security

You need either the Fine-Grained Reader role or the Masked Reader role at different levels, such as organization, folder, project, and policy tag. The Fine-Grained Reader role grants raw data access, while the Masked Reader role grants access to masked data. You can use the IAM Troubleshooter to check this permission at the project level.