Column Level Masking on MySQL

Data is an invaluable asset to any organisation and every data should not be viable to all DB users. I had a requirement from one of our Support client to hide (mask) a few columns to the end user. Those columns contains sensitive information like payment details and mobile numbers.

In this blog post I am going to explain the how data masking features in Maxscale can be benefitted in such use case.

Maxscale Masking

Masking filter was introduced on the Maxscale 2.1 version . Below is a simple scenario. The columns “name” and “mobile” from the table student has to be masked.

[root@labs7.mydbops.com ~]# maxscale --version                                                           MaxScale 2.3.6

#Table Structure 

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `rollno` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `mark` int(11) DEFAULT NULL,
   PRIMARY KEY (`rollno`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1
1 row in set (0.00 sec) 

The masking rules has to be written on a JSON file and path of the JSON file has to be mapped with Maxscale config file (rules). I am sharing the sample JSON file I created for masking the columns

#masking rules
#directory : /etc/maxscale.modules.d
#file : masking_rule.json

{
  "rules": [
       {
           "replace": {
               "database": "student",
               "table": "student",
               "column": "name"
           },
           "with": {
               "value": "X"
           }
       },
       {
           "replace": {
               "database": "student",
               "table": "student",
               "column": "mobile"
           },
           "with": {
               "value": "X"
           }
       }
  ]
}

Maxscale Config File module for masking

[MyMasking]
type=filter
module=masking
warn_type_mismatch=always
large_payload=abort
rules=/etc/maxscale.modules.d/masking_rule.json
prevent_function_usage=false
require_fully_parsed=true

[MyService]
type=service
router=readwritesplit
filters=MyMasking
servers=Mysql

The Maxscale server has to be reloaded to take effect of the Masking Module.

[root@labs7.mydbops.com ~]# mysql -P6603 -h127.0.0.1 -u user -pS3cret -e "select name,mobile from student.student limit 1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+------------+
| name   | mobile     |
+--------+------------+
| XXXXXX | XXXXXXXXXX |
+--------+------------+

Masking the last N characters in a column:

Maxscale also allows an option to mask the N characters ( First/last ) and the rules has to be defined accordingly.

{
  "rules": [
       {
           "replace": {
               "database": "student",
               "table": "student",
               "column": "name",
               "match": "...$"
           },
           "with": {
               "fill": "X"
           }
       },
       {
           "replace": {
               "database": "student",
               "table": "student",
               "column": "mobile",
               "match": "^......."
           },
           "with": {
               "fill": "X"
           }
       }
  ]
}

Reload the config to take effect. Let us validate the same query with the new config

[root@labs7.mydbops.com ~]# mysql -P6603 -h127.0.0.1 -u user -pS3cret -e "select name, mobile from student.student limit 1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+------------+
| name   | mobile     |
+--------+------------+
| RanXXXX | XXXXXXX236|
+--------+------------+

User Specified Data Masking:

 Then I have tried masking data for a specific MySQL user. Below is the config is used

{
 "rules": [
      {
          "replace": {
              "database": "student",
              "table": "student",
              "column": "name"

          },
          "with": {
              "fill": "X"
          },
          "applies_to": ["user1"]
      },
      {
          "replace": {
              "database": "student",
              "table": "student",
              "column": "mobile"
          },
          "with": {
              "fill": "X"
          },
          "applies_to": ["user1"]
      }
 ]
}

Let us validate the settings with MySQL user specified (user1).

[root@labs7.mydbops.com ~]# mysql -P6603 -h127.0.0.1 -u user1 -pUser@123 -e "select name, mobile from student.student"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+------------+
| name   | mobile     |
+--------+------------+
| XXXXXX | XXXXXXXXXX |
+--------+------------+

The config works as expected. Let us try a new MySQL user (user2) which is not white listed.

Maxscale Data Masking
[root@labs7.mydbops.com ~]# mysql -P6603 -h127.0.0.1 -u user2 -pUser@342 -e "select name, mobile from student.student"
mysql: [Warning] Using a password on the command line interface can be insecure.

+--------+------------+
| name   | mobile     |
+--------+------------+
| Ranjith | 7100470036|
+--------+------------+

We can view the actual data for both the columns without any mask.

Hope this blog has given an overview about masking in Maxscale. This can solve the need for masking with MySQL/MariaDB.

Note : ProxySQL can do masking too. But it needs complex query rules based on your query patterns.

Featured Image Photo by Lukas Eggers on Unsplash

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s