Securing Sensitive Data in Google BigQuery: A Comprehensive Approach

Think of table-level security as your perimeter defense. It’s the simplest to implement but provides only coarse-grained control.

Key Features:

Implementation:

— Basic table access control

GRANT `roles/bigquery.dataViewer`
ON TABLE `project.dataset.table`
TO "user:analyst@company.com";

Pro tip: Organize tables into datasets based on sensitivity levels. This simplifies permission management tremendously when working at scale.

Key Features:

Implementation:

SQL Method:

CREATE ROW ACCESS POLICY filter_by_department
ON project.dataset.employee_data
GRANT TO ('group:hr@company.com') —> ('user:aatish@company.com')
FILTER USING (department = 'HR');

Pro tip: Be mindful of performance impacts with complex row filters. Consider materialized views for frequently accessed filtered data.

When you need to restrict access to specific fields containing sensitive data, column-level security through Data Catalog policy tags.

Implementation:

Step 1: Create your taxonomy structure:
Allows granular control over data access.Helps in maintaining compliance and
governance.

BigQuery CLI :

bq mk --taxonomy "data_sensitivity_taxonomy"

OR
Using Cloud SDK:

gcloud data-catalog taxonomies create \
  --display-name="Data Sensitivity Taxonomy" \
  --location=us \
  --description="Taxonomy for data sensitivity levels"

Step 2: Define Policy Tags:
Each tag will represent a specific sensitive level.tags can have hierarchical relationships.

Using Cloud SDK:

# Create tags for different sensitivity levels
gcloud data-catalog policy-tags create \
  --taxonomy-name=[TAXONOMY_ID] \
  --display-name="Confidential" \
  --description="Highly sensitive data requiring strict access control"

Step 3: Apply Policy Tags to Sensitive Columns:
Marking specific columns.

SQL Method :

-- Apply policy tag to sensitive columns
ALTER TABLE `project.dataset.customers`
ALTER COLUMN  SET OPTIONS (
  policy_tags = ['projects/[PROJECT_ID]/locations/[LOCATION]/taxonomies/[TAXONOMY_ID]/policyTags/[TAG_ID]']
);

Cloud SDK Method:

gcloud data-catalog columns tag \
  --project=[PROJECT_ID] \
  --dataset=[DATASET_NAME] \
  --table=[TABLE_NAME] \
  --column=email \
  --taxonomy=[TAXONOMY_ID] \
  --tag=[CONFIDENTIAL_TAG_ID]

Step 4: Configure Access Permissions
Grant Access to Specific Users/Groups

Cloud SDK Method:

# Grant fine-grained access to a specific policy tag
gcloud data-catalog taxonomies add-iam-policy-binding \
  --taxonomy=[TAXONOMY_ID] \
  --location=[LOCATION] \
  --member=user:[] \
  --role=roles/datacatalog.categoryFineGrainedReader

Pro tip: Create a clear taxonomy that mirrors your organization’s data classification policy. This alignment makes auditing and compliance reporting much simpler.

Sometimes you need to provide access to data structures while obscuring the actual sensitive values. Dynamic data masking provides a flexible solution without duplicating data or creating complex views.

Key Features:

Implementation:

— Create a masking policy for credit card numbers

CREATE OR REPLACE MASKING POLICY project.dataset.credit_card_masking
  USING (CASE
    WHEN SESSION_USER() LIKE '%@finance.company.com' 
      THEN cc_number
    ELSE CONCAT('XXXX-XXXX-XXXX-', SUBSTR(cc_number, LENGTH(cc_number)-3))
  END);

— Apply the policy to a column

ALTER TABLE project.dataset.transactions
  ALTER COLUMN cc_number SET OPTIONS (masking_policy = 'project.dataset.credit_card_masking');

Advanced Pattern:

For locations with multiple column types requiring similar masking:

— Create a masking policy with selectable levels

CREATE OR REPLACE MASKING POLICY project.dataset.email_masking
  USING (CASE
    WHEN SESSION_USER() IN (SELECT email FROM project.dataset.admin_users) 
      THEN email_address -- Full access
    WHEN SESSION_USER() IN (SELECT email FROM project.dataset.support_users)
      THEN REGEXP_REPLACE(email_address, '(.{2})(.*)(@.*)', r'\1***\3') -- Partial masking
    ELSE 'masked@example.com' -- Full masking
  END);

Pro tip: Combine data masking with column-level security for defense in depth. Use masking for moderate sensitivity data and column-level security for high sensitivity data.

Authorized views provide a powerful alternative to row-level security when you need more flexibility or better query performance.

Key Features:

Implementation:

— Create the filtered view:

CREATE OR REPLACE VIEW project.secure_dataset.filtered_customers AS
SELECT customer_id, name,city,state,CASE  WHEN region = 'EMEA' THEN revenue ELSE NULL
 END AS revenue FROM project.raw_dataset.customers WHERE region = 'EMEA';

— Grant the view access to source tables:

GRANT `roles/bigquery.dataViewer`
ON TABLE `project.raw_dataset.customers`
TO VIEW `project.secure_dataset.filtered_customers`;

— Grant users access to only the view, not underlying tables:

GRANT `roles/bigquery.dataViewer`
ON VIEW `project.secure_dataset.filtered_customers`
TO "group:emea_analysts@company.com";

Pro tip: Authorized views can be combined with row-level security and column-level security for sophisticated multi-layered protection. Use views to simplify complex security predicates and improve performance.

Real-world example for authorized views :
For a healthcare client, we created authorized views that joined patient and billing data but only exposed specific fields to different departments – clinical staff saw patient information without billing details, while billing staff saw financial data with minimal patient information.