Data masking is a handy tool to obscure sensitive information. Percona Server for MySQL is a free, fully compatible, enhanced, and open source drop-in replacement for any MySQL database. It provides superior performance, scalability, and instrumentation including data masking. With Percona Server for MySQL, you get the enterprise features without the cost.
So how does data masking work? The server obscures the output so that the sensitive data is not displayed. As you will see in later examples, there are many options to help you with your data masking needs.
Installation
Installation is easy as all you need to do is to load a shared object library from a MySQL client program with INSTALL PLUGIN data_masking SONAME ‘data_masking.so’;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | stoker@testbox:~/Downloads$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 22 Server version: 8.0.30-22 Percona Server (GPL), Release '22', Revision '7e301439b65' Copyright (c) 2009-2022 Percona LLC and/or its affiliates Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> INSTALL PLUGIN data_masking SONAME 'data_masking.so'; Query OK, 0 rows affected (0.04 sec) mysql> |
Basic usage
We will start by looking at the MASK_INNER() and MASK_OUTER() functions. First, we will need some data to work with. The following creates a two-column table and populates it with some data.
1 2 3 4 5 6 7 8 | mysql> create table sensative_data (id int, hushhush bigint); Query OK, 0 rows affected (1.25 sec) mysql> insert into sensative_data values (1,1234567890),(2,0987654321); Query OK, 2 rows affected (0.20 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> |
Note that for the second row the leading zero will be stripped off so that ‘987654321’ is stored not ‘0987654321’. This is why you see CHAR() data types occasionally where you would expect an INT().
The MASK_INNER() will mask, with either the character of your choice or use the default of an octothorpe (#), the inner characters of a column. And MASK_OUTER() masks the data on either end of the column.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> SELECT id, hushhush as 'Original', MASK_INNER(convert(hushhush using binary),2,3) as 'Inner', MASK_OUTER(convert(hushhush using binary),3,3) as 'Outer' FROM sensative_data; +------+------------+------------+------------+ | id | Original | Inner | Outer | +------+------------+------------+------------+ | 1 | 1234567890 | 12XXXXX890 | XXX4567XXX | | 2 | 987654321 | 98XXXX321 | XXX654XXX | +------+------------+------------+------------+ 2 rows in set (0.00 sec) mysql> |
The CONVERT() function is used to ensure that multi-byte character sets do not overwhelm the receiving functions.
SSN numbers
Social Security Numbers (SSN) are not supposed to be used as a general identifier despite them being used that way. There is a special data mask just for SSNs. Again we need some sample data and please note that the mask will complain if it is not working with eleven characters.
1 2 3 4 5 6 7 8 9 | mysql> create table employee (id int, name char(15), ssn char(11)); Query OK, 0 rows affected (1.33 sec) mysql> create table employee (id int, name char(15), ssn char(11)); Query OK, 0 rows affected (1.33 sec) mysql> insert into employee values (1,"Moe",123-12-1234), (2,"Larry",22-222-2222),(3,'Curly',99-999-9999); Query OK, 3 rows affected (0.14 sec) Records: 3 Duplicates: 0 Warnings: 0 |
The MASK_SSN() is like the previous data masks save that it only returns the last four numbers.
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> select id, name, mask_outer(name,1,1,'#') as 'masked', mask_ssn(ssn) as 'Masked SSN' from employee; +------+-------+--------+-------------+ | id | name | masked | Masked SSN | +------+-------+--------+-------------+ | 1 | Moe | #o# | XXX-XX-1234 | | 2 | Larry | #arr# | XXX-XX-2222 | | 3 | Curly | #url# | XXX-XX-9999 | +------+-------+--------+-------------+ 3 rows in set (0.01 sec) |
Credit card numbers
Credit card numbers get their own masking function. The credit card number itself, known as the Primary Account Number or PAN, is routinely masked to return only the last four digits. To see how this works, we alter our employee table for a new column to house the 16-digit credit card, er, PAN, number.
1 2 3 4 5 6 7 | SQL > alter table employee add column cc char(16); Query OK, 0 rows affected (1.6026 sec) Records: 0 Duplicates: 0 Warnings: 0 SQL > update employee set cc = "1234123412341234"; Query OK, 3 rows affected (0.0969 sec) Rows matched: 3 Changed: 3 Warnings: 0 |
And now we can try MASK_CC().
1 2 3 4 5 6 7 8 9 10 | SQL > select mask_pan(cc) from employee; +------------------+ | mask_pan(cc) | +------------------+ | XXXXXXXXXXXX1234 | | XXXXXXXXXXXX1234 | | XXXXXXXXXXXX1234 | +------------------+ 3 rows in set (0.0011 sec) SQL > |
Using a view with a mask
You may be asking how you can mask the sensitive data when they have access to the underlying table. The traditional way to protect the raw data is with a VIEW. The VIEW can have different privileges than the person using it. Best of all is that the VIEW can be treated like a table by the user even if they have restricted access to the data. And you can combine a VIEW with a mask to protect the data. The trick is that you have to set up, use, and maintain the use of the VIEW.
To create a user with no privs, but can see masked data, we need to start with a ‘bare bones’ account. The account ‘nopriv’ was set up with no grants and if we try to access anything in the schema with our sensitive data with this account it will be unsuccessful.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | stoker@testbox:~$ mysql -u nopriv -p test Enter password: ERROR 1044 (42000): Access denied for user 'nopriv'@'%' to database 'test' stoker@testbox:~$ mysql -u nopriv -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 Server version: 8.0.30-22 Percona Server (GPL), Release '22', Revision '7e301439b65' Copyright (c) 2009-2022 Percona LLC and/or its affiliates Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select * from test.employee; ERROR 1142 (42000): SELECT command denied to user 'nopriv'@'localhost' for table 'employee' mysql> |
Now as ‘root’ or some similarly privileged account we can create a VIEW in the schema we are using.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL > create view e1_cc as SELECT id, name, mask_pan(cc) from employee; Query OK, 0 rows affected (0.1664 sec) SQL > select * from e1_cc; +----+-------+------------------+ | id | name | mask_pan(cc) | +----+-------+------------------+ | 1 | Moe | XXXXXXXXXXXX1234 | | 2 | Larry | XXXXXXXXXXXX1234 | | 3 | Curly | XXXXXXXXXXXX1234 | +----+-------+------------------+ 3 rows in set (0.0028 sec) SQL > |
Now we can give the unprivileged access to the VIEW we created. Remember to do this from a privileged account!
1 | SQL> GRANT SELECT ON test.e1_cc TO 'nopriv'@'%'; |
Now we have to log out of our unprivileged account and log in again to get the new privilege in effect. You can now, as the unprivileged user, get masked data from the sensitive employee table. This account cannot see the actual data in the table but it can get what is allowed via the view. This can be very handy.
1 2 3 4 5 6 7 8 9 | SQL > select * from e1_cc; +----+-------+------------------+ | id | name | mask_pan(cc) | +----+-------+------------------+ | 1 | Moe | XXXXXXXXXXXX1234 | | 2 | Larry | XXXXXXXXXXXX1234 | | 3 | Curly | XXXXXXXXXXXX1234 | +----+-------+------------------+ 3 rows in set (0.0028 sec) |
Wrap up
Data masking is a powerful technique to keep sensitive data protected. And Percona provides this feature with the free, open source Percona Server for MySQL software. You will need to purchase a subscription from their vendors if you wish to use data masking with Oracle’s Enterprise Edition or MariaDB’s MaxScale.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
what aren’t you using the component infrastructure to extend MySQL?