How to Configure BigQuery Column Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up bigquery column level security by creating a taxonomy with policy tags. Next, you attach those tags to table columns and grant read roles to specific groups. Most data teams start by giving broad table roles to analysts and engineers. Broad roles expose every field in a table to anyone who runs a query. If a table holds card numbers or emails, broad permissions create an instant risk. How bigquery column level security works in practice The native setup relies on a hierarchy of dat

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up bigquery column level security by creating a taxonomy with policy tags. Next, you attach those tags to table columns and grant read roles to specific groups. Most data teams start by giving broad table roles to analysts and engineers. Broad roles expose every field in a table to anyone who runs a query. If a table holds card numbers or emails, broad permissions create an instant risk.

How bigquery column level security works in practice

The native setup relies on a hierarchy of data tags inside your cloud project. First, a security admin defines a taxonomy that groups sensitive data by risk level. A common setup splits tags into high, medium, and low tiers. High tier tags might cover national IDs and card numbers. Medium tier tags often cover emails and phone numbers.

Once the taxonomy exists, the admin assigns one policy tag to each sensitive column. A single column can hold only one policy tag at a time. When the tag is in place, the database blocks read queries on that column by default. Users who could read the full table before will now see permission errors.

To restore read permissions, the admin grants a fine-grained reader role on the policy tag. Only users or groups with that role can query the tagged column. You can also attach a masking rule to the policy tag for specific groups. Masking rules rewrite matched fields in results, such as replacing emails or phone numbers with hashes.

Where native policy tags fall short during live work

Native policy tags work well for static analytical datasets with fixed schemas. They help teams separate public columns from sensitive ones inside the same table. You do not need to maintain duplicate views just to hide a few columns.

Still, policy tags only check who has a role on a declared schema column. They do not inspect what individual queries do in flight once a user logs in. If an engineer needs temporary production permissions to fix a bug, static roles become hard to manage. Admins must grant the tag role, wait for the fix, and remember to revoke it later.

Column-Level Encryption + BigQuery IAM: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Policy tags also require manual maintenance across every dataset and region. When a pipeline adds a new column with phone numbers, someone must tag it right away. Until that tag is attached, any user with table permissions can read the raw values. Native tags also miss sensitive strings stored inside unstructured text or JSON columns. Because a column holds one tag, you cannot mask emails while leaving other fields inside a JSON blob untouched.

Managing schema changes and ad hoc queries

Fast-moving teams change database schemas every day as apps grow. Relying only on manual catalog tags means security always lags behind engineering deploys. A developer might push a new table to staging or production during a busy release. If the tagging workflow fails or runs late, sensitive card numbers sit open to broad roles.

Fixing also breaks down when permissions live purely in static cloud roles. Support engineers often run ad hoc queries to check a broken customer record. Giving them permanent read roles on high tier tags exposes every row in the table by default. You need a way to control live queries and mask matched fields without changing cloud roles every hour.

Govern statements and responses across databases and APIs

Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev sits inline in the protocol beside each supported service as an open-source proxy, a sidecar for your database or API. For every query or command an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human approval.

First, hoop.dev guardrails block banned queries before they run or hold risky ones for review, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

What happens when a user queries a column without policy tag permissions?

The database rejects the entire query and returns an access denied error. The user must remove the restricted column from their select statement to run the query. If a masking rule applies to their group, the query runs and returns masked values instead.

Can a single column have more than one policy tag in BigQuery?

No, each column can have only one policy tag attached to it at any time. Because tags sit in a tree structure, child tags inherit rules from parent tags. You must design your tag tree carefully before applying tags across large tables.

Do policy tags protect sensitive data inside JSON or free-text columns?

Policy tags apply to the whole column rather than specific patterns inside the text. If you restrict or mask a JSON column, the rule blocks or alters the entire field. Native tags do not scan query results in flight to rewrite individual emails or card numbers inside text.

The query runs. The emails stay hidden.