Masking Sensitive Data Architectures

In this lesson, you will learn how to design your relational schema to obscure PII (Personally Identifiable Information) at the column level while keeping the original data intact for authorized processes.

The Principle of Column-Level Masking

When designing a schema, we often face a conflict: the database needs the full, raw value (like a full Social Security Number) for back-end processing, but application users or reporting tools should only see a partial, masked version (like ***-**-6789). Rather than masking data in the application layer—which risks leakage if someone queries the database directly—we implement masking patterns directly within our schema architecture.

Implementation Patterns

There are two primary ways to approach this at the structural level:

1. Computed Masking Columns: You keep the sensitive data in a protected column and create a "virtual" or "generated" column that serves the masked version to standard users.
2. View-Based Abstraction: You keep the base table restricted and expose data only through a VIEW that applies a function to scramble or truncate the sensitive fields.

Consider this example where we define a schema for a user profile:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    full_name VARCHAR(100),
    raw_ssn CHAR(9) -- This column has strict access controls
);

CREATE VIEW vw_user_directory AS
SELECT 
    user_id, 
    full_name,
    CONCAT('***-**-', RIGHT(raw_ssn, 4)) AS masked_ssn
FROM users;

Why this Architecture Matters

By handling masking at the schema level, you decouple the security policy from the application code. If a data analyst queries the vw_user_directory view, they only ever see the masked output, regardless of which tool they use to connect to the database. This pattern works harmoniously with other concepts we have covered, such as Database Security at the Schema Level, ensuring that even if a developer has access to the database, the design itself enforces the principle of least privilege by hiding sensitive PII by default.

Key Takeaway: Masking sensitive data within the schema—using views or generated columns—ensures that PII protection is a structural property of your database rather than an optional feature of your application code.