Dynamic Data Masking in Percona Server for MySQL: Protecting Sensitive Data in Production

Modern regulatory frameworks such as GDPR, HIPAA, and PCI-DSS mandate strict controls over who can view sensitive customer information. Personally Identifiable Information (PII), including payment card details, national identification numbers, and contact information must be protected from unauthorized internal and external exposure.

Database administrators often face a difficult trade-off. Your backend application needs direct, uninterrupted access to raw records for payment processing and verification, but support agents, QA engineers, and business analysts working on the exact same database should only ever see obfuscated data.

This is where Dynamic Data Masking (DDM) fits in.

Dynamic Data Masking obscures sensitive field values on the fly at query execution time. The underlying data stored on disk remains unchanged.

Dynamic Masking vs. Static Masking

• Static Data Masking (SDM): Permanently alters data in place or copies transformed datasets through slow, storage-heavy ETL pipelines. This approach works well for populating lower environments, but it cannot serve live production systems where raw values must remain accessible to automated services.
• Dynamic Data Masking (DDM): Applies transformation rules in memory when the query executes. Stored rows remain intact, application code requires minimal alterations, and access control can be handled dynamically based on database privileges and active roles.

How the Percona Data Masking Component Works

Starting with Percona Server for MySQL 8.0.34, Percona replaced the legacy shared-library plugin (data_masking.so) with the modern component subsystem (component_masking_functions).

The component architecture expands native masking capabilities by providing:

• A wider array of formatting algorithms
• Built-in pseudo-random synthetic data generators
• Database-backed substitution dictionaries
• Multibyte character support
• Granular dictionary administration privileges

Installing the Component

To enable masking capabilities, load the component into your server instance:

mysql> INSTALL COMPONENT 'file://component_masking_functions';
Query OK, 0 rows affected (0.01 sec)

Verify that the component loaded successfully:

SELECT * FROM mysql.component;

Output:

+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn                      |
+--------------+--------------------+------------------------------------+
|            1 |                  1 | file://component_masking_functions |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)

Practical Example: Masking Customer Records

Consider an online retail database containing an orders_customers table. It stores customer contact details, tax identifiers, and credit card numbers:

CREATE TABLE orders_customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    tax_id VARCHAR(11),          
    card_number VARCHAR(19),     
    shipping_city VARCHAR(50),
    order_total DECIMAL(10,2)
);

INSERT INTO orders_customers
    (full_name, email, phone, tax_id, card_number, shipping_city, order_total)
VALUES
    ('Priya Raman', 'priya.raman@shopmail.com', '9840012345', '123-45-6789', '4111111111111111', 'Chennai', 4599.00),
    ('Arjun Mehta', 'arjun.mehta@webmail.com', '9820098765', '987-65-4321', '5500005555555559', 'Mumbai', 2150.00);

Raw, Unrestricted Output

Running a standard query returns full plaintext records:

SELECT * FROM orders_customers;

+----+-------------+--------------------------+------------+-------------+------------------+---------------+-------------+
| id | full_name   | email                    | phone      | tax_id      | card_number      | shipping_city | order_total |
+----+-------------+--------------------------+------------+-------------+------------------+---------------+-------------+
|  1 | Priya Raman | priya.raman@shopmail.com | 9840012345 | 123-45-6789 | 4111111111111111 | Chennai       |     4599.00 |
|  2 | Arjun Mehta | arjun.mehta@webmail.com  | 9820098765 | 987-65-4321 | 5500005555555559 | Mumbai        |     2150.00 |
+----+-------------+--------------------------+------------+-------------+------------------+---------------+-------------+

Masked Output via Functions

Applying masking functions directly inside a query keeps sensitive columns private while leaving operational data intact:

SELECT 
    id, 
    full_name, 
    mask_inner(email, 2, 5) AS email,
    mask_pan(card_number) AS card_number, 
    mask_ssn(tax_id) AS tax_id 
FROM orders_customers;

