Data Security and Masking

Lesson Overview

This lesson covers data security principles and data masking techniques to protect sensitive information.

What You'll Learn:

• Data security fundamentals
• Data masking techniques
• Access control principles
• Compliance requirements
• Security best practices

Key Concepts:

• Data Security: Protection of data from unauthorized access
• Data Masking: Hiding sensitive data while maintaining format
• Access Control: Restricting data access to authorized users
• Encryption: Converting data to encoded format
• PII: Personally Identifiable Information

Data Masking and Anonymization

What is Data Masking

Data Masking is the process of hiding original data with modified content (characters or other data) to protect sensitive information while maintaining the format and structure of the original data.

Masking Objectives

• Protect sensitive information from unauthorized access
• Maintain data format for testing and development
• Enable safe data sharing with third parties
• Support compliance with privacy regulations

Types of Data Masking

Static Data Masking

• Applied to data at rest in databases
• Creates a masked copy of production data
• Used for non-production environments
• One-time masking process

Dynamic Data Masking

• Applied to data in transit during queries
• Real-time masking based on user permissions
• No permanent changes to stored data
• Context-aware masking

Masking Techniques

Substitution

• Replace real data with fake but realistic data
• Maintains format and data type
• Example: Replace real names with fake names
• Preserves statistical properties

Shuffling

• Randomly rearrange data within the same column
• Maintains data distribution
• Example: Shuffle salary values among employees
• Preserves aggregate statistics

Nulling/Deletion

• Replace sensitive data with NULL values
• Simple but may break application logic
• Example: Remove phone numbers from customer data
• High security but low utility

Encryption

• Convert data to encrypted format
• Requires decryption keys for access
• Example: Encrypt social security numbers
• High security, reversible with proper keys

Short SQL Example: Data Masking

-- Example of data masking techniques
WITH masking_examples AS (
    SELECT 
        customer_id,
        -- Original sensitive data
        email,
        phone,
        social_security_number,
        credit_card_number,
        -- Masked versions using different techniques
        
        -- Email masking (substitution)
        CASE 
            WHEN email LIKE '%@%' THEN 
                CONCAT('user', customer_id, '@example.com')
            ELSE '[email protected]'
        END as masked_email,
        
        -- Phone masking (format preservation)
        CASE 
            WHEN phone REGEXP '^[0-9]{10}$' THEN 
                CONCAT('XXX-XXX-', RIGHT(phone, 4))
            WHEN phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$' THEN 
                CONCAT('XXX-XXX-', SUBSTRING(phone, 9, 4))
            ELSE 'XXX-XXX-XXXX'
        END as masked_phone,
        
        -- SSN masking (partial masking)
        CASE 
            WHEN social_security_number REGEXP '^[0-9]{9}$' THEN 
                CONCAT('XXX-XX-', RIGHT(social_security_number, 4))
            ELSE 'XXX-XX-XXXX'
        END as masked_ssn,
        
        -- Credit card masking (tokenization)
        CASE 
            WHEN credit_card_number REGEXP '^[0-9]{16}$' THEN 
                CONCAT('XXXX-XXXX-XXXX-', RIGHT(credit_card_number, 4))
            ELSE 'XXXX-XXXX-XXXX-XXXX'
        END as masked_credit_card
    FROM customers
)
SELECT 
    'Data Masking Example' as example_type,
    customer_id,
    masked_email,
    masked_phone,
    masked_ssn,
    masked_credit_card,
    -- Masking effectiveness
    CASE 
        WHEN masked_email NOT LIKE '%@%' THEN 'Failed'
        WHEN masked_email LIKE '%example.com' THEN 'Success'
        ELSE 'Partial'
    END as email_masking_status,
    CASE 
        WHEN masked_phone LIKE 'XXX%' THEN 'Success'
        ELSE 'Failed'
    END as phone_masking_status
FROM masking_examples
LIMIT 10;

Data Anonymization

Anonymization vs Masking

• Masking: Reversible with proper authorization
• Anonymization: Irreversible, removes all identifiers
• Pseudonymization: Replaces identifiers with pseudonyms
• Generalization: Converts specific values to ranges

