mysql屏蔽
In this article, I am going to explain Data Masking feature in MySQL. The data masking is the MySQL enterprise edition feature. You can download the trial version of MySQL enterprise edition from here. Data Masking is a process of hiding confidential data like credit card information, SSNs, addresses etc. to safeguard sensitive data.
在本文中，我将解释MySQL中的数据屏蔽功能。 数据屏蔽是MySQL企业版功能。 您可以从此处下载MySQL企业版的试用版。 数据屏蔽是一种隐藏机密数据（例如信用卡信息，SSN，地址等）的过程，以保护敏感数据。
安装 (Installation)
When we install MySQL server, the installer creates a plugin library to store various plugins. The default location of the plugin library is as follows:
当我们安装MySQL服务器时，安装程序会创建一个插件库来存储各种插件。 插件库的默认位置如下：
- Windows operating system: C:\Program Files\MySQL\MySQL Server 8.0\lib\plugin\ Windows作业系统： C：\ Program Files \ MySQL \ MySQL Server 8.0 \ lib \ plugin \
- Linux operating system: /usr/lib/mysql/plugin Linux操作系统： / usr / lib / mysql / plugin
You can get the location of the plugin directory by executing the following command in the MySQL command-line client.
您可以通过在MySQL命令行客户端中执行以下命令来获取插件目录的位置。
Once MySQL is installed, the dll file of the data masking plugin will be created in the plugin library. The name of the plugin is “data_masking.dll“.
安装MySQL后，将在插件库中创建数据屏蔽插件的dll文件。 插件的名称为“ data_masking.dll ”。
To install the plugin, we will use the INSTALL PLUGIN and CREATE FUNCTION statement. For more information about plugins and UDFs, refer to MySQL Server Plugins and MySQL User-Defined Functions.
要安装插件，我们将使用INSTALL PLUGIN和CREATE FUNCTION语句。 有关插件和UDF的更多信息，请参考MySQL服务器插件和MySQL用户定义函数 。
The installation process is simple. By executing the following set of queries, the plugin will be installed, and required user-defined functions will be created.
安装过程很简单。 通过执行以下查询集，将安装插件，并创建所需的用户定义功能。
INSTALL PLUGIN data_masking SONAME ‘data_masking.dll’;
CREATE FUNCTION gen_blacklist RETURNS STRING
SONAME ‘data_masking.dll ‘;
CREATE FUNCTION gen_dictionary RETURNS STRING
SONAME ‘data_masking.dll ‘;
CREATE FUNCTION gen_dictionary_drop RETURNS STRING
SONAME ‘data_masking.dll ‘;
CREATE FUNCTION gen_dictionary_load RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION gen_range RETURNS INTEGER
SONAME ‘data_masking.dll’;
CREATE FUNCTION gen_rnd_email RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION gen_rnd_pan RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION gen_rnd_ssn RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION gen_rnd_us_phone RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION mask_inner RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION mask_outer RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION mask_pan RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION mask_pan_relaxed RETURNS STRING
SONAME ‘data_masking.dll’;
CREATE FUNCTION mask_ssn RETURNS STRING
SONAME ‘data_masking.dll’;
安装插件data_masking SONAME'data_masking.dll';
CREATE FUNCTION gen_blacklist返回STRING
SONAME'data_masking.dll';
CREATE FUNCTION gen_dictionary返回STRING
SONAME'data_masking.dll';
CREATE FUNCTION gen_dictionary_drop返回STRING
SONAME'data_masking.dll';
CREATE FUNCTION gen_dictionary_load返回STRING
SONAME'data_masking.dll';
CREATE FUNCTION gen_range返回整数
SONAME'data_masking.dll';
创建函数gen_rnd_email返回STRING
SONAME'data_masking.dll';
CREATE FUNCTION gen_rnd_pan返回STRING
SONAME'data_masking.dll';
创建函数gen_rnd_ssn返回STRING
SONAME'data_masking.dll';
CREATE FUNCTION gen_rnd_us_phone返回STRING
SONAME'data_masking.dll';
创建函数mask_inner返回STRING
SONAME'data_masking.dll';
创建功能mask_outer返回STRING
SONAME'data_masking.dll';
创建功能mask_pan返回STRING
SONAME'data_masking.dll';
创建函数mask_pan_relaxed返回STRING
SONAME'data_masking.dll';
创建函数mask_ssn返回STRING
SONAME'data_masking.dll';
- Note: If you are installing the plugin in the windows server, then you must use 注意：如果要在Windows服务器中安装插件，则必须使用*.dll as the plugin suffix, * .dll作为插件后缀， e.g., data_masking.dll, and if you are installing the plugin in Linux then use 例如data_masking.dll，如果要在Linux中安装插件，则请使用*.so as the plugin suffix, * .so作为插件后缀， e.g., data_masking.so.例如，data_masking.so 。
Once plugins are installed, you can check the activation status by executing following query.
安装插件后，您可以通过执行以下查询来检查激活状态。
select plugin_name,plugin_status, plugin_type,plugin_description from information_schema.plugins where plugin_name=’data_masking’ \G;
从information_schema.plugins中选择plugin_name，plugin_status，plugin_type，plugin_description，其中plugin_name ='data_masking'\ G;
Following is the output.
以下是输出。
演示准备 (Demo preparation)
To demonstrate the various data masking functions, I am going to use the “customer” table of a sample database named “sakila“. If you are installing the MySQL Server for the first time, then you can choose the option to install the sample databases. If you have already installed MySQL, you can set up the database using the MySQL community installer.
为了演示各种数据屏蔽功能，我将使用名为“ sakila ”的示例数据库的“ customer ”表。 如果是第一次安装MySQL Server，则可以选择安装示例数据库的选项。 如果已经安装了MySQL，则可以使用MySQL社区安装程序来设置数据库。
数据屏蔽功能 (Data Masking functions)
Following is the list of the functions that are used for data masking:
以下是用于数据屏蔽的功能列表：
- Mask_inner() Mask_inner（）
- Mask_outer() Mask_outer（）
- Mask_pan() Mask_pan（）
- Maks_pan_relaxed() Maks_pan_relaxed（）
Mask_inner（输入字符串，unmasked_chars_left，unmaske_chars_right，[masking_char]） (Mask_inner( input_string, unmasked_chars_left, unmaske_chars_right, [masking_char]))
This function masks the interior part of the input string and leaves the ending parts untouched and returns the results.
此函数掩盖了输入字符串的内部部分，并使结尾部分保持不变，并返回结果。
Argument:
论据：
- Input_string: The input string or column expression that you want to mask Input_string：您要屏蔽的输入字符串或列表达式
- Unmasked_chars_left: The number of characters that you want to keep unmasked on the left end of the string or column expression. This argument is a non-negative integer Unmasked_chars_left：要在字符串或列表达式的左端保持不屏蔽的字符数。 此参数是一个非负整数
- Unmasked_chars_right: The number of characters that you want to keep unmasked on the right end of the string or column expression. This argument is a non-negative integer Unmasked_chars_right：要在字符串或列表达式的右端保持不屏蔽的字符数。 此参数是一个非负整数
- [masking_char]: The character which is used for masking. This argument is optional, and the default value of this argument is ‘x’ [masking_char]：用于遮罩的字符。 此参数是可选的，此参数的默认值为'x'
Example:
例：
For example, you want to return the last three characters of the input string and mask the remaining characters. The query should be written as follows:
例如，您要返回输入字符串的最后三个字符并屏蔽其余字符。 该查询应编写如下：
mysql> use sakila;
Database changed,br /> mysql> select first_name, mask_inner(first_name,0,3)as ‘Sensitive Data’ from customer limit 15;
mysql>使用sakila;
数据库已更改，mysql>从客户限制15中选择first_name，mask_inner（first_name，0,3）作为“敏感数据”；
Output:
输出：
Mask_outer（输入字符串，unmasked_chars_left，unmaske_chars_right，[masking_char]） (Mask_outer (input_string, unmasked_chars_left, unmaske_chars_right, [masking_char]))
This function masks the right and left end of the input string and leaves the interior parts of string untouched and returns the results.
此函数掩盖了输入字符串的右端和左端，并保持字符串的内部不变，并返回结果。
Argument:
论据：
- Input_string: The input string or column expression that you want to mask Input_string：您要屏蔽的输入字符串或列表达式
- Unmasked_chars_left: The number of characters that you want to mask on the left end of the string or column expression. This argument is a non-negative integer Unmasked_chars_left：要在字符串或列表达式的左端屏蔽的字符数。 此参数是一个非负整数
- Unmasked_chars_right: The number of characters that you want to mask on the right end of the string or column expression. This argument is a non-negative integer Unmasked_chars_right：要在字符串或列表达式的右端屏蔽的字符数。 此参数是一个非负整数
- [masking_char]: The character which is used for masking. This argument is optional, and the default value of this argument is ‘x’ [masking_char]：用于遮罩的字符。 此参数是可选的，此参数的默认值为'x'
Example:
例：
For example, you want to mask the first two and last two character of the input string or column expression and the masking character must be # (hash). The query should be written as following:
例如，您要屏蔽输入字符串或列表达式的前两个字符和后两个字符，并且屏蔽字符必须为＃ （哈希）。 该查询应编写如下：
mysql> select first_name, mask_outer(first_name,2,2,’#’)as ‘Sensitive Data’ from customer limit 15;
mysql>从客户限制15中选择first_name，mask_outer（first_name，2,2，'＃'）作为“敏感数据”；
Output:
输出：
Mask_pan（输入字符串） (Mask_pan (input_string))
This masking function is useful if you are storing credit or debit card numbers in the database. This function returns the last four digit of card number and masks all the remaining numbers using ‘x’
如果要在数据库中存储信用卡号或借记卡号，则此屏蔽功能很有用。 此函数返回卡号的最后四位数，并使用'x'掩盖所有剩余的号码
Argument:
论据：
- Input_string: The input string is the 16 digit credit or debit card number Input_string：输入字符串是16位信用卡或借记卡号
Example:
例：
For example, you want to return the last four digits of the debit card number and mask the remaining characters. The query should be written as follows:
例如，您要返回借记卡号的最后四位数字并掩盖其余字符。 该查询应编写如下：
mysql> SELECT mask_pan(gen_rnd_pan());
mysql>选择mask_pan（gen_rnd_pan（））;
Output
输出量
Mask_pan_relaxed（输入字符串） (Mask_pan_relaxed (input_string))
This function returns first six digits and last four digits of the card number, and it masks all the remaining numbers using ‘x’
此函数返回卡号的前六位数字和后四位数字，并使用'x'掩盖所有剩余的数字
Argument:
论据：
- Input_string: The input string is the 16 digit credit or debit card number Input_string：输入字符串是16位信用卡或借记卡号
Example:
例：
For example, if you want to return the last four digits of the debit card number and mask the remaining characters. The query should be written as follows:
例如，如果要返回借记卡号的最后四位数字并屏蔽其余字符。 该查询应编写如下：
mysql> SELECT mask_pan_relaxed(gen_rnd_pan());
mysql>选择mask_pan_relaxed（gen_rnd_pan（））;
Output
输出量
使用屏蔽数据创建视图 (Create views using masked data)
Instead of querying a table directly and perform masking on output, you can create a view that shows the masked data. In that way, the application directly selects the data from the view.
您可以创建一个显示被屏蔽数据的视图，而不是直接查询表并对输出执行屏蔽。 这样，应用程序可以直接从视图中选择数据。
The view can be defined as the following:
该视图可以定义如下：
mysql> create view CustomerData as select mask_inner(first_name,0,3) as FirstName, email, create_date from customer Limit 10;
Query OK, 0 rows affected (0.01 sec)
mysql>创建视图CustomerData，并选择mask_inner（first_name，0,3）作为FirstName，从客户限制10发送电子邮件，创建日期；
查询正常，受影响的0行（0.01秒）
To select the data from the view, execute the following query:
要从视图中选择数据，请执行以下查询：
mysql> select * from customerdata;
mysql>从客户数据中选择*;
The output is as follows:
输出如下：
摘要 (Summary)
In this article, I have explained how we can use the various functions of the MySQL Enterprise Data Masking feature to protect the sensitive data within the database. You can read more about data masking here, The data masking Wikipedia.
在本文中，我解释了如何使用MySQL Enterprise Data Masking功能的各种功能来保护数据库中的敏感数据。 您可以在此处阅读有关数据屏蔽的更多信息， 数据屏蔽Wikipedia 。
翻译自: https://www.sqlshack.com/enterprise-data-masking-in-mysql/
mysql屏蔽
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
