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'sbq mk
andbq 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.
Before you begin
-
Sign in to your Google Account.
If you don't already have one, sign up for a new account.
-
In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.
- Enable the Data Catalog API.
- BigQuery is automatically enabled in new projects. To activate BigQuery in a preexisting project, go to Enable the BigQuery 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.
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.
In the Google Cloud Console, go to the IAM page.
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.
In the Edit permissions page, click Add another role.
Click Data Catalog, and then click Policy Tags Admin.
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.
Open the IAM page in the Cloud Console.
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.
In the Edit permissions page, click Add another role.
Click Data Catalog, and then click Fine Grained Reader.
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.
Open the Data Catalog Taxonomies page in the Cloud Console.
Click Create taxonomy.
On the New taxonomy page:
- For Taxonomy name, enter the name of the taxonomy that you want to create.
- For Description, enter a description.
- If needed, change the project listed under Project.
- If needed, change the location listed under Location.
- Under Policy Tags, enter a policy tag name and description.
- To add a child policy tag for a policy tag, click Add child policy tag.
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.Continue adding policy tags and child policy tags as needed for your taxonomy.
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:
Open the Data Catalog Taxonomies page in the Cloud Console.
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:
Open the Data Catalog Taxonomies page in the Cloud Console.
Click the taxonomy with the policy tags that you want to assign permission.
Click the policy tags that you want to assign permission.
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:
Open the Data Catalog Taxonomies page in the Cloud Console.
Click the taxonomy with the policy tags that you want to update permission.
Click the policy tags that you want to update permission.
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.
When you are done, click Save.
Search for policy tags
To search for policy tags in Data Catalog, follow these steps:
- Open the Data Catalog home page and perform a search for data assets from the Search field.
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:
View the policy tags for the taxonomy that contains the policy tag.
Find the policy tag whose resource name you want to copy.
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.
Console
To modify a schema using the Cloud Console, set the policy tag when you edit the schema.
- In the Current schema screen, select your column and click Add policy tag.
- In the Add a policy tag screen, select the policy tag that you want to apply to the column.
Click Select. Your screen should look similar to the following:
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.
- For Schema, click Edit as text.
Modify the schema to set a policy tag on a column. For the value of the
names
field ofpolicyTags
, use the policy tag resource name.
bq
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.
Modify schema.json to set a policy tag on a column. For the value of the
names
field ofpolicyTags
, 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",] } }, ... ]
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 thessn
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
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.
Modify schema.json to clear a policy tag from a column.
[ ... { "name": "ssn", "type": "STRING", "mode": "REQUIRED", "policyTags": { "names": [] } }, ... ]
Update the schema.
bq update \ project-id:dataset.table schema.json
API
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:
Open the Data Catalog Taxonomies page in the Cloud Console.
Click the taxonomy whose column-level security you want to enforce.
If Enforce access control is not already on, click Enforce access control.
Turn off access control
Open the Data Catalog Taxonomies page in the Cloud Console.
Click the taxonomy whose column-level security you want to turn off.
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.