Row-Level Security, Column Masking, and Policy Tags in BigQuery

Most BigQuery access control conversations stop at IAM: grant roles/bigquery.dataViewer on a dataset and move on. That works until an auditor, a privacy review, or a multi-tenant customer asks the harder question — can an analyst in the EU team see US customer rows, and can the support team query the table without seeing raw email addresses? IAM alone cannot answer that. BigQuery's three fine-grained controls can: policy tags for column-level access, masking rules for dynamic redaction, and row-level access policies for row filtering.

This tutorial builds all three on one table, shows how they interact, and covers the gotchas that bite in production.

The scenario

A customers table serving three audiences:

CREATE OR REPLACE TABLE `demo.customers` (
  customer_id   STRING,
  full_name     STRING,
  email         STRING,
  phone         STRING,
  region        STRING,   -- 'EU' or 'US'
  lifetime_value NUMERIC,
  created_at    TIMESTAMP
);

The requirements:

• Analysts see all rows but never raw email or phone.
• The EU analytics group sees EU rows only; the US group sees US rows only.
• A small data-protection group sees everything unmasked.

Step 1: Build a taxonomy and policy tags

Column-level security in BigQuery is driven by a taxonomy — a hierarchy of policy tags managed in Dataplex Universal Catalog (formerly Data Catalog). Create it in the same region as your data, because taxonomies are regional and a tag cannot be applied across regions.

gcloud data-catalog taxonomies create \
  --location=us \
  --display-name="PII Sensitivity" \
  --activated-policy-types=FINE_GRAINED_ACCESS_CONTROL

# note the taxonomy ID it returns, then:
gcloud data-catalog taxonomies policy-tags create \
  --location=us --taxonomy=TAXONOMY_ID \
  --display-name="High"

A practical taxonomy is small: High (direct identifiers), Medium (quasi-identifiers), Low (business data). Resist modelling every regulation as its own tag — you will be maintaining the sprawl for years.

Grant the data-protection group the Fine-Grained Reader role on the High tag; anyone without that role is denied the tagged columns, even if they have dataViewer on the table.

Step 2: Attach tags to columns

Tags live in the table schema, so they can be applied in DDL:

ALTER TABLE `demo.customers`
ALTER COLUMN email
SET OPTIONS (
  policy_tags = STRUCT(['projects/PROJECT/locations/us/taxonomies/TAXONOMY_ID/policyTags/TAG_ID'] AS names)
);

Now SELECT * FROM demo.customers fails for an ordinary analyst with an access-denied error naming the column. That is the first surprise for users: column-level security does not silently drop columns, it rejects the query. Analysts must select explicit column lists, or you give them a view. This is exactly why masking exists.

Step 3: Add a masking rule so SELECT * still works

A data policy attaches a masking rule to a policy tag and a principal. Members of the masked-reader role get a transformed value instead of a denial:

gcloud bigquery datapolicies create mask_email \
  --location=us \
  --data-policy-type=DATA_MASKING_POLICY \
  --policy-tag="projects/PROJECT/locations/us/taxonomies/TAXONOMY_ID/policyTags/TAG_ID" \
  --masking-expression="SHA256"

Then grant roles/bigquerydatapolicy.maskedReader on that data policy to the analyst group.

Built-in masking routines include SHA256, ALWAYS_NULL, DEFAULT_MASKING_VALUE (type-appropriate default: empty string, 0, epoch), EMAIL_MASK (XXXXX@example.com), FIRST_FOUR_CHARACTERS, LAST_FOUR_CHARACTERS and DATE_YEAR_MASK. You can also point a policy at a custom SQL UDF for domain-specific redaction.

Precedence matters and is easy to get wrong:

1. Fine-Grained Reader on the tag → sees the raw value.
2. Masked Reader only → sees the masked value.
3. Neither → the query is rejected.

SHA256 masking is useful because joins still work: two masked columns hash consistently, so analysts can join or count distinct users without ever seeing the identifier.

Step 4: Row-level access policies

