Data Masking¶
This feature was implemented in Percona Server for MySQL version Percona Server for MySQL 5.7.32-35.
The Percona Data Masking plugin is a free and Open Source implementation of the MySQL’s data masking plugin. Data Masking provides a set of functions to hide sensitive data with modified content.
Data masking can have either of the characteristics:
-
Generation of random data, such as an email address
-
De-identify data by transforming the data to hide content
Installing the plugin¶
The following command installs the plugin:
mysql> INSTALL PLUGIN data_masking SONAME 'data_masking.so';
Data Masking functions¶
The data masking functions have the following categories:
-
General purpose
-
Special purpose
-
Generating Random Data with Defined characteristics
-
Using Dictionaries to Generate Random Data
General Purpose¶
The general purpose data masking functions are the following:
Parameter |
Description |
Sample |
|---|---|---|
mask_inner(string, margin1, margin2 [, character]) |
Returns a result where only the inner part of a string is masked. An optional masking character can be specified. |
mysql> SELECT mask_inner('123456789', 1, 2);
+-----------------------------------+
| mask_inner("123456789", 1, 2) |
+-----------------------------------+
|1XXXXXX89 |
+-----------------------------------+
|
mask_outer(string, margin1, margin2 [, character]) |
Masks the outer part of the string. The inner section is not masked. |
mysql> SELECT mask_outer('123456789', 2, 2);
+------------------------------------+
| mask_outer("123456789", 2, 2). |
+------------------------------------+
| XX34567XX |
+------------------------------------+
|
Special Purpose¶
The special purpose data masking functions are as follows:
Generating Random Data for Specific Requirements¶
The following functions generate random values for specific requirements:
Parameter |
Description |
Sample |
|---|---|---|
gen_range(lower, upper) |
Generates a random number based on a selected range and supports negative numbers. |
mysql> SELECT gen_range(10, 100) AS result;
+--------------------------------------+
| result |
+--------------------------------------+
| 56 |
+--------------------------------------+
mysql> SELECT gen_range(100,80);
+--------------------------------------+
| gen_range(100,80) |
+--------------------------------------+
| 91 |
+--------------------------------------+
|
gen_rnd_email() |
Generates a random email address. The domain is |
mysql> SELECT gen_rnd_email();
+---------------------------------------+
| gen_rnd_email() |
+---------------------------------------+
| sma.jrts@example.com |
+---------------------------------------+
|
gen_rnd_pan([size in integer]) |
Generates a random primary account number. This function should only be used for test purposes. |
mysql> SELECT mask_pan(gen_rnd_pan());
+-------------------------------------+
| mask_pan(gen_rnd_pan()) |
+-------------------------------------+
| XXXXXXXXXXXX4444 |
+-------------------------------------+
|
gen_rnd_us_phone() |
Generates a random U.S. phone number. The generated number adds the 1 dialing code and is in the 555 area code. The 555 area code is not valid for any U.S. phone number. |
mysql> SELECT gen_rnd_us_phone();
+-------------------------------+
| gen_rnd_us_phone() |
+-------------------------------+
| 1-555-635-5709 |
+-------------------------------+
|
gen_rnd_ssn() |
Generates a random, non-legitimate US Social Security Number in
an |
mysql> SELECT gen_rnd_ssn()
+-----------------------------+
| gen_rnd_ssn() |
+-----------------------------+
| 995-33-5656 |
+-----------------------------+
|
Using Dictionaries to Generate Random Terms¶
Data masking returns a value from a range. To use a predefined file as the range to select a string value, load and use a dictionary. A dictionary supports only strings and is loaded from a file with the following characteristics:
-
Plain text
-
One term per line
-
Must contain at least one entry
An example of a dictionary, which is a list of trees, located in /usr/local/mysql/dict-files/testdict
-
Black Ash
-
White Ash
-
Bigtooth Aspen
-
Quaking Aspen
The following table displays the commands for using dictionaries to generate random terms:
Parameter |
Description |
Sample |
|---|---|---|
gen_range(lower, upper) |
Generates a random number based on a selected range and supports negative numbers. |
mysql> SELECT gen_range(10, 100) AS result;
+--------------------------------------+
| result |
+--------------------------------------+
| 56 |
+--------------------------------------+
mysql> SELECT gen_range(100,80);
+--------------------------------------+
| gen_range(100,80) |
+--------------------------------------+
| 91 |
+--------------------------------------+
|
gen_rnd_email() |
Generates a random email address. The domain is |
mysql> SELECT gen_rnd_email();
+---------------------------------------+
| gen_rnd_email() |
+---------------------------------------+
| sma.jrts@example.com |
+---------------------------------------+
|
gen_rnd_pan([size in integer]) |
Generates a random primary account number. This function should only be used for test purposes. |
mysql> SELECT mask_pan(gen_rnd_pan());
+-------------------------------------+
| mask_pan(gen_rnd_pan()) |
+-------------------------------------+
| XXXXXXXXXXXX4444 |
+-------------------------------------+
|
gen_rnd_us_phone() |
Generates a random U.S. phone number. The generated number adds the 1 dialing code and is in the 555 area code. The 555 area code is not valid for any U.S. phone number. |
mysql> SELECT gen_rnd_us_phone();
+-------------------------------+
| gen_rnd_us_phone() |
+-------------------------------+
| 1-555-635-5709 |
+-------------------------------+
|
gen_rnd_ssn() |
Generates a random, non-legitimate US Social Security Number in
an |
mysql> SELECT gen_rnd_ssn()
+-----------------------------+
| gen_rnd_ssn() |
+-----------------------------+
| 995-33-5656 |
+-----------------------------+
|
Uninstalling the plugin¶
The UNINSTALL PLUGIN statement disables and uninstalls the plugin.