Column Level Masking on MySQL

Aug 5, 2019

Mins to Read

All

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.

 
[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

No items found.

About the Author

Mydbops

Column Level Masking on MySQL

About the Author

Topic covered

Continue Reading

Mastering Null Handling in MongoDB

Mastering Sorting in MongoDB: Arrays, Objects, and Collation

Dumpling – Logical backup tool for TiDB

Stable API for Seamless MongoDB Upgrades

Subscribe Now!