+----+-------------+--------------------------+------------------+-------------+
| id | full_name   | email                    | card_number      | tax_id      |
+----+-------------+--------------------------+------------------+-------------+
|  1 | Priya Raman | prXXXXXXXXXXXXXXXXXl.com | XXXXXXXXXXXX1111 | ***-**-6789 |
|  2 | Arjun Mehta | arXXXXXXXXXXXXXXXXl.com  | XXXXXXXXXXXX5559 | ***-**-4321 |
+----+-------------+--------------------------+------------------+-------------+
2 rows in set (0.00 sec)

The underlying table remains unchanged, but the result set prevents unauthorized data exposure.

Built-In Function Groups

The component provides three primary function categories:

1. General-Purpose Masking Functions

These functions retain structural identifiers (such as the prefix or suffix) while masking the sensitive core:

• mask_ssn(): Masks standard Social Security / Tax ID formats.
• mask_pan(): Obfuscates payment card Primary Account Numbers, leaving the last 4 digits visible.
• mask_pan_relaxed(): A relaxed variant of PAN masking.
• mask_inner(str, v1, v2): Masks the interior portion of a string, leaving v1 leading and v2 trailing characters exposed.
• mask_outer(str, v1, v2): Masks the exterior characters while keeping the middle section visible.
• mask_iban(): Masks International Bank Account Numbers.
• mask_uuid(): Replaces segments of standard UUIDs.

2. Mock Data Generators

These functions generate synthetically valid data on the fly. They are especially useful for populating staging or QA environments where real customer records must not exist:

• gen_rnd_email()
• gen_rnd_ssn()
• gen_rnd_pan()
• gen_rnd_us_phone()
• gen_rnd_uuid()
• gen_rnd_iban()
• gen_rnd_uk_nin()
• gen_rnd_canada_sin()

3. Substitution Dictionaries

When data needs to remain deterministic and realistic—for instance, replacing shipping locations with real-looking placeholder city names for third-party analytics—Percona supports dictionary substitution:

• gen_dictionary(): Selects a replacement term from a registered dictionary table.
• gen_blocklist(): Prevents forbidden terms from appearing by swapping them with defined alternatives.

Limitations of Query-Level Masking (And How to Fix It)

Masking functions only modify data when a user explicitly runs them in a SELECT statement. They do not enforce access boundaries on the underlying table.

If a support engineer has SELECT privileges directly on orders_customers, nothing prevents them from omitting the function:

-- The masking rule is bypassed if table permissions are too broad:
SELECT card_number FROM orders_customers;

A masking function is a transformation tool, not an access barrier. To build a secure system, you must combine masking functions with database views and MySQL roles.

Combining Masking with Role-Based Views

To enforce masking reliably, revoke direct table permissions from end users and grant them access only through a secure view:

CREATE OR REPLACE VIEW customer_orders AS
SELECT
    id,
    full_name,

    -- Full email for finance, partially masked for support team 
    CASE
        WHEN CURRENT_ROLE() LIKE '%`role_finance`%' THEN email
        ELSE mask_inner(email, 2, 5)
    END AS email,

    -- Full card number for finance, masked for support team 
    CASE
        WHEN CURRENT_ROLE() LIKE '%`role_finance`%' THEN card_number
        ELSE mask_pan(card_number)
    END AS card_number,

    -- Full tax ID for finance, masked for support team 
    CASE
        WHEN CURRENT_ROLE() LIKE '%`role_finance`%' THEN tax_id
        ELSE mask_ssn(tax_id)
    END AS tax_id,

    shipping_city,
    order_total
FROM orders_customers;

Querying as role_finance:

SET ROLE role_finance;
SELECT * FROM customer_orders;

+----+-------------+--------------------------+------------------+-------------+---------------+-------------+
| id | full_name   | email                    | card_number      | tax_id      | shipping_city | order_total |
+----+-------------+--------------------------+------------------+-------------+---------------+-------------+
|  1 | Priya Raman | priya.raman@shopmail.com | 4111111111111111 | 123-45-6789 | Chennai       |     4599.00 |
|  2 | Arjun Mehta | arjun.mehta@webmail.com  | 5500005555555559 | 987-65-4321 | Mumbai        |     2150.00 |
+----+-------------+--------------------------+------------------+-------------+---------------+-------------+

