For enterprises, data desensitization can be used to protect sensitive data (such as credit cards, social security cards, addresses, etc.) when data is shared or tested. By desensitizing sensitive data, organizations can minimize the risk of data breaches and unauthorized access while still being able to use the data they need for real-world development, testing, and analysis purposes.
There are many methods for data desensitization, such as masking, replacement, shuffling and encryption, etc., which are suitable for different scenarios. This article mainly focuses on “occlusion”, using specific symbols (such as X or ) to obscure sensitive data. This method can desensitize while maintaining the original look and feel of the data.
MySQL enterprise-level data desensitization plug-in
On the MySQL official side, data desensitization is only provided as a plug-in in MySQL Enterprise Edition. The working principle of the MySQL data masking plug-in is that the plug-in contains syntax for data masking, such as mask_inner, mask_outer, mask_ssn, etc. .
Someone with authority in the organization (usually a database administrator) first defines a view (VIEW) that displays the desensitized data. Even if users have limited access to sensitive data, they can view the view as a table. Therefore, to access data, users do not directly query using desensitized syntax, but query from the view.
This method is straightforward, but has certain limitations:
- Rely on fine-grained MySQL user accounts/roles. In fact, most MySQL instances have only a few users. To adopt this plugin, the account settings in MySQL need to be redesigned.
- Different desensitization rules require different views to be defined. This becomes increasingly difficult to manage as the number of underlying tables and variants increases.
- There is no dedicated module to manage desensitization (it’s just a regular MySQL VIEW after all).
Percona data desensitization plug-in
Percona is a free and open source implementation of the aforementioned MySQL plug-in. It also provides a set of functions for desensitizing data.
Again, the way to protect raw data is to use views (VIEW). However, Percona data masking only works with Percona Server for MySQL. If you use the more mainstream MySQL, you’ll need to look elsewhere.
General purpose?General desensitization
The general purpose data masking functions are the following:
.
| Function | Description |
| mask_inner(string, margin1, margin2 [, character]) | Returns a result where only the inner part of a string is masked. A different masking character can be specified. |
| mask_outer(string, margin1, margin2 [, character ]) | Masks the outer part of the string. The inner section is not masked. A different masking character can be specified. |
Examples?
An example of mask_inner:
mysql> SELECT mask_inner('123456789', 1, 2);
Expected output
+ ---------------------------------- +
| mask_inner('123456789', 1, 2) |
+----------------------------------+
|1XXXXXX89 |
+----------------------------------+
An example of mask_outer:
mysql> SELECT mask_outer('123456789', 2, 2);
Expected output
+ ------------------------------------ +
| mask_outer('123456789', 2, 2). |
+ ------------------------------------ +
| XX34567XX |
+ ------------------------------------ +
Special Purpose?Special desensitization
The special purpose data masking functions are as follows:
| Parameter | Description |
| mask_pan(string) | Masks the Primary Account Number (PAN) by replacing the string with an “X” except for the last four characters. The PAN string must be 15 characters or 16 characters in length. |
| mask_pan_relaxed(string ) | Returns the first six numbers and the last four numbers. The rest of the string is replaced by “X”. |
| mask_ssn(string) | Returns a string with only the last four numbers visible. The rest of the string is replaced by “X”. |
Examples?
An example of mask_pan.
mysql> SELECT mask_pan (gen_rnd_pan());
Expected output
+ ------------------------------------ + | mask_pan(gen_rnd_pan()) | + ------------------------------------ + | XXXXXXXXXXX2345 | + ------------------------------------ +
An example of mask_pan_relaxed:
mysql> SELECT mask_pan_relaxed(gen_rnd_pan());
Expected output
+ ------------------------------------------------ + | mask_pan_relaxed(gen_rnd_pan()) | +------------------------------------------------+ | 520754XXXXXX4848 | +------------------------------------------------+
An example of mask_ssn:
mysql> SELECT mask_ssn('555-55-5555');
Expected output
+ ------------------------- +
| mask_ssn('555-55-5555') |
+-------------------------+
| XXX-XX-5555 |
+-------------------------+
Generate random data for specific requirements? Random desensitization
These functions generate random values for specific requirements.
| Parameter | Description |
| gen_range(lower, upper) | Generates a random number based on a selected range and supports negative numbers. |
| gen_rnd_email() | Generates a random email address. The domain is example.com. |
| gen_rnd_pan([size in integer]) | Generates a random primary account number. This function should only be used for test purposes. |
| 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. |
| gen_rnd_ssn() | Generates a random, non-legitimate US Social Security Number in an AAA-BBB-CCCC format. This function should only be used for test purposes. |
Examples?
An example of gen_range(lower, upper):
mysql> SELECT gen_range(10, 100);
Expected output
+ ---------------------------------------------- + | gen_range(10,100) | +-----------------------------------------+ | 56 | +-----------------------------------------+
An example of gen_range(lower, upper) with negative numbers:
mysql> SELECT gen_range(-100,-80);
Expected output
+ ----------------------------------------- + | gen_range(-100,-80) | +-----------------------------------------+ | -91 | +-----------------------------------------+
An example of gen_rnd_email():
mysql> SELECT gen_rnd_email();
Expected output
+ --------------------------------------------- + | gen_rnd_email() | + --------------------------------------------- + | [email protected] | + --------------------------------------------- +
An example of mask_pan(gen_rnd_pan()):
mysql> SELECT mask_pan(gen_rnd_pan());
Expected output
+ --------------------------------------------- + | mask_pan(gen_rnd_pan()) | + ------------------------------------- + | XXXXXXXXXXXX4444 | + ------------------------------------- +
An example of gen_rnd_us_phone():
mysql> SELECT gen_rnd_us_phone();
Expected output
+ ---------------------------------- + | gen_rnd_us_phone() | + ---------------------------------- + | 1-555-635-5709 | + ---------------------------------- +
An example of gen_rnd_ssn():
mysql> SELECT gen_rnd_ssn()
Expected output
+ -------------------------- + | gen_rnd_ssn() | +-----------------------------+ | 995-33-5656 | +-----------------------------+
Use dictionaries to generate random terms?Dictionary desensitization
Use a selected dictionary to generate random terms. The dictionary must be loaded from a file with the following characteristics:
-
Plain text
-
One term per line
-
Must contain at least one entry
Copy the dictionary files to a directory accessible to MySQL. Percona Server for MySQL* 8.0.21-12 enabled using the secure-file-priv option for gen_dictionary_load(). The secure-file-priv option defines the directories where gen_dictionary_load() loads the dictionary files.
Note
Percona Server for MySQL 8.0.34 deprecates the gen_blacklist() function. Use gen_blocklist() instead.
| Parameter | Description | Returns | |
| gen_blacklist(str, dictionary_name, replacement_dictionary_name ) | Replaces a term with a term from a second dictionary. Deprecated in Percona Server for MySQL 8.0.34. | A dictionary term | |
| gen_blocklist(str, dictionary_name, replacement_dictionary_name) | Replaces a term with a term from a second dictionary. | A dictionary term | |
| gen_dictionary(dictionary_name) | Randomizes the dictionary terms | A random term from the selected dictionary. | |
| gen_dictionary_drop(dictionary_name ) | Removes the selected dictionary from the dictionary registry. | Either success or failure | |
| gen_dictionary_load(dictionary path, dictionary name) | Loads a file into the dictionary registry and configures the dictionary name. The name can be used with any function. If the dictionary is edited, you must drop and then reload the dictionary to view the changes. | Loads a file into the dictionary registry and configures the dictionary name. td> | Either success or failure |
Example?
An example of gen_blocklist():
mysql> SELECT gen_blocklist('apple', 'fruit', 'nut');
Expected output
+ ----------------------------------------- +
| gen_blocklist('apple', 'fruit', 'nut') |
+-----------------------------------------+
| walnut |
+-----------------------------------------+
An example of gen_dictionary():
mysql> SELECT gen_dictionary('trees');
Expected output
+ -------------------------------------------------- ---- +
| gen_dictionary('trees') |
+------------------------------------------------- - +
|Norway spruce|
+------------------------------------------------- - +
An example of gen_dictionary_drop():
mysql> SELECT gen_dictionary_drop('mytestdict')
Expected output
+ --------------------------------------------- +
| gen_dictionary_drop('mytestdict') |
+ ------------------------------------- +
| Dictionary removed |
+ ------------------------------------- +
An example of gen_dictionary_load(path, name):
mysql> SELECT gen_dictionary_load('/usr/local/mysql/dict-files/testdict', 'testdict');
Expected output
+ -------------------------------------------------- ----------------------------------+
| gen_dictionary_load('/usr/local/mysql/mysql/dict-files/testdict', 'testdict') |
+------------------------------------------------- ------------------------------- +
| Dictionary load successfully |
+------------------------------------------------- ------------------------------- +
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 77540 people are learning the system