Oracle#

Oracle Data Masking and Subsetting

Oracle offers a dedicated tool for data masking and subsetting to help organizations securely provision production data in non-production environments. This solution can:

• Mask sensitive information by replacing real data with realistic, fictitious values
• Subset data to reduce storage requirements while preserving data relationships and integrity
• Work in conjunction with Transparent Data Encryption (TDE) to secure data at rest

Key Features:

• Format-preserving masking
• Conditional masking based on data relationships
• Application-aware subsetting
• Integration with Oracle Enterprise Manager

Official Resources:

• Oracle Data Masking and Subsetting
• Oracle Transparent Data Encryption (TDE)

Microsoft SQL Server#

Dynamic Data Masking & Encryption

SQL Server provides built-in capabilities that help protect sensitive data without altering the underlying database schema:

• Dynamic Data Masking (DDM): Masks sensitive data in query results for unauthorized users
• Always Encrypted: Protects sensitive data both at rest and during query execution by keeping encryption keys separate from the database
• Transparent Data Encryption (TDE): Encrypts the entire database at rest

Example of Dynamic Data Masking:

1
-- Create a table with masked columns
2
CREATE TABLE Customers (
3
    CustomerID int PRIMARY KEY,
4
    FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
5
    Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
6
    CreditCard varchar(20) MASKED WITH (FUNCTION = 'default()') NULL
7
);

Official Resources:

• Dynamic Data Masking (SQL Server)
• Always Encrypted Overview
• SQL Server Transparent Data Encryption (TDE)

IBM Db2#

Data Redaction & Native Encryption

IBM Db2 includes features to protect sensitive data at query time and at rest:

• Data Redaction: Dynamically masks sensitive data during query execution based on user privileges
• Native Encryption: Encrypts databases using a two-tier key architecture (data encryption key protected by a master key)

Key Features:

• Column-level masking policies
• Role-based data access
• Hardware-accelerated encryption
• Integrated key management

Official Resources:

• IBM Db2 Data Redaction
• IBM Db2 Native Encryption

MySQL Enterprise Edition#

Data Masking, De-Identification, and Encryption

MySQL Enterprise Edition provides tools to safeguard sensitive information:

• Data Masking and De-Identification: Allows dynamic masking of sensitive fields for non-production use
• Transparent Data Encryption (TDE): Encrypts data at rest using a two-tier key architecture via a keyring plugin

Example of Data Masking in MySQL:

1
-- Using MySQL Enterprise Data Masking functions
2
SELECT
3
    mask_inner(email, 2, 2) AS masked_email,
4
    mask_ssn(ssn) AS masked_ssn,
5
    mask_pan(credit_card) AS masked_cc
6
FROM customers;

Official Resources:

• MySQL Enterprise Data Masking and De-Identification
• MySQL Enterprise Encryption

PostgreSQL#

Community and Third-Party Extensions

PostgreSQL does not include built-in data masking or subsetting features similar to commercial databases. However, you can achieve similar functionality via:

• pgcrypto: An official extension that provides cryptographic functions for encryption or obfuscation
• pg_tde: Community-driven transparent data encryption extension
• Third-Party Tools: Commercial products offer masking and subsetting services

Example using pgcrypto:

1
-- Install pgcrypto extension
2
CREATE EXTENSION pgcrypto;
3

4
-- Encrypt sensitive data
5
UPDATE customers
6
SET credit_card = pgp_sym_encrypt(credit_card, 'encryption_key');
7

8
-- Decrypt when needed
9
SELECT pgp_sym_decrypt(credit_card::bytea, 'encryption_key') AS credit_card
10
FROM customers;

Official Resources:

• pgcrypto – PostgreSQL Documentation

MongoDB#

Encryption and Custom Masking Options

MongoDB Enterprise provides robust security features:

• Encryption at Rest: Ensures data files are encrypted using a master key mechanism
• Client-Side Field Level Encryption: Encrypts specific fields before data leaves the client application
• Custom Masking: Use aggregation pipeline or views to obscure data