Querying as an unprivileged or support user:

SET ROLE role_support;
SELECT * FROM customer_orders;

+----+-------------+-----------------------------+------------------+-------------+---------------+-------------+
| id | full_name   | email                       | card_number      | tax_id      | shipping_city | order_total |
+----+-------------+-----------------------------+------------------+-------------+---------------+-------------+
|  1 | Priya Raman | prXXXXXX.raman@shopmail.com | XXXXXXXXXXXX1111 | ***-**-6789 | Chennai       |     4599.00 |
|  2 | Arjun Mehta | arXXXXXX.mehta@webmail.com  | XXXXXXXXXXXX5559 | ***-**-4321 | Mumbai        |     2150.00 |
+----+-------------+-----------------------------+------------------+-------------+---------------+-------------+

Because users only hold privileges on the view, they cannot query the underlying table to expose plaintext values.

Performance Enhancements in Percona Server for MySQL 8.4.4

Prior to MySQL 8.4.4, dictionary-based functions such as gen_dictionary() and gen_blocklist() read the dictionary storage table (mysql.masking_dictionaries) on disk or through the InnoDB buffer pool on every processed row. On high-volume tables, this caused heavy CPU saturation and I/O bottlenecks.

Percona Server for MySQL 8.4.4 addresses this with an internal In-Memory Dictionary Term Cache.

1. In-Memory Dictionary Term Cache

Dictionaries are now cached directly in memory, converting disk lookups into fast RAM reads.

2. Manual Cache Reloading

Because terms are cached in memory, direct inserts or updates to mysql.masking_dictionaries will not take effect immediately. Percona introduced the masking_dictionaries_flush() function to force a cache reload:

INSERT IGNORE INTO mysql.masking_dictionaries (Dictionary, Term)
VALUES 
    ('cities_dict', 'Metropolis'), 
    ('cities_dict', 'Gotham'),
    ('cities_dict', 'Springfield');

-- Flush the dictionary cache into memory
SELECT masking_dictionaries_flush();

Executing masking_dictionaries_flush() requires the MASKING_DICTIONARIES_ADMIN privilege.

3. Automatic Replication Synchronization

In master-replica setups, DDL and DML operations on mysql.masking_dictionaries replicate to read replicas, but the replica's in-memory cache previously remained stale.

Percona Server 8.4.4 added the system variable:

component_masking_functions.dictionaries_flush_interval_seconds

Setting this interval ensures replicas automatically check and refresh their in-memory dictionary terms on a scheduled cycle without requiring manual administrative intervention.

Example: Using Cached Dictionaries

SELECT 
    customer_name, 
    real_city AS raw_data, 
    gen_dictionary('cities_dict') AS masked_city 
FROM customers;

+---------------+-------------+-------------+
| customer_name | raw_data    | masked_city |
+---------------+-------------+-------------+
| Alice Smith   | New York    | Springfield |
| Bob Jones     | Los Angeles | Metropolis  |
| Charlie Brown | Chicago     | Metropolis  |
+---------------+-------------+-------------+
3 rows in set (0.00 sec)

Summary

Dynamic Data Masking in Percona Server for MySQL provides an efficient, non-destructive way to protect sensitive data in production.

Keep these core principles in mind when implementing DDM:

1. Never rely on masking functions alone: Functions alter queries, but they do not secure tables. Always pair them with views and MySQL 8.0 roles.
2. Use synthetic generators for non-production environments: Avoid exporting real customer records to development and staging databases.
3. Take advantage of Percona 8.4.4 caching: For dictionary substitution on large datasets, utilize the in-memory term cache and configure automatic replica flushes to avoid replication drift.

Need Help Securing Your Production MySQL Databases?

Implementing robust security controls without disrupting production workloads requires careful planning across user permissions, schema architecture, and performance tuning.

The database architects at Mydbops help organizations build hardened, enterprise-ready database environments across MySQL, Percona Server, and MariaDB.