Anonymization Techniques

• K-Anonymity: Each record indistinguishable from k-1 others
• L-Diversity: Ensures diversity in sensitive attributes
• T-Closeness: Distribution close to original data
• Differential Privacy: Adds mathematical privacy guarantees

Protecting Sensitive Data in Test Environments

Test Environment Challenges

Security Risks

• Production data exposed to developers
• Weak access controls in test systems
• Data leakage through testing processes
• Inadequate monitoring and auditing

Compliance Issues

• Regulatory violations for using real PII
• Lack of proper data handling procedures
• Missing audit trails for data access
• Inadequate data retention policies

Test Data Management Strategies

Data Substitution

• Replace production data with synthetic data
• Generate realistic test data sets
• Maintain referential integrity
• Preserve data relationships

Data Subset Creation

• Extract small, representative samples
• Apply statistical sampling techniques
• Maintain data distribution
• Reduce exposure risk

Environment Isolation

• Separate test from production networks
• Implement strict access controls
• Use dedicated test databases
• Monitor data access patterns

Short SQL Example: Test Data Generation

-- Generate masked test data from production
WITH test_data_generation AS (
    SELECT 
        customer_id,
        -- Generate synthetic test data
        CONCAT('testuser', customer_id) as test_username,
        CONCAT('test', customer_id, '@example.com') as test_email,
        -- Generate realistic but fake phone numbers
        CONCAT('555-', 
               LPAD(FLOOR(RANDOM() * 1000), 3, '0'), '-', 
               LPAD(FLOOR(RANDOM() * 10000), 4, '0')) as test_phone,
        -- Generate test addresses
        CONCAT(FLOOR(RANDOM() * 9999) + 1, ' Test St.') as test_address,
        CONCAT('Test City', FLOOR(RANDOM() * 10) + 1) as test_city,
        -- Generate test dates within realistic ranges
        DATE('2020-01-01', '+' || (FLOOR(RANDOM() * 1460)) || ' days') as test_created_date,
        -- Generate test financial data
        ROUND(RANDOM() * 100000 + 50000, 2) as test_income,
        ROUND(RANDOM() * 10000, 2) as test_balance,
        -- Data quality indicators
        'SYNTHETIC' as data_source,
        CURRENT_TIMESTAMP as generation_timestamp
    FROM customers
    WHERE customer_id <= 1000  -- Limit to subset for testing
)
SELECT 
    'Test Data Generation' as process_type,
    COUNT(*) as records_generated,
    COUNT(DISTINCT test_email) as unique_emails,
    COUNT(DISTINCT test_phone) as unique_phones,
    -- Data quality checks
    COUNT(CASE WHEN test_email LIKE '%@example.com' THEN 1 END) as valid_emails,
    COUNT(CASE WHEN test_phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$' THEN 1 END) as valid_phones,
    -- Date range validation
    MIN(test_created_date) as earliest_date,
    MAX(test_created_date) as latest_date,
    -- Financial data validation
    MIN(test_income) as min_income,
    MAX(test_income) as max_income,
    AVG(test_income) as avg_income
FROM test_data_generation;

Test Environment Security

Access Control

• Role-based access permissions
• Principle of least privilege
• Regular access reviews
• Temporary access grants

Data Protection

• Encryption at rest and in transit
• Database-level security
• Application-level masking
• Network segmentation

Monitoring and Auditing

• Access log monitoring
• Data usage tracking
• Anomaly detection
• Regular security assessments

Compliance with GDPR / HIPAA

GDPR Compliance

Key Requirements

• Data Minimization: Collect only necessary data
• Purpose Limitation: Use data only for stated purposes
• Storage Limitation: Retain data only as long as necessary
• Accuracy: Maintain accurate and up-to-date data
• Security: Implement appropriate technical measures

Data Subject Rights

• Right to Access: Know what data is stored
• Right to Rectification: Correct inaccurate data
• Right to Erasure: "Right to be forgotten"
• Right to Portability: Transfer data to other services
• Right to Object: Restrict processing of personal data

HIPAA Compliance

Protected Health Information (PHI)

• Medical records and test results
• Billing and payment information
• Insurance claims and coverage
• Patient communications and appointments

Security Rules

• Administrative Safeguards: Policies and procedures
• Physical Safeguards: Facility access and controls
• Technical Safeguards: Access control and audit controls

Short SQL Example: Compliance Monitoring

-- GDPR/HIPAA compliance monitoring dashboard
WITH compliance_metrics AS (
    -- Data retention compliance
    SELECT 
        'Data Retention' as compliance_area,
        table_name,
        COUNT(*) as total_records,
        COUNT(CASE WHEN created_date < CURRENT_DATE - INTERVAL '7 years' THEN 1 END) as expired_records,
        ROUND(COUNT(CASE WHEN created_date < CURRENT_DATE - INTERVAL '7 years' THEN 1 END) * 100.0 / COUNT(*), 2) as non_compliance_percentage,
        CASE 
            WHEN COUNT(CASE WHEN created_date < CURRENT_DATE - INTERVAL '7 years' THEN 1 END) = 0 THEN 'Compliant'
            WHEN COUNT(CASE WHEN created_date < CURRENT_DATE - INTERVAL '7 years' THEN 1 END) < COUNT(*) * 0.05 THEN 'Minor Issues'
            ELSE 'Non-Compliant'
        END as compliance_status
    FROM customer_data
    GROUP BY table_name
    
    UNION ALL
    
    -- Data access monitoring
    SELECT 
        'Access Control' as compliance_area,
        'Data Access Logs' as table_name,
        COUNT(*) as total_access_events,
        COUNT(CASE WHEN access_reason IS NULL THEN 1 END) as unauthorized_access,
        ROUND(COUNT(CASE WHEN access_reason IS NULL THEN 1 END) * 100.0 / COUNT(*), 2) as non_compliance_percentage,
        CASE 
            WHEN COUNT(CASE WHEN access_reason IS NULL THEN 1 END) = 0 THEN 'Compliant'
            WHEN COUNT(CASE WHEN access_reason IS NULL THEN 1 END) < COUNT(*) * 0.01 THEN 'Minor Issues'
            ELSE 'Non-Compliant'
        END as compliance_status
    FROM access_logs
    WHERE access_timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY 'Data Access Logs'
    
    UNION ALL
    
    -- Data masking verification
    SELECT 
        'Data Masking' as compliance_area,
        'PII Fields' as table_name,
        COUNT(*) as total_pii_fields,
        COUNT(CASE WHEN is_masked = false THEN 1 END) as unmasked_fields,
        ROUND(COUNT(CASE WHEN is_masked = false THEN 1 END) * 100.0 / COUNT(*), 2) as non_compliance_percentage,
        CASE 
            WHEN COUNT(CASE WHEN is_masked = false THEN 1 END) = 0 THEN 'Compliant'
            WHEN COUNT(CASE WHEN is_masked = false THEN 1 END) < COUNT(*) * 0.05 THEN 'Minor Issues'
            ELSE 'Non-Compliant'
        END as compliance_status
    FROM pii_field_audit
    GROUP BY 'PII Fields'
)
SELECT 
    compliance_area,
    table_name,
    total_records as total_items,
    expired_records as compliance_issues,
    non_compliance_percentage,
    compliance_status,
    -- Action recommendations
    CASE 
        WHEN compliance_status = 'Non-Compliant' THEN 'Immediate Action Required'
        WHEN compliance_status = 'Minor Issues' THEN 'Schedule Review'
        ELSE 'Maintain Compliance'
    END as recommended_action
FROM compliance_metrics
ORDER BY non_compliance_percentage DESC;

Compliance Implementation

Data Classification

• Identify and classify sensitive data
• Tag PII and PHI fields
• Document data processing activities
• Maintain data inventory

Privacy by Design

• Build privacy into systems from start
• Implement default privacy settings
• Minimize data collection
• Ensure data accuracy

Documentation and Reporting

• Maintain privacy policies
• Document data processing activities
• Report data breaches promptly
• Conduct regular privacy assessments

Secure Data Handling Practices

Data Lifecycle Security

Data Collection

• Validate input data
• Minimize data collection
• Obtain proper consent
• Document data sources

Data Storage

• Encrypt sensitive data
• Implement access controls
• Regular security updates
• Backup and recovery plans

Data Processing

• Process data in secure environments
• Use masked data for development
• Implement audit trails
• Monitor data access

Data Disposal

• Secure deletion methods
• Verify data destruction
• Maintain disposal records
• Comply with retention policies

Access Control Implementation

Authentication

• Multi-factor authentication
• Strong password policies
• Regular credential rotation
• Session management

Authorization

• Role-based access control
• Principle of least privilege
• Regular access reviews
• Temporary access grants

Accountability

• Comprehensive audit logging
• Access monitoring
• Anomaly detection
• Incident response procedures

Short SQL Example: Access Control Monitoring

-- Monitor data access for security compliance
WITH access_analysis AS (
    SELECT 
        user_id,
        user_role,
        table_accessed,
        operation_type,
        access_timestamp,
        -- Risk assessment
        CASE 
            WHEN table_accessed IN 'customers', 'patients', 'employees' THEN 'High Risk'
            WHEN table_accessed IN 'orders', 'transactions', 'claims' THEN 'Medium Risk'
            ELSE 'Low Risk'
        END as risk_level,
        -- Access pattern analysis
        COUNT(*) OVER (PARTITION BY user_id, table_accessed) as user_table_access_count,
        COUNT(*) OVER (PARTITION BY user_id) as total_user_access,
        -- Time-based analysis
        EXTRACT(HOUR FROM access_timestamp) as access_hour,
        CASE 
            WHEN EXTRACT(HOUR FROM access_timestamp) BETWEEN 9 AND 17 THEN 'Business Hours'
            ELSE 'After Hours'
        END as access_time_category
    FROM access_logs
    WHERE access_timestamp >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT 
    'Access Control Analysis' as analysis_type,
    user_id,
    user_role,
    table_accessed,
    operation_type,
    risk_level,
    user_table_access_count,
    total_user_access,
    access_time_category,
    -- Security flags
    CASE 
        WHEN risk_level = 'High Risk' AND user_role NOT IN 'admin', 'manager' THEN 'Suspicious Access'
        WHEN access_time_category = 'After Hours' AND user_role NOT IN 'admin', 'analyst' THEN 'Unusual Time'
        WHEN user_table_access_count > 1000 THEN 'Excessive Access'
        ELSE 'Normal'
    END as security_flag,
    -- Recommended action
    CASE 
        WHEN security_flag != 'Normal' THEN 'Investigate'
        ELSE 'Monitor'
    END as recommended_action
FROM access_analysis
WHERE security_flag != 'Normal'
ORDER BY risk_level DESC, user_table_access_count DESC;

Security Best Practices

Technical Controls

• Encryption at rest and in transit
• Database security configurations
• Network security measures
• Regular vulnerability scanning

Administrative Controls

• Security policies and procedures
• Employee training programs
• Incident response plans
• Regular security assessments

Physical Controls

• Secure data center access
• Environmental controls
• Backup and recovery systems
• Disaster recovery planning

Incident Response

Detection

• Monitor security alerts
• Analyze access patterns
• Investigate anomalies
• Document incidents

Response

• Contain security breaches
• Notify affected parties
• Implement corrective actions
• Learn from incidents

Recovery

• Restore secure operations
• Update security measures
• Conduct post-incident reviews
• Improve security posture

Key Takeaways

1. Data Masking: Essential for protecting sensitive data while maintaining usability
2. Test Environment Security: Critical for preventing production data exposure
3. Compliance: GDPR and HIPAA require specific data protection measures
4. Access Control: Foundation of data security implementation
5. Monitoring: Continuous security monitoring is essential for maintaining compliance
6. Incident Response: Preparedness for security incidents is crucial
7. Privacy by Design: Build security into systems from the beginning

Next Steps

In the next lesson, we'll explore bias, ethics, and fairness in data analytics to understand the social implications of data-driven decisions.