Temporary Raw Data Access: A Working Framework for Regulated Environments

Here’s a problem we’ve encountered in data governance. The choice between "no access" and "unrestricted access." Either your teams wait weeks for a simple query, or they get standing permissions that nobody ever revokes.

We built a system to bridge this gap. It ensures that compliance becomes a forcing function for better engineering rather than a roadblock.

The Access Control Paradox

In environments governed by 21 CFR Part 11, HIPAA, or SOC 2, auditors expect you to know exactly who accessed what data, when, and why. However, reality often leads to "temporary" access that never gets revoked. Three months later, fifty people still have raw database permissions nobody remembers granting.

Step 1: The Intake Form (Audit Trail)

The first step in bridging this gap is a standardized intake form. This isn't just paperwork; it’s a legal record of intent that maps directly to your infrastructure code.

DATA ACCESS REQUEST FORM
========================
Requestor: [user@company.com]
Date: [MM/DD/YYYY]

SCOPE:
- Source System: [DATABASE 1, DATABASE 2, etc.]
- Specific Table(s): [exact table names]

DURATION:
- [ ] 1 week (Validation / Bug Hunting)
- [ ] 30-90 days (Product Gap / Missing Field)

JUSTIFICATION:
- Use Case: [Validation / Missing Field / Reporting]
- Description: [Specific reporting requirement not met by existing Marts]

TECHNICAL CONTEXT:
- [Attach specific SQL query if applicable]
      

Step 2: Risk-Based Approval

Duration determines who approves and what happens next:

• Short-Term (1 Week): Requires Data Product Owner (PO) approval for logic validation or one-off investigations.
• Medium-Term (30-90 Days): Requires Data Product Owner (PO) approval. This duration implies a gap in the Data Mart that must be ticketed for development to ensure a permanent solution is built.

Step 3: Technical Enforcement (BigQuery & Terraform)

Every access grant lives in code with a mandatory sunset tag. By using a conditional environment check and a for_each loop, we ensure the principle of least privilege is applied only where needed:

# SOP-DE-004: Temporary Raw Data Access Management
# EXPIRY: 2026-04-27 | TICKET: DE-999 | USER: analyst@company.com

resource "google_bigquery_table_iam_member" "raw_table_viewer" {
  for_each = var.environment == "prod" ? toset(["table_1","table_2"]) : []
  project    = module.sources_project.project_id
  dataset_id = "dataset_name"
  table_id   = each.value
  role       = "roles/bigquery.dataViewer"
  member     = "user:analyst@company.com"
}
      

The Monthly Audit (Self-Maintaining System)

Once a month, we run a simple bash script to walk the Terraform files. If the # EXPIRY: YYYY-MM-DD comment in the code has passed, the block is flagged for removal. This moves the burden of "remembering to revoke" from humans to a repeatable, automated process.

When a user identifies a recurring reporting need during their temporary access period, we move the logic into a Hex project. The workflow looks like this:

• The user prototypes the query using their temporary IAM permissions.
• Once validated, the logic is committed to a Hex project using a service account with the necessary (and restricted) scopes.
• Individual raw access is then revoked immediately.

This ensures the "how" (the code) and the "what" (the data) are version-controlled and audited within Hex, while the direct human access to the raw tables is eliminated.

Why This Works for Regulators

When an auditor asks for proof of control, you show them the Intake Form (Intent), the Jira Ticket (Authorization), the Terraform Code (Current State), and the Audit Log (Revocation). This makes the entire lifecycle of data access transparent and deterministic.

If you're looking to adapt this pattern for your own stack or need help architecting compliant data workflows, feel free to reach out and schedule a consultation.