Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 37 ยท Security and Cost

Row-Level and Column-Level Security

Restrict BigQuery data by row with row access policies, by column with policy tags, and mask sensitive values with data masking, plus encryption basics.

Dataset-level IAM is all-or-nothing for a table: a user either reads it or does not. Real organizations need finer control. Regional managers should see only their own region's sales. Analysts should see order totals but not email addresses or card digits. BigQuery supports this with row-level security, column-level security and dynamic data masking, all enforced by the engine, so the rules apply no matter which tool runs the query.

All statements below need real BigQuery and are read-only. The runnable examples show the same logic with SQL so you can see what each policy does to the result set.

Row access policies

A row access policy is a filter attached to a table and granted to specific principals. Users who are covered by at least one policy see only the rows passing their policies. Users covered by none see no rows at all.

SQL
CREATE ROW ACCESS POLICY eu_managers
ON `my-project.sales.orders`
GRANT TO ("group:[email protected]")
FILTER USING (region = "EU");

CREATE ROW ACCESS POLICY us_managers
ON `my-project.sales.orders`
GRANT TO ("group:[email protected]")
FILTER USING (region = "US");

-- Give an auditor everything
CREATE ROW ACCESS POLICY all_rows
ON `my-project.sales.orders`
GRANT TO ("group:[email protected]")
FILTER USING (TRUE);

Key points: the table must already exist; the filter can use columns of the table and simple functions such as SESSION_USER(); policies add up (a user in two groups sees the union of both filters); and removing every policy with DROP ALL ROW ACCESS POLICIES returns the table to normal dataset-level access. Row policies work with partitioned and clustered tables, and pruning still applies.

Here is the same idea modelled in SQL: a policy table maps users to regions and a join applies it.

SQL A row access policy modelled in SQL

Press Run (or Ctrl + Enter) to see the result here.

An intern principal with no row in policies would return nothing, which is the secure default.

Column-level security with policy tags

Some columns are sensitive in every row. For those, create a taxonomy of policy tags in Dataplex (Data Catalog), for example PII > Email and PII > Financial, then attach a tag to a column in the table schema. Only principals with the Fine-Grained Reader role on that tag (roles/datacatalog.categoryFineGrainedReader) can read the column; everyone else gets an access error if their query touches it.

SQL
CREATE TABLE `my-project.sales.customers` (
  customer_id INT64,
  name        STRING,
  email       STRING OPTIONS (
    policy_tags = ['projects/my-project/locations/us/taxonomies/123/policyTags/456']
  ),
  country     STRING
);

Two practical details. First, SELECT * on a table with a restricted column fails for unauthorized users, so give them a view or an EXCEPT list: SELECT * EXCEPT (email) FROM .... Second, column security is checked at query time, so it also protects the column inside views and joins when the viewer lacks access.

Dynamic data masking

Sometimes you want people to see that a value exists without seeing the real thing. A data policy attached to a policy tag returns a masked value to users who lack the Fine-Grained Reader role but hold the Masked Reader role. Built-in rules include:

  • Default masking: returns a type-specific constant, such as '' for strings or 0 for numbers.
  • SHA-256 hash: a repeatable pseudonym that still allows joins on the value.
  • Email mask: keeps the domain, such as [email protected].
  • First four characters, last four characters, date year mask and custom routines.
SQL Masking modelled in SQL

Press Run (or Ctrl + Enter) to see the result here.

In BigQuery the masked reader runs the same SELECT email FROM customers and the engine swaps in the mask automatically. You do not rewrite queries.

Choosing the right control

Which control fits?

Row access policy Different rows per group
Policy tag Hide a sensitive column
Data masking Show a transformed value
Authorized view Share a prepared slice

Combine them: they stack.

1 / 2

Use policies when the rule must hold for every consumer of the table, and an authorized view when you want to publish a ready-made, simplified slice. Tags and masks scale better than one view per team.

Encryption and network controls

  • Encryption at rest and in transit is on by default with Google-managed keys. For stricter control use customer-managed encryption keys (CMEK) in Cloud KMS, which you can rotate or disable to make data unreadable.
  • VPC Service Controls put BigQuery behind a perimeter, reducing the risk of data exfiltration to projects outside it.
  • Audit logging records who accessed which tables; export the logs into BigQuery and query them like any dataset.
  • Data Loss Prevention scans can find unexpected sensitive values in tables before you publish them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A user in no row access policy queries a table that has policies. What do they see?

    Once a table has row access policies, users not covered by any policy see zero rows.
  2. 2. How do you restrict a single sensitive column such as email for most users?

    Column-level security uses policy tags from a taxonomy; only principals with the tag's reader role can read the column.
  3. 3. A user belongs to two groups, each with a different row access policy. What do they see?

    Policies are additive: a row is visible if any policy that applies to the user allows it.
  4. 4. What is the purpose of dynamic data masking?

    Masked readers receive a masked value such as a hash or a partial string from the same query.

Summary

  • Row access policies filter rows per principal; uncovered users see nothing and multiple policies union.
  • Policy tags restrict whole columns; the Fine-Grained Reader role grants access.
  • Data masking returns hashed, partial or default values to masked readers.
  • Authorized views complement these controls for curated slices.
  • Add CMEK, VPC Service Controls and audit logs for defense in depth.

Next: Pricing: On-Demand and Editions