Row filtering is pure DDL and needs no taxonomy:

CREATE OR REPLACE ROW ACCESS POLICY eu_analysts
ON `demo.customers`
GRANT TO ('group:eu-analytics@example.com')
FILTER USING (region = 'EU');

CREATE OR REPLACE ROW ACCESS POLICY us_analysts
ON `demo.customers`
GRANT TO ('group:us-analytics@example.com')
FILTER USING (region = 'US');

Key semantics:

• Policies are additive. A user in both groups sees the union of both filters, never an intersection.
• A user matching no policy sees zero rows once any policy exists on the table — this is the single most common support ticket after rollout. Add an explicit all-access policy for admins: GRANT TO ('group:data-platform@example.com') FILTER USING (TRUE).
• SESSION_USER() enables data-driven entitlements without one policy per team:

CREATE OR REPLACE ROW ACCESS POLICY by_entitlement
ON `demo.customers`
GRANT TO ('group:all-analysts@example.com')
FILTER USING (
  region IN (
    SELECT region FROM `demo.user_region_entitlements`
    WHERE user_email = SESSION_USER()
  )
);

Keep the lookup table small and clustered on user_email; it is read on every query against the protected table.

Step 5: Verify, then audit

Never trust a policy you have not tested from the other side. Use impersonation rather than asking a colleague to try it:

gcloud auth print-access-token \
  --impersonate-service-account=analyst-test@PROJECT.iam.gserviceaccount.com

Run the same three queries as each test principal: SELECT *, a filtered count by region, and a join that touches a masked column.

Then inventory what exists, because policies drift:

-- Every row access policy in a dataset
SELECT table_name, row_access_policy_name, filter_predicate, grantees
FROM `demo`.INFORMATION_SCHEMA.ROW_ACCESS_POLICIES;

-- Every tagged column in the project
SELECT table_name, field_path, policy_tags
FROM `region-us`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE policy_tags IS NOT NULL;

Pair that with Cloud Audit Logs: protoPayload.metadata.jobChange records the principal, the tables touched and whether the job was denied, which is what an auditor actually asks for.

Caveats worth knowing before you roll out

• Copy, export and streaming reads. Table copy jobs, extract jobs and the Storage Read API do not honour row-level policies the way queries do — BigQuery blocks those operations on protected tables for users without the right permissions rather than silently exporting everything. Plan your export pipelines around a service account with an explicit FILTER USING (TRUE) policy.
• Materialized views and BI Engine. A materialized view over a table with row-level policies is restricted; BI Engine acceleration is bypassed for queries subject to row-level security, so expect slower dashboards.
• Cached results. Results are cached per user, so one analyst's cached rows are never served to another. Cache hit rates drop, however, which quietly raises on-demand cost.
• Wildcard and SELECT * behaviour. Column-level denial breaks SELECT *, dbt star() macros and BI tools that introspect all columns. Masking, not raw tagging, is what keeps those tools working.
• Views do not launder policies. Row-level policies apply to the underlying table even through a logical view, unless you use an authorized view with a service-account-style bypass — which is precisely the pattern you should be replacing.
• Region lock-in. Taxonomies are regional; a multi-region estate needs one taxonomy per region and a process to keep them in sync.

Which control for which problem

Requirement	Use
Hide a whole column from a group	Policy tag, no masked-reader grant
Show a usable but non-identifying value	Masking rule (SHA256, EMAIL_MASK)
Restrict which rows a group sees	Row access policy
Per-user entitlements from a table	Row access policy with SESSION_USER()
Share aggregates with a partner	Authorized view or authorized dataset
Cross-organisation sharing	Analytics Hub listing over a curated view

Start with one sensitive table, one taxonomy of three tags, and one row policy per region. Rolling fine-grained access out across an estate is mostly organisational work — deciding who owns the taxonomy and how entitlements are requested — and only lightly technical.

If you need a governed BigQuery estate that survives an audit, our data governance and compliance service covers taxonomy design, policy rollout and audit reporting. Get in touch with the shape of your data and we will sketch the controls.