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.
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:
- Windows operating system: C:\Program Files\MySQL\MySQL Server 8.0\lib\plugin\
- Linux operating system: /usr/lib/mysql/plugin
You can get the location of the plugin directory by executing the following command in the MySQL command-line client.
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“.
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.
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’;
- Note: If you are installing the plugin in the windows server, then you must use *.dll as the plugin suffix, e.g., data_masking.dll, and if you are installing the plugin in Linux then use *.so as the plugin suffix, e.g., data_masking.so.
- Mask_inner()
- Mask_outer()
- Mask_pan()
- Maks_pan_relaxed()
- Input_string: The input string or column expression that you want to mask
- 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_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
- [masking_char]: The character which is used for masking. This argument is optional, and the default value of this argument is ‘x’
- Input_string: The input string or column expression that you want to mask
- 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_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
- [masking_char]: The character which is used for masking. This argument is optional, and the default value of this argument is ‘x’
- Input_string: The input string is the 16 digit credit or debit card number
- Input_string: The input string is the 16 digit credit or debit card number
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022
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;
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.
Data Masking functions
Following is the list of the functions that are used for data masking:
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:
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;
Output:
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:
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;
Output:
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’
Argument:
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());
Output
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’
Argument:
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());
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)
To select the data from the view, execute the following query:
mysql> select * from customerdata;
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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com