MySQL数据脱敏（Data masking plugin functions）

淡淡的烟草味﹌ 2024-02-19 08:18 9阅读 0赞

对于企业而言，数据脱敏可以在数据共享或测试时用于保护敏感数据（如信用卡，社保卡，地址等）。通过对敏感数据进行脱敏处理，组织可以最大限度地降低数据泄露和未经授权访问的风险，同时仍能够使用真实的开发，测试和分析目的所需的数据。

![file][]

有很多方法进行数据脱敏，比如遮挡，替换，洗牌和加密，等等，它们适用于不同场景。本文主要聚焦「遮挡」，用特定符号 (比如 X 或) 遮挡敏感数据，这种方法可以在脱敏的同时保持原有数据感观。

### MySQL 企业级数据脱敏插件 ###

MySQL 官方这边，数据脱敏只[作为插件][Link 1]在 MySQL 企业版中提供 。 MySQL 数据脱敏插件的工作原理是插件中包含了用于进行数据脱敏的语法，例如 `mask_inner`, `mask_outer`, `mask_ssn` 等。

![file][file 1]

组织里有权限的人员（通常来说是数据库管理员）会首先定义一个显示脱敏数据的视图 (VIEW)。即使用户对敏感数据的访问受限，他们也可以将该视图视为一张表。因此，要访问数据，用户不是直接使用脱敏语法进行直接查询，而是从视图中查询即可。

这种方法很直接，但也有一定限制：

*  依赖于细粒度的 MySQL 用户账户 / 角色。实际上，大多数 MySQL 实例只有少数几个用户。要采用此插件，需要重新设计 MySQL 中的账户设置。
 *  不同的脱敏规则需要定义不同的视图。随着底层表和变体数量增加，这会越来越难管理。
 *  没有专门的模块来管理脱敏（毕竟只是普通的 MySQL VIEW）。

### Percona 数据脱敏插件 ###

Percona是前述 MySQL 插件的免费开源实现。它也提供了一组用于脱敏数据的函数。

![file][file 2]

同样，保护原始数据的方法是使用视图 (VIEW)。 然而，Percona 数据脱敏仅适用于 Percona Server for MySQL。如果你使用更主流的 MySQL，那就需要另寻他路了。

### General purpose[¶][Link 2]通用脱敏 ###

The general purpose data masking functions are the following:

<table> 
 <tbody> 
  <tr> 
   <td>Function</td> 
   <td>Description</td> 
  </tr> 
  <tr> 
   <td>mask_inner(string, margin1, margin2 [, character])</td> 
   <td>Returns a result where only the inner part of a string is masked. A different masking character can be specified.</td> 
  </tr> 
  <tr> 
   <td>mask_outer(string, margin1, margin2 [, character])</td> 
   <td>Masks the outer part of the string. The inner section is not masked. A different masking character can be specified.</td> 
  </tr> 
 </tbody> 
</table>

#### Examples[¶][Link 3] ####

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[¶][Link 4]特殊脱敏 ###

The special purpose data masking functions are as follows:

<table> 
 <tbody> 
  <tr> 
   <td>Parameter</td> 
   <td>Description</td> 
  </tr> 
  <tr> 
   <td>mask_pan(string)</td> 
   <td>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.</td> 
  </tr> 
  <tr> 
   <td>mask_pan_relaxed(string)</td> 
   <td>Returns the first six numbers and the last four numbers. The rest of the string is replaced by “X”.</td> 
  </tr> 
  <tr> 
   <td>mask_ssn(string)</td> 
   <td>Returns a string with only the last four numbers visible. The rest of the string is replaced by “X”.</td> 
  </tr> 
 </tbody> 
</table>

#### Examples[¶][Link 5] ####

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[¶随机脱敏][Link 6] ###

These functions generate random values for specific requirements.

<table> 
 <tbody> 
  <tr> 
   <td>Parameter</td> 
   <td>Description</td> 
  </tr> 
  <tr> 
   <td>gen_range(lower, upper)</td> 
   <td>Generates a random number based on a selected range and supports negative numbers.</td> 
  </tr> 
  <tr> 
   <td>gen_rnd_email()</td> 
   <td>Generates a random email address. The domain is example.com.</td> 
  </tr> 
  <tr> 
   <td>gen_rnd_pan([size in integer])</td> 
   <td>Generates a random primary account number. This function should only be used for test purposes.</td> 
  </tr> 
  <tr> 
   <td>gen_rnd_us_phone()</td> 
   <td>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.</td> 
  </tr> 
  <tr> 
   <td>gen_rnd_ssn()</td> 
   <td>Generates a random, non-legitimate US Social Security Number in an AAA-BBB-CCCC format. This function should only be used for test purposes.</td> 
  </tr> 
 </tbody> 
</table>

#### Examples[¶][Link 7] ####

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()                       |
    +---------------------------------------+
    | sma.jrts@example.com                  |
    +---------------------------------------+

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[¶字典脱敏][Link 8] ###

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.

<table> 
 <tbody> 
  <tr> 
   <td>Parameter</td> 
   <td>Description</td> 
   <td>Returns</td> 
  </tr> 
  <tr> 
   <td>gen_blacklist(str, dictionary_name, replacement_dictionary_name)</td> 
   <td>Replaces a term with a term from a second dictionary. Deprecated in Percona Server for MySQL 8.0.34.</td> 
   <td>A dictionary term</td> 
  </tr> 
  <tr> 
   <td>gen_blocklist(str, dictionary_name, replacement_dictionary_name)</td> 
   <td>Replaces a term with a term from a second dictionary.</td> 
   <td>A dictionary term</td> 
  </tr> 
  <tr> 
   <td>gen_dictionary(dictionary_name)</td> 
   <td>Randomizes the dictionary terms</td> 
   <td>A random term from the selected dictionary.</td> 
  </tr> 
  <tr> 
   <td>gen_dictionary_drop(dictionary_name)</td> 
   <td>Removes the selected dictionary from the dictionary registry.</td> 
   <td>Either success or failure</td> 
  </tr> 
  <tr> 
   <td>gen_dictionary_load(dictionary path, dictionary name)</td> 
   <td>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.</td> 
   <td>Either success or failure</td> 
  </tr> 
 </tbody> 
</table>

#### Example[¶][Link 9] ####

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                                                  |
    +-------------------------------------------------------------------------------+

[file]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/93d2e4b92657464b8a6780c4c3c722e5.png
[Link 1]: https://www.oschina.net/action/GoToLink?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fdata-masking-plugin-usage.html
[file 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/7dbf5bbf23b441c9b3318958e03545d0.png
[file 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/c3c40ce4c55146d094af2e05aad0e78a.png
[Link 2]: https://docs.percona.com/percona-server/8.0/data-masking-plugin-functions.html#general-purpose
[Link 3]: https://docs.percona.com/percona-server/8.0/data-masking-plugin-functions.html#examples
[Link 4]: https://docs.percona.com/percona-server/8.0/data-masking-plugin-functions.html#special-purpose
[Link 5]: https://docs.percona.com/percona-server/8.0/data-masking-plugin-functions.html#examples_1
[Link 6]: https://docs.percona.com/percona-server/8.0/data-masking-plugin-functions.html#generate-random-data-for-specific-requirements
[Link 7]: https://docs.percona.com/percona-server/8.0/data-masking-plugin-functions.html#examples_2
[Link 8]: https://docs.percona.com/percona-server/8.0/data-masking-plugin-functions.html#use-dictionaries-to-generate-random-terms
[secure-file-priv]: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv
[Link 9]: https://docs.percona.com/percona-server/8.0/data-masking-plugin-functions.html#example