Example of field-level masking in MongoDB:

1
// Using aggregation pipeline for data masking
2
db.customers.aggregate([
3
  {
4
    $project: {
5
      name: 1,
6
      email: {
7
        $concat: [
8
          { $substr: ["$email", 0, 3] },
9
          "****",
10
          { $substr: ["$email", { $indexOfCP: ["$email", "@"] }, -1] },
11
        ],
12
      },
13
      phone: {
14
        $concat: ["XXX-XXX-", { $substr: ["$phone", -4, 4] }],
15
      },
16
    },
17
  },
18
]);

Official Resources:

• MongoDB Encryption at Rest
• Client-Side Field Level Encryption

SAP HANA#

Data Security in SAP HANA

SAP HANA includes strong encryption capabilities and data anonymization features:

• Full-database and column-level encryption
• Data anonymization techniques for masking
• Integration with SAP Data Privacy features
• Dynamic data masking capabilities

Teradata#

Teradata Data Masking and Privacy

Teradata provides enterprise-grade security solutions including:

• Data Masking/Redaction for dynamic query-time masking
• Data Encryption for securing large-scale data warehouses
• Row-level security policies
• Temporal data management for compliance

Delphix Data Platform#

Provides comprehensive data virtualization, masking, and subsetting across multiple database platforms:

• Automated sensitive data discovery
• Format-preserving masking
• Referential integrity maintenance
• API-driven automation

Delphix Data Masking

Informatica Data Masking#

Offers enterprise-scale data privacy and security solutions:

• Persistent and dynamic data masking
• Test data management
• Sensitive data discovery
• Multi-platform support

Informatica Data Masking

IBM InfoSphere Optim#

Provides data lifecycle management with masking and subsetting:

• Context-aware data masking
• Realistic test data generation
• Data archiving and subsetting
• Compliance reporting

IBM InfoSphere Optim

1. Data Discovery and Classification#

Before implementing masking or encryption:

1
-- Example: Identify sensitive columns in SQL Server
2
SELECT
3
    SCHEMA_NAME(t.schema_id) AS schema_name,
4
    t.name AS table_name,
5
    c.name AS column_name,
6
    ty.name AS data_type
7
FROM sys.tables t
8
INNER JOIN sys.columns c ON t.object_id = c.object_id
9
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
10
WHERE c.name LIKE '%ssn%'
11
   OR c.name LIKE '%credit%'
12
   OR c.name LIKE '%password%'
13
   OR c.name LIKE '%email%'
14
ORDER BY schema_name, table_name, column_name;

2. Choosing the Right Masking Technique#

Different data types require different masking approaches:

• Format-preserving: Maintains data format (e.g., credit card numbers)
• Randomization: Replaces with random values
• Shuffling: Reorders existing values within a column
• Nulling: Replaces with NULL values
• Character masking: Replaces characters with ‘X’ or ’*‘

3. Maintaining Referential Integrity#

When masking related data across tables:

1
-- Example: Consistent masking across related tables
2
WITH MaskMapping AS (
3
    SELECT
4
        customer_id,
5
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS masked_id
6
    FROM customers
7
)
8
UPDATE o
9
SET o.customer_id = m.masked_id
10
FROM orders o
11
INNER JOIN MaskMapping m ON o.customer_id = m.customer_id;

4. Performance Considerations#

• Use indexed columns for masking operations
• Batch large masking operations
• Consider parallel processing for large datasets
• Monitor resource usage during masking

Layered Security Approach#

1
graph TD
2
    A[Application Layer] --> B[Dynamic Data Masking]
3
    B --> C[Database Layer]
4
    C --> D[Encryption at Rest]
5
    D --> E[Storage Layer]
6

7
    F[Access Control] --> B
8
    F --> C
9

10
    G[Audit Logging] --> B
11
    G --> C
12
    G --> D

Key Management Best Practices#

1. Separate key storage from data
2. Implement key rotation policies
3. Use Hardware Security Modules (HSMs) for production
4. Maintain key backup and recovery procedures