Part 186: Dynamic Data Masking & Anonymization for GDPR & HIPAA

Masking sensitive data in staging, reporting, and customer support environments.

1. What is Dynamic Data Masking (DDM)?

Obfuscates sensitive data on the fly based on user permissions: - Authorized payroll users see real salaries: $125,000.00. - Help desk staff see masked salaries: XXXX. - The underlying physical disk data remains unchanged!

2. SQL Server Dynamic Data Masking

CREATE TABLE dbo.Employees (
    EmployeeID INT IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
    CreditCard NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(0, "XXXX-XXXX-XXXX-", 4)'),
    Salary DECIMAL(10,2) MASKED WITH (FUNCTION = 'default()')
);

-- Helpdesk user queries table:
-- Email: j***@example.com
-- CreditCard: XXXX-XXXX-XXXX-1234
-- Salary: 0.00

3. GDPR Right-to-be-Forgotten Anonymization Script

CREATE PROCEDURE anonymize_user_data(p_user_id INT) AS $$
BEGIN
    UPDATE users 
    SET 
        full_name = 'Deleted User ' || id,
        email = 'anonymized_' || id || '@deleted.example.com',
        phone_number = NULL,
        shipping_address = 'REDACTED',
        is_anonymized = TRUE
    WHERE id = p_user_id;
END;
$$ LANGUAGE plpgsql;

Key Takeaways

• Dynamic data masking protects production data views without requiring separate sanitized test databases.

In Part 187, we explore High-Performance Bulk Data Loading: COPY & Unlogged Tables!