How to Mask Data in MariaDB

Galaxy Glossary

How do I mask sensitive columns in MariaDB without duplicating data?

Data masking in MariaDB hides sensitive information on-the-fly with built-in masking functions and privilege checks.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use data masking in MariaDB?

Protect PII, comply with GDPR/PCI, and let analysts query production data without exposing emails or card numbers. Masking functions return obfuscated values at query time, so no copy of raw data is created.

How do MariaDB masking functions work?

MariaDB Server 10.4+ ships the "data_masking" plugin. After INSTALL SONAME 'data_masking', functions such as mask_inner(), mask_outer(), mask_pan(), and digit-specific variants obfuscate strings or numbers.Users with the UNMASK privilege see real data; everyone else sees masked output.

What privileges control masking?

Grant UNMASK to roles that must see clear text: GRANT UNMASK ON *.* TO 'admin'@'%';. Grant SHOW_ROUTINE and EXECUTE if functions are used in stored routines. Revoke UNMASK from analysts to enforce masking.

How to mask email addresses?

Select emails with only the first character visible: SELECT mask_outer(email, '@', 1, 0) AS email_masked FROM Customers;.Analysts see "a*****@domain.com"; admins with UNMASK see the full address.

How to mask credit card numbers?

SELECT mask_pan('4111111111111111'); returns "411111******1111". Replace literal with column name in payment table.

Can I combine masking with views?

Yes. Create a view limiting access: CREATE VIEW v_customers AS SELECT id, name, mask_outer(email,'@',1,0) AS email FROM Customers;. Analysts query the view; admins query the base table.

Best practice: version control your privileges

Store GRANT/REVOKE scripts with schema migrations.Review UNMASK grants during audits to ensure least-privilege access.

When should I avoid masking?

Data masking only protects at query time. If users can dump tables or access backups, masking is bypassed. Combine with encryption, strict backup policies, and audit logging.

Why How to Mask Data in MariaDB is important

How to Mask Data in MariaDB Example Usage


-- Hide all but first letter of customer email and last 4 digits of order total
SELECT c.id,
       mask_outer(c.email, '@', 1, 0)     AS email_masked,
       o.id                                AS order_id,
       mask_outer_digits(o.total_amount, '*', 0, 2) AS total_masked
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
WHERE o.order_date >= '2023-01-01';

How to Mask Data in MariaDB Syntax


-- Enable plugin (once per server)
INSTALL SONAME 'data_masking';

-- General function syntax
mask_inner(value, keep)
mask_outer(value, pad_char, prefix_keep, suffix_keep)
mask_inner_digits(value, keep)
mask_outer_digits(value, pad_char, prefix_keep, suffix_keep)
mask_pan(value)

-- Example parameters using ecommerce tables
SELECT mask_outer(email, '@', 1, 0) AS email_masked
FROM Customers;

SELECT mask_pan(card_number) AS card_masked
FROM Orders;

Common Mistakes

Mistake: Forgetting to INSTALL SONAME 'data_masking'. Without loading the plugin, masking functions are undefined. Fix: run INSTALL once with SUPER or SYSTEM_VARIABLES_ADMIN privileges.
Mistake: Granting UNMASK to analysts. This negates masking entirely. Fix: only grant UNMASK to roles that truly need full data visibility, typically service accounts or auditors.