How to Implement Data Masking in MySQL

Data masking is a crucial security technique used to protect sensitive user information, such as personally identifiable information (PII), from unauthorized access while maintaining its usability. This article provides a straightforward guide on how to implement data masking in MySQL, covering standard SQL functions, secure database views, and the native MySQL Enterprise Data Masking plugin to secure your database environments.

Method 1: Masking Data Using SQL Functions

For basic security requirements, you can mask data on the fly using standard MySQL string manipulation functions like CONCAT(), LEFT(), RIGHT(), and REPEAT(). This method is highly compatible and does not require any special plugins.

Masking an Email Address

To mask an email address so that only the first two characters and the domain are visible, use the following query:

SELECT 
    email,
    CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS masked_email
FROM users;

Masking a Credit Card Number

To mask a credit card number, showing only the last four digits:

SELECT 
    credit_card,
    CONCAT('XXXX-XXXX-XXXX-', RIGHT(credit_card, 4)) AS masked_card
FROM users;

Method 2: Using Database Views for Restricted Access

Modifying queries manually can be tedious and prone to errors. A more robust approach is to create database views that automatically present masked data to non-privileged users, while keeping the raw data restricted to administrators.

Step 1: Create the Masked View

CREATE VIEW v_secure_users AS
SELECT 
    id,
    username,
    CONCAT('XXX-XX-', RIGHT(ssn, 4)) AS masked_ssn,
    CONCAT(LEFT(phone, 3), '-XXX-XX', RIGHT(phone, 2)) AS masked_phone
FROM users;

Step 2: Grant Permissions to the View

Restrict direct access to the base table and grant read permissions only to the masked view for standard database users:

-- Revoke access to the raw table
REVOKE SELECT ON mydb.users FROM 'staff_user'@'%';

-- Grant access to the secure view
GRANT SELECT ON mydb.v_secure_users TO 'staff_user'@'%';

Method 3: MySQL Enterprise Data Masking Plugin

If you are using MySQL Enterprise Edition, you can utilize the native data_masking plugin. This plugin provides built-in components to perform advanced, dynamic data masking, including random data generation and specific formatting masks.

Step 1: Install the Plugin

To enable the enterprise masking features, execute the following command:

INSTALL PLUGIN data_masking SONAME 'fns_data_masking.so';

Step 2: Use Built-in Masking Functions

Once installed, you can use specialized functions designed for specific data types: