Restricting access with BigQuery column-level security

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

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

You update the table schema to set a policy tag on a column. You can use the 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 Cloud Console. If you use the 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.

Before you begin

1. Sign in to your Google Account.

   If you don't already have one, sign up for a new account.

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 the project selector page

3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

4. Enable the Data Catalog API.

   Enable the API

5. BigQuery is automatically enabled in new projects. To activate BigQuery in a preexisting project, go to Enable the BigQuery API.

   Enable the API

Roles and permissions

There are several roles related to policy tags for users and service accounts. On this page, the roles discussed are the Data Catalog Policy Tags Admin and the Data Catalog Fine Grained Reader role.

• Users or service accounts that administer policy tags are required to have the Data Catalog Policy Tags Admin role. This role can manage taxonomies and policy tags, and can grant or remove ACL policies.

  Key Point: The Data Catalog Policy Tags Admin role is not the same role as Data Catalog Admin. The Data Catalog Policy Tags Admin role is narrowly focused on managing policy tags, and does not include all the permissions that are included in the Data Catalog Admin role.

• Users or service accounts that query data that is protected by column-level security must have the Data Catalog Fine Grained Reader role.

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

The Policy Tags Admin role

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

To grant the Data Catalog Policy Tags Admin role, you must have resourcemanager.projects.setIamPolicy permissions on the project for which you will grant the Policy Tags Admin. If you do not have 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). If the user email address is not in the list, first add the user to your project before proceeding to the next step.

3. In the Edit permissions page, click Add another role.

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

5. Click Save.

For more information about this role, see Roles used with column-level security.

The Fine Grained Reader role

Users that need access to the data protected with column-level security need the Data Catalog Fine Grained Reader role. This role is usually granted as part of setting a policy tag on a column.

Before you can perform the following steps, you must have resourcemanager.projects.setIamPolicy permissions on the project for which you will grant the Fine Grained Reader role. If you do not have resourcemanager.projects.setIamPolicy permission, ask a Project Owner to either grant you the permission, or to perform the following steps for you.

1. Open the IAM page in the Cloud Console.

   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). If the user email address is not in the list, first add the user to your project before proceeding to the next step.

3. In the Edit permissions page, click Add another role.

4. Click Data Catalog, and then click Fine Grained Reader.

5. Click Save.

For more information about this role, see Roles used with column-level security.

Create a taxonomy

You use Data Catalog to create a taxonomy and add policy tags for your data.

The user account for the following steps is required to have the Data Catalog Policy Tags Admin role.

1. Open the Data Catalog Taxonomies page in the Cloud Console.

   Open the 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 child policy tag.

   7. To add a new policy tag at the same level as another policy tag, click the + icon.
      
      The following shows the New taxonomy page for an example taxonomy.

      

   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 Save.

View policy tags

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

1. Open the Data Catalog Taxonomies page in the Cloud Console.

   Open the Taxonomies page

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

   

View permissions on policy tags

To view permission on a policy tag:

1. Open the Data Catalog Taxonomies page in the Cloud Console.

   Open the Taxonomies page

2. Click the taxonomy with the policy tags that you want to assign permission.

3. Click the policy tags that you want to assign permission.

   

4. In the right panel, you can see the members and permissions for the selected policy tags.

   

Update permissions on policy tags

To update permission to a policy tag:

1. Open the Data Catalog Taxonomies page in the Cloud Console.

   Open the Taxonomies page

2. Click the taxonomy with the policy tags that you want to update permission.

3. Click the policy tags that you want to update permission.

   

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

5. When you are done, click Save.

Search for policy tags

To search for policy tags in Data Catalog, follow these steps:

1. Open the Data Catalog home page and perform a search for data assets from the Search field.

   Open the Data Catalog home page

For more information about finding assets like policy tags in Data Catalog, see How to search for data assets.

Retrieve a policy tag resource name

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.

   

Set a policy tag on a column

The user or service account that sets a policy tag needs the datacatalog.taxonomies.get permission. This permission is included in the Policy Tags Admin role and the Project Viewer role.

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

Console

To modify a schema using the Cloud Console, set the policy tag when you edit the schema.

1. In the Current schema screen, select your column and click Add policy tag.
2. In the Add a policy tag screen, select the policy tag that you want to apply to the column.

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

   

4. Click Save.

Alternatively, when you create a table or modify a schema using the Cloud Console, set the policy tag when you edit the schema as text.

1. For Schema, click Edit as text.

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

   

bq

1. Retrieve 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

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

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

Other ways to set a policy tag on a column

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.

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 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.

Query data with column-level security

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 *, the user will receive an error which lists the column(s) that the user cannot access. To resolve the error, you can either:

• Modify the query to exclude the column(s) 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 example above, 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.

Clear a policy tag

Update the table schema to clear a policy tag from an column. You can use the 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.

Enforce access control

To enforce access control:

1. Open the Data Catalog Taxonomies page in the Cloud Console.

   Open the Taxonomies page

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

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

Turn off access control

1. Open the Data Catalog Taxonomies page in the Cloud Console.

   Open the Taxonomies page

2. Click the taxonomy whose column-level security you want to turn off.

3. If Enforce access control is on, click Enforce access control to turn it off.

FAQs

Does BigQuery column-level security work for views?

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

Note that 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 security.

Does column-level security work on STRUCT or RECORD columns?

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

Can I use both legacy SQL and standard SQL?

You can use standard SQL to query tables that are protected by column-level security.

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 security.

Is copying a table impacted by column-level security?

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 won't have column-level security. 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 do not apply any data transformation, policy tags are automatically propagated to the target tables.

Is column-level security compatible with Virtual Private Cloud?

Yes, column-level security and VPC are compatible and complementary.

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

Troubleshooting

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 security.

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