BigQuery Security, Masking and Cloud DLP

Row- and column-level security and identity governance in BigQuery (opens in a new tab) combines Identity and Access Management (IAM) roles with fine-grained data controls so each user and tool sees only what it needs. BigQuery resources form a hierarchy of organization, project, dataset, table, column, and view, where a dataset is a child of a project and tables inherit permissions from their dataset. Organization and project roles control the ability to run jobs or manage the project, while dataset, table, row, and column controls narrow what data is visible inside the tables.

1.1IAM Roles and Least-Privilege Resource Hierarchy

Identity and Access Management (IAM) in BigQuery enforces least privilege by granting permissions grouped into roles to a user, group, or service account at a specific level of the hierarchy. The IAM hierarchy flows from organization to folder to project to resource, and child resources inherit the policies of their parents. Permissions are assigned with no-access, read-only, or write scope so identities receive only the minimum access needed for their work.

IAM provides three role types that differ in granularity:

• Basic roles: Owner, Editor, and Viewer applied at the project level through the Google Cloud console, IAM API, or gcloud CLI.
• Predefined roles: Product-focused roles, such as BigQuery roles for datasets and resources inside datasets, built for common access patterns.
• Custom roles: User-defined collections of permissions for granular access when predefined roles are too broad.

For strongest security, use predefined or custom roles instead of basic roles and create dedicated least-privilege service accounts for pipelines and visualization connections. For example, a pipeline service account has permission to read from only one Cloud Storage bucket and write to its destination, so compromise of one pipeline does not expose unrelated data.

1.2Table-Level Access and Authorized Views for Visualization

Authorized views are BigQuery views that let specified users query the view without having read access to the underlying base tables. Table-level access control determines which users, groups, and service accounts can access a table or view, so a user can be given access to specific tables or views without access to the whole dataset. When a visualization tool queries an authorized view, access flows from the user to the view and then from the view's authorized identity to the base tables, so the tool never needs direct rights on sensitive base data.

Use authorized views when different audiences need different subsets, aggregations, or joins for dashboards. The same pattern applies to authorized user-defined functions (UDFs): the routine is authorized on the dataset so callers can run its logic without direct access to the tables it reads. Anything not protected by table controls or views remains available to all users with dataset access, so authorize only the views and routines each audience requires.

1.3Column-Level Security with Policy Tags

Policy tags implement BigQuery column-level security by attaching a classification to a sensitive column and granting the Fine-Grained Reader role only to allowed identities. Column-level access control provides fine-grained access to sensitive columns using policy tags, while column-level dynamic data masking selectively obscures column data for groups of users while still allowing access to the column. An analyst must hold the Fine-Grained Reader role on the tag to read the protected column, even after data is re-identified.

A common taxonomy creates nested levels where higher access implies lower access, such as 1_Sensitive for non-public data like credit limit, 2_Private for sensitive personally identifiable information (PII) like first name, and 3_Confidential for very sensitive data like credit card numbers. In this model, access to 3_Confidential also grants access to 2_Private and 1_Sensitive, access to 2_Private also grants access to 1_Sensitive but not 3_Confidential, and access to 1_Sensitive grants no access to the higher tags. Column policies on a base table apply to all authorized views on that table, so tag enforcement cannot be bypassed by creating a new view.

1.4Row-Level Security with Filter Policies

Row-level security in BigQuery filters data so a query returns only the rows that satisfy conditions for the requesting user or group. Unlike column controls that hide an entire column, a row-level policy evaluates the requesting identity for each row and keeps unauthorized rows out of results from tables and authorized views. This allows one shared table to serve regional teams, tenants, or role-based audiences while each identity sees only its own slice.

For multi-tenant visualization, choose between tenant-specific authorized views and tenant-specific subset tables based on scale and isolation needs:

Cluster the parent table on tenant ID when using authorized views to improve tenant segmentation performance, and use subset tables when tenants need different regions or physical layouts.

1.5Conditional and Time-Bound Identity Governance

Conditional and time-bound governance extends IAM roles with attribute, network, and lifecycle controls around analytical access. IAM Conditions enforce conditional, attribute-based access, for example allowing a service account to trigger a pipeline only if the request comes from a trusted Cloud Build trigger. Principal access boundary (PAB) policies limit the resources a principal can access, while Privileged Access Manager controls just-in-time, temporary privilege elevation for select principals with later review in audit logs.

