Column-level security and data masking in BigQuery

How BigQuery policy tags restrict a column to some users, what a query on a restricted column does, how data masking rules return hashed, nulled or truncated values instead, and what we measured on tagged tables.

Column-level security in BigQuery means a column carries a policy tag, and only principals who hold the Fine-Grained Reader role on that tag can read the column. Everyone else gets an Access Denied error as soon as a query touches the column. Data masking adds a second option on top: for chosen principals the column is readable, but BigQuery returns a hashed, nulled or truncated value instead of the real one.

Scope note: we could not create taxonomies, policy tags or data policies in our test project. The setup steps and the masking rules below are Google’s documented behavior. What a query does when it meets a protected column, we tested by putting a policy tag on a column that nobody can read. The errors and the rules for which statements are denied match what Google describes. Each section says which is which.

How the pieces fit

Google’s setup has four steps:

1. Create a taxonomy (a tree of policy tags, for example High and Medium, with leaf tags like employee_ssn below them). A taxonomy belongs to one region and can be five levels deep.
2. Put a policy tag on each sensitive column, through the table schema (policyTags.names in a bq update schema file, or the console).
3. Turn on access control for the taxonomy.
4. Grant datacatalog.categoryFineGrainedReader on a tag to the people who may read the columns under it.

A column has at most one policy tag, a table at most 1,000 distinct tags, and the table and taxonomy must be in the same region and the same organization. Legacy SQL is rejected on any table with policy tags. Creating taxonomies needs the Data Catalog Policy Tag Admin role. Attaching a tag needs bigquery.tables.setCategory, which bigquery.dataOwner and bigquery.admin have.

In the schema, the tag is part of the column:

{
  "name": "ssn",
  "type": "STRING",
  "policyTags": { "names": ["projects/p/locations/us/taxonomies/123/policyTags/456"] }
}

BigQuery accepted that schema for a table whose taxonomy did not exist: bq mk --table succeeded, and INFORMATION_SCHEMA.COLUMN_FIELD_PATHS listed the tag in its policy_tags array for the column. The tag is checked later, at read time. (Copying such a table failed with Not found: policy tag ... taxonomy ... not found, so the existence check happens on copy and clone.) To find every tagged column in a dataset, filter COLUMN_FIELD_PATHS on ARRAY_LENGTH(policy_tags) > 0.

What a query does with a protected column

We queried a table with id and a tagged ssn column, as a user without access to the tag. Results:

The error names the tag and the column:

Access Denied: BigQuery BigQuery: User does not have permission to access policy tag
projects/.../taxonomies/123/policyTags/456. on column dataset.t.ssn.

Any use of the column counts, not only the select list: a filter, a sort, a function argument and COUNT(column) are all denied. A query that never reads the column’s data is fine, and COUNT(*) doesn’t read it. The two zero-row cases show the same thing from the other side: a scan that can’t return rows isn’t denied. Don’t count on that for security, it only means there was nothing to read.

SELECT * is the common surprise. Adding a tag to a column breaks every SELECT * over the table for the people who can’t read it, and Google’s fix is SELECT * EXCEPT (ssn). Because a dry run fails the same way, tools find out before the query runs. A view that selects the column behaves the same for its readers: SELECT * from our test view failed, SELECT * EXCEPT (ssn) on it worked, and SELECT id on it worked.

The owner of the table is no exception. We created the table, and we were denied. The tag decides, not who owns the table.

Authorized views don’t bypass it. Google says column-level security on the underlying tables is the only check that applies to an authorized view, and that table-level and dataset-level IAM are not used in that case. See authorized views and access control.

Writes are different

Reading is blocked, writing mostly isn’t. On the tagged table we ran:

The rule is that a statement is denied when it reads the protected column, in a WHERE, in the new value, or in a source SELECT. A statement that only writes the column isn’t. That matches Google’s text: inserts need no fine-grained access, and updates, deletes and merges need it on the columns they read. Loads and streaming inserts check nothing, and the user can’t read what they loaded. It means someone who can’t read a column can still overwrite it, which is worth knowing when you rely on tags to stop edits. Tags stop reads, and nothing here stops writes.

Dropping the tagged column also worked for us without any access to the tag.

What removes a tag

Google lists the operations that silently drop policy tags: CREATE OR REPLACE TABLE, WRITE_TRUNCATE loads and query destinations, deleting or expiring the table, and copying a table without tags over one with tags. TRUNCATE TABLE keeps them.

We checked two of those against INFORMATION_SCHEMA.COLUMN_FIELD_PATHS: after TRUNCATE TABLE the ssn column still had one tag, and after CREATE OR REPLACE TABLE t (id INT64, ssn STRING) it had none, and SELECT * worked again. Anyone who can replace a table can strip its protection. CREATE TABLE t2 LIKE t kept the tags on the new table, so use that rather than a hand-written column list when you want the copy to stay protected.

Time travel and views

Google says that for a historical query the current policy tags apply when the old schema is the same as or a subset of the current one; if the column’s schema differs, the query fails. We ran FOR SYSTEM_TIME AS OF with id only (rows) and with ssn (denied). Before relaxing a tag on data you want to be sure nobody can still read, wait out the table’s time travel window.

Data masking

Masking is a data policy attached to a policy tag (or directly to a column). A data policy has one masking rule and a list of principals. A principal with the Masked Reader role (roles/bigquerydatapolicy.maskedReader) and no Fine-Grained Reader role sees the masked value, and the query doesn’t fail. A principal with Fine-Grained Reader sees the real value. A principal with neither is denied. Existing queries keep working, since the column is still there, just rewritten.

Google lists these rules:

Google says masking takes precedence over the other operations in a query. That means a filter such as WHERE email LIKE '%@corp.com' works on the masked value, and can return different rows for a masked reader than for a fine-grained reader.

The hash fallbacks have a documented output we can check. Google’s examples show randomtext masked by the email rule as jQHDyQuj7vJcveEe59ygb3Zcvj0B5FJINBzgM6Bypgw=. That is the base64 of the SHA-256 digest, and the same function is available to everyone:

-- Google documents the masked output for these inputs. A value that is not a valid email goes through SHA-256 and comes back as base64.
SELECT
  value,
  TO_BASE64(SHA256(value)) = documented AS hash_matches_documented
FROM UNNEST([
  STRUCT('randomtext' AS value, 'jQHDyQuj7vJcveEe59ygb3Zcvj0B5FJINBzgM6Bypgw=' AS documented),
  ('test@[email protected]', 'Qdje6MO+GLwI0u+KyRyAICDjHbLF1ImxRqaW08tY52k=')
])
ORDER BY value

This is a check of Google’s published values, not a run of a masked column. It also shows the weakness Google points out: a hashed 4-character string is easy to reverse with a lookup table, which is why “first four characters” has “very low” strength for short values.

Joins on masked columns

The masking rule decides whether a masked column is still useful as a join key. Hash gives the same output for the same input, so joins still match. The default value is the same for every row, and nullify gives NULL, so those two can’t identify anyone and break joins in different ways. We simulated each rule by hand on two three-row tables:

-- Simulates what a join sees when the join column is masked by each rule. The masking is done by hand here.
WITH customers AS (
  SELECT * FROM UNNEST([STRUCT('[email protected]' AS email, 'gold' AS tier), ('[email protected]', 'silver'), ('[email protected]', 'gold')])
),
orders AS (
  SELECT * FROM UNNEST([STRUCT('[email protected]' AS email, 10 AS amount), ('[email protected]', 20), ('[email protected]', 30)])
),
joined AS (
  SELECT 1 AS rule_order, 'no masking' AS rule, COUNT(*) AS matched_rows
  FROM customers c JOIN orders o ON c.email = o.email
  UNION ALL
  SELECT 2, 'hash (SHA-256)', COUNT(*)
  FROM customers c JOIN orders o ON SHA256(c.email) = SHA256(o.email)
  UNION ALL
  SELECT 3, 'default value', COUNT(*)
  FROM (SELECT '' AS email FROM customers) c JOIN (SELECT '' AS email FROM orders) o ON c.email = o.email
  UNION ALL
  SELECT 4, 'nullify', COUNT(*)
  FROM (SELECT CAST(NULL AS STRING) AS email FROM customers) c
  JOIN (SELECT CAST(NULL AS STRING) AS email FROM orders) o ON c.email = o.email
)
SELECT rule, matched_rows FROM joined ORDER BY rule_order

With a hash the join gives the right 3 rows. With NULL it gives none. With a constant it gives 9: every customer matches every order, a silent wrong answer. Google’s docs only say a default value is “not sufficiently unique” for joins. The consequence is a cross product. Random hash joins only within one query, since the salt changes per query, and only when both columns’ data policies belong to the same project.

When several rules apply

A user in two groups can match two data policies on the same tag. BigQuery then picks one rule by a fixed order, not the most restrictive: custom routine, random hash, SHA-256 hash, email mask, last four, first four, date year, default value, nullify. A user in a group that nullifies and a group that hashes gets the hash, so one more group membership can change which rule applies.

Roles are checked from the tag the column carries up to the root of the taxonomy, and the first match wins. If someone has Fine-Grained Reader on a tag and Masked Reader on its parent, they see real data. If it is the other way around, with Masked Reader on the tag and Fine-Grained Reader higher up, they see masked data. Google recommends granting Masked Reader per data policy, not on the project: at project level it covers every data policy there.

Interaction with row-level security

The two combine. A row access policy filters rows first, and masking runs on what is left: with a policy on location = 'US' and location masked, the user sees the US rows with the column masked. So a row policy can filter on a column the user only sees masked. A subquery row policy needs Fine-Grained Reader on the columns it references. See row-level security.

Billing and limits

Column-level access control uses Data Catalog as well as BigQuery, and Google bills both. Taxonomy names must be unique in the organization, and tables can’t be copied across regions when a policy tag is on them. Reads of tagged data write the referenced tags to Cloud Logging, but not tied to the query that triggered the check.