Network and context controls further restrict how visualization users and tools connect to data. Identity-Aware Proxy (IAP) with context-aware access restricts access based on IP address and device attributes, and Access Context Manager evaluates access each time a user authenticates to protected instances. For external sharing, manage external parties through groups with viewer permission on a shared-data project, keep the service account that updates the shared data owned by the internal project, and share across privacy boundaries with BigQuery sharing, BigQuery data clean rooms, or Looker Action Hub for secure delivery into built-in destinations.

A Cloud Data Loss Prevention (Cloud DLP) (opens in a new tab) de-identification pipeline is an ingestion path that detects personally identifiable information (PII) and transforms it before storage for analysis or display in dashboards. The pipeline uses Sensitive Data Protection, which includes Cloud DLP, to inspect content with an inspection template and transform matches with a de-identification template. Raw values such as credit card numbers, addresses, and phone numbers are replaced by masked, tokenized, or generalized values, so analysts and dashboards can still use the data while privacy requirements are met.

2.1Discover and Classify Sensitive Data Before De-identification

Sensitive data discovery is the continuous profiling process that determines where sensitive data exists before a de-identification pipeline is built. Discovery is configured at the organization, folder, or project level and generates data profiles containing sensitivity levels and data risk levels. The discovery service acts as a source of truth about data assets, detects new projects and uploads automatically, and can export profiles to Security Command Center, Google SecOps, and Knowledge Catalog for audit and threat investigation.

Discovery results guide whether a pipeline must create a de-identified copy. When sensitive data is expected and properly protected, profiles are kept in audit reports and monitoring workflows while watching for changes that create risk. When sensitive data is unexpected or lacks proper access controls, the remediation is to make a de-identified copy that masks or tokenizes sensitive columns, lock down access with IAM and perimeter controls, and continue monitoring. A high free-text score with multiple infoTypes such as PHONE_NUMBER, US_SOCIAL_SECURITY_NUMBER, and DATE_OF_BIRTH signals unstructured note fields that need on-demand inspection to locate exact PII before transformation.

2.2De-identification Transformations and Templates

De-identification is the removal or replacement of identifying information from health, financial, or demographic data so the data can be used and shared under privacy rules. Sensitive Data Protection applies transformation methods including masking with a masking character, tokenization and pseudonymization, crypto-based tokenization, replacement with a dummy or surrogate value, redaction, and generalization such as bucketing. Detection is driven by infoTypes and infoType detectors, which identify categories such as email address, phone number, and bank account details.

A production pipeline separates detection logic from transformation logic into reusable templates. An inspection template defines what to find and a de-identification template defines how to transform each finding, and both templates are referenced by name in pipeline jobs. A rule configuration maps each PII field type to its transformation, for example email fields are replaced with a dummy value, phone fields are masked for a defined number of characters, free-text comment fields are inspected for embedded email addresses, and bank account fields are tokenized with a crypto-based method that can support later re-identification by authorized processes.

2.3De-identified Copies of Cloud Storage Data

Storage de-identification creates sanitized copies of files in Cloud Storage without changing the source files or disrupting upstream pipelines. The process starts when an inspection job, a DlpJob, is created with de-identification configured for a Cloud Storage location. Sensitive Data Protection scans each file in the specified location, inspects it according to the inspection configuration, de-identifies data that matches the sensitivity criteria, and writes the result to a new file in a designated output bucket while preserving the original folder hierarchy.

Common pipeline patterns for storage de-identification include:

• Generate anonymized datasets: Create sanitized copies for non-production use before staging production files into development, testing, or training.
• Share sanitized repositories: Obfuscate sensitive fields in bulk documents while keeping folder structure for partners or auditors.
• Automate privacy for new uploads: Transform sensitive data from newly uploaded documents into a designated output bucket.
• Enforce residency and compartmentalization: Store de-identified mirrors in separate Cloud Storage buckets with distinct IAM access controls.

The separated output bucket is a trust boundary: the source bucket retains raw PII with restricted access, while the destination bucket holds only de-identified content that analysts, model training, and downstream exports can safely consume.

2.4Streaming Ingestion Pipeline From Cloud Storage to BigQuery

The Data Masking/Tokenization from Cloud Storage to BigQuery (opens in a new tab) template is a Cloud Dataflow streaming pipeline that de-identifies CSV data during ingestion from Cloud Storage to BigQuery. The pipeline depends on existing Sensitive Data Protection templates and an existing BigQuery dataset, and the input CSV files must already exist in Cloud Storage. Data flows from Cloud Storage through the DLP API to BigQuery, so no raw PII is written to the analysis table.

The ordered flow preserves dependencies between read, transform, and load:

1. Read: Dataflow reads CSV rows from Cloud Storage using an input file pattern that can include wildcards.
2. De-identify: Each batch of rows flows from Dataflow to the Cloud DLP API, which inspects and transforms values according to the referenced templates.
3. Write: The de-identified rows flow from Dataflow to the specified BigQuery dataset and table for analysis and visualization.

The template supports two detection modes that use different template combinations:

Required parameters are the Cloud Storage input file pattern, the de-identification template name, the destination BigQuery dataset name, and the DLP project ID that owns the DLP API resource. The de-identification template name must use a global path such as projects/PROJECT_ID/locations/global/deidentifyTemplates/TEMPLATE_ID, and the inspection template uses the parallel global inspect-template path. The optional batchSize controls how many CSV rows are sent per DLP API call, and sizing must respect the DLP API payload limit of 524 KB per call.

2.5Remediate High-Risk Findings Before Analysis and Visualization

Remediation of data profiler findings lowers data risk before tables and files reach dashboards. For BigQuery columns with high data risk, meaning evidence of sensitive data without additional protections, access is restricted with BigQuery policy tags, column-level and row-level security, and IAM roles with least privilege. The raw sensitive data is then de-identified with masking or tokenization, automatic tagging can set the profiled asset risk to Low after controls are applied, and unneeded high-risk data is removed.

Free-text columns with high risk need location-specific inspection because PII may be embedded in sentences such as birth dates in customer notes. For BigQuery and Cloud Storage data, an on-demand inspection job identifies the exact locations of the PII so masking and tokenization rules can target those fields without destroying analytical utility. The profiler service agent must retain permission to profile tables that have column-level restrictions, or profiling shows an error, and VPC Service Controls perimeters with ingress and egress rules plus encryption with customer-managed encryption keys (CMEKs) in Cloud KMS further prevent exfiltration of the raw data that feeds the de-identification pipeline.

Dynamic Data Masking with Dataplex policy tags protects sensitive BigQuery columns during analysis and visualization without copying or changing the stored data. A taxonomy created in Dataplex holds policy tags that classify sensitive attributes, the tags are attached to table columns, and BigQuery evaluates the querying identity's IAM permissions at query time to return either clear values or obfuscated values.

3.1Dynamic Data Masking Behavior

Dynamic data masking is column-level obfuscation that is applied when a query runs, not when data is written. The underlying table still stores the original values, but the result sent to the requester shows masked values if the requester's identity lacks permission on the associated policy tag. This allows the same table and the same dashboard to serve privileged users who see full detail and restricted users who see only safe placeholders, while the schema, row counts, and visualization layout stay consistent.

Masking is distinct from encryption at rest (opens in a new tab), which protects stored bytes from infrastructure-level access. BigQuery automatically encrypts all data at rest by default, with customer-managed encryption keys (CMEK) available in Enterprise and Enterprise Plus editions, while dynamic data masking controls what a specific SQL identity is allowed to read in the result set.

3.2Dataplex Taxonomies and Policy Tags

A Dataplex taxonomy is the governance container that authors and organizes policy tags for an organization. The taxonomy defines the hierarchy of classifications, such as a high-level group for personally identifiable information with child tags for email, account number, or location, and data stewards manage who can create, attach, and use those tags. Once published, a policy tag becomes the link between governance policy and physical columns: attaching the tag to a BigQuery column marks that column as governed by the corresponding access rule.

Access to the data then flows from column to taxonomy to identity. The column depends on the policy tag for its protection, the policy tag depends on IAM grants for its enforcement, and the query result depends on whether the caller's identity holds permission on that tag. Removing a tag from a column removes the masking control, while changing IAM grants immediately changes who sees clear versus masked values on the next query, with no pipeline reload required.

3.3Column-Level Security and Masking Options

Column-level security enforced through policy tags supports several related controls that differ in what the restricted user experiences:

Column-level access control and dynamic data masking can be used together in one taxonomy design. Use denial when exposure of even the masked shape is unacceptable, and use masking when analysts and dashboards need to count, group, or display rows without revealing the underlying sensitive attribute.

Key Facts

• Authorized views let users query a BigQuery view without read access to the underlying base tables, with access flowing from the user to the view and then from the view to the base tables.
• Column policies on a BigQuery base table apply to all authorized views on that table, and a dataset supports a limit of 2500 authorized resources.
• The Cloud Storage to BigQuery Dataflow masking pipeline reads CSV rows, sends batches to the Cloud DLP API for transformation, and writes de-identified rows to BigQuery, respecting the 524 KB DLP API payload limit.
• Dynamic data masking obfuscates BigQuery column values at query time based on the caller's IAM permission on the Dataplex policy tag, while the stored values remain